The action prepares the execution of the SQL command SELECT.
|handleIdent_Int||in||Identifier - the unique number (handle) of a connection.|
|retCodeIdent_Int||out||Return code identifier.|
|selectStringExpr||in||Expression of Text type.|
|_locVar1, _locVar2, ...||in||List of local variables.|
|_locVarRowIdent||in||Reference to a row of local variable of Record type.|
|_locVarRecordIdent||in||Identifier of local variable of Record type.|
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 (specified by a value of the identifier handleIdent_Int). Success of the action is indicated by a value of retCodeIdent_Int.
The second phase is required if the keyword BINDOUT was used. This keyword means that the expression selectStringExpr used parametrization and it is necessary to use the command SQL_BINDIN to specify the input parameters of the expression selectStringExpr before actual execution of the SQL statement.
The last phase is the sequential reading of the rows, prepared by the command SELECT, using the action SQL_FETCH.
Values read are saved into local variables listed after the keyword BIND or BINDOUT of the action SQL_PREPARE. There are three possiblevariants:
- List of non-structured local variables.
Reading is executed row by row into local variables, which are listed after the key word BIND or BINDOUT.
- Reference to one row of local variable of Record type.
Reading is executed one row into one row of the local variable. The structure of data that are read must be the same as the structure of the local variable.
- Reference to local variable of Record type.
Reading is executed either one or more rows of the local variable. Its size may be changed as necessary before the result. The structure of data that are read must be the same as the structure of the local variable.
One reading (gained by the action SQL_CONNECT) may be active just for one
handle. The action SQL_PREPARE will cancel the validity of the previous
action. The action SQL_FREE allows to
finish a reading.
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 SQL_FETCH.
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.