Examples

• Examples by database type

• Oracle 7 / OCI
  
  
• Oracle 8/8i
  
  
• Oracle 8,8i / OCI
  
• Oracle 8i Unicode/OCI
  
  
• Oracle 9i
  
  
• Oracle 9i / OCI
• Oracle 9i Unicode / OCI
  
• Oracle 9i UTF8 / OCI
  
  
• Oracle 10g
  
  
• Oracle 10g / OCI
• Oracle 10g Unicode / OCI
• Oracle 10g UTF8 / OCI
  
  
• Oracle 8/8i/9i/10g Unicode / ODBC
  
  
• Oracle 11g Release 2 (11.2)
  
  
• DB2
  
  
• DB2 / CLI
• DB2 Unicode / CLI
  
  
• MS SQL Server
  
  
• MS SQL Server / ODBC 
• MS SQL Server Unicode / ODBC
• MS SQL Server / FreeTDS ODBC
  
  
• Sybase
  
  
• Sybase SQL Server / ODBC, 
• Sybase SQL Server / FreeTDS ODBC
  
  
• MySQL
  
  
• MySQL / MyODBC 3.5,
  
• MySQL / MyODBC 2.5,
  
  
• PostgreSQL / ODBC


• EnterpriseDB / ODBC
  
  
• Informix CLI
  
  
• MAX-DB / SAP-DB examples (ODBC)
  
  
• FireBird Database / ODBC
  
  
• SQLite ODBC
  
  
• TimesTen ODBC
  
  
• Microsoft ACCESS ODBC
  
  
• Generic ODBC (examples for Oracle / ODBC, MS SQL Server / ODBC, etc.)
  
  

• Oracle Call Interface 7 (OCI7)

• Example 1 (Simple Insert/Select)
• Example 2 ("Incompatible datatypes in stream operation" exception)
• Example 3 (PL/SQL block)
• Example 4 (Insert and Select via referenced cursor)
• Example 5 (Simple Insert/Select with the LONG datatype)
• Example 6 (Simple Insert/Select with the LONG RAW datatype)
• Example 7 (otl_stream::describe_select() with a simple Select)
• Example 8 (otl_stream:: describe_select() with a referenced cursor)
• Example 9 (otl_stream and NULLs)

• Oracle Call Interface 8 (OCI8)

 
• Example 10 (Simple Insert/Select)
• Example 11 ("Incompatible datatypes in stream operation" exception)
• Example 12 (PL/SQL block)
• Example 13 (Insert and Select via referenced cursor)
• Example 14 (Simple Insert/Select with the LONG datatype)
• Example 15 (Simple Insert/Select with the LONG RAW datatype)
• Example 16 (otl_stream::describe_select() with a simple Select)
• Example 17 (otl_stream::describe_select() with a referenced cursor)
• Example 18 (otl_stream and NULLs)
• Example 19 (Simple Insert/Select with the CLOB datatype)
• Example 20 (Simple Insert/Select with the BLOB datatype)
 

 
• Oracle Call Interface 8 (OCI8) for Oracle 8i that requires special treatment

 
• Example 62 (Insert and Select via referenced cursor for Oracle 8i)
• Example 63 (otl_stream::describe_select() with a simple Select for Oracle 8i)
• Example 64 (otl_stream::describe_select() with a referenced cursor for Oracle 8i)

• Oracle Call Interface 8i (OCI8i) with Unicode
 
• Example 174 (Simple Unicode Insert/Select)
• Example 175 (Simple Unicode Insert/Select in the NLS (NVARCHAR2))
• Example 176 (Unicode Insert and Select via referenced cursor)
• Example 177 (Simple Unicode Insert/Select with LONG)
• Example 178 (Simple Unicode Insert/Select with CLOB)
• Example 179 (Simple Unicode Insert/Select with NCLOB)
• Example 180 (Simple Unicode Insert/Select with CLOB via referenced cursor)
• Example 181 (Simple Unicode Insert/Select with NCLOB via referenced cursor)
• Example 182 (Simple Unicode Insert/Select with CLOB, and LOB streams)
• Example 183 (Simple Unicode Insert/Select with NCLOB, and LOB streams)
• Example 184 (Unicode, VARCHAR2, and otl_refcur_stream)
• Example 185 (Unicode, NVARCHAR2, and otl_refcur_stream)
• Example 186 (Unicode, PLSQL/index-by tables as parameters)
• Example 187 (Unicode, PLSQL/index-by tables as parameters, and NVARCHAR2s)

