📄 otl2odbc.htm
字号:
}
}catch(otl_exception& p){ // intercept exception
cerr<<p.code<<endl; // print out error code
cerr<<p.msg<<endl; // print out error message
cerr<<p.sqlstate<<endl; // print out SQLSTATE
if(p.stm_text)
cerr<<p.stm_text<<endl; // print out SQL that caused the error
}
</xmp>
<h2><a name="sec24">2.4. Constant SQL statement or stored procedure call</h2>
<p>
SQL statement or stored procedure call 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 numeric, f2 varchar(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 stored procedure call
otl_exception::disabled // disable OTL exceptions,
// in other words, ignore any
// database error
); // drop table
</xmp>
<h2><a name="sec25">2.5. Examples</h2>
<p>
All examples below assume Oracle 8.x or SQL Server 6.5/7.0 ODBC
drivers as their data sources.
</p>
<a name="sec251"><h3>Example 1 (with otl_stream class)</h3>
<p>
This example works with both Oracle 8.x and SQL Server 6.5/7.0 without
any modifications to the table structure or datatypes.
</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="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(: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="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>=:1<int> and f1<=:2<int>*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"><<</a>8<a href="#ref003"><<</a>8; <i><font color="#804000">// assigning :1 = 8, :2 = 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">>></a>f1<a href="#ref004">>></a>f2;
cout<<<font color="#008080">"f1="</font><<f1<<<font color="#008080">", f2="</font><<f2<<endl;
}
i<a href="#ref003"><<</a>4<a href="#ref003"><<</a>4; <i><font color="#804000">// assigning :1 = 4, :2 = 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">>></a>f1<a href="#ref004">>></a>f2;
cout<<<font color="#008080">"f1="</font><<f1<<<font color="#008080">", f2="</font><<f2<<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_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 int, f2 varchar(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<<<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=8, f2=Name8
f1=9, f2=Name9
f1=10, f2=Name10
f1=11, f2=Name11
f1=12, f2=Name12
f1=13, f2=Name13
f1=14, f2=Name14
f1=15, f2=Name15
f1=16, f2=Name16
f1=4, f2=Name4
f1=5, f2=Name5
f1=6, f2=Name6
f1=7, f2=Name7
f1=8, f2=Name8
</pre>
<a name="sec252a"><h3>Example 2a (with an Oracle stored procedure call)</h3>
<p>
This example works with Oracle 8.x ODBC drivers only. Check out <a
href="#sec252b">example 2b</a> to see how to call a stored procedure
in an MS SQL Server 6.5/7.0.
</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="ref201"></a><h4><font color="#0000A0">void</font> stored_proc()</h4>
<i><font color="#804000">// invoking stored procedure</font></i>
{
<a href="#ref001">otl_stream</a> o(1, <i><font color="#804000">// buffer size should be equal to 1 in case of stored procedure call</font></i>
<font color="#008080">"{call my_proc("</font>
<font color="#008080">" :1<int,inout>, "</font>
<font color="#008080">" :2<char[31],out>, "</font>
<font color="#008080">" :3<char[31],in> "</font>
<font color="#008080">")}"</font>,
<i><font color="#804000">// stored procedure call</font></i>
db <i><font color="#804000">// connect object</font></i>
);
o<a href="#ref003"><<</a>1<a href="#ref003"><<</a><font color="#008080">"Test String1"</font>; <i><font color="#804000">// assigning :1 = 1, :3 = "Test String1"</font></i>
<font color="#0000A0">int</font> a;
<font color="#0000A0">char</font> b[31];
o<a href="#ref004">>></a>a<a href="#ref004">>></a>b;
cout<<<font color="#008080">"A="</font><<a<<<font color="#008080">", B="</font><<b<<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">"CREATE OR REPLACE PROCEDURE my_proc "</font>
<font color="#008080">" (A IN OUT NUMBER, "</font>
<font color="#008080">" B OUT VARCHAR2, "</font>
<font color="#008080">" C IN VARCHAR2) "</font>
<font color="#008080">"IS "</font>
<font color="#008080">"BEGIN "</font>
<font color="#008080">" A := A+1; "</font>
<font color="#008080">" B := C; "</font>
<font color="#008080">"END;"</font>
); <i><font color="#804000">// create stored procedure</font></i>
<a href="#ref201">stored_proc()</a>; <i><font color="#804000">// invoking stored procedure</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>
}
db.<a href="#ref009">logoff()</a>; <i><font color="#804000">// disconnect from the data source</font></i>
<font color="#0000A0">return</font> 0;
}
</pre>
<h4>Output</h4>
<pre>
A=2, B=Test String1
</pre>
<a name="sec252b"><h3>Example 2b (with an MS SQL Server stored procedure call)</h3>
<p>
This example works with Oracle 8.x ODBC drivers only. Check out <a
href="#sec252a">example 2a</a> to see how to call a stored procedure
in Oracle 8.x
</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="ref201b"></a><h4><font color="#0000A0">void</font> stored_proc()</h4>
<i><font color="#804000">// invoking stored procedure</font></i>
{
<a href="#ref001">otl_stream</a> o(1, <i><font color="#804000">// buffer size should be equal to 1 in case of stored procedure call</font></i>
<font color="#008080">"{call my_proc("</font>
<font color="#008080">" :1<int,inout>, "</font>
<font color="#008080">" :2<char[31],out>, "</font>
<font color="#008080">" :3<char[31],in> "</font>
<font color="#008080">")}"</font>,
<i><font color="#804000">// stored procedure call</font></i>
db <i><font color="#804000">// connect object</font></i>
);
o<a href="#ref003"><<</a>1<a href="#ref003"><<</a><font color="#008080">"Test String1"</font>; <i><font color="#804000">// assigning :1 = 1, :3 = "Test String1"</font></i>
<font color="#0000A0">int</font> a;
<font color="#0000A0">char</font> b[31];
o<a href="#ref004">>></a>a<a href="#ref004">>></a>b;
cout<<<font color="#008080">"A="</font><<a<<<font color="#008080">", B="</font><<b<<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 PROCEDURE my_proc"</font>,
0 <i><font color="#804000">// ignore any errors</font></i>
); <i><font color="#804000">// drop stored procedure</font></i>
<a href="#sec24">otl_cursor::direct_exec</a>
(
db,
<font color="#008080">"CREATE PROCEDURE my_proc "</font>
<font color="#008080">" @A int out, "</font>
<font color="#008080">" @B varchar(60) out, "</font>
<font color="#008080">" @C varchar(60) "</font>
<font color="#008080">"AS "</font>
<font color="#008080">"BEGIN "</font>
<font color="#008080">" SELECT @A=@A+1"</font>
<font color="#008080">" SELECT @B=@C "</font>
<font color="#008080">"END"</font>
); <i><font color="#804000">// create stored procedure</font></i>
<a href="#ref201b">stored_proc()</a>; <i><font color="#804000">// invoking stored procedure</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>
}
db.<a href="#ref009">logoff()</a>; <i><font color="#804000">// disconnect from the data source</font></i>
<font color="#0000A0">return</font> 0;
}
</pre>
<h4>Output</h4>
<pre>
A=2, B=Test String1
</pre>
<a name="sec253"><h3>Example 3 (with printf/scanf functions)</h3>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -