Please, send your questions and comments to skuchin@gmail.com.

Q. OTL: Why when MySQL ODBC 8.0.x driver is used on a SELECT statement that returns a BLOB in OTL LOB stream mode, the SELECT doesn't return the BLOB's value?

  It's a bug in the MySQL ODBC driver. Here's a link to a workaround.


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.
 

Q. OTL: Does OTL support SELECT statements against tables that have clustered columnstore indexes?

  What should you do when you get the following error message:

   [Microsoft][ODBC SQL Server Driver][SQL Server]Cursors are not supported on a table which has a clustered columnstore index.

The otl_stream needs to be opened with otl_implicit_select, for example:

   otl_stream my_stream(100,
                        "SELECT ... FROM table_that_has_clustered_columnstore_index WHERE ...",
                        db,
                        otl_implicit_select);

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 link.

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(), for example:
  db.rlogon("UID=scott;PWD=tiger;DSN=mysql51;CHARSET=utf8");

Also, see code example 759 for more detail.

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
 #include <otlv4.h>
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 released another 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.

Q. 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?
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 Notification).

Problem description: OTL registered callback function does not get invoked when change notification is sent by the database server.

Symptoms:

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.

Solutions:

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.

For example:

Example 585 was used as the OTL client.  The problem above was initially observed when OTL client with 11.2.0.2 oracle instant client registered subscription remotely to Oracle database server 11.2.0.1.The client was running on Linux and the server was running on Solaris.

Use the solution above, the OTL client with 11.2.0.1 Oracle instant client on Linux can subscribe and receive notification successfully when it talks to Oracle database server 11.2.0.1 on Linux.

In summary, please check your client/server OS platform and its Oracle version.

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:

    CREATE PROCEDURE my_proc
    ...
    AS
    SET NOCOUNT ON
    ...

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:
 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 procedure that returns an implicit result set. I don't know why the database server does it, probably as an optimization technique.


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 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 developer's responsibility to wrap up OTL calls with mutex locks. 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 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 11g?

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 more detail.

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 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 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 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?

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 detail.

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 are concerned?

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, 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. 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).