otl2.htm

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

</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 otl_null n); 
  // write Oracle 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>%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 PL/SQL block may have placeholders which are
usually connected with the corresponding bind variables in the
program. In Pro*C, the user needs to declare such variables directly in
the program. OTL provides the same functionality in another way. There
is a small parser which parses a SQL statament or PL/SQL block
declaration and allocates corresponding bind variables dynamically
inside the stream.
</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>char[length] (length >= 3 & length <= 32545)
</ul>

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

<h4>Examples</h4>
<p>
Here is some examples:
</p>
<pre>

 begin
   :rc&lt;int,out&gt; := my_func(:salary&lt;float,in&gt;,  
                           :ID&lt;int,inout&gt;, 
                           :name&lt;char[32],out&gt;
                          );
 end;
</pre>
<p>
Invoke the my_func function; return the function result into
the :rc variable; the function has three parameters: salary
(input), ID (iput/output), name (output)
</p>
<pre>

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

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

   insert into tab1 values( :f1&lt;double&gt;, :f2;&lt;char[32]&gt;, :f3&lt;int&gt; )

</pre>
<p>
Insert row { :f1(double), :f2(string), :f3(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(:f1< double >, :f2< char [ 32 ] > , :f3< int>);
  :rc< int, out > := ...;
</xmp>

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

<p>
In case of Oracle 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 Logon Descriptor Area (Oracle 7)
or out of OCI error handle (Oracle 8)
<xmp>
otl_exception(Lda_Def& lda,const char* sqlstm=0);
  
                     or 

otl_exception(OCIError* errhp,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>error message buffer
<xmp>
unsigned char msg[1000]; 
</xmp>
<li>error code
<xmp>
int code;
</xmp>
<li>SQL statement or PLSQL block that caused the error
<xmp>
 unsigned char* stm_text;
</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 :v1<char[33]>",
               db // connect object
             );
  char name[33];
  int  code;

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

 }catch(otl_exception& p){ // intercept exception

  cerr<<p.code<<endl; // print out error code
  cerr<<p.msg<<endl; // print out error message
  cerr<<p.stm_text<<endl; // print out SQL that caused the error

 }
</xmp>


<h2><a name="sec24">2.4. Constant SQL statement or PL/SQL block</h2>
<p>
SQL statement or PL/SQL block is considered to be constant if it does
not have any bind variables. OTL has a static (in class) function to
execute constant statements or blocks, e.g.
</p>
<xmp>
  otl_cursor::direct_exec
   (db, // connect object
    "create table test_tab(f1 number, f2 varchar2(30))"
    );  // create table
</xmp>
<p>
otl_cursor is one of OTL internal classes. There is another format of
the direct_exec function call:
</p>
<xmp>
  otl_cursor::direct_exec
   (db, // connect object
    "drop table test_tab", // SQL statement or PL/SQL block
    otl_exception::disabled // disable OTL exceptions,
                            // in other words, ignore any
                            // Oracle error
   ); // drop table

</xmp>

<h2><a name="sec25">2.5. Examples</h2>

<h3>Example 1 (with otl_stream class)</h3>
<h4>Source code</h4>
<pre>

<font color="#FF0000">#include &lt;iostream.h&gt;</font>
<font color="#FF0000">#include &lt;stdio.h&gt;</font>
<font color="#FF0000">#include &lt;otl.h&gt;</font>

<a href="#ref002">otl_connect</a> db; <i><font color="#804000">// connect object</font></i>

<a name="ref101"></a><h4><font color="#0000A0">void</font> insert()</h4>
<i><font color="#804000">// insert rows into table</font></i>
{ 
 <a href="#ref001">otl_stream</a> o(50, <i><font color="#804000">// buffer size</font></i>
	      <font color="#008080">"insert into test_tab values(:f1&lt;float&gt;,:f2&lt;char[31]&gt;)"</font>, 
	         <i><font color="#804000">// SQL statement</font></i>
	      db <i><font color="#804000">// connect object</font></i>
	     );
 <font color="#0000A0">char</font> tmp[32];

 <font color="#0000A0">for</font>(<font color="#0000A0">int</font> i=1;i&lt;=100;++i){
  sprintf(tmp,<font color="#008080">"Name%d"</font>,i);
  o<a href="#ref003">&lt;&lt;</a>(<font color="#0000A0">float</font>)i<a href="#ref003">&lt;&lt;</a>tmp;
 }
}

<a name="ref102"></a><h4><font color="#0000A0">void</font> select()</h4>
{ 
 <a href="#ref001">otl_stream</a> i(50, <i><font color="#804000">// buffer size</font></i>
	      <font color="#008080">"select * from test_tab where f1&gt;=:f&lt;int&gt; and f1&lt;=:f*2"</font>,
	         <i><font color="#804000">// SELECT statement</font></i>
	      db <i><font color="#804000">// connect object</font></i>
	     ); 
   <i><font color="#804000">// create select stream</font></i>
 
 <font color="#0000A0">int</font> f1;
 <font color="#0000A0">char</font> f2[31];

 i<a href="#ref003">&lt;&lt;</a>8; <i><font color="#804000">// assigning :f = 8</font></i>
   <i><font color="#804000">// SELECT automatically executes when all input variables are</font></i>
   <i><font color="#804000">// assigned. First portion of out rows is fetched to the buffer</font></i>

 <font color="#0000A0">while</font>(!i.<a href="#ref005">eof()</a>){ <i><font color="#804000">// while not end-of-data</font></i>
  i<a href="#ref004">&gt;&gt;</a>f1<a href="#ref004">&gt;&gt;</a>f2;
  cout&lt;&lt;<font color="#008080">"f1="</font>&lt;&lt;f1&lt;&lt;<font color="#008080">", f2="</font>&lt;&lt;f2&lt;&lt;endl;
 }
 
 i<a href="#ref003">&lt;&lt;</a>4; <i><font color="#804000">// assigning :f = 4</font></i>
   <i><font color="#804000">// SELECT automatically re-executes when all input variables are</font></i>
   <i><font color="#804000">// assigned. First portion of out rows is fetched to the buffer</font></i>

 <font color="#0000A0">while</font>(!i.<a href="#ref005">eof()</a>){ <i><font color="#804000">// while not end-of-data</font></i>
  i<a href="#ref004">&gt;&gt;</a>f1<a href="#ref004">&gt;&gt;</a>f2;
  cout&lt;&lt;<font color="#008080">"f1="</font>&lt;&lt;f1&lt;&lt;<font color="#008080">", f2="</font>&lt;&lt;f2&lt;&lt;endl;
 }

}

<h4><font color="#0000A0">int</font> main()</h4>
{
 <a href="#ref006">otl_connect::otl_initialize()</a>; <i><font color="#804000">// initialize OCI environment</font></i>
 <font color="#0000A0">try</font>{

  db.<a href="#ref007">rlogon</a>(<font color="#008080">"scott/tiger"</font>); <i><font color="#804000">// connect to Oracle</font></i>

  <a href="#sec24">otl_cursor::direct_exec</a>
   (
    db,
    <font color="#008080">"drop table test_tab"</font>,
    <a href="#ref008">otl_exception::disabled</a> <i><font color="#804000">// disable OTL exceptions</font></i>
   ); <i><font color="#804000">// drop table</font></i>

  <a href="#sec24">otl_cursor::direct_exec</a>
   (
    db,
    <font color="#008080">"create table test_tab(f1 number, f2 varchar2(30))"</font>
    );  <i><font color="#804000">// create table</font></i>

  <a href="#ref101">insert()</a>; <i><font color="#804000">// insert records into table</font></i>
  <a href="#ref102">select()</a>; <i><font color="#804000">// select records from table</font></i>

 }

 <font color="#0000A0">catch</font>(<a href="#sec23">otl_exception</a>& p){ <i><font color="#804000">// intercept OTL exceptions</font></i>
  cerr&lt;&lt;<a href="#sec23">p.msg</a>&lt;&lt;endl; <i><font color="#804000">// print out error message</font></i>
  <font color="#0000A0">if</font>(p.stm_text)
   cerr&lt;&lt;<a href="#sec23">p.stm_text</a>&lt;&lt;endl; <i><font color="#804000">// print out SQL that caused the error</font></i>
 }

 <a href="#ref009">db.logoff()</a>; <i><font color="#804000">// disconnect from Oracle</font></i>

 <font color="#0000A0">return</font> 0;