Reading a database by the command SELECT is implemented in two or three phases.
The first (preparatory) phase is executed by the action SQL_PREPARE. The command
SELECT, defined by a value of the expression selectStringExpr, is prepared (and if the keyword BINDOUT
is not used, then also executed) in the database.
If the keyword BINDOUT was used, it means that the SQL SELECT command was parameterized,
and the second phase is needed. The command SQL_BINDIN must be used to specify the values of the input parameters
and execute the SQL statement.
The last phase is the sequential reading of the rows, prepared by the
command SELECT, using the action SQL_FETCH.
Note: By using parameterization it is possible to make the work of SQL database easier, because
the preparation (compilation) of parameterized SQL query will be performed only once (by the action SQL_PREPARE).
Consequently the values of parameters must be specified by the action SQL_BINDIN (which will also execute the SQL command)
and then the action SQL_FETCH may be called once or more times to obtain the results.
Then it is possible to set new values of the parameters and re-execute the SQL command by repeating the action
SQL_BINDIN and obtain the new results by one or more calls of the action
By proper setting of the database parameters (e.g. Oracle: session_cached_cursors) it is possible to ensure recycling of cursors
(compiled statements) between the calls of SQL_PREPARE.
INT _handle ; handle to database
INT _retCode ; return code
TEXT _name ; product name
TEXT _type ; product type
; parameterized SQL command
TEXT _sql = "SELECT Name, Type FROM Products WHERE ID>= #PAR# AND ID<= #PAR#"
SQL_CONNECT MyDatabase, _handle, _retCode
SQL_PREPARE _handle, _retCode, _sql BINDOUT _name, _type
SQL_BINDIN _handle, _retCode, 1, 100 ; read all products between 1 and 100
SQL_FETCH _handle, _retCode
EXIT_LOOP _retCode # _ERR_NO_ERROR
; data processing goes here