Omnis SQL
4
Select Tables and Cursors
A select table is a table of results that belongs to a session. When you send a SQL SELECT
statement to the server and there is no error, the results of the SELECT become available to
Omnis as the select table for the current session. The select table can be empty; in this case,
the flag is true after the execution of the select and is only set to false when you attempt to
fetch the first row after the end of the select table.
You can map the data in the select table into Omnis data in three ways:
– Declare cursor and Fetch next row
– Build list from select table
– Retrieve rows to file
When you fetch data from the server, Omnis converts data types between the native SQL
server and the Omnis data type if possible, including numeric precision. If there is a total
mismatch between Omnis field types and SQL column types, you can lose information or
get a SQL error.
Declare cursor and Fetch next row
The Declare cursor and Fetch next row commands let you map each row in the select table
into the CRB on a row-by-row basis.
Declare cursor defines a SQL cursor, a named pointer to a row in the select table, and
associates a SQL select statement with the cursor. Open cursor opens the cursor, parses the
SQL statement, binds input data, and executes the SQL statement. Set current cursor
switches Omnis to use the named cursor.
When you execute a SQL SELECT statement, the current cursor points to the first row in
the resulting select table. When you Fetch next row, you fetch the row pointed to by the
current cursor and move the cursor to the next row. You can have more than one cursor
active at a time, letting you select rows based on values retrieved from a completely
separate select table. You use the Set current cursor command to use a particular cursor as
the current cursor with the Fetch commands.
Unless you are using multiple cursors, you don't need to explicitly open a cursor; Omnis
automatically opens one for you.
The Fetch next row command loads the column values for a single row of the select table
into the Omnis CRB fields.
If the list of fields does not match the columns in the select table, Omnis tries to map the
data as best it can. If there are more columns than fields, then Omnis doesn't copy the extra
column values into Omnis variables. On the other hand, if there are more fields than
columns, then Omnis leaves the extra field values unchanged.
The usual retrieval process is to fetch the rows in a loop, one at a time, until there are no
more rows in the select table. To do this, you use Fetch next row which fetches the row