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 statement / PL/SQL block / stored procedure call and allocates the corresponding bind variables dynamically inside the stream.

In Oracle, the naming convention of placeholders 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 placeholder 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 4.0/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 data type specifications, 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 sufficient to define scalar data containers to hold just one row. In OTL 4.0, placeholders extended with data type declarations are called extended placeholders or simply bind variables.

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

bigint Signed 64-bit 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 prior to OCI 11.2 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 data type 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.

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 recommended to use OTL's "bigint". #define OTL_ORA_MAP_BIGINT_TO_LONG can be used to map <bigint> to 64-bit longs on LP64 platforms, which is more efficient than the char[XXX] OCI binding for <bigint>.

OCI 11.2 and higher has native support for signed 64-bit ints, so it's sufficient to define OTL_BIGINT and OTL_ORA11G_R2, in order to enable native support for signed 64-bit ints
blob for Oracle 8 and higher; BLOB
bdouble
Same as double for OTL/OCI10 and higher. Should only be used when PL/SQL tables of type BINARY_DOUBLE are used. bdouble 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 data type that is used to bind BINARY_DOUBLE table columns / scalar PL/SQL procedure parameters works fine, except for PL/SQL tables of BINARY_DOUBLE. PL/SQL engine does not like what OTL tries to bind with a PL/SQL table of BINARY_DOUBLE. bdouble should be used instead of double in cases like that.
bfloat
Same as float for OTL/OCI10 and higher. Should only be used when PL/SQL tables of type BINARY_FLOAT are used. bfloat 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 data type that is used to bind BINARY_FLOAT table columns / scalar PL/SQL procedure parameters works fine, except for PL/SQL tables of BINARY_FLOAT. PL/SQL engine does not like what OTL tries to bind with a PL/SQL table of BINARY_FLOAT. bfloat should be used instead of float in cases like that.
char[length]

OTL 4.0.118 and higher: char(length)

null terminated string; length is database dependent: for Oracle in the range of [3..32545] when the corresponding C++ side bind variable is used with a PL/SQL VARCHAR2 data type, or in the range of [3..2GB] when the C++ side variable is used with CLOB/ LONG SQL data types; 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 declaration needs to include an extra byte / Unicode character, in order to accommodate 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_exception, 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 data type 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 and higher: 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  signed 32-bit int
long
  • 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).

  • ODBC : signed 32-bit integer on 32-bit platforms; signed 32-bit or 64-bit integer 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 OTL_MAP_LONG_TO_SQL_C_SBIGINT.

  • DB2-CLI: signed 32-bit integer of all platforms. If you want to map <long> to a signed 64-bit integer, use #define OTL_MAP_LONG_TO_SQL_C_SBIGINT.

<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). <bigint> is a more portable option.

If you develop code for a single platform for a single database type, <long> is okay to use.
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., or #define OTL_ORA_UTF8. 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, or #define OTL_ORA_UTF8. 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/11: 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/10/11: RAW, LONG RAW; for ODBC: SQL_LONGVARBINARY, SQL_VARBINARY; for DB2: BLOB
refcur
for Oracle 8/9/10/11. When a stored procedure returns a reference cursor, a bind variable of refcur type may be declared in the PL/SQL block that calls the stored procedure, for example:

              "begin "
              " my_pkg.my_proc(:f1<int,in>,:f2<int,in>, "
              "         :str1<char[100],out>, "
      // :str1 is an output string parameter
              "         :cur1<refcur,out[50]>, "
              "         :cur2<refcur,out[50]>); "
     // :cur1, :cur2 are a bind variable names, refcur -- their types, 
     // out -- output parameter, 50 -- the buffer size when this
     // reference cursor will be attached to otl_refcur_stream
              "end;"
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
ubigint
Natively supported unsigned 64-bit int. #define OTL_UBIGINT needs to be enabled.
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:

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

   {
    :rc<int,out> = call my_func(:salary<float,in>,  
                                :ID<int,inout>, 
                                :name<char[32],out>
                                )
   }
Old (OTL 2.0/ODBC) style:
   {
    :1<int,out> = call 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, yet a whitespace in a bind variable declaration was found in the SQL statement. 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, data types, and column lengths dynamically right after the SELECT statement is parsed. OTL maps column internal data types into C++ external data types by default. The default mapping can be overridden 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 data types 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:

Also, it depends on the internal type of the column to be overridden 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 corresponding 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 data type 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 data types 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 definitions 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.

Declaration of SQL tool neutral bind variables for Oracle

OTL 4.0.209 and higher supports an alternative bind variable notation that is neutral to SQL tools for Oracle. Neutral in this context means that the same SQL statements / PLSQL blocks can be executed both in the SQL tools and OTL. All OTL extended bind variable declarations are made SQL comments, for example:

	INSERT INTO test_tab VALUES(:f1/*int*/,:f2/*char[31]*/);

In general, the regular :var<...> syntax can be converted to :var/*...*/. The same rule of "no space characters in between" still applies. The rule was made in order to keep the OTL SQL statement parser as light as possible (short of writing a full-blown SQL parser). OTL also has SELECT column override syntax. The SELECT column override variables get replaced with spaces in the SQL statement, because they are not real placeholders. So, the SQL tool neutral syntax for the SELECT column override variables puts the variables into SQL comments, for example:

	regular syntax ==> SELECT f1, :#1<int> f2 :#2<char[31]> FROM test_tab WHERE f1=:f1<int>

SQL neutral syntax ==> SELECT f1, /*:#1<int>*/ f2 /*:#2<char[31]>*/ FROM test_tab WHERE f1=:f1/*int*/

The SELECT statement that uses the SQL neutral syntax for its bind variables can be copied and pasted into an SQL tool, and executed without any changes. "EXPLAIN PLAN" can be run on the SELECT statement.


    

    


Prev NextContentsGo Home

Copyright 1996-2014, Sergei Kuchin, email: skuchin@gmail.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. THE SOFTWARE IS PROVIDED "AS IS" AND THE AUTHOR DISCLAIMS ALL WARRANTIES WITH REGARD TO THIS SOFTWARE INCLUDING ALL IMPLIED WARRANTIES OF MERCHANTABILITY AND FITNESS. IN NO EVENT SHALL THE AUTHOR BE LIABLE FOR ANY SPECIAL, DIRECT, INDIRECT, OR CONSEQUENTIAL DAMAGES OR ANY DAMAGES WHATSOEVER RESULTING FROM LOSS OF USE, DATA OR PROFITS, WHETHER IN AN ACTION OF CONTRACT, NEGLIGENCE OR OTHER TORTIOUS ACTION, ARISING OUT OF OR IN CONNECTION WITH THE USE OR PERFORMANCE OF THIS SOFTWARE.