DBGETRESULTS - Get results from a SQL database query

Syntax:	DBGETRESULTS
Arguments:	None
Options:	None

This script command is used to obtain the results of a previous SQL statement executed by the DBQUERY command. Each time the DBGETRESULTS command is called, another row of the resultset is returned. This command may be called repeatedly in a loop. When using the built-in SQLite database engine a maximum of 1000 rows are available. This row limit does not apply to ODBC connections made using a DSN. The DBREWIND command is available to restart from the first row.

This command works by creating script variables of the form %db_ColumnName and %db[ColumnName] with ColumnName corresponding to the name of each column in the resultset. These variables are assigned the column values of the current row. The total number of column variables created is stored in a variable named %dbqueryvariables. These variables can change from one call of DBGETRESULTS to the next when using batched queries or certain stored procedures that return multiple result sets so each call to DBGETRESULTS clears any previously returned variables.

For example, if a table in a given SQL database is created with two columns, one named “animal” and the other “color”, then two %db_ColumnName variables will be created by the DBGETRESULTS command as shown below:

The following example calls the DBGETRESULTS command in a loop to process a multi-row resultset and exits the loop when the error value $ERROR_DB_ALL_RESULTS_RTND is returned:

When using a SQLite database, the DBGETRESULTS command may not recognize the results of a query and/or may be unable to create corresponding %db_ColumnName variables. In such a case, DBGETRESULTS returns the error value $ERROR_DB_RAW_QUERY_RESULTS and the raw results string is assigned to the %dbqueryrawresult script variable. If you are expecting a differently formatted query result then you can parse this string programmatically or, otherwise, use this variable for debugging purposes. This does not apply to ODBC data sources.

Calling the DBCLOSE or DBGETRESULTS command will remove the previously set %db_ColumnName and %db[ColumnName] variables created by DBGETRESULTS. The DISPLAY command used without any arguments will show a list of all currently declared script variables, including the %db_ColumnName variables created by DBGETRESULTS. Like with other user-created variables, using the SET command with only name of a %db_ColumnName variable and no values will cause that variable to be removed.