otl2odbc.htm

ISO_C++:C++_OTL开发文档

stream is automatically flushed when the buffer gets full.
<xmp>
void flush(void); 
</xmp>
<li>Clean up buffer without flushing it.
<xmp>
void clean(void);
</xmp>
<li>Rewind stream
<xmp>
void rewind(void);
</xmp>
<li>Test if NULL was fetched from the stream
<xmp>
int is_null(void);
</xmp>
<li>Set "auto-commit" flag. When the buffer is flushed, current
transaction is automatically commited, if the flag is set. By default,
the flag is set. In order to prevent current transaction from
"auto-committing", unset the flag using this function.<br>
This auto-commit flag has nothing to do with the database auto-commit
mode. This auto-commit is otl_stream specific. If it is more
convenient to have "auto-commit off" by default then it makes sense to
derive another stream class from otl_stream and unset the flag in
constructors of the derived class.
<xmp>
void set_commit(int auto_commit=0);

</xmp>
<li>Open stream
<xmp>
void open(
	  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>Close stream
<xmp>
void close(void);
</xmp>
<li>Test if the stream was opened okay
<xmp>
int good(void);
</xmp>
<li><a name="ref004"></a>Read objects from stream
<xmp>
otl_stream& operator>>(char& c);
otl_stream& operator>>(unsigned char& c);
otl_stream& operator>>(char* s);
otl_stream& operator>>(unsigned char* s);
otl_stream& operator>>(int& n);
otl_stream& operator>>(unsigned& u);
otl_stream& operator>>(short& sh);
otl_stream& operator>>(long int& l);
otl_stream& operator>>(float& f);
otl_stream& operator>>(double& d);
otl_stream& operator>>(otl_long_string& s);
otl_stream& operator>>(TIMESTAMP_STRUCT& t);
otl_stream& operator>>(CTime& t);
</xmp>
<li><a name="ref003"></a>Write objects into stream
<xmp>
otl_stream& operator<<(const char c);
otl_stream& operator<<(const unsigned char c);
otl_stream& operator<<(const char* s);
otl_stream& operator<<(const unsigned char* s);
otl_stream& operator<<(const int n);
otl_stream& operator<<(const unsigned u);
otl_stream& operator<<(const short sh);
otl_stream& operator<<(const long int l);
otl_stream& operator<<(const float f);
otl_stream& operator<<(const double d);
otl_stream& operator<<(const TIMESTAMP_STRUCT& t);
otl_stream& operator<<(const CTime& t);
otl_stream& operator<<(const otl_long_string& s);
otl_stream& operator<<(const otl_null n); 
  // write NULL into stream. otl_null is a dummy class
  // which has only a empty default constructor. The only purpose
  // of creating this class was providing a function to write
  // NULL into the database
</xmp>
<li><a name="ref011"></a>C-style printf/scanf functions
<xmp>
void printf(const char* fmt,...);
void scanf(const char* fmt,...);
</xmp>
<p>
The following format specifiers are supported:
</p>
<ul>
<li><b>%d</b>  -- int
<li><b>%u</b>  -- unsigned 
<li><b>%ld</b> -- long int
<li><b>%f</b>  -- float
<li><b>%lf</b> -- double
<li><b>%c</b>  -- char
<li><b>%t</b>  -- ODBC timestamp, requires TIMESTAMP_STRUCT or CTime class
<li><b>%s</b>  -- string
<li><b>%N</b>  -- specifier for writing NULL into streams
</ul>
</ul>
<xmp>
};
</xmp>

<h2><a name="sec221">2.2.1. Stream bind variables declaration</h2>

<p>
This section explains in detail how to declare bind variables (or
extended placeholders) in the SQL streams.
</p>
<p>
A SQL statement or stored procedure call may have placeholders which
are usually connected with the corresponding bind variables in the
program. OTL has a small parser which parses a SQL statament or stored
procedure call and allocates corresponding bind variables dynamically
inside the stream.
</p>
<p>
In OTL, placeholder names represent positions starting with <b>:1</b>,
<b>:2</b>, etc.
</p>
<p>
The following data types for extneded placeholder declarations are
available:
</p>
<ul>
<li>int
<li>unsigned
<li>short
<li>long -- (long integer)
<li>float
<li>double
<li>timestamp -- ODBC timestamp, requires TIMESTAMP_STRUCT or CTime class
<li>char[length] (length >= 3 and length <= datasource specific value)
<li>varchar_long -- corresponds to SQL_LONGVARCHAR (see <a href="#sec222">2.2.2.</a>)
<li>raw_long -- corresponds to SQL_LONGVARBINARY (see <a href="#sec222">2.2.2.</a>)
</ul>

<p>
For stored procedure calls, special qualifiers are introduced to distinguish
between input and output variables:
</p>
<ul>
<li>in    -- input variable
<li>out   -- output variable
<li>inout -- input/output variable
</ul>

<h4>Examples</h4>
<p>
Here is some examples:
</p>
<pre>
   {
    call :1&lt;int,out&gt; := my_func(:2&lt;float,in&gt;,  
                                :3&lt;int,inout&gt;, 
                                :4&lt;char[32],out&gt;
                               )
   }
</pre>
<p>
Invoke the my_func function; return the function result into
the :1 variable; the function has three parameters: <b>:1</b>
(input), <b>:2</b> (iput/output), <b>:3</b> (output)
</p>
<pre>

   select * from tab1 where f1 &gt; :1&lt;double&gt;

</pre>
<p>
Select all columns from the tab1 table where f1 is greater
than <b>:1</b>
</p>
<pre>

   insert into tab1 values( :1&lt;double&gt;, :2&lt;char[32]&gt;, :3&lt;int&gt; )

</pre>
<p>
Insert row { <b>:1</b>(double), <b>:2</b>(string), <b>:3</b>(integer) } into the tab1
table.
</p>
<p>
In the extended placeholder declaration, spaces in the data type
section and in the access qualifier section ARE NOT allowed. The
following code is invalid:
</p>
<xmp>
  insert into tab1 values(:1< double >, :2< char [ 32 ] > , :3< int>);
  :1< int, out > := ...;
</xmp>


<h2><a name="sec222">2.2.2. otl_long_string class</h2>
<p>
This class represents a data container for SQL_LONGVARCHAR and
SQL_LONGVARBINARY datatypes. These two datatypes are defined in ODBC
to map them to the TEXT/IMAGE datatypes in MS SQL Server 6.5/7.0,
LONG/LONG RAW and CLOB/LOB in Oracle 8.x. Respectively, OTL has
varchar_long and raw_long datatypes for defining <a
href="#sec221">extended placeholders</a> which get bound to the
SQL_LONGVARCHAR/SQL_LONGVARBINARY table columns.
</p>
<pre>
class otl_long_string{
</pre>
<ul>
<li>Class constructor. It allocates a block of memory according to the
specified size <i>str_size</i>.
<xmp>
 otl_long_string(const int str_size=32760)
</xmp>
<li>Class destructor
<xmp>
 ~otl_long_string()
</xmp>
<li> <a name="set_len"></a>Set string's dynamic length
<xmp>
void set_len(const int len=0)
</xmp>
<li> Get string's dynamic length
<xmp>
 int len()
</xmp>
<li>[] operator for accessing individual elements of the long string.
<xmp>
 unsigned char& operator[](int ndx)
</xmp>
</ul>
<pre> 
};
</pre>
<p>
Before writing the string to a stream, a dynamic length of the string
needs to be set by calling set_len() function.  When reading the
string from a stream, the dynamic length field is updated with an
actual string length.  It is not guaranteed that for "varchar_long" on
the output otl_long_string is null terminated: always check the length
of the string. Besides, before opening a stream with such columns the
<a href="#set_max">otl_connect::set_max_long_size()</a> function needs
to be called in order to set the maximum size of <i>long</i> columns
for the connect object. See example <a href="#sec257a">7a</a> for more
detail.
</p>


<h2><a name="sec23">2.3. Exception handling</h2>

<p>
In case of database failure or inconsistent use of SQL streams,
exceptions of the otl_exception type are raised by the library
functions. The main advantage of using this exception handling
mechanism is that exceptions can be processed in one catch block,
instead of checking return codes from every library function call.
</p>
<xmp>
class otl_exception{
public:
</xmp>
<ul>
<li><a name="ref008"></a>This <i>enum</i> defines two constants which may be used in the
<a href="#sec24">direct_exec</a> function
<pre>
enum{ disabled, enabled };
</pre>
<li>Create exception out of either environment or database or
statement handle
<xmp>
otl_exception(HENV henv,HDBC hdbc,HSTMT hstmt,const char* sqlstm=0);
</xmp>
<li>Create exception from amsg, acode and sqlstm
<xmp>
otl_exception(const char* amsg,const int acode,const char* sqlstm=0);
</xmp>
<li>Copy constructor
<xmp>
otl_exception(const otl_exception& p);
</xmp>
<li>Default constructor
<xmp>
 otl_exception();
</xmp>
<br>
<hr size=3>
<br>
<li>native error message buffer
<xmp>
unsigned char msg[1000]; 
</xmp>
<li>native error code
<xmp>
int code;
</xmp>
<li>SQL statement or stored procedure call that caused the error
<xmp>
 unsigned char* stm_text;
</xmp>
<li>SQLSTATE message buffer
<xmp>
unsigned char sqlstate[1000]; 
</xmp>

</ul>


<xmp>
};

</xmp>

<h3>Example</h3>

<xmp>
 try{

  otl_stream s(50, // fetch 50 rows per one fetch operation
	      "select state_name, state_code "
              "from state "
               "where state_name like :1<char[33]>",
               db // connect object
             );
  char name[33];
  int  code;

  s<<"M%";
  while(!s.eof)){
   s>>name>>code;
   cout<<"State="<<name<<", Code="<<code<<endl;