ARAP can connect to database servers in order to access, edit, and add data. The capability was developed for and tested with MySQL-type servers, but can in principal connect to many other servers (PostgreSQL, Microsoft, Oracle, etc.) - contact Nova Astronomics for advice if you need to do this.

Usually MySQL database servers run on Linux or Unix-type servers, however it is possible to install such a server on Windows too - the XAMPP software is a good choice.

Initial Setup

The database configuration file "FDConnectionDefs.ini", located in the ARAP installation directory, needs to be created. It is in Windows "ini" configuration file format. See example:

[aro]

Server=192.168.1.201

User_Name=arouser

Password=<password>

Database=aro

DriverID=MySQL

The [aro] above is the name ARAP uses for the database. The next four parameters below need to indicate how to connect to the database. The file can contain one or more databases.

All database-related commands are accessed using the single ARAP command 'database' with one or more parameters added. All commands set the boolean variable DATABASEERROR to "true" if an error occurs or "false" if no error occurs.

Syntax: database command <option1> <option2> .. <option6>

Most database commands update the following variables:

  • DATABASEERROR - "true" if a major database error occurred.
  • DATABASESOFTERROR
    • "true" if: find command was not successful
    • "true" if prior command is now at the first record
    • "true" if next command is now at the last record
    • "true" if first or last commands failed
  • DATABASERECNO - the current table record pointer
  • DATABASERECORDS - the number of records in the table (or filtered table)

Before using any other database commands, database open must be used.

The available database commands are below:

open

Syntax: database open databasename

This command connects to the database server and opens the database "databasename" as defined in the "FDConnectionDefs.ini" file. If this operation is successful, the variable DATABASECONNECTED is set to "true". All commands in this section must be preceded by this command.

close

Syntax: database close

This command terminates the connection with the currently open database and sets the variable DATABASECONNECTED to "false".

selecttable

Syntax: database selecttable tableno

This command selects the active table number (1-10) to "tableno". This allows up to ten database tables to be open at the same time, each maintaining their own record pointer and filter parameter. Once set all other commands below operate on the selected table.

opentable

Syntax: database opentable tablename

This command opens the database table named "tablename".

closetable

Syntax: database closetable

This command closes the database table.

index

Syntax: database index field

This command sets which field or fields ("field") form the sort index. Some examples for "field" are below:

fieldname                - sort on table field "fieldname" (default sort order)

fieldname:D                - sort on table field "fieldname" descending

fieldname:A                - sort on table field "fieldname"  ascending

fieldname:DN                - sort on table field "fieldname" descending (case-insensitive)

fieldname1;fieldname2        - sort on table field "fieldname1" then on "fieldname2"

find

Syntax: database find fieldname searchterm

This command searches for the first record where the field "fieldname" equals "searchterm". If found the record pointer is set to the record found. If not found, the variable DATABASESOFTERROR is "true".

filter

Syntax: database filter <filterexpression>

This command filters the table using "filterexpression". Filtering a table causes records that don't match the filter expression to be disabled. This is an advanced feature - see this webpage for help on how "filterexpression" should be set. If "filterexpression" is left out or set to NULL, the filter is removed.

getfield

Syntax: database getfield fieldname

This command sets the variable DATABASEFIELD to the value of the field "fieldname" at the current record.

setfield

Syntax: database setfield fieldname fieldvalue

This command sets field "fieldname" at the current record to "fieldvalue". It is not written to the database until the post command is executed.

getinfo

Syntax: database getinfo

This command updates the variables DATABASERECNO and DATABASERECORDS.

setrecno

Syntax: database setrecno recno

This command sets the record pointer to "recno".

delete

Syntax: database delete

This command sets deletes the current record.

append

Syntax: database append

This command adds a new record and points to that record. The field values will be the default values.

refresh

Syntax: database refresh

This command refreshes the database - this handles the situation that can cause stale data if another program is writing to the database.

post

Syntax: database post

This command write all cached table data to the database.

first

Syntax: database first

This command sets the record pointer to the first record.

last

Syntax: database last

This command sets the record pointer to the last record.

prior

Syntax: database prior

This command sets the record pointer to the next lower record number. It will not step past the first record.

next

Syntax: database next

This command sets the record pointer to the next higher record number. It will not step past the last record.