RSS

ODBC / SQL access

Writing data to a table

The RTES command WSQL provides a way to access databases using SQL via ODBC.
The format of the command is WSQL name
name is the name of a RTES register that defines a file array. The file identified by the value of the register is interpreted by the report generator and passed on to ODBC.

The [ODBC]section in RTES32.CNF defines:

• The database name ("DSN") to use
• The username and the password that will authorize the access

If the databse is located on a server that is only accessible using it's IP address, then 2 more parameters are required:

• The IP address of the server
• The name of the server script that will handle the queries

The file referenced by the WSQL command may contain any SQL statement that accesses the predefined database to write data to a table, delete data from a table or modify data in a table.

Any data returned by an WSQL query will be ignored. Use the RSQL command to retrieve data from a table.

You may execute the WSQL command from an RTES32 command line, as an X task statement or as an EXEC function, or also from a batch file you run with a GET command.

Example:

R0123 LOG     1.00    FAULT.FMT
R0124         0.00    DEFECT.FMT
R0125         0.00    DOWNTIME.FMT

A task issues the command: WSQL LOG

Since LOG=1, the selected file is DEFECT.FMT

DEFECT.FMT contains:

INSERT INTO Defects(Machine,Defect Type,Timestamp) VALUES (@@MC# NUMBER@,@@TYPE LIST 0 TYPES 0@,@@TOD REAL 6 2@)

Note that "INSERT INTO" is a standard SQL keyword, "Defects" is the name of a table you defined in the database specified in the [ODBC] section of RTES32.INI, "Machine", "Defect Type" and "Timestamp" are fields that you defined in that table, using the appropriate data types.

MC#, TYPE, TYPES and TOD are RTES register names.

Reading data from a table

The RTES command RSQL provides a way to retrieve data from databases using SQL via ODBC.
The format of the command is RSQL name destination
name is the name of a RTES register that defines a file array. The file identified by the value of the register is interpreted by the report generator and passed on to ODBC.

destination is the name of the first of a series of consecutive RTES registers that will receive the data.

The [ODBC]section in RTES32.CNF defines:

• The database name ("DSN") to use
• The username and the password that will authorize the access

If the databse is located on a server that is only accessible using it's IP address, then 2 more parameters are required:

• The IP address of the server
• The name of the server script that will handle the queries

The file referenced by the name paramter of the RSQL command may contain any SQL statement that accesses the predefined database to read data from table, but will also operate with statements that write data to a table, delete data from a table or modify data in a table.

In the event that the SQL satement returns several rows, only the first row is read.

You may execute the RSQL command from an RTES32 command line, as an X task statement or as an EXEC function, or also from a batch file you run with a GET command.

Example:

R0123 LAST    1.00    LAST_FAULT.FMT
R0124         0.00    LAST_DEFECT.FMT
R0125         0.00    LAST_DOWNTIME.FMT

R0200 MC#     0.00
R0201 TYPE    0.00
R0202 TOD     0.00

A task issues the command: RSQL LAST MC#

Since LOG=1, the selected file is LAST_DEFECT.FMT

LAST_DEFECT.FMT contains:

SELECT Machine,Defect Type,Timestamp FROM Defects ORDER BY TOD DESC

Note that "SELECT .... FROM", "ORDER BY", "DESC" are standard SQL keyword, "Defects" is the name of a table you defined in the database specified in the [ODBC] section of RTES32.INI, "Machine", "Defect Type" and "Timestamp" are fields that you defined in that table, using the appropriate data types.

Example of how WSQL may be used to record live data into a database.

In the case of a remote database, it is assumed that the server is IIS (Internet Information Services) Version 6 or equivalent, that it accepts sql queries on port 80 (http) and that it has a compatible query handler.

Note that the format of the tables in the database should follow certain guidelines if you intend to use the trend display facilities we supply.

Contact FAI for assisstance.

Logging alarm and change of state events.

The LOGS parameter defines a table in the database that will be used by RTES32 to log the alarms and change of state events. This table must exist and must contain the following fields:

• indx - This must an auto-increment number defined as primary key
• name - This must be a text
• dtstamp - This must be a date and time stamp
• message - This must be a text
• status - This must be a text

Here is an example of how to create a table named "eventlogs" in MySql:
In this case, RTES32.INI will contain in the [ODBC] section: LOGS=eventlogs CREATE TABLE `eventlogs` ( `indx` BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY , `name` VARCHAR(15) NULL , `dtstamp` DATETIME NOT NULL , `message` VARCHAR(45) NULL , `status` VARCHAR(15) NULL)