Parameterization of SQL commands and expressions means symbolic specification of values in SQL commands and expressions and
later filling-in the parameters by actual values.
An example of parameterized expression: " Id= #PAR# AND Name LIKE #PAR# "
An example of parameterized command: "SELECT Name, Surname FROM Persons WHERE Id= #PAR# AND Name LIKE #PAR# "
There are 3 ways of parameter specification usable by selected actions executed by the process
D2000 DBManager:
Parameterization can be used in SQL expression by a selected set of database-oriented actions, specifically the actions:
If the SQL expression is parameterized, the action must use the keyword BINDIN followed by one of these choices:
The values of parameters will be filled-in according to the values specified after the keyword BINDIN.
Parameterization is supported also by the action SQL_PREPARE, but in this case the list of
parameters' values is not part of the action.
Using the keyword BINDOUT (instead of BIND) specifies, that the SQL command is parameterized and consequently the values of the
parameters must be filled-in by calling the action SQL_BINDIN. The advantage is that to obtain results
corresponding to different values of the parameters the action SQL_PREPARE doesn't have to be called again
- it is sufficient to repeat the action SQL_BINDIN to fill-in new values of the parameters and consequently
the action SQL_FETCH can be used to obtain results.
The meaning of parameterization is to speed up and make easier the work of SQL engine. The processing of SQL command includes its parsing and
compilation. If the same SQL command is reused, the SQL engine can recycle already parsed and compiled command. E.g. by the means of parameterization
the commands
"SELECT Name FROM Person WHERE Id=1"
"SELECT Name FROM Person WHERE Id=5"
"SELECT Name FROM Person WHERE Id=100"
can be replaced by a single command
"SELECT Name FROM Person WHERE Id= #PAR#"
and instead of three SQL commands the database engine will parse and compile only one SQL command (and during second and further executions it
can be recycled).
If the parameterization is used in the action SQL_PREPARE and parameters' values are set by multiple calls to
the action SQL_BINDIN, the parsing and compilation of SQL command is guaranteed to be performed only once
(during the call to SQL_PREPARE).
If the parameterization is used in the actions DB(S)_DELETE, DB(S)_READ
or DB(S)_UPDATE, it is recommended to modify the database parameters (e.g. Oracle: session_cached_cursors),
so that the SQL engine caches sufficient number of compiled commands (cursors), so that they stay in the cursor cache between calls to
DB(S)_* actions.