• Oracle Call Interface 9i (OCI9i) with Unicode
 
• Example 188  (Simple Unicode Insert/Select)
• Example 189 (Simple Unicode Insert/Select an the NLS (NVARCHAR2))
• Example 190 (Unicode Insert and Select via referenced cursor)
• Example 191 (Simple Unicode Insert/Select with LONG)
• Example 192 (Simple Unicode Insert/Select with CLOB)
• Example 193 (Simple Unicode Insert/Select with NCLOB)
• Example 194 (Simple Unicode Insert/Select with CLOB via referenced cursor)
• Example 195 (Simple Unicode Insert/Select with NCLOB via referenced cursor)
• Example 196 (Simple Unicode Insert/Select with CLOB, and LOB streams)
• Example 197 (Simple Unicode Insert/Select with NCLOB, and LOB streams)
• Example 198 (Unicode, VARCHAR2, and otl_refcur_stream)
• Example 199 (Unicode, NVARCHAR2, and otl_refcur_stream)
• Example 200 (Unicode, PLSQL/index-by tables as parameters)
• Example 201 (Unicode, PLSQL/index-by tables as parameters, and NVARCHAR2s)

• Open DataBase Connectivity (ODBC)

 
• Example 21 (Simple Insert/Select)
• Example 22 ("Incompatible datatypes in stream operation" exception)
• Example 23 (Oracle stored procedure call)
• Example 24 (MS SQL Server stored procedure call)
• Example 25 (Implicit Select/Result Set returned by a stored procedure)
• Example 26 (TIMESTAMP_STRUCT and Oracle)
• Example 27 (TIMESTAMP_STRUCT and MS SQL Server)
• Example 28 (MS SQL Server and TEXT column)
• Example 29 (MS SQL Server and IMAGE column)
• Example 30 (Oracle and LONG column)
• Example 31 (Oracle and LONG RAW column)
• Example 32 (otl_stream::describe_select() with a simple Select)
• Example 33 (MS SQL Server: otl_stream::describe_select() and result sets)
• Example 34 (otl_stream and NULLs)

 
• MyODBC for MySQL

 
• Example 78 (Simple Insert/Select/Update, MyODBC 2.50)
• Example 79 ("Incompatible datatypes in stream operation" exception, MyODBC 2.50)
• Example 80 (otl_datetime container and datetime/date table columns, MyODBC 2.50)
• Example 81 (otl_long_string container and LONGTEXT column, MyODBC 2.50)
• Example 82 (otl_long_string container and LONGBLOB column, MyODBC 2.50)
• Example 83 (std::string and STL-compliant stream iterators, MyODBC 2.50)
• Example 84 (otl_stream::describe_select() with a simple Select, MyODBC 2.50)

 
• Example 225 (Simple Insert/Select/Update, MyODBC 3.50)
• Example 226 ("Incompatible datatypes in stream operation" exception, MyODBC 3.50)
• Example 227 (otl_datetime container and datetime/date table columns, MyODBC 3.50)
• Example 228 (otl_long_string container and LONGTEXT column, MyODBC 3.50)
• Example 229 (otl_long_string container and LONGBLOB column, MyODBC 3.50)
• Example 230 (std::string and STL-compliant stream iterators, MyODBC 3.50)
• Example 231 (otl_stream::describe_select() with a simple Select, MyODBC 3.50)

• DB2 Call Level Interface (CLI)

 
• Example 89 (Simple Insert/Select/Update)
• Example 90 ("Incompatible datatypes in stream operation" exception)
• Example 91 (otl_datetime container and DATE/TIME/TIMESTAMP table columns)
• Example 92 (otl_long_string container and CLOB column)
• Example 93 (otl_long_string container and BLOB column)
• Example 94 (std::string and STL-compliant stream iterators)
• Example 95 (otl_stream::describe_select() with a simple Select)
• Example 96 (otl_stream and NULLs)

• FireBird Database / ODBC

• Example 133 (Simple Insert/Select/Update)
• Example 134 ("Incompatible datatypes in stream operation" exception)
• Example 135 (otl_datetime container and DATE/TIME/TIMESTAMP  table columns)
• Example 136 (otl_long_string container and a BLOB column)
• Example 137 (std::string and STL-compliant stream iterators)
• Example 138 (otl_stream::describe_select() with a simple Select)
• Example 139 (otl_stream and NULLs)

• PostgreSQL / PostgreSQL ODBC

• Example 140 (Simple Insert/Select/Update)
• Example 141 ("Incompatible datatypes in stream operation" exception)
• Example 142 (otl_datetime container and DATE/TIME/TIMESTAMP  table columns)
• Example 143 (otl_long_string container and a TEXT column)
• Example 144 (std::string and STL-compliant stream iterators)
• Example 145 (otl_stream::describe_select() with a simple Select)
• Example 146 (otl_stream and NULLs)

• Miscellaneous
 
• Example 35 (otl_nocommit_stream in Oracle 7)
• Example 36 (otl_nocommit_stream in Oracle 8)
• Example 37 (otl_nocommit_stream in ODBC)

 
• Examle 38 (otl_datetime in Oracle 7)
• Examle 39 (otl_datetime in Oracle 8)
• Examle 40 (otl_datetime in ODBC)

 
• Example 41 (Insert/Update/Select with CLOB in Oracle 8)
• Example 42 (ODBC, OCI: LONG ==> CLOB. How to use OTL namespaces)

 
• Example 43 (Get Rows Processed Count in Oracle 7)
• Example 44 (Get Rows Processed Count in Oracle 8)
• Example 45 (Get Rows Processed Count in ODBC)

 
• Example 46 ("Auto-commit every SQL statement / commit-on-success" mode in Oracle 7)
• Example 47 ("Auto-commit every SQL statement / commit-on-sucess" mode in Oracle 8)
• Example 48 ("Auto-commit every SQL statement / commit-on-sucess" mode in ODBC)

 
• Example 49 (PL/SQL tables as parameters, Oracle 7)
• Example 50 (PL/SQL tables as parameters, Oracle 8)
• Example 51 (PL/SQL table of DATEs as parameter, Oracle 7)
• Example 52 (PL/SQL table of DATEs as parameter, Oracle 8)

 
• Example 53 (Datatype override in SELECT, Oracle 7)
• Example 54 (Datatype override in SELECT, Oracle 8)
• Example 55 (Datatype override in SELECT, MS SQL Server)

 
• Example 56 (Insert/Update/Select with Oracle 8 CLOB in stream mode)
• Example 57 (Insert/Update/Select with Oracle 8 BLOB in stream mode)

 
• Example 58 (Using OTL/OCI7 in Pro*C/C++ environment)
• Example 59 (Using OTL/OCI8 in Pro*C/C++ environment)

 
• Example 60 (Using OTL/OCI8: server_attach/detach and session_begin/end)
• Example 61 (Using OTL/OCI8: server_attach/detach, session_begin/end, and session_reopen)

 
• Example 65 (Simple Insert/Select and otl_stream::clean() function for Oracle 7)
• Example 66 (Simple Insert/Select and otl_stream::clean() function for Oracle 8/8i)
• Example 67 (Simple Insert/Select and otl_stream::clean() function for OTL/ODBC and MS SQL Server)

 
• Example 68 (Dynamically allocated PL/SQL tables as parameters, Oracle 7)
• Example 69 (Dynamically allocated PL/SQL tables as parameters, Oracle 8)
• Example 70 (Dynamically allocated PL/SQL table of DATEs as parameter, Oracle 7)
• Example 71 (Dynamically allocated PL/SQL table of DATEs as parameter, Oracle 8)

 
• Example 72 (STL std::strings, ANSI C++ typecasts, Oracle 7)
• Example 73 (STL std::strings, ANSI C++ typecasts, Oracle 8)
• Example 74 (STL std::strings, ANSI C++ typecasts, MS SQL Server)

 
• Example 75 (STL-compliant OTL stream iterators, Oracle 7)
• Example 76 (STL-compliant OTL stream iterators, Oracle 8)
• Example 77 (STL-compliant OTL/ODBC stream iterators, MS SQL Server)

 
• Example 85 (STL vector based PL/SQL tables as parameters, Oracle 7)
• Example 86 (STL vector based PL/SQL tables as parameters, Oracle 8)
• Example 87 (STL vector based PL/SQL table of DATEs as parameter, Oracle 7)
• Example 88 (STL vector based PL/SQL table of DATEs as parameter, Oracle 8)

 
• Example 97 (otl_value<T>, Oracle 7)
• Example 98 (otl_value<T>, Oracle 8/8i)
• Example 99 (otl_value<T>, ODBC / MS SQL Server)
• Example 100 (otl_value<T>, DB2-CLI)

 
• Example 101 (#define OTL_UNCAUGHT_EXCEPTION_ON, Oracle 7)
• Example 102 (#define OTL_UNCAUGHT_EXCEPTION_ON, Oracle 8/8i)
• Example 103 (#define OTL_UNCAUGHT_EXCEPTION_ON, ODBC / MS SQL Server)

 
• Example 104 (#define OTL_EXTENDED_EXCEPTION, ODBC / MS SQL Server)
• Example 105 (OTL/ODBC and MS SQL Server's GUIDs)

 
• Example 106 (OTL/ODBC and bulk operations with Oracle's LONG)
• Example 107 (OTL/ODBC and bulk operations with Oracle's CLOB)
• Example 108 (OTL/ODBC and bulk operations with MS SQL Server's TEXT)
• Example 109 (OTL/DB2-CLI and bulk operations with DB2's CLOB)

 
• Example 110 (Group data type override in SELECT, Oracle 7)
• Example 111 (Group data type override in SELECT, Oracle 8/8i)
• Example 112 (Group data type override in SELECT, MS SQL Server)

 
• Example 113 (OTL stream pooling, Oracle 8/8i)
• Example 114 (OTL stream pooling, OTL/ODBC)
• Example 115 (OTL stream pooling, DB2-CLI)

 
• Example 116 (otl_stream::set_flush(), Oracle 8/8i)
• Example 117 (otl_stream::set_flush(), OTL/ODBC)
• Example 118 (otl_stream::set_flush(), DB2-CLI)

 
• Example 119 (OTL_VALUE_TEMPLATE_ON, OTL_USER_DEFINED_STRING_CLASS_ON, Oracle 8/8i)
• Example 120 (OTL_VALUE_TEMPLATE_ON, OTL_USER_DEFINED_STRING_CLASS_ON,  ODBC / MS SQL Server)
• Example 121 (OTL_VALUE_TEMPLATE_ON, OTL_USER_DEFINED_STRING_CLASS_ON, DB2-CLI)

 
• Example 122 (Insert/Update/Select with Oracle 8i CLOB in stream mode)
• Example 123 (Insert/Update/Select with Oracle 8i BLOB in stream mode)
• Example 124 (Insert/Update/Select with MS SQL Server TEXT  in stream mode)
• Example 125 (Insert/Update/Select with  MS SQL Server IMAGE in stream mode)
• Example 126 (Insert/Update/Select with DB2 CLOB  in stream mode)
• Example 127 (Insert/Update/Select with DB2 BLOB in stream mode)
• Example 128 (Insert/Update/Select with MySQL LONGTEXT  in stream mode)
• Example 129 (Insert/Update/Select with  MySQL LONGBLOB in stream mode)

 
• Example 130 (Describe otl_stream's bind variables for Oracle 8i)
• Example 131 (Describe otl_stream's bind variables for MS SQL Server)
• Example 132 (Describe otl_stream's bind variables for DB2)

 
• Example 147 (backward compatibility with ODBC 2.5)

 
• Example 148 (otl_refcur_stream, basic)
• Example 149 (otl_refcur_stream, stored procedure)
• Example 150 (otl_refcur_stream, PL/SQL tables and reference cursors as parameters in stored procedure)
• Example 151 (otl_refcur_stream and otl_lob_stream, used together)

 
• Example 152 (get_stream_type)

 
• Example 153 (create_stored_proc_call)

 
• Example 154 (set_transaction_isolation_level, DB2-CLI)
• Example 155 (set_transaction_isolation_level, ODBC, MS SQL)

 
• Example 156 (OTL/OCI8/9, otl_connect::cancel())

 
• Example 157 (external logon, DB2-CLI)
• Example 158 (external logon, ODBC, MS SQL)

 
• Example 159 (otl_stream::clean(), cancelling SELECT's fetch sequence, Oracle 8i/9i )
• Example 160 (otl_stream::clean(), cancelling SELECT's fetch sequence, MS SQL Server)
• Example 161 (otl_stream::clean(), cancelling SELECT's fetch sequence, DB2 )
• Example 162 (otl_stream::clean(), cancelling REF CURSOR's fetch sequence, Oracle 8i/9i)
• Example 163 (otl_stream::clean(), cancelling Result Set's fetch sequence, MS SQL Server)

 
• Example 164 (otl_stream::clean(), cleaning the SELECT stream's error flags, Oracle 8i/9i )
• Example 165 (otl_stream::clean(), cleaning the SELECT stream's error flags, MS SQL Server )
• Example 166 (otl_stream::clean(), cleaning the SELECT stream's error flags, DB2 )
• Example 167 (otl_stream::clean(), cleaning the REF CURSOR otl_stream's error flags, Oracle 8i/9i )
• Example 168 (otl_stream::clean(), cleaning the Result Set stream's error flags, MS SQL Server )

 
• Example 169 (#define's OTL_DEFAULT_XXX_NULL_TO_VAL, Oracle 7/8/8i/9i)
• Example 170 (#define's OTL_DEFAULT_XXX_NULL_TO_VAL, MS SQL Server)
• Example 171 (#define's OTL_DEFAULT_XXX_NULL_TO_VAL, DB2)

 
• Example 172 (#define OTL_ORA_TEXT_ON, Oracle 8/8i/9i)
• Example 173 (#define OTL_ORA_TEXT_ON, Oracle 8/8i/9i)

 
• Example 202 (otl_datetime container with TIMESTAMP values down to microseconds in DB2)
• Example 203 (otl_datetime container with Datetime values down to milliseconds in MS SQL Server)
• Example 204 (otl_datetime container with TIMESTAMP values down to microseconds in Oracle 9i)

 
• Example 205 (otl_exception, derived from a base class, Oracle)
• Example 206 (otl_exception, derived from a base class, MS SQL Server)
• Example 207 (otl_exception, derived from a base class, DB2)

 
• Example 208 (lob_stream and SELECT INTO FOR  UPDATE, Oracle)

 
• Example 209 (MS SQL Server and VARBINARY column)

 
• Example 210 (Oracle and RAW column)

 
• Example 211 (OTL_ADD_NULL_TERMINATOR_STRING_SIZE,Oracle)
• Example 212 (OTL_ADD_NULL_TERMINATOR_STRING_SIZE,MS SQL Server)
• Example 213 (OTL_ADD_NULL_TERMINATOR_STRING_SIZE, DB2)
• Example 214 (OTL_ADD_NULL_TERMINATOR_STRING_SIZE, PostgreSQL)
• Example 215 (OTL_ADD_NULL_TERMINATOR_STRING_SIZE, MySQL)
• Example 216 (OTL_ADD_NULL_TERMINATOR_STRING_SIZE, FireBird Database)

 
• Example 217 (otl_stream::cancel() and DB2-CLI)
• Example 218 (otl_stream::cancel() and ODBC)

 
• Example 219 (otl_connect::change_password() and Oracle 8i/9i)

 
• Example 220 (std::string and LONGs, Oracle 7)
• Example 221 (std::string and LONGs, Oracle 8/8i/9i)
• Example 222 (std::string and CLOBs, Oracle 8/8i/9i)
• Example 223 (std::string and TEXTs, MS SQL Server)
• Example 224 (std::string and CLOBs, DB2)

 
• Example 232 (otl_stream::flush() and duplicate rows, Oracle 8, 8i, 9i)

 
• Example 233 (#define OTL_ORA_MAP_STRINGS_TO_CHARZ, Oracle 8, 8i, 9i)

 
• Example 234 (SQL statement label, Oracle 8,8i,9i)
• Example 235 (SQL statement label, MS SQL Server)
• Example 236 (SQL statement label, DB2)
  
  
  
• Example 237 (OTL/ODBC, accessing the system data dictionary via ODBC functions, MS SQL Server)
• Example 238 (OTL/DB2-CLI, accessing the system data dictionary via DB2 CLI functions, DB2)
• Example 239 (OTL/ODBC, accessing the system data dictionary via ODBC functions, MS SQL Server)
• Example 240 (OTL/ODBC, accessing the system data dictionary via ODBC functions, MS SQL Server)
  
  
• Example 241 (otl_datetime container with TIMESTAMP WITH LOCAL TIME ZONE, TIMESTAMP WITH TIME ZONE, Oracle 9i)
  
  
• Example 242 (64-bit integers (bigint), MS SQL Server, OTL/ODBC)
• Example 243 (64-bit integers (bigint), DB2, OTL/DB2-CLI)
  
  
• Example 244 (user defined string class and LONGs, Oracle 7)
• Example 245 (user defined string class and LONGs, Oracle 8/8i/9i)
• Example 246 (user defined string class and CLOBs, Oracle 8/8i/9i)
• Example 247 (user defined string class and TEXTs, MS SQL Server)
• Example 248 (user defined string class and CLOBs, DB2)
  
  
• Example 249 (OTL tracing, Oracle 7)
• Example 250 (OTL tracing, Oracle 8/8i/9i)
• Example 251 (OTL tracing, ODBC, MS SQL Server)
• Example 252 (OTL tracing, DB2 CLI)
  
  
• Example 253 (Explict bind variables in SELECT statement column definitions, Oracle 7)
• Example 254 (Explict bind variables in SELECT statement column definitions, Oracle 8/8i/9i)
• Example 255 (Explict bind variables in SELECT statement column definitions, ODBC, MS SQL Server)
• Example 256 (Explict bind variables in SELECT statement column definitions, DB2)
  
  
• Example 257 (64-bit signed integers, Oracle 7)
• Example 258 (64-bit signed integers, Oracle 8/8i/9i)
  
  
• Example 259 (#define OTL_THROWS_ON_SQL_SUCCESS_WITH_INFO, ODBC, MS SQL Server)
  
  
• Example 260 (OTL stream read iterator, simple SELECT, Oracle)
• Example 261 (OTL stream read iterator, reference cursor, Oracle)
• Example 262 (OTL stream read iterator, simple SELECT, MS SQL / ODBC)
• Example 263 (OTL stream read iterator, implicit result set, MS SQL / ODBC)
• Example 264 (OTL stream read iterator, simple SELECT, DB2 CLI)

Copyright © 1996-2009, Sergei Kuchin, email: skuchin@aceweb.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.