📄 otl2odbc.htm
字号:
(
db,
<font color="#008080">"drop table test_tab2"</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_tab2(f1 int, f2 datetime)"</font>
); <i><font color="#804000">// create table</font></i>
<a href="#ref101b">insert()</a>; <i><font color="#804000">// insert records into table</font></i>
<a href="#ref102b">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<<<a href="#sec23">p.msg</a><<endl; <i><font color="#804000">// print out error message</font></i>
cerr<<<a href="#sec23">p.code</a><<endl; <i><font color="#804000">// print out error code</font></i>
cerr<<<a href="#sec23">p.sqlstate</a><<endl; <i><font color="#804000">// print out SQLSTATE message</font></i>
<font color="#0000A0">if</font>(p.stm_text)
cerr<<<a href="#sec23">p.stm_text</a><<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 the data source</font></i>
<font color="#0000A0">return</font> 0;
}
</pre>
<h4>Output</h4>
<pre>
f1=1, f2=10/19/1998 23:12:12.0
f1=2, f2=10/19/1998 23:12:12.0
f1=3, f2=10/19/1998 23:12:12.0
f1=4, f2=10/19/1998 23:12:12.0
f1=5, f2=10/19/1998 23:12:12.0
f1=6, f2=10/19/1998 23:12:12.0
f1=7, f2=10/19/1998 23:12:12.0
f1=8, f2=10/19/1998 23:12:12.0
f1=9, f2=10/19/1998 23:12:12.0
f1=10, f2=10/19/1998 23:12:12.0
</pre>
<a name="sec255a"><h3>Example 5a (with otl_stream class, CTime class
and Oracle)</h3>
<p>
</p>
<h4>Source code</h4>
<pre>
<font color="#FF0000">#define USE_MFC</font> <i><font color="#804000">// needs to be defined if CTime and MFC are used </font></i>
<font color="#FF0000">#include <iostream.h></font>
<font color="#FF0000">#include <stdio.h></font>
<font color="#FF0000">#include <otl.h></font>
<a href="#ref002">otl_connect</a> db; <i><font color="#804000">// connect object</font></i>
<a name="ref105a"></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(3, <i><font color="#804000">// buffer size</font></i>
<font color="#008080">"insert into test_tab2 values(:1<float>,:2<timestamp>)"</font>,
<i><font color="#804000">// SQL statement</font></i>
db <i><font color="#804000">// connect object</font></i>
);
CTime tm=CTime(1998,10,19,23,12,12);
<font color="#0000A0">for</font>(<font color="#0000A0">int</font> i=1;i<=10;++i){
o<a href="#ref003"><<</a>(<font color="#0000A0">float</font>)i<a href="#ref003"><<</a>tm;
}
}
<a name="ref102"></a><h4><font color="#0000A0">void</font> select()</h4>
{
<a href="#ref001">otl_stream</a> i(3, <i><font color="#804000">// buffer size</font></i>
<font color="#008080">"select * from test_tab2 where f2=:1<timestamp>"</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;
CTime tm=CTime(1998,10,19,23,12,12);
CTime f2;
i<a href="#ref003"><<</a>tm; <i><font color="#804000">// assigning :1 = tm</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">>></a>f1<a href="#ref004">>></a>f2;
cout<<<font color="#008080">"f1="</font><<f1<<<font color="#008080">", f2="</font><<f2.GetMonth()<<<font color="#008080">"/"</font><<f2.GetDay()<<<font color="#008080">"/"</font>
<<f2.GetYear()<<<font color="#008080">" "</font><<f2.GetHour()<<<font color="#008080">":"</font><<f2.GetMinute()<<<font color="#008080">":"</font>
<<f2.GetSecond()
<<endl;
}
}
<h4><font color="#0000A0">int</font> main()</h4>
{
<a href="#ref006">otl_connect::otl_initialize()</a>; <i><font color="#804000">// initialize ODBC environment</font></i>
<font color="#0000A0">try</font>{
db.<a href="#ref007">rlogon</a>(<font color="#008080">"UID=scott;PWD=tiger;DSN=my_db"</font>); <i><font color="#804000">// connect to data source MY_DB</font></i>
<a href="#sec24">otl_cursor::direct_exec</a>
(
db,
<font color="#008080">"drop table test_tab2"</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_tab2(f1 int, f2 date)"</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<<<a href="#sec23">p.msg</a><<endl; <i><font color="#804000">// print out error message</font></i>
cerr<<<a href="#sec23">p.code</a><<endl; <i><font color="#804000">// print out error code</font></i>
cerr<<<a href="#sec23">p.sqlstate</a><<endl; <i><font color="#804000">// print out SQLSTATE message</font></i>
<font color="#0000A0">if</font>(p.stm_text)
cerr<<<a href="#sec23">p.stm_text</a><<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 the data source</font></i>
<font color="#0000A0">return</font> 0;
}
</pre>
<h4>Output</h4>
<pre>
f1=1, f2=10/19/1998 23:12:12
f1=2, f2=10/19/1998 23:12:12
f1=3, f2=10/19/1998 23:12:12
f1=4, f2=10/19/1998 23:12:12
f1=5, f2=10/19/1998 23:12:12
f1=6, f2=10/19/1998 23:12:12
f1=7, f2=10/19/1998 23:12:12
f1=8, f2=10/19/1998 23:12:12
f1=9, f2=10/19/1998 23:12:12
f1=10, f2=10/19/1998 23:12:12
</pre>
<a name="sec255b"><h3>Example 5b (with otl_stream class, CTime class
and MS SQL Server)</h3>
<p>
This example works with MS SQL Server only.
</p>
<h4>Source code</h4>
<pre>
<font color="#FF0000">#define USE_MFC</font> <i><font color="#804000">// needs to be defined if CTime and MFC are used </font></i>
<font color="#FF0000">#include <iostream.h></font>
<font color="#FF0000">#include <stdio.h></font>
<font color="#FF0000">#include <otl.h></font>
<a href="#ref002">otl_connect</a> db; <i><font color="#804000">// connect object</font></i>
<a name="ref105b"></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(3, <i><font color="#804000">// buffer size</font></i>
<font color="#008080">"insert into test_tab2 values(:1<float>,:2<timestamp>)"</font>,
<i><font color="#804000">// SQL statement</font></i>
db <i><font color="#804000">// connect object</font></i>
);
CTime tm=CTime(1998,10,19,23,12,12);
<font color="#0000A0">for</font>(<font color="#0000A0">int</font> i=1;i<=10;++i){
o<a href="#ref003"><<</a>(<font color="#0000A0">float</font>)i<a href="#ref003"><<</a>tm;
}
}
<a name="ref106b"></a><h4><font color="#0000A0">void</font> select()</h4>
{
<a href="#ref001">otl_stream</a> i(3, <i><font color="#804000">// buffer size</font></i>
<font color="#008080">"select * from test_tab2 where f2=:1<timestamp>"</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;
CTime tm=CTime(1998,10,19,23,12,12);
CTime f2;
i<a href="#ref003"><<</a>tm; <i><font color="#804000">// assigning :1 = tm</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">>></a>f1<a href="#ref004">>></a>f2;
cout<<<font color="#008080">"f1="</font><<f1<<<font color="#008080">", f2="</font><<f2.GetMonth()<<<font color="#008080">"/"</font><<f2.GetDay()<<<font color="#008080">"/"</font>
<<f2.GetYear()<<<font color="#008080">" "</font><<f2.GetHour()<<<font color="#008080">":"</font><<f2.GetMinute()<<<font color="#008080">":"</font>
<<f2.GetSecond()
<<endl;
}
}
<h4><font color="#0000A0">int</font> main()</h4>
{
<a href="#ref006">otl_connect::otl_initialize()</a>; <i><font color="#804000">// initialize ODBC environment</font></i>
<font color="#0000A0">try</font>{
db.<a href="#ref007">rlogon</a>(<font color="#008080">"UID=sa;PWD=;DSN=mssql"</font>); <i><font color="#804000">// connect to data source MSSQL</font></i>
<a href="#sec24">otl_cursor::direct_exec</a>
(
db,
<font color="#008080">"drop table test_tab2"</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_tab2(f1 int, f2 datetime)"</font>
); <i><font color="#804000">// create table</font></i>
<a href="#ref105b">insert()</a>; <i><font color="#804000">// insert records into table</font></i>
<a href="#ref106b">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<<<a href="#sec23">p.msg</a><<endl; <i><font color="#804000">// print out error message</font></i>
cerr<<<a href="#sec23">p.code</a><<endl; <i><font color="#804000">// print out error code</font></i>
cerr<<<a href="#sec23">p.sqlstate</a><<endl; <i><font color="#804000">// print out SQLSTATE message</font></i>
<font color="#0000A0">if</font>(p.stm_text)
cerr<<<a href="#sec23">p.stm_text</a><<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 the data source</font></i>
<font color="#0000A0">return</font> 0;
}
</pre>
<h4>Output</h4>
<pre>
f1=1, f2=10/19/1998 23:12:12
f1=2, f2=10/19/1998 23:12:12
f1=3, f2=10/19/1998 23:12:12
f1=4, f2=10/19/1998 23:12:12
f1=5, f2=10/19/1998 23:12:12
f1=6, f2=10/19/1998 23:12:12
f1=7, f2=10/19/1998 23:12:12
f1=8, f2=10/19/1998 23:12:12
f1=9, f2=10/19/1998 23:12:12
f1=10, f2=10/19/1998 23:12:12
</pre>
<a name="sec256"><h3>Example 6 (implicit SELECT/result set return by
a stored procedure)</h3>
<p>
This example works MS SQL Server 6.5/7.0 only. In order to do the same
thing with Oracle via an Oracle ODBC driver, you need the Oracle ODBC
driver 8.0.5.x and the Oracle Client 8.0.5.x. In other words, it is
possible to return Oracle's normal referenced cursor in the format of an
implicit result set. Oracle's stored procedure call would look like
this:
</p>
<xmp>
{call my_proc(:1<int,in>,?)}
</xmp>
<p>where <b>?</b> is used to tell the Oracle ODBC driver 8.0.5.x that
the second parameter in the my_proc procedure is the referenced cursor
to be returned. The driver allocates the referenced cursor variable
inside and returns the fetch sequence in the format of the implicit
result set. For more info, refer to the Oracle ODBC driver 8.0.5.x's
manual.
</p>
<h4>Source code</h4>
<pre>
<font color="#FF0000">#include <iostream.h></font>
<font color="#FF0000">#include <stdio.h></font>
<font color="#FF0000">#include <otl.h></font>
<a href="#ref002">otl_connect</a> db; <i><font color="#804000">// connect object</font></i>
<a name="ref110"></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(:1<float>,:2<char[31]>)"</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<=100;++i){
sprintf(tmp,<font color="#008080">"Name%d"</font>,i);
o<a href="#ref003"><<</a>(<font color="#0000A0">float</font>)i<a href="#ref003"><<</a>tmp;
}
}
<a name="ref111"></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">"{call my_proc(:1<int,in>)}"</font>,
<i><font color="#804000">// implicit SELECT statement</font></i>
db, <i><font color="#804000">// connect object</font></i>
otl_implicit_select <i><font color="#804000">// implicit SELECT statement</font></i>
);
<i><font color="#804000⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -