Examples

• Examples by database type

• Oracle 7 / OCI
  
• Oracle 8,8i / OCI , Oracle 8i Unicode/OCI
  
• Oracle 9i / OCI, Oracle 9i Unicode / OCI
• Oracle 10g / OCI, Oracle 10g Unicode / OCI
• Oracle 8/8i/9i/10g Unicode / ODBC
  
• DB2 / CLI, DB2 Unicode / CLI
  
• MS SQL Server / ODBC, MS SQL Server Unicode / ODBC
• Sybase SQL Server / ODBC,
• MySQL / MyODBC 3.5, MySQL / MyODBC 2.5,
  
• PosgtreSQL / ODBC
• 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, 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.