📄 otl3_bind_variables.htm
字号:
<!DOCTYPE html PUBLIC "-//w3c//dtd html 4.0 transitional//en">
<html>
<head>
<meta http-equiv="Content-Type"
content="text/html; charset=iso-8859-1">
<meta name="Author" content="Sergei Kuchin">
<meta name="GENERATOR"
content="Mozilla/4.77 [en] (Win95; U) [Netscape]">
<meta name="KeyWords"
content="OTL, Oracle, ODBC, DB2, CLI, database API, C++, Template Library">
<title>OTL 4.0, Declaration of bind variables</title>
</head>
<body>
<center>
<h1>OTL 4.0, Declaration of bind variables</h1>
</center>
<h1>
<a name="otl_bind_variables"></a>Declaration of bind variables</h1>
This section explains in detail how to declare bind variables in the
<a href="otl3_stream_class.htm">otl_stream</a>.
<p>A SQL statement, PL/SQL block or a stored procedure call may have
placeholders
which are usually connected with bind variables in the program. OTL 4.0
has a small parser that parses the SQL statament / PL/SQL block /
stored
procedure call and allocates the corresponding bind variables
dynamically
inside the stream.
</p>
<p><a name="NAMED_BV_NOTATION"></a>In Oracle, the naming convension of
placholders is quite different
from
the one in ODBC/DB2-CLI. Oracle placeholders are names, prefixed with
the
colon, e.g. <b>:f1</b>, <b>:supervisor_name</b>, <b>:employee_id. </b>A
placholder may be referenced more than once in the same SQL statement.
</p>
<p>In ODBC/DB2-CLI, placeholders are positional, presented as question
marks, for example:
</p>
<pre> INSERT INTO my_table values(<b><font
size="+2">?</font></b>,<b><font size="+2">?</font></b>,<b><font
size="+2">?</font></b>,<b><font size="+2">?</font></b>)</pre>
OTL 2.x/ODBC also had a positional notation for placeholders:
<pre> INSERT INTO my_table values(<b><font
size="+1">:1</font></b><int>,<b><font size="+1">:2</font></b><char[32]>,<b><font
size="+1">:3</font></b><double>,<b><font size="+1">:4</font></b><char[128]>)</pre>
<pre>:<Number> gets translated into ?.</pre>
OTL 4.0/ODBC still supports the :<Number> notation for
placeholders.
However, OTL 4.0 for Oracle, ODBC, and DB2-CLI have the named notation
for placeholders, so it is recommended to use it in both cases. There
is
only one restriction on named placeholders in OTL 4.0/ODBC and OTl
3.2/DB2-CLI:
the same placeholder may not be referenced more than once in the same
SQL
statement. This restriction is imposed by ODBC/DB2-CLI (see above).
<p>The Oracle traditional named notation for placeholders was extended
with datatype specificions, e.g.:
</p>
<pre> INSERT INTO my_table2 values(<font size="+1">:</font>employee_id<b><int></b>,<font
size="+1">:</font>supervisor_name<b><char[32]></b>)</pre>
It makes the placeholder declaration complete, so there is no need to
declare
host arrays in the program and bind them by calling special bind
functions<i>.
</i>It
is suffecient to define scalar data containers to hold just one row.
In
OTL 4.0, placeholders extended with datatype declarations are called <i>extended
placeholders</i> or simply <i>bind variables</i>.
<p>The following datatypes for declaring extended placeholder are
available
in OTL 4.0:<br>
</p>
<table style="text-align: left; width: 100%;" border="1" cellpadding="2"
cellspacing="2">
<tbody>
<tr>
<td style="vertical-align: top;"><b><a name="bigint"></a>bigint</b></td>
<td style="vertical-align: top;">64-bit signed integer, for
binding with BIGINT table columns (or stored procedure parameters) in
MS
SQL Server, DB2, MySQL, PostrgeSQL, etc. ODBC, and DB2 CLI support this
kind bind variables natively, so does OTL. 32-bit OCIs do not have
native
support for 64-bit
integers, so OTL has to emulate it via string (<char[XXX]>) bind
variables internally and does string-to-bigint and bigint-to-string
conversion. See also (1) <a href="otl3_compile.htm#OTL_BIGINT">OTL_BIGINT</a>,
(2) <a href="otl3_compile.htm#OTL_BIGINT_TO_STR">OTL_BIGINT_TO_STR,</a>
(3) <a href="otl3_compile.htm#OTL_STR_TO_BIGINT">OTL_STR_TO_BIGINT</a>.
With OTL/OCIx all three #defines (1), (2), and (3) need to be defined
in order to make OTL compilable, because the bigint datatype name, the
bigint-to-string conversion code, and the string-to-bigint conversion
code get expanded into the source code of the OTL header file in the
process of compilation.<br>
<br>
64-bit OCIs have support for 64-bit integers in LP64 compliant
platforms (which have 64-bit "long int"). 64-bit Windows is LLP64
("long int" is 32 bits), so it's recommeded to
use OTL's "bigint".<br>
</td>
</tr>
<tr>
<td style="vertical-align: top;"><a name="blob"></a><b>blob</b></td>
<td style="vertical-align: top;">for Oracle 8/9; BLOB</td>
</tr>
<tr>
<td style="vertical-align: top;"><a name="char"></a><b>char[</b>length<b>]<br>
<br>
</b>OTL 4.0.118 and higher:<span style="font-weight: bold;"> char<big><big>(</big></big></span>length<big><big><span
style="font-weight: bold;">)</span></big></big><br>
<br>
</td>
<td style="vertical-align: top;">null
terminated
string;
length is database dependent; for Oracle in [3,32545]; for ODBC it
depends
on the database backend and the ODBC driver; for DB2-CLI >2. In
Unicode
OTL (see #define <a href="otl3_compile.htm#OTL_UNICODE">OTL_UNICODE</a>),
this type of bind variable declaration means a null terminated Unicode
character string (two bytes per character). The <i>length </i>field
of
this declarator needs to include an extra byte / Unicode character, in
order to accomodate the null terminator itself (for example
char[11]
can be used in binding with a VARCHAR(9) column), unless #define <a
href="otl3_compile.htm#OTL_ADD_NULL_TERMINATOR_TO_STRING_SIZE">OTL_ADD_NULL_TERMINATOR_TO_STRING_SIZE</a>
is enabled.<br>
<br>
<a name="INVALID_CHAR"></a>"char" declaration without a specified
length is invalid and is going to result in an otl_exceprtion, for
example:<br>
<br>
<span style="font-family: monospace;">
"INSERT INTO test_tab VALUES(:f1<int>,:f2<char>)"<br>
<br>
Error code: 32013<br>
Error message: Invalid bind variable
declaration<br>
var info: f2 char<br>
</span><br>
</td>
</tr>
<tr>
<td style="vertical-align: top;"><span style="font-weight: bold;"><a
name="charz"></a>charz</span><br>
</td>
<td style="vertical-align: top;">Same as <a
href="otl3_bind_variables.htm#char">char</a>[] for <a
href="otl3_compile.htm#OTL_ORA7">OTL_ORA7,</a> <a
href="otl3_compile.htm#OTL_ORA8">OTL_ORA8,</a> <a
href="otl3_compile.htm#OTL_ORA8I">OTL_ORA8I</a>, <a
href="otl3_compile.htm#OTL_ORA9I">OTL_ORA9I</a>, <a
href="otl3_compile.htm#OTL_ORA10G">OTL_ORA10G</a>. Should be used only
when <a href="otl3_bind_variables.htm#pl_tab">PL/SQL tables</a> of
type CHAR(XXX) are used. charz is actually a workaround for the
following Oracle error: <span style="font-style: italic;">PLS-00418:
array bind type must match PL/SQL table row type. </span>Normally, the
internal OCI datatype that is used to bind VARCHAR2/CHAR table columns
/ scalar PL/SQL procedure parameters works fine, except for PL/SQL
tables of CHAR(XXX). PL/SQL engine does not like what OTL tries to bind
with a PL/SQL table of CHAR(XXX). <span style="font-style: italic;">charz[]
</span>should be used instead of char[] in cases like that.<br>
</td>
</tr>
<tr>
<td style="vertical-align: top;"><a name="clob"></a><b>clob</b></td>
<td style="vertical-align: top;">for Oracle 8/9: CLOB, NCLOB</td>
</tr>
<tr>
<td style="vertical-align: top;"><a name="db2date"></a><b>db2date</b></td>
<td style="vertical-align: top;">for DB2 DATEs; should
be
used in
the binding of a placeholder with a DB2 DATE column in case of both</td>
</tr>
<tr>
<td style="vertical-align: top;"><a name="db2time"></a><b>db2time</b></td>
<td style="vertical-align: top;">for DB2 TIMEs; should
be
used in
the binding of a placeholder with a DB2 TIME column in case of both
OTL/DB2-CLI
and OTL/ODBC for DB2; requires <a
href="otl3_stream_class.htm#otl_datetime">otl_datetime</a>
as a data container. See example <a href="otl3_ex91.htm">91</a> for
more
detail.</td>
</tr>
<tr>
<td style="vertical-align: top;"><b>double</b></td>
<td style="vertical-align: top;">8-byte floating point number</td>
</tr>
<tr>
<td style="vertical-align: top;"><b>float </b></td>
<td style="vertical-align: top;">4-byte floating point number</td>
</tr>
<tr>
<td style="vertical-align: top;"><b>int </b></td>
<td style="vertical-align: top;">signed 32-bit int</td>
</tr>
<tr>
<td style="vertical-align: top;"><span style="font-weight: bold;"><a
name="long"></a>long</span><br>
</td>
<td style="vertical-align: top;">
<ul>
<li>OCIs: signed 32-bit integer on 32-bit platforms and LLP64
compliant platforms (64-bit Windows); signed 64-bit integer on LP64
compliant platforms (Linux, AIX, Solaris, etc).<br>
<br>
</li>
<li>ODBC : signed 32-bit integer on 32-bit platforms; signed
32-bit or 64-bit ineteger depending on the ODBC driver. According
to the ODBC standard, SQL_C_LONG is a signed 32-bit integer, yet some
ODBC drivers implement it as a 64-bit integer. If you want to map
<long> to a signed 64-bit integer, use #define <a
href="otl3_compile.htm#OTL_MAP_LONG_TO_SQL_C_SBIGINT">OTL_MAP_LONG_TO_SQL_C_SBIGINT</a>.<br>
<br>
</li>
<li>DB2-CLI: signed 32-bit integer of all platformts. If you
want to map <long> to a signed 64-bit integer, use #define <a
href="otl3_compile.htm#OTL_MAP_LONG_TO_SQL_C_SBIGINT">OTL_MAP_LONG_TO_SQL_C_SBIGINT</a>.<br>
<br>
</li>
</ul>
<long> is not recommended for use in portable 32/64-bit code that
is supposed to work in Windows as well as Linux/Unix, across multiple
database types (Oracle, MS SQL Server, DB2, etc). <<a href="#bigint">bigint</a>>
is a more partable option.<br>
<br>
If you develop code for a single platform for a single database type,
<long> is okay to use.<br>
</td>
</tr>
<tr>
<td style="vertical-align: top;"><a name="ltz_timestamp"></a><b>ltz_timestamp</b><a
href="otl3_stream_class.htm#otl_datetime"></a></td>
<td style="vertical-align: top;">Oracle 9i
TIMESTAMP WITH LOCAL TIME ZONE, in a combination with #define <a
href="otl3_compile.htm#OTL_ORA_TIMESTAMP">OTL_ORA_TIMESTAMP</a>,
and <a href="otl3_stream_class.htm#otl_datetime">otl_datetime</a></td>
</tr>
<tr>
<td style="vertical-align: top;"><span style="font-weight: bold;"><a
name="nchar"></a>nchar</span>[length]<br>
</td>
<td style="vertical-align: top;">Same as <a
href="otl3_bind_variables.htm#char">char</a>[] + otl_connect::<a
href="otl3_connect_class.htm#set_character_set">set_character_set(</a>SQLCS_NCHAR)
for Oracle 8i/9i/10g only, under #define <a
href="otl3_compile.htm#OTL_UNICODE">OTL_UNICODE</a>., or #define <a
href="otl3_compile.htm#OTL_ORA_UTF8">OTL_ORA_UTF8</a>. nchar[] is
required only when both VARCHAR2/CHAR and NVARCHAR2/NCHAR need to be
declared in the same SQL statement, or PL/SQL block.</td>
</tr>
<tr>
<td style="vertical-align: top;"><span style="font-weight: bold;"><a
name="nclob"></a>nclob</span><br>
</td>
<td style="vertical-align: top;">Same as <a href="#clob">clob</a>
+ otl_connect::<a
href="file:///D%7C/oscl/vc/doc/otl4/otl3_connect_class.htm#set_character_set">set_character_set(</a>SQLCS_NCHAR)
for Oracle 8i/9i/10g only, under #define <a
href="file:///D%7C/oscl/vc/doc/otl4/otl3_compile.htm#OTL_UNICODE">OTL_UNICODE</a>,
or #define <a href="otl3_compile.htm#OTL_ORA_UTF8">OTL_ORA_UTF8</a>.
nclob is required only when both CLOB and NCLOB need to be
declared in the same SQL statement, or PL/SQL block. <br>
</td>
</tr>
<tr>
<td style="vertical-align: top;"><a name="raw"></a><b>raw[</b>length<b>]</b></td>
<td style="vertical-align: top;">for Oracle 7/8/9/10: RAW, LONG
RAW; for ODBC: SQL_BINARY (BINARY in MS SQL, Sybase 15, MySQL;
CHAR (XXX) BYTE in SAP/MAX DB),
SQL_VARBINARY (VARBINARY in MS SQL, Sybase 15, MySQL; BYTEA in
PostgreSQL, VARCHAR(XXX) BYTE in SAP/MAX DB). Columns of this type can
be written to / read from with <a href="otl3_long_string.htm">otl_long_string</a>()'s.
The maximum allowed size is specific to each supported database type.
Also, see #define <a
href="otl3_compile.htm#OTL_MAP_SQL_VARBINARY_TO_RAW_LONG">OTL_MAP_SQL_VARBINARY_TO_RAW_LONG</a>,
#define <a href="otl3_compile.htm#OTL_MAP_SQL_GUID_TO_CHAR">OTL_MAP_SQL_GUID_TO_CHAR</a>,
#define <a href="otl3_compile.htm#OTL_MAP_SQL_BINARY_TO_CHAR">OTL_MAP_SQL_BINARY_TO_CHAR</a><a
href="file:///D%7C/oscl/vc/doc/otl4/otl3_compile.htm#OTL_MAP_SQL_BINARY_TO_CHAR"><br>
</a> </td>
</tr>
<tr>
<td style="vertical-align: top;"><a name="raw_long"></a><b>raw_long</b></td>
<td style="vertical-align: top;">for Oracle 7: RAW,
LONG
RAW;
for Oracle 8/9: RAW, LONG RAW; for ODBC: SQL_LONGVARBINARY,
SQL_VARBINARY;
for DB2: BLOB</td>
</tr>
<tr>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -