# OTL 4.0, Declaration of bind variables

# Declaration of bind variables

This section explains in detail how to declare bind variables in the otl_stream.

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.

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. :f1, :supervisor_name, :employee_id. A placholder may be referenced more than once in the same SQL statement.

In ODBC/DB2-CLI, placeholders are positional, presented as question marks, for example:

         INSERT INTO my_table values(?,?,?,?)

OTL 2.x/ODBC also had a positional notation for placeholders:

         INSERT INTO my_table values(:1<int>,:2<char[32]>,:3<double>,:4<char[128]>)

    :<Number> gets translated into ?.

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).

The Oracle traditional named notation for placeholders was extended with datatype specificions, e.g.:

       INSERT INTO my_table2 values(:employee_id<int>,:supervisor_name<char[32]>)

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. It is suffecient to define scalar data containers to hold just one row. In OTL 4.0, placeholders extended with datatype declarations are called extended placeholders or simply bind variables.

The following datatypes for declaring extended placeholder are available in OTL 4.0:

bigint | 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. 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) OTL_BIGINT, (2) OTL_BIGINT_TO_STR, (3) OTL_STR_TO_BIGINT. 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.
blob | for Oracle 8/9; BLOB
char[length] OTL 4.0.118 and higher: char(length) | 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 OTL_UNICODE), this type of bind variable declaration means a null terminated Unicode character string (two bytes per character). The length 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 OTL_ADD_NULL_TERMINATOR_TO_STRING_SIZE is enabled.
"char" declaration without a specified length is invalid and is going to result in an otl_exceprtion, for example:
"INSERT INTO test_tab VALUES(:f1<int>,:f2<char>)"
Error code: 32013
Error message: Invalid bind variable declaration
var info: f2 char
charz | Same as char[] for OTL_ORA7, OTL_ORA8, OTL_ORA8I, OTL_ORA9I, OTL_ORA10G. Should be used only when PL/SQL tables of type CHAR(XXX) are used. charz is actually a workaround for the following Oracle error: PLS-00418: array bind type must match PL/SQL table row type. 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). charz[] should be used instead of char[] in cases like that.
clob | for Oracle 8/9: CLOB, NCLOB
db2date | for DB2 DATEs; should be used in the binding of a placeholder with a DB2 DATE column in case of both
db2time | 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 otl_datetime as a data container. See example 91 for more detail.
double | 8-byte floating point number
float | 4-byte floating point number
int | 32-bit signed int
ltz_timestamp | Oracle 9i TIMESTAMP WITH LOCAL TIME ZONE, in a combination with #define OTL_ORA_TIMESTAMP, and otl_datetime
nchar[length] | Same as char[] + otl_connect::set_character_set(SQLCS_NCHAR) for Oracle 8i/9i/10g only, under #define OTL_UNICODE. nchar[] is required only when both VARCHAR2/CHAR and NVARCHAR2/NCHAR need to be declared in the same SQL statement, or PL/SQL block.
nclob | Same as clob + otl_connect::set_character_set(SQLCS_NCHAR) for Oracle 8i/9i/10g only, under #define OTL_UNICODE. nclob is required only when both CLOB and NCLOB need to be declared in the same SQL statement, or PL/SQL block.
raw[length] | 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 otl_long_string()'s. The maximum allowed size is specific to each supported database type. Also, see #define OTL_MAP_SQL_VARBINARY_TO_RAW_LONG, #define OTL_MAP_SQL_GUID_TO_CHAR, #define OTL_MAP_SQL_BINARY_TO_CHAR
raw_long | for Oracle 7: RAW, LONG RAW; for Oracle 8/9: RAW, LONG RAW; for ODBC: SQL_LONGVARBINARY, SQL_VARBINARY; for DB2: BLOB
short | short int (16-bit signed integer)
timestamp | MS SQL Server/Sybase DATETIME, DB2 TIMESTAMP, Oracle DATE, Oracle 9i TIMESTAMP (when #define OTL_ORA_TIMESTAMP is enabled) ; it requires TIMESTAMP_STRUCT (OTL/ODBC, OTL/DB2-CLI), or otl_datetime (ODBC, DB2-CLI, and OCIx). OTL/DB2-CLI and OTL/ODBC for DB2; requires otl_datetime as a data container. See example 91 for more detail
tz_timestamp | Oracle 9i TIMESTAMP WITH TIME ZONE, in a combination with #define OTL_ORA_TIMESTAMP, and otl_datetime
unsigned | unsigned int (32-bit unsigned integer)
varchar_long | for Oracle 7: LONG; for Oracle 8/9: LONG; for ODBC: SQL_LONGVARCHAR; for DB2: CLOB

varchar_long, raw_long clob and blob require the otl_long_string class as a data container. In order to set the maximum size for varchar_long, raw_long, clob or blob, see the set_max_long_size() function in the otl_connect class for more detail.

For PL/SQL blocks (OTL 4.0/OCI7, OTL4.0/OCI8/9) or stored procedure calls (OTL 4.0/ODBC, OTL 4.0/DB2-CLI), special qualifiers are introduced to distinguish between input and output variables:

- in -- input variable
- out -- output variable
- inout -- input/output variable

Example 1 (Oracle):

     BEGIN
       :rc<int,out> := my_func(:salary<float,in>,
                               :ID<int,inout>,
                               :name<char[32],out>
                              );
     END;

Example 2 (ODBC or DB2-CLI):

New (OTL 4.0/ODBC, OTL 4.0/DB2-CLI) style

       {
        call :rc<int,out> := my_func(:salary<float,in>,
                                     :ID<int,inout>,
                                     :name<char[32],out>
                                    )
       }

    Old (OTL 2.0/ODBC) style:

       {
        call :1<int,out> := my_func(:2<float,in>,
                                    :3<int,inout>,
                                    :4<char[32],out>
                                   )
       }

In the bind variable declaration, spaces in the datatype section and in the access qualifier section ARE NOT allowed. The following code is invalid:

Example 1

      insert into tab1 values(:salary< double >, :name< char [ 32 ] > , :emp_id< int>);

Example 2

      :rc< int, out > := ...;

### Embedding colon (":") literals into SQL statements in OTL/ODBC

Informix Call Level Interface (CLI) can be used in a combination with OTL/ODBC. Informix SQL allows colons (":") as legitimate characters in Informix SQL statements. Therefore, there is a need to embed colon literal into such SQL statements. The following notation can be used for embedding colons into SQL statements under OTL/ODBC: "\\:". OTL/ODBC 4.0.68 and higher supports the notation.

###
Explicit bind variables in output column definitions in SELECT statements

OTL describes output column names, datatypes, and column lengths dynamically right after the SELECT statement is parsed. OTL maps column internal datatypes into C++ external datatypes by default. The default mapping can be overriden by otl_stream::set_column_type() and otl_stream::set_all_column_types() function calls. However, it is not as convenient as defining explicit bind variables right in the text of the SQL statement.In OTL 4.0.73, a special kind of bind variables is introduced: explicit bind variables for defining datatypes of output columns in SELECT statements. The format for defining explicit bind variables in SELECT statement's columns is as follows: :#N<datatype>, where N is 1,2,3 (column's relative position in the SELECT statement)... SELECT output columns do not have a direct equivalent of :VAR (OCI), or ? (ODBC, DB2-CLI), so if an explicit bind variable is defined for a column in a SELECT statement, the bind variable has to be blanked out in the resulting format of the SELECT statement that gets passed into the database API. For example:

SELECT f1 :#1<short>, f2
FROM test_tab
...

Here is the resulting SELECT statement that gets passed into the database API:

SELECT f1 , f2
FROM test_tab

The set of datatypes allowed in :#N<datatype> is limited and database API specific, In general, the following types are allowed:

- <char[XXX]>
- <double>
- <float>
- <int>
- <bigint> (which is supported natively in ODBC / DB2-CLI, and OTL internally emulates bigints via strings (char[XXX]) for OCIs).
- <unsigned>
- <short>
- <long>
- <raw>
- <raw_long>
- <timestamp>
- <varchar_long>

Also, it depends on the internal type of the column to be overriden whether the target type (from the list above) is compatible with the source / internal type of the column or not. For more detail, refer to the correspoding database API manual. A quick way to figure out if the types are compatible is through trial and error.
The OTL internal to external default data type mapping for output columns in SELECT statement works okay for the most part, except for rare cases when there may be some special considerations for performance, or external datatype definitions (for example, compatibility with predefined or general-purpose C++ containers).
In OTL 4.0.117 and higher, it is possible to do the same kind of overriding (as described above) for output column datatypes of an implicit result set (ODBC/DB2-CLI), or a reference cursor (Oracle), for example:
Oracle:
otl_stream
s(50,
"BEGIN "
" my_proc1(:f1<int,in>,:res_set); "
" :#1<int,out> "
" :#2<char[31],out> "
"END;",
db, // otl_connect object
":res_set" // reference cursor / result set's placeholder name
);
ODBC/DB2-CLI:
otl_stream
s(50,
"{ "
" my_proc1(:f1<int,in>) "
" :#1<int,out> "
" :#2<char[31],out> "
"}",
db, // otl_connect object
otl_implicit_select // implicit result set / select flag
);
Output column overrides are optional. If a column is not overriden explicitly, the default datatype mapping applies..

### Declaration of PL/SQL tables (OTL/OCIx)

OTL 3.x/OCIx, release OTL 3.1.0 and higher, supports PL/SQL tables via the otl_stream class and special template PL/SQL table container classes. This feature works only for PL/SQL blocks and stored procedures. For example, a stored procedure, which takes PL/SQL tables as arguments, gets called in a block. The PL/SQL table containers can be used to read/write the whole PL/SQL table from/to the OTL stream in one shot. In the OCIx and Pro*C, it is a well known technique, only the interface is a way too complex.

In OTL 4.0/OCIx, in PL/SQL blocks, a PL/SQL table dimension can be added to the access qualifiers in/out/inout, e.g.:

     BEGIN
       my_pkg.my_proc(:salary<float,in[100]>,
                      :ID<int,inout[200]>,
                      :name<char[32],out[150]>
                     );
     END;

[100] is the maximum size of the :salary placeholder, which is an input PL/SQL table of float[100]. [200] is the maximum size of the :ID placeholder, which is an input/output PL/SQL table of int[100]. [150] is the maximum size of the :name placeholder, which is an output PL/SQL table of char[150][32].

The size of the otl_stream with the definition in the example above needs to be set to 1, since stored procedures cannot be called in bulk. However, PL/SQL table type parameters are not scalars, they are vectors with maximum sizes, predefined in the defintions of the bind variables.

Starting with OTL 4.0.115 and on, the maximum PL/SQL table size is no longer limited to 32767. In order to turn the old limit of 32767 back on, use #define OTL_STREAM_LEGACY_BUFFER_SIZE_TYPE.

For more detail, see examples 49, 50, 51, 52.

Prev Next Contents Go Home

Copyright © 1996, 2007, Sergei Kuchin, email: skuchin@ispwest.com, skuchin@gmail.com

Permission to use, copy, modify and redistribute this document for any purpose is hereby granted without fee, provided that the above copyright notice appear in all copies.