Date: Fri, 29 Mar 2024 01:52:02 +0100 (CET) Message-ID: <1124939228.111368.1711673522247@srvdoc.doc.ipesoft.com> Subject: Exported From Confluence MIME-Version: 1.0 Content-Type: multipart/related; boundary="----=_Part_111367_337337624.1711673522247" ------=_Part_111367_337337624.1711673522247 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable Content-Location: file:///C:/exported.html
Parameterization of SQL commands and expressions means symbolic specific=
ation of values in SQL commands and expressions and later filling-in the pa=
rameters by actual values.
An example of parameterized expression: " Id=3D #PAR# AND Name LIKE #PA=
R# "
An example of parameterized command: "SELECT Name, Surname FROM Persons=
WHERE Id=3D #PAR# AND Name LIKE #PAR# "
There are 3 ways of parameter specification usable by selected actions exe=
cuted by the process D2000 =
DBManager:
Parameterization can be used in SQL expression by a selected set of data= base-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 speci=
fied 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 (inste=
ad of BIND) specifies, that the SQL command is parameterized and con=
sequently the values of the parameters must be filled-in by calling the act=
ion SQL_BINDIN. The advantag=
e is that to obtain results corresponding to different values of the parame=
ters the action SQL_PREPARE=
doesn't have to be called again - it is sufficient to repeat the action SQL_BINDIN to fill-in new valu=
es 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 compi=
lation. If the same SQL command is reused, the SQL engine can recycle alrea=
dy parsed and compiled command. E.g. by the means of parameterization the c=
ommands
"SELECT Name FROM Person WHERE Id=3D1"
"SELECT Nam=
e FROM Person WHERE Id=3D5"
"SELECT Name FROM Person WHERE Id=3D1=
00"
can be replaced by a single command
"SELECT Name FROM Person WHERE I=
d=3D #PAR#"
and instead of three SQL commands the database engine will parse and compi=
le 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=
a>, the parsing and compilation of SQL command is guaranteed to be performe=
d only once (during the call to SQL_PREPARE).
If the parameterization is used in the actions DB(S)_DELETE, D=
B(S)_READ or DB(S)_UPDATE=
, it is recommended to modify the database parameters (e.g. Oracle: sess=
ion_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.