📄 otl4_ex684.htm
字号:
<!DOCTYPE html PUBLIC "-//w3c//dtd html 4.0 transitional//en">
<html>
<head>
<meta http-equiv="Content-Type"
content="text/html; charset=iso-8859-1">
<meta name="Author" content="Sergei Kuchin">
<meta name="GENERATOR"
content="Mozilla/4.75 [en] (Win98; U) [Netscape]">
<meta name="KeyWords"
content="OTL, Oracle, ODBC, DB2, CLI, database API, C++, Template Library">
<title>OTL 4.0, Example 684 (Insert/Update/Select with MS SQL Server
2005 VARBINARY(MAX) in stream mode)</title>
</head>
<body>
<center>
<h1>OTL 4.0, Example 684 (Insert/Update/Select with MS SQL Server 2008
VARBINARY(MAX)
FILESTREAM in stream
mode)</h1>
</center>
This example demonstrates INSERT, UPDATE, and SELECT statements with
the
MS SQL Server 2008 VARBINARY(MAX) FILESTREAM data type in the <a
href="otl3_lob_stream.htm">stream
mode</a>. FILESTREAM is rather a new property of the VARBINARY(MAX)
type than a new date type. FILESTREAM allows VARBINARY(MAX) to be
larger than 2GB, but OTL still has the 2GB limit on the maximum size.
Microsoft recommends FILESTREAM to be used for VARBINARY values larger
than 1MB for performance.<br>
<br>
For more detail on FILESTREAM, see the MS SQL 2008 manual. <br>
<h2>Source Code</h2>
<pre>#include <iostream><br>using namespace std;<br><br>#include <stdio.h></pre>
<pre>#define <a href="otl3_compile.htm#OTL_ODBC_MSSQL_2008">OTL_ODBC_MSSQL_2008</a> // Compile OTL 4/ODBC, MS SQL 2008<br>#include <otlv4.h> // include the OTL 4.0 header file<br><br><a
href="otl3_connect_class.htm">otl_connect</a> db; // connect object<br><br>void insert()<br>// insert rows into table<br>{<a
href="otl3_long_string.htm">otl_long_string</a> f2(6000); // define long string variable<br> <a
href="otl3_stream_class.htm">otl_stream</a> o; // defined an otl_stream variable<br> o.<a
href="otl3_stream_class.htm#set_lob_stream_mode">set_lob_stream_mode</a>(true); // set the "lob stream mode" flag<br> o.<a
href="otl3_stream_class.htm#open">open</a>(1, // buffer size has to be set to 1 for operations with LOBs<br> "insert into test_tab (f1,f2,f3) "<br> "values(:f1<int>,:f2<raw_long>,:f3<raw_long>) ",<br> // SQL statement<br> db // connect object<br> );<br> o.<a
href="otl3_stream_class.htm#set_commit">set_commit</a>(0); // setting stream "auto-commit" to "off". It is required<br> // when LOB stream mode is used.</pre>
<pre> int i,j;<br> <a href="otl3_lob_stream.htm">otl_lob_stream</a> lob; // LOB stream for reading/writing unlimited number<br> // of bytes regardless of the buffer size.<br> <a
href="otl3_lob_stream.htm">otl_lob_stream</a> lob2; // LOB stream for reading/writing unlimited number<br> // of bytes regardless of the buffer size.<br><br> for(i=1;i<=20;++i){<br> o<<i;<br> o<a
href="otl3_stream_class.htm#stream_write_lob"><<</a>lob; // Initialize otl_lob_stream by writing it<br> // into otl_stream.<br> o<a
href="otl3_stream_class.htm#stream_write_lob"><<</a>lob2; // Initialize otl_lob_stream by writing it<br> // into otl_stream.<br><br> for(j=0;j<5000;++j)<br> f2[j]='*';<br> f2[5000]='?';<br> f2.<a
href="otl3_long_string.htm#set_len">set_len</a>(5001);<br><br> lob.<a
href="otl3_lob_stream.htm#set_len">set_len</a>(5001+2123); // setting the total size of<br> // the VARBINARY(MAX) value to be written.<br> <br> lob<a
href="otl3_lob_stream.htm#write"><<</a>f2; // writing first chunk of the VARBINARY(MAX) value into lob<br><br><br> f2[2122]='?';<br> f2.set_len(2123); // setting the size of the second chunk<br><br> lob<<f2; // writing the second chunk of the VARBINARY(MAX) value into lob<br> lob.<a
href="otl3_lob_stream.htm">close</a>(); // closing the otl_lob_stream<br><br> for(j=0;j<5000;++j)<br> f2[j]='*';<br> f2[5000]='?';<br> f2.<a
href="otl3_long_string.htm#set_len">set_len</a>(5001);<br> lob2.<a
href="otl3_lob_stream.htm#set_len">set_len</a>(5001+2123); // setting the total size of<br> // the VARBINARY(MAX) value to be written.<br> <br> lob2<a
href="otl3_lob_stream.htm#write"><<</a>f2; // writing first chunk of the VARBINARY(MAX) value into lob<br><br> f2[2122]='?';<br> f2.set_len(2123); // setting the size of the second chunk<br><br> lob2<<f2; // writing the second chunk of the VARBINARY(MAX) value into lob<br> lob2.<a
href="otl3_lob_stream.htm">close</a>(); // closing the otl_lob_stream<br><br> }<br><br> db.commit(); // committing transaction.<br>}</pre>
<pre>void update()<br>// insert rows in table<br>{</pre>
<pre> <a href="otl3_long_string.htm">otl_long_string</a> f2(6200); // define long string variable<br><br> <a
href="otl3_stream_class.htm">otl_stream</a> o; // defined an otl_stream variable<br> o.<a
href="otl3_stream_class.htm#set_lob_stream_mode">set_lob_stream_mode</a>(true); // set the "lob stream mode" flag<br> o.<a
href="otl3_stream_class.htm#open">open</a>(1, // buffer size has to be set to 1 for operations with LOBs<br> "update test_tab "<br> " set f2=:f2<raw_long> "<br> "where f1=:f1<int> ",<br> // SQL statement<br> db // connect object<br> );<br><br> <a
href="otl3_lob_stream.htm">otl_lob_stream</a> lob;<br><br> o.<a
href="otl3_stream_class.htm#set_commit">set_commit</a>(0); // setting stream "auto-commit" to "off". <br><br><br> for(int j=0;j<6000;++j){<br> f2[j]='#';<br> }<br><br> f2[6000]='?';<br> f2.<a
href="otl3_long_string.htm#set_len">set_len</a>(6001);<br><br> o<a
href="otl3_stream_class.htm#stream_write_lob"><<</a>lob; // Initialize otl_lob_stream by writing it<br> // into otl_stream.<br> o<<5;<br><br> lob.<a
href="otl3_lob_stream.htm#set_len">set_len</a>(6001*4); // setting the total size of of the VARBINARY(MAX) value to be written<br> for(int i=1;i<=4;++i)<br> lob<a
href="otl3_lob_stream.htm#write"><<</a>f2; // writing chunks of the VARBINARY(MAX) value into the otl_lob_stream<br><br> lob.<a
href="otl3_lob_stream.htm#close">close</a>(); // closing the otl_lob_stream<br><br> db.commit(); // committing transaction<br><br>}<br><br>void select()<br>{ <br> <a
href="otl3_long_string.htm">otl_long_string</a> f2(3000); // define long string variable<br><br> <a
href="otl3_stream_class.htm">otl_stream</a> i; // defined an otl_stream variable<br> i.<a
href="otl3_stream_class.htm#set_lob_stream_mode">set_lob_stream_mode</a>(true); // set the "lob stream mode" flag<br> i.<a
href="otl3_stream_class.htm#open">open</a>(1, // buffer size. To read VARBINARY(MAX) values, it should be set to 1<br> "select f1,f2,f3 from test_tab where f1>=:f11<int> and f1<=:f12<int>*2",<br> // SELECT statement<br> db // connect object<br> ); <br> // create select stream<br> <br> int f1;<br> <a
href="otl3_lob_stream.htm">otl_lob_stream</a> lob; // Stream for reading VARBINARY(MAX) value<br> <a
href="otl3_lob_stream.htm">otl_lob_stream</a> lob2; // Stream for reading VARBINARY(MAX) value<br><br> i<<4<<4; // assigning :f11 = 4, :f12 = 4<br> // SELECT automatically executes when all input variables are<br> // assigned. First portion of output rows is fetched to the buffer<br><br> while(!i.eof()){ // while not end-of-data<br> i>>f1;<br> cout<<"f1="<<f1<<endl;<br> i<a
href="otl3_stream_class.htm#stream_read_lob">>></a>lob; // initializing LOB stream by reading the VARBINARY(MAX) value reference <br> // into the otl_lob_stream from the otl_stream.<br> i<a
href="otl3_stream_class.htm#stream_read_lob">>></a>lob2; // initializing LOB stream by reading the VARBINARY(MAX) value reference <br> // into the otl_lob_stream from the otl_stream.<br> int n=0;<br> while(!lob.<a
href="otl3_lob_stream.htm#eof">eof</a>()){ // read while not "end-of-file" -- end of VARBINARY(MAX) value<br> ++n;<br> lob<a
href="otl3_lob_stream.htm#read">>></a>f2; // reading a chunk of VARBINARY(MAX) value<br> cout<<" chunk #"<<n;<br> cout<<", f2="<<f2[0]<<f2[f2.len()-1]<<", len="<<f2.len()<<endl;<br> }<br> lob.<a
href="otl3_lob_stream.htm#close">close</a>(); // closing the otl_lob_stream.<br> n=0;<br> while(!lob2.<a
href="otl3_lob_stream.htm#eof">eof</a>()){ // read while not "end-of-file" -- end of VARBINARY(MAX) value<br> ++n;<br> lob2<a
href="otl3_lob_stream.htm#read">>></a>f2; // reading a chunk of VARBINARY(MAX) value<br> cout<<" chunk #"<<n;<br> cout<<", f3="<<f2[0]<<f2[f2.len()-1]<<", len="<<f2.len()<<endl;<br> }<br> lob2.<a
href="otl3_lob_stream.htm#close">close</a>(); // closing the otl_lob_stream. <br> <br> }<br><br>}<br><br>int main()<br>{<br> <a
href="otl3_connect_class.htm">otl_connect::otl_initialize</a>(); // initialize the environment<br> try{<br><br> db.rlogon("scott/tiger@mssql2008"); // connect to the database<br><br> <a
href="otl3_const_sql.htm">otl_cursor::direct_exec<br></a> (<br> db,<br> "drop table test_tab",<br> otl_exception::disabled // disable OTL exceptions<br> ); // drop table<br><br> <a
href="otl3_const_sql.htm">otl_cursor::direct_exec<br></a> (<br> db,<br> "create table test_tab "<br> "(id uniqueidentifier not null rowguidcol "<br> " primary key default newid(), "<br> " f1 int, "<br> " f2 varbinary(max) filestream null, "<br> " f3 varbinary(max) filestream null) "<br> ); // create table<br><br> insert(); // insert records into table<br> update(); // update records in table<br> select(); // select records from table<br><br> }<br><br> catch(<a
href="otl3_exception_class.htm">otl_exception</a>& p){ // intercept OTL exceptions<br> cerr<<p.msg<<endl; // print out error message<br> cerr<<p.sqlstate<<endl; // print out SQLSTATE <br> cerr<<p.stm_text<<endl; // print out SQL that caused the error<br> cerr<<p.var_info<<endl; // print out the variable that caused the error<br> }<br><br> db.logoff(); // disconnect from the database<br><br> return 0;<br><br>}</pre>
<h2>
Output</h2>
<pre>f1=4<br> chunk #1, f2=**, len=3000<br> chunk #2, f2=**, len=3000<br> chunk #3, f2=*?, len=1124<br> chunk #1, f3=**, len=3000<br> chunk #2, f3=**, len=3000<br> chunk #3, f3=*?, len=1124<br>f1=5<br> chunk #1, f2=##, len=3000<br> chunk #2, f2=##, len=3000<br> chunk #3, f2=?#, len=3000<br> chunk #4, f2=##, len=3000<br> chunk #5, f2=##, len=3000<br> chunk #6, f2=##, len=3000<br> chunk #7, f2=##, len=3000<br> chunk #8, f2=##, len=3000<br> chunk #9, f2=#?, len=4<br> chunk #1, f3=**, len=3000<br> chunk #2, f3=**, len=3000<br> chunk #3, f3=*?, len=1124<br>f1=6<br> chunk #1, f2=**, len=3000<br> chunk #2, f2=**, len=3000<br> chunk #3, f2=*?, len=1124<br> chunk #1, f3=**, len=3000<br> chunk #2, f3=**, len=3000<br> chunk #3, f3=*?, len=1124<br>f1=7<br> chunk #1, f2=**, len=3000<br> chunk #2, f2=**, len=3000<br> chunk #3, f2=*?, len=1124<br> chunk #1, f3=**, len=3000<br> chunk #2, f3=**, len=3000<br> chunk #3, f3=*?, len=1124<br>f1=8<br> chunk #1, f2=**, len=3000<br> chunk #2, f2=**, len=3000<br> chunk #3, f2=*?, len=1124<br> chunk #1, f3=**, len=3000<br> chunk #2, f3=**, len=3000<br> chunk #3, f3=*?, len=1124<br><br><hr
width="100%"></pre>
<center><a href="otl3_examples.htm">Examples</a> <a href="otl3.htm">Contents</a><a
href="home.htm">Go
Home</a></center>
<p>Copyright © 1996, 2008, Sergei Kuchin, email: <a
href="mailto:skuchin@aceweb.com">skuchin@aceweb.com</a>,
<a href="mailto:skuchin@gmail.com">skuchin@gmail.com
<script language="JavaScript"><!-- hide from old browsers
var modDate = new Date(document.lastModified)
document.write("<i> Last Updated:</i> " + (modDate.getMonth()+1) + "/" +
modDate.getDate() + "/" + "0"+(modDate.getYear())%100+".");
//-->
</script></a>.
</p>
<p><i>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.</i>
</p>
<script type="text/javascript">
var gaJsHost = (("https:" == document.location.protocol) ? "https://ssl." : "http://www.");
document.write(unescape("%3Cscript src='" + gaJsHost + "google-analytics.com/ga.js' type='text/javascript'%3E%3C/script%3E"));
</script>
<script type="text/javascript">
var pageTracker = _gat._getTracker("UA-5456201-1");
pageTracker._trackPageview();
</script>
</body>
</html>
⌨️ 快捷键说明
复制代码
Ctrl + C
搜索代码
Ctrl + F
全屏模式
F11
切换主题
Ctrl + Shift + D
显示快捷键
?
增大字号
Ctrl + =
减小字号
Ctrl + -