📄 otl2odbc.htm
字号:
<html>
<head>
<title>One-Page ODBC 2.5 API, ODBC Template Library
(OTL), Version 2.1</title>
</head>
<body>
<h1>One-Page ODBC 2.5 API, ODBC Template Library (OTL),
Version 2.1</h1>
<p><i>Sergei Kuchin, email:
<a href="mailto:skuchin@aceweb.com">skuchin@aceweb.com</a>,
<a href="mailto:skuchin@gmail.com">skuchin@gmail.comgmail</i>
<xmp>
Copyright (C) Sergei Kuchin, 1996, 1997, 1998, 1999
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.
</xmp>
<h1>Table of Contents</h1>
<ul>
<li><a href="#sec1">1. Introduction</a>
<li><a href="#sec11">1.1. SQL streams</a>
<li><a href="#sec2">2. <i>One-page</i> ODBC API</a>
<ul>
<li><a href="#sec21">2.1. Connect object</a>
<li><a href="#sec22">2.2. otl_stream class</a>
<ul>
<li><a href="#sec221">2.2.1. Stream bind variables declaration</a>
<li><a href="#sec222">2.2.2. otl_long_string class</a>
</ul>
<li><a href="#sec23">2.3. Exception handling</a>
<li><a href="#sec24">2.4. Constant SQL statement or stored procedure call</a>
<li><a href="#sec25">2.5. Examples</a>
<ul>
<li><a href="#sec251">Example 1 (with otl_stream class)</a>
<li><a href="#sec252a">Example 2a (with an Oracle stored procedure call)</a>
<li><a href="#sec252b">Example 2b (with an MS SQL Server stored procedure call)</a>
<li><a href="#sec253">Example 3 (with printf/scanf functions)</a>
<li><a href="#sec254a">Example 4a (with otl_stream class,
TIMESTAMP_STRUCT and Oracle)</a>
<li><a href="#sec254b">Example 4b (with otl_stream class,
TIMESTAMP_STRUCT and MS SQL Server)</a>
<li><a href="#sec255a">Example 5a (with otl_stream class, CTime class
and Oracle)</a>
<li><a href="#sec255b">Example 5b (with otl_stream class, CTime class
and MS SQL Server)</a>
<li><a href="#sec256">Example 6 (implicit SELECT/result set returned by
a stored procedure, MS SQL Server)</a>
<li><a href="#sec257a">Example 7a (with MS SQL Server's TEXT column)</a>
<li><a href="#sec257b">Example 7b (with MS SQL Server's IMAGE column)</a>
<li><a href="#sec257c">Example 7c (with Oracle's LONG column)</a>
<li><a href="#sec257d">Example 7d (with Oracle's LONG RAW column)</a>
<li><a href="#sec257e">Example 7e (with Oracle's CLOB column)</a>
<li><a href="#sec257f">Example 7f (with Oracle's LOB column)</a>
</ul>
</ul>
<li><a href="#sec3">3. STL-compliant iterators for otl_stream</a>
<ul>
<li><a href="#sec31">3.1. Example</a>
</ul>
<li><a href="#app_a">Appendix A. How to download the source code</a>
<li><a href="#app_b">Appendix B. OTL exception list</a>
</ul>
<h1><a name="sec1"> 1. Introduction</h1>
<p>
This document describes the ODBC Template Library,
Version 2.1 (OTL). The library introduces a <i>one-page</i> ODBC
API in the form of <a href="#sec11">SQL streams</a>.
</p>
<p>
OTL is a new kind of C++ libraries, similar to the <a
href="http://www.sgi.com/Technology/STL/">Standard Template
Library</a>. This kind of libraries is easy to use, since the user
needs only to include C++ header files which contain template classes
and inline functions. There is no need to link additional object
modules into C++ applications. The code instantiated from template
classes and inline functions is efficient and reliable in terms of
runtime performance and C++ strict type checking.
</p>
<p>
OTL automatically uses ODBC bulk operations (<a href="#sec11">SQL
streams</a> are buffered by definition) and has a higher runtime
performance than scalar operations. Runtime performance can be improved by
increasing the stream buffer size.
</p>
<p>
OTL provides an exception handling mechanism in the format of the <a
href="#sec23">otl_exception</a> class. This mechanism takes advantage
of C++ exception handling compared to coding database applications in
plain C. The user does not need to check out return codes after each
function call. The code, instantiated from the OTL templates and
inline functions is much nicer and cleaner in comparison with the
straight ODBC code or even with the MFC database classes.
</p>
<p>
Also, OTL integrates with the <a
href="http://www.sgi.com/Technology/STL/">Standard Template
Library</a> by means of <a href="#sec3">STL-compliant iterator
classes</a>. This feature is very powerful because it allows to
combine the power of <a
href="http://www.metabyte.com/~fbp/stl/readings.html">generic
programming</a> and a database backend into compact, reliable,
top performance and easy-to-maintain C++ database applications.
</p>
<p>
This version of OTL available for ODBC drivers, versions 2.5 or
higher. It was tested at least with ODBC drivers for MS SQL Server 6.5/7.0
and for Oracle 8.x. <a href="#sec25">Examples</a> for both Oracle 8.x
and MS SQL Server can found in chapter 2 of this manual.
<p>
The fact that OTL is available in source code adds more value to this
material -- folks new to ODBC can use the OTL source code to learn it
</p>
<p>
Besides the OTL for ODBC, there is <a href="otl_1pg.htm#">two
versions</a> of OTL for Oracle 7 and Oracle 8. They use native Oracle
APIs: OCI7 and OCI8. OTL ODBC was designed with Oracle OTL in mind, to
keep OTL applications portable across databases.
</p>
<h2><a name="sec11">1.1. SQL stream classes</h2>
<p>
OTL introduces the concept of SQL streams. The idea here is to combine
streams and SQL. Any SQL statement or stored procedure call can be
treated as a functional element with input/output parameters. There
are functions to put objects into a stream, that is, to assign values
to input variables of the SQL statement. Also, there are functions to
get objects from the stream, that is, to get values from output
variables of the SQL statement.
</p>
<center>
<img src="otlodbc.gif">
</center>
<p>
When values of all input variables of the functional element
are filled out then the element is executed. Resulting
values are assigned to the output variables right after the
execution. Sets of input and output variables are allowed to
overlap.
</p>
<p>
Logically, a SQL stream is a structured stream with input
and output rows. The format of the input row is defined by a
set of output variables of the stream. Similarly, the output
row is defined by input variables of the stream. When
objects are written into the stream, values are actually
assigned to the input variables. Likewise, when objects are
read from the stream, values are read from the output
variables of the stream.
</p>
<p>
SQL streams are similar to buffered files. A SQL statement or stored
procedure call is opened as an ordinary buffered file. The logic of
he SQL stream operations remains the same as the file operations with
the only exception -- the SQL stream has separate input and output
buffers which may overlap.
</p>
<p>
The SQL stream has a flush function for flushing its input buffer when
the buffer gets full and a collection of >> and << operators for
reading and writing object of different data types. The most important
advantage of the SQL streams is their unified interface to SQL
statements and stored procedure call of any kind. This means that the
application developer needs to remember just a few syntactical
constructs and function names which he already got familiar with when
he started working with C++ streams.
</p>
<p>
Inside the SQL stream there is a small parser for parsing <a
href="#sec221">declarations of bind variables</a> and their data
types. There is no need to declare C/C++ host variables and bind them
with placeholders by special bind function calls. All necessary
buffers are created dynamically inside the stream. The stream just
needs to be opened for reading input values and writing output values.
</p>
<p>
The OTL stream interface requires use of the OTL exceptions. This
means that potentially any OTL stream operation can throw an exception
of the <a href="#sec23">otl_exception</a> type. In order to intercept
the exception and prevent the program from aborting, wrap up the OTL
stream code with the corresponding try & catch block.
</p>
<h1><a name="sec2">2. <i>One-page</i> ODBC API</h1>
<h2><a name="sec21">2.1. Connect object</h2>
<p>
This class encapsulates the ODBC <i>connect</i>
functions: connect, commit, roll back, etc. In other words,
otl_connect is the class for creating "connect" objects.
</p>
<a name="ref002"></a><xmp>
class otl_connect{
public:
</xmp>
<ul>
<li><a name="ref006"></a>Initialize ODBC program. This function needs to be called only once
before connecting to the database for the first time. The function is
static in class.
<xmp>
static void otl_initialize(void);
</xmp>
<li>Set timeout (in seconds) for the connect object. If timeout is set then any SQL
statement may time out.
<xmp>
void set_timeout(const int atimeout=0);
</xmp>
<li>Set cursor type for all subsequent SELECT statements open under
the connect object
<xmp>
void set_cursor_type(const int acursor_type=0);
</xmp>
<p>
The following cursor types are available:
</p>
<ul>
<li>SQL_CURSOR_FORWARD_ONLY
<li>SQL_CURSOR_STATIC
<li>SQL_CURSOR_KEYSET_DRIVEN
<li>SQL_CURSOR_DYNAMIC
</ul>
<br><br>
<li><a name="set_max"></a>Set Max Size for operations with LONGVARCHAR and LONGVARBINARY datatypes
<xmp>
void set_max_long_size(const int amax_size);
</xmp>
<li>Get Max Size for LONGVARCHAR and LONGVARBINARY datatypes
<xmp>
int get_max_long_size(void);
</xmp>
<li>Create "connect" object.
<xmp>
otl_connect();
</xmp>
<li>Create "connect" object and connect to data source using the
"connect_str" connect string. Both Oracle and MFC styles of connect
strings are supported:<br><br>
<ul>
<li> Oracle: "USERNAME/PASSWORD@DSN"
<li> MFC: "DSN=value;UID=value;PWD=value"
</ul>
<p>
Both styles are case insensitive
</p>
<xmp>
otl_connect(const char* connect_str);
</xmp>
<li>Destructor
<xmp>
~otl_connect();
</xmp>
<li><a name="ref007"></a>Concurrent logon; ODBC application is allowed to have more than one
concurrent logon. Returns 1 on success, 0 on failure. The format of
connect string is the same as connect(const char* connect_str).
<xmp>
int rlogon(const char* connect_str);
</xmp>
<li>Set connect option: "option" is the same as 2nd parameter in
SQLSetConnectOption, "param" is the same as 3rd parameter in
SQLSetConnectOption. For more detail, see the ODBC programmer's
Guide.
<xmp>
void set_option(UWORD option, UDWORD param);
</xmp>
<li><a name="ref009"></a>Disconnect from / log off data source. Returns 1 on success, 0 on
failure.
<xmp>
int logoff(void);
</xmp>
<li>Commit current transaction. Returns 1 on success, 0 on failure.
<xmp>
int commit(void);
</xmp>
<li>Roll back current transaction. Returns 1 on success, 0 on failure.
<xmp>
int rollback(void);
</xmp>
</ul>
<xmp>
};
</xmp>
<h2><a name="sec22">2.2. otl_stream class</h2>
<p>
OTL introduces the otl_stream class which is the actual implementation
of the <a href="#sec11">SQL Stream</a> concept. It is unified for any
SQL statement or stored procedure call which potentially may have
input/output bind variables (placeholders).
</p>
<p>
An OTL stream needs to have at least one <a href="#sec221">bind
variable</a>. See <a href="#sec24">2.4.</a> for more information on
how to execute a constant SQL statement or stored procedure call.
</p>
<xmp>
class otl_stream{
public:
</xmp>
<ul>
<li><a name="ref001"></a>General conctructor. SQL statement is parsed, all host input and
output host variables are automatically allocated and bound. This
constructor allows the user to use <a href="#sec221">extended
placeholder declarations</a>.
<xmp>
otl_stream(
short arr_size, // host array size
const char* sqlstm, // SQL statement
otl_connect& db, // connect object
const int implicit_select=otl_explicit_select
// "implicit" SELECT indicator. It defaults to "explicit"
// SELECT meaning that the SQL statement statement in the stream
// is "SELECT ...". "Implicit" SELECT means that a result set
// is returned by a stored procedure implicitly.
);
</xmp>
<li>Default constructor
<xmp>
otl_stream();
</xmp>
<li>Destructor
<xmp>
~otl_stream();
</xmp>
<li><a name="ref005"></a>Test if all data has been already read from the stream
<xmp>
int eof(void);
</xmp>
<li><a name="ref010"></a>Flush stream's output buffer. It actually means to execute the SQL
statement as many times as rows entered to the output buffer. The
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -