Database Query Commands

You can use the following database query command set to perform SQL queries on a database, via an ODBC driver.  All the commands in this example use connection 1, but you can have up to 10 connections open at one time.

Sample template for Database Query commands

Cell

Formula

Explanation

A1

If ( Q_Connect 1,“Shelf DB; UID=scott; PWd=tiger”, ” N”, ” Y”,) Goto C1, Goto B1

Get database connection.

If it succeeds, go to C1. If you can’ t connect, go to B1.

B1

Message “Connection failed.”: Exit

Connection failed – display message and exit the template.

C1

If ( Q_Start 1, “SELECT * FROM Shelf_Life”), Goto E1, Goto D1

Ask for data for connection 1. If you get data, go to E1. If the select query doesn’ t return any data, go to D1.

D1

Message “ Shelf_Life table is empty.” : Exit

Select query did not return data – display message and exit the template.

E1

Message Q_Col_S 1, 0

Display the data in column 0 (first column).

F1

If Q_Next 1, Goto E1, Goto G1

Get the next record for connection 1 from the select query in C1. If you get data, go to E1. If the next record doesn’ t return any data, go to G1.

G1

Q_Stop 1: Q_Disconnect 1: Exit

You have reached the end of the select query. End query and disconnect from connection 1.

Sql_statement_string ( Q_START command):

  1. “*” selects all fields in the table specified. To limit the fields returned, specify each field explicitly, e.g. “SELECT Data1, Data2, Data3 FROM Shelf_Life”.

  2. The query may be filtered using the WHERE clause. For example, “SELECT Data1 FROM Shelf_Life WHERE Data1 <> 0.0” (retrieve all Data1 values that are not zero).

  3. The PREP_Q command can be used to prepare strings to be inserted into tables.

If the data must be deleted after the table is read to ensure that it is not read in again, use the formula Q_START 1, “DELETE From Shelf_Life”. This will delete all rows from the Shelf_Life table. For more advanced applications, you may want to UPDATE a field in the table, signifying that the data has been read, or INSERT INTO a table and then read the data later.

Note: In versions prior to version 7.2 it wasn’ t necessary to designate a specific connection for database query commands to use. In version 7.2, you must now designate the connection number. Existing templates will continue to function properly and will assume a connection number of 1. If you edit an existing template that uses database query commands, you will need to assign connection numbers to those commands.