# Oracle, Odbc and DB2-CLI Template Library, Version 4.0

# Frequently Asked Questions

Please, send your questions and comments to skuchin@ispwest.com, skuchin@gmail.com
Q. OTL: how does OTL handle NULLs?

- Call otl_stream::is_null() to check whether the value that was just read from the stream is NULL or not.
- Use template class otl_value<T>.
- If there is a need to set the output variable to a value when NULL is fetched (by default, OTL does not set the output byffer to any value in the case of NULL), the following #defines could be enabled:

- OTL_DEFAULT_NUMERIC_NULL_TO_VAL
- OTL_DEFAULT_DATETIME_NULL_TO_VAL
- OTL_DEFAULT_STRING_NULL_TO_VAL
- OTL_DEFAULT_CHAR_NULL_TO_VAL
- OTL_USER_DEFINED_STRING_CLASS_DEFAULT_NULL_TO_VAL

Q. OTL: why nested queries with bind variables do not work in MS SQL Server?
Try to add otl_implicit_select right after the otl_connect parameter, when you are opening the nested SELECT:

     otl_stream s(1,
                  "select dt from "
                  "  (select  "
                  "     dateadd(day, :v<int>, getdate() "
                  "  )dt ) xt ",
                  db, // connect object
                  otl_implicit_select
                 );

Problem is that SQLPrepare() doesn't return any SELECT output column descriptors (OTL exception: Column: 0<UNKNOWN>), and it looks like the nested SELECT gets converted into a temporary stored procedure, which means you have to specify that it is a stored proc that returns an implicit result set. I don't know why the database server does it, probably as an optimization technique.
Q. OTL: How do I connect to my database without creating an ODBC DSN?
Take a look this Web site.
Q. OTL: is it thread safe?

OTL does not set any mutex locks or critical sections. In case if each thread has its own otl_connect object, there is no need for mutex locks. In case of sharable connections, it is the devloper's responsibility to wrap up OTL calls with mutexes. In general, database APIs do not guarantee thread safety. otl_connect::otl_initialize passes a flag to the underlying database API only to let the API know that the progam is running in a multi-threaded mode.

Q. OTL: does it work with SQLite?

Yes, it does. OTL/ODBC works with SQLite. See the following examples for detail.

Q. OTL: does it support Oracle 10g?

The answer is Yes. Use #define OTL_ORA10G, OTL_ORA10G_R2

Q. OTL: does it work with SAP DB?

Yes, it does. OTL/ODBC works with SAP DB. Fore more detail, see OTL examples for SAP DB.

Q. OTL: does it support CLOBs/BLOBs in PL/SQL?

The answer is: it depends. You need to keep in mind that "CLOB/BLOB" as a PL/SQL datatype is a locator of a large object, not a value. That is, the object itself needs to be created first, its locator needs to be retrieved from the database into C++, and only then the locator can be passed into PL/SQL. Also, see code example 378.

Q. OTL: does it support Unicode?

The answer is Yes. See OTL examples and the manual.

Q. OTL: does it support Oracle 9i?

The answer is Yes. Starting with version 4.0.0, OTL introduces #define OTL_ORA9I that is dedicated to Oracle 9i. In OTL 4.0.0 itself, it's not much more than a synonym for OTL_ORA8I, but in subsequent releases, when OTL starts using OCI9 specific features, only OTL_ORA9I should be used. See "what's new in OTL 4" for more detail.

Q. OTL: can it work with Interbase 6.x?

The answer is Yes. Starting with version 4.0.0, OTL supports an Open Source ODBC driver for Interbase. See "what's new in OTL 4" for more detail.

Q. OTL: can it work with PostgreSQL 7.x?

The answer is Yes. Starting with version 4.0.0, OTL supports PostgreSQL via ODBC. See "what's new in OTL 4" for more detail.

Q. OTL: can it work with DB2, Call Level Interface (CLI)?

The answer is Yes. Starting with version 3.2.7, there is a new flavor of OTL: OTL/DB2-CLI. OTL/DB2-CLI uses DB2's native database API: the Call Level Interface. DB2-CLI is 99.5% compatible with the ODBC 3.x specification, plus extensions. The OTL header file #includes CLI's native header files and can be used with DB2-CLI's native object libraries in Windows or Unix (primarily AIX) platforms.

Q. OTL/ODBC: can it work with MyODBC/MySQL?

The answer is Yes. Starting with version 3.2.4, OTL/ODBC is compatible with MyODBC, and the following #define was introduced to do just that: #define OTL_ODBC_MYSQL. For more detail see also MyODBC for MySQLThe new #define turns off transactional ODBC function calls, since MySQL does not support transactions [yet].

Q. OTL/OCI8 and XA-connectivity: can they work together?

The answer is Yes. When an XA type connection is established, xaoEnv() and xaoSvcCtx() functions need to be invoked in order to get OCI8 compatible environment and service context handles. After that, otl_connect::rlogon() can be called to create an OTL connection from the handles. It is the same rlogon() that is used with Pro*C 8.x. How to use this type of rlogon() function, see example 59 for more detail.

Q. Are C++ strings (std::string) supported, and how is OTL integrated with the Standard Template Library (STL)?

OTL 3.2.0 and higher support std::strings (see examples 72, 73, 74). Also, OTL supports STL-compliant stream iterators: otl_input_iterator, otl_output_iterator.

Q. What major C++ compilers are supported, anyway?

- Sun C++ Workshop 5.x/6.0, Forte C++ (Sun C++ WS 6.1 and higher)
- GNU C++ 2.9x.x, 3.0.x, 3.1.x, 3.2.x., 3.3.x, 3.4.x, 4.0.x
- VC++ 6.0, VC++ 7.1, VC++ 8.0
- HP ANSI C++ (aCC) 1.x
- AIX, xlC 4.x and higher
- C++ in OSF Alfa Unix and Oracle 7.3.4
- Borland C++ 5.x
- KAI C++ 3.x and higher (multi-platform)
- Intel C++ 7.0, 8.0 (Windows, Linux)

In order to complete the list, you are welcome to send me an email with the name of your C++ compiler, if it's not already in the list.

Q. Is there a way to read / write large BLOBs without having to allocate large buffers in memory?

OTL 3.1.4 supports stream mode for Oracle 8 CLOBs/BLOBs. Fore more detail, see examples 56, 57.

Q. Is there a way to call a stored procedure which takes PL/SQL tables as arguments ?

OTL 3.1 supports PL/SQL table via otl_streams and special template data containers.Fore more detail, see examples 49, 50, 51, 52.

Q. Will you stick with email delivery of your code, or do you intend to put it on your website later ?

From now on, the OTL header file is available for download.

Q. What is different in behavior of Large Objects (LOBs) compared to other datatypes as far as NULLs are concerned?

For more detail, see "Large Objects and NULLs."

Q. How to read/write date&time informarion from/to the otl_stream?

The answer is: use the otl_datetime data container, which was introduced in OTL 3.1.26. For more detail, se examples 38, 39, 40.
Bottom line: Oracle DATE, or, say, MS SQL Server DATETIME, can be read/written with the use of otl_datetime variables.

Q. How to get the rows processed count after the SQL statement is finished?

The direct_exec() function returns a long int value, which, if it is >= 0, it retunrs the rows processed count of the constant SQL statement.Also, otl_stream::get_rpc() returns a long int value, which is the rows processed count. The rows processed count is defined for INSERT, UPDATE, and DELETE statements. For any other SQL statement, PL/SQL block, or a stored procedure call, the count is always 0.

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.