Please, send your questions and comments to firstname.lastname@example.org.
Oracle, Odbc and DB2-CLI Template Library, Version 4.0
Frequently Asked Questions
Q. OTL: Why does OTL return
incorrect integer values for 64-bit SQLite3?
A connection property needs to be specified
in order to let the SQLite ODBC driver know to map SQLite "int" to
ODBC SQL_BIGINT. See this link
for more detail.
OTL: Does OTL support SELECT statements
against tables that have clustered columnstore indexes?
What should you do when you get the following
[Microsoft][ODBC SQL Server Driver][SQL Server]Cursors
are not supported on a table which has a clustered columnstore
The otl_stream needs to be opened with otl_implicit_select, for
"SELECT ... FROM table_that_has_clustered_columnstore_index WHERE
Such SELECT statements are treated by the SQL Server ODBC driver as
a stored procedure that returns an implicit result set. For more
detail on columnstore indexes, see this
Q. OTL: Does
OTL work with UTF-8 and MySQL?
Yes, it does. The regular non-Unicode MyODBC driver
(a.k.a. ANSI ODBC driver) can handle UTF-8 strings correctly. Either
the corresponding property for the MyODBC driver needs to be set up
via ODBC Driver Manager in Windows, or in a text file (see more
detailed instructions for the unixodbc package) in Linux/Unix. An
alternative way to enable support for UTF-8 in MyODBC is to append
";CHARSET=utf8" to the string passed to otl_connect::rlogon(),
Also, see code example 759 for more
Q. OTL: Does OTL work with MS SQL
Server and Linux?
Yes, it does. The old way is to use the FreeTDS ODBC driver. FreeTDS
lacks any support for the TDS protocol for MS SQL Server 2005 (and
higher). A few years back, Microsoft released
a 64-bit SNAC / ODBC driver for Linux. It's not Open Source, though.
You'll have to install an RPM package for RHEL 5.x, or 6.x, or a
binary for non-RHEL Linux distributions. In order to compile OTL in
Linux for use with MS SQL Server in Windows, the following #defines
should be used:
#define OTL_ODBC_UNIX // Enables #include of ODBC header files,
but doesn't include Windows specific header files.
#define OTL_ODBC_MSSQL_2008 // Works with MS SQL 2008
(R2), 2012, and 2014
And the object files need to be linked with the UnixODBC driver
manager library: -lodbc.
Since this question & answer was added to the F.A.Q., Microsoft
SNAC (version 11 to be at the same level as SNAC for Windows,
I assume) for Linux, which is recommended over the first
SNAC for Linux.
When #define OTL_ENABLE_MSSQL_MARS
is enabled, it doesn't actually enable SNAC MARS.
"MultipleActiveResultSets=true" clause needs to be added to the
corresponding entry in odbc.ini.
OTL: Does OTL handle stored procedures that return multiple
implicit result sets (when used with ODBC, MS SNAC, or DB2 CLI)?
As of the OTL version 4.0.264, the answer is No. OTL
can only handle a stored procedure that returns one implicit result
set. Essentially, in the case of a stored procedure that returns an
implicit result set, the otl_stream
class handles the implicit result set as a SELECT statement. Such
stored procedures are limited to having only input parameters.
Q. OTL: how does OTL handle NULLs?
- Call otl_stream::is_null()
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 buffer
to any value in the case of NULL), the following #defines could
Q. OTL: Why don't my Oracle
otl_subscriber receive notifications on INSERT or UPDATE
or DELETE statements?
Issue category: OTL with
Oracle 11g R2 Continuous Query Notification (aka Database Change
Problem description: OTL
registered callback function does not get invoked when change
notification is sent by the database server.
1. The subscription entry is removed from
USER_CHANGE_NOTIFICATION_REGS table when the insert/update is done
on the registered table.
2. The client’s callback function does not get invoked when
change notification is sent by the db server. Use network data
analyzer such as WireShark to ensure that client receives data from
the server. In the example 585,
it uses port 5005. Capture the data traffic on the port using
tcpdump(Linux) or snoop(Solaris).
Note: If you see
ICMPmessage with destination unreachable, please check if the
firewall blocks the communication between the client and the server.
To solve the issue above, please ensure that OTL client uses the
same Oracle version as its database server. Both client and
the database server are also running on the same OS platform.
Example 585 was used as the OTL
client. The problem above was initially observed when OTL
client with 188.8.131.52 oracle instant client registered subscription
remotely to Oracle database server 184.108.40.206.The client was running
on Linux and the server was running on Solaris.
Use the solution above, the OTL client with 220.127.116.11 Oracle instant
client on Linux can subscribe and receive notification successfully
when it talks to Oracle database server 18.104.22.168 on Linux.
In summary, please check your client/server OS platform and its
Note: I did not observe
this problem on 11g R1, the client and the server work regardless
their OS platform. However, I saw difference in the
registration between R1 and R2. The “CALLBACK” in the
USER_CHANGE_NOTIFICATION_REGS are different between R1 and R2.
The notification message from the server also looks different.
Q. OTL: Why my T-SQL
stored procedure that has INSERT or UPDATE or DELETE statements
doesn't work with OTL?
INSERT/UPDATE/DELETE statеments inside T-SQL stored procedures
generate row [processed] counts (RPC). The RPCs get returned back to
the caller, in this case to the OTL/C++ code, as separate result
sets via MS SQL Native Client (SNAC). OTL doesn't handle such RPC
result sets, so they need to be turned off with "SET NOCOUNT ON"
statement in the stored procedure, for example:
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 a nested SELECT, for example:
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
procedure that returns an implicit result set. I don't know why the
database server does it, probably as an optimization technique.
"select dt from "
" (select "
" dateadd(day, :v<int>, getdate() "
" )dt ) xt ",
db, // connect object
Q. OTL: When I insert a new row into a table in
MySQL, I can't
SELECT it, what's going on?
If you're using a prepared SELECT statement in an otl_stream, and
keep executing / reusing the statement to get new rows, you need to
commit (call otl_connect::commit()) after the fetch sequence is
exhausted each time. The commit call will let your MySQL Server know
that your current read only transaction is finished, and the server
can start a new transaction, which will make newly inserted rows to
be visible to your SELECT statement. In other words, you need to
commit your SELECT statements in order to able to see new rows.
Q. OTL: How do I connect to
my database without creating an ODBC DSN?
Take a look this Web
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
developer's responsibility to wrap up OTL calls with mutex locks.
In general, database APIs do not guarantee thread safety.
passes a flag to the underlying database API only to let the API
know that the program 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 or Oracle
The answer is Yes. Use #define OTL_ORA10G, OTL_ORA10G_R2, or OTL_ORA11G.
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 data type 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. OTL supports UTF-16 string/CLOB/NCLOB bind
variables for ODBC, DB2-CLI, and Oracle. OTL also supports UTF-8
string/CLOB/NCLOB bind variables for Oracle. PostgreSQL ODBC
supports UTF-8 string values out of the box, which can be used
with OTL when the client character set is to UTF-8. OTL does not
support Unicode SQL statements.
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
Q. OTL: can it work with PostgreSQL 7.x / 8.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
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
Q. OTL/ODBC: can it work
The answer is Yes. Starting with OTL 3.2.4, OTL/ODBC is
compatible with MyODBC. For more detail on MyODBC see MyODBC for MySQL. #define OTL_ODBC_MYSQL should
be used with the default MySQL table type, because OTL turns off
transactional ODBC function calls in that case. When InnoDB type
tables are used with MyODBC 3.5, #define OTL_ODBC should be used.
Q. OTL/OCI8 and XA-connectivity: can they work
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
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
OTL 3.2.0 and higher support std::strings (see examples 72, 73, 74). Also, OTL supports STL-compliant
stream iterators: otl_input_iterator,
Q. What major C++ compilers are supported,
In order to complete the list, you are welcome to send me an email
with the name and the version of your C++ compiler, if it's not
already in the list. Also, OTL uses C++11 features that C++
compilers (like Visual C++, or GNU C++) support. See #define OTL_CPP_11_ON for more
- Sun C++ Workshop 6.x and higher
- GNU C++ 3.3.x and higher
- Visual C++ 8.0 (2005), 9.0 (2008), 10 (2010), 11 (2012)
- HP ANSI C++ (aCC) 1.x and higher
- AIX, Visual Age C++ 6.x and higher
- Borland C++ 5.x and higher
- Intel C++ 7.0, 8.0, 9.x, and higher (Windows, Linux)
- CLANG 3.x and higher
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 data types as far as NULLs
For more detail, see "Large
Objects and NULLs."
Q. How to read/write date&time information
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,
see 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 returns the
rows processed count of the constant SQL statement.Also,
returns a long int value, which is the rows processed count. The
rows processed count is defined for INSERT, UPDATE, and DELETE
statements. RPC for SELECT statements means rows fetched count. For any
other SQL statement, PL/SQL block, or a stored procedure call, the
count is always 0.
Q. Can OTL handle
std::strings that have NULL characters ('\0') in the middle?
Yes, it can. Only instead of char[XXX]
binding, raw[XXX] binding
needs to be used. Also, see code examples 747
(Oracle, OCI7), 748 (Oracle, OCI8 and
higher), 749 (DB2. DB2 CLI), 750 (MS SQL, SNAC).
Copyright © 1996-2018, Sergei Kuchin, email: email@example.com, firstname.lastname@example.org
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.