otl_stream s(1,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
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
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?
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,
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).
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.