C++ otl_connect::commit方法代码示例

int main()
{
 otl_connect::otl_initialize(); // initialize ODBC environment
 try{

  db1.rlogon("UID=scott;PWD=tiger;DSN=freetds_mssql"); // connect to ODBC

  db2.rlogon("UID=scott;PWD=tiger;DSN=freetds_mssql"); // connect to ODBC
  db3.rlogon("UID=scott;PWD=tiger;DSN=freetds_mssql"); // connect to ODBC

  db2.set_transaction_isolation_level(otl_tran_read_uncommitted);
  db3.set_transaction_isolation_level(otl_tran_read_committed);

  otl_cursor::direct_exec
   (
    db1,
    "drop table test_tab",
    otl_exception::disabled // disable OTL exceptions
   ); // drop table

  otl_cursor::direct_exec
   (
    db1,
    "create table test_tab(f1 int, f2 varchar(30))"
    );  // create table

  db1.commit(); // committing the DDLs to the database
  
  insert(); // insert records into table

  cout<<"=======> Reading uncommitted data..."<<endl; 
  select_nolock(db2); // select records from table

  db1.commit(); // committing the inserted rows to the database

  cout<<"=======> Reading committed data..."<<endl; 
  select_committed(db3); // select records from table

 }

 catch(otl_exception& p){ // intercept OTL exceptions
  cerr<<p.msg<<endl; // print out error message
  cerr<<p.stm_text<<endl; // print out SQL that caused the error
  cerr<<p.sqlstate<<endl; // print out SQLSTATE message
  cerr<<p.var_info<<endl; // print out the variable that caused the error
 }

 db1.logoff(); // disconnect from ODBC
 db2.logoff(); // disconnect from ODBC
 db3.logoff(); // disconnect from ODBC


 return 0;

}

void insert()
// insert rows into table
{ 
 otl_stream o(1, // buffer size
              "insert into test_tab (f2) "
               "OUTPUT INSERTED.f1, INSERTED.f2 "
               "values(:f2<char[31],in>)", 
                 // SQL statement
              db, // connect object
              otl_implicit_select // the statement returns an implicit result set
             );
 o.set_commit(0); // set stream auto-commit to OFF
 char f2_in[32];
 int f1;
 char f2[32];

 for(int i=1;i<=10;++i){
#if defined(_MSC_VER)
#if (_MSC_VER >= 1400) // VC++ 8.0 or higher
   sprintf_s(f2_in,sizeof(f2_in),"Name%d",i);
#else
   sprintf(f2_in,"Name%d",i);
#endif
#else
   sprintf(f2_in,"Name%d",i);
#endif
   o<<f2_in; // write input variable :f2
   while(!o.eof()){ // while not end-of-data
     o>>f1>>f2; // fetch the columns from the OUTPUT clause
     cout<<"f1="<<f1<<", f2="<<f2<<endl;
   }
 }
 db.commit(); // commit transaction

}

void insert()
// insert rows into table
{otl_long_string f2(5000); // define long string variable
 otl_stream o(1, // buffer size has to be set to 1 for operations with LOBs
              "begin "
              "  insert_proc(:f1<int,in>,:f2<clob,in>); "
              "end;", // PL/SQL block that calls a stored procedure
              db // connect object
             );
 o.set_commit(0); // setting stream "auto-commit" to "off". It is required
                  // when LOB stream mode is used.

 otl_lob_stream lob; // LOB stream for reading/writing unlimited number
                     // of bytes regardless of the buffer size.

 for(int i=1; i<=3; ++i){
   for(int j=0;j<4000;++j)
     f2[j]='*';
   f2[4000]='?';
   f2.set_len(4001);
   o<<i;
   o<<lob; // Initialize otl_lob_stream by writing it into the otl_stream
   lob.set_len(4001); // setting the total size of of the CLOB to be written
   lob<<f2; // writing f2 into lob
   lob.close(); // closing the otl_lob_stream
 }
 db.commit(); // committing transaction.
}

void insert()
// insert rows into table
{ 
 otl_nocommit_stream o
    (50, // buffer size
     "insert into test_tab values(:f1<float>,:f2<char[31]>)", 
        // SQL statement
     db // connect object
    );
 char tmp[32];

 for(int i=1;i<=100;++i){
#if defined(_MSC_VER)
#if (_MSC_VER >= 1400) // VC++ 8.0 or higher
  sprintf_s(tmp,sizeof(tmp),"Name%d",i);
#else
  sprintf(tmp,"Name%d",i);
#endif
#else
  sprintf(tmp,"Name%d",i);
#endif
  o<<static_cast<float>(i)<<tmp;
 }

 o.flush();
 db.commit();

}

void update()
// insert rows in table
{ 
 string f2; 
 otl_stream o; // defined an otl_stream variable
 o.set_lob_stream_mode(true); // set the "lob stream mode" flag
 o.open(1, // buffer size has to be set to 1 for operations with LOBs
        "update test_tab "
        "   set f2=:f2<varchar_long> "
        "where f1=:f1<int> ",
            // SQL statement
        db // connect object
       );
 otl_lob_stream lob;
 o.set_commit(0); // setting stream "auto-commit" to "off". 
 f2.append(6000,'#');
 f2+='?';
 o<<lob; // Initialize otl_lob_stream by writing it
         // into otl_stream.
 o<<5;
 lob.set_len(6001*4); // setting the total size of of the CLOB to be written
 for(int i=1;i<=4;++i)
  lob<<f2; // writing chunks of the CLOB into the otl_lob_stream
 lob.close(); // closing the otl_lob_stream
 db.commit(); // committing transaction

}

void insert()
// insert rows into table
{ 
 otl_stream o(10, // buffer size
              "insert into test_tab values(:f1<int>,:f2<char[31]>)", 
                 // SQL statement
              db // connect object
             );
 o.set_commit(0); // turning off the stream's autocommit flag
 char tmp[32];

 for(int i=1;i<=100;++i){
#if defined(_MSC_VER)
#if (_MSC_VER >= 1400) // VC++ 8.0 or higher
  sprintf_s(tmp,sizeof(tmp),"Name%d",i);
#else
  sprintf(tmp,"Name%d",i);
#endif
#else
  sprintf(tmp,"Name%d",i);
#endif
  o<<i;
  if(i==17)
   throw "Making a little trouble in the middle of the INSERT...";
  o<<tmp;
 }
 o.flush(); // flushing the stream's buffer
 db.commit(); // committing the changes to the database
}

int main()
{
 otl_connect::otl_initialize(); // initialize the database API environment
 try{

  db.rlogon("scott/[email protected]"); // connect to the database

  otl_cursor::direct_exec
   (
    db,
    "drop table test_tab",
    otl_exception::disabled // disable OTL exceptions
   ); // drop table
   db.commit();
  otl_cursor::direct_exec
   (
    db,
    "create table test_tab(f1 int, f2 ntext)"
    );  // create table
  insert(); // insert records into table
  select(); // select records from table

 }

 catch(otl_exception& p){ // intercept OTL exceptions
  cerr<<p.msg<<endl; // print out error message
  cerr<<p.stm_text<<endl; // print out SQL that caused the error
  cerr<<p.var_info<<endl; // print out the variable that caused the error
 }

 db.logoff(); // disconnect from the database

 return 0;

}

void update()
// insert rows in table
{ 

 otl_long_string f2(5200); // define long string variable

 otl_stream o(1, // buffer size has to be set to 1 for operations with LOBs
              "begin "
              "  update_proc(:f1<int,in>,:f2<clob,in>); "
              "end;", // PL/SQL block that calls a stored procedure
              db // connect object
             );

  otl_lob_stream lob;

  o.set_commit(0); // setting stream "auto-commit" to "off". 


 for(int j=0;j<5000;++j){
  f2[j]='#';
 }
 f2[5000]='?';
 f2.set_len(5001);
 o<<2;
 o<<lob; // Initialize otl_lob_stream by writing it
         // into otl_stream.
 lob.set_len(5001*4); // setting the total size of of the CLOB to be written
 for(int i=1;i<=4;++i)
   lob<<f2; // writing chunks of the CLOB into the otl_lob_stream
 lob.close(); // closing the otl_lob_stream

 db.commit(); // committing transaction

}

void insert()
// insert rows into table
{ 
 otl_stream o(10, // make the buffer size larger than the actual 
                  // row set to inserted, so that the stream will not
                  // flush the buffer automatically
              "insert into test_tab values(:f1<int>,:f2<char[31]>)", 
                 // SQL statement
              db // connect object
             );

 o.set_commit(0); // set stream's auto-commit to OFF.

 long total_rpc=0; // total "rows processed count"
 long rpc=0; // rows successfully processed in one flush() call
 int iters=0; // number of rows to be bypassed

 try{
  o<<1<<"Line1"; // Enter one row into the stream
  o<<1<<"Line1"; // Enter the same data into the stream
                 // and cause a "duplicate key" error.
  o<<2<<"Line2"; // Enter one row into the stream
  o<<3<<"Line3"; // Enter one row into the stream
  o<<4<<"Line4"; // Enter one row into the stream
  o<<4<<"Line4"; // Enter the same data into the stream
                 // and cause a "duplicate key" error.
  o.flush();
 }catch(otl_exception& p){
  if(p.code==1){
   // ORA-0001: ...duplicate key...
    ++iters;
    rpc=o.get_rpc();
    total_rpc=rpc;
    do{
      try{
        cout<<"TOTAL_RPC="<<total_rpc<<", RPC="<<rpc<<endl;
        o.flush(total_rpc+iters, // bypass the duplicate row and start
                                 // with the rows after that
                true // force buffer flushing regardless
               );
        rpc=0;
      }catch(otl_exception& p2){
        if(p2.code==1){
          // ORA-0001: ... duplicate key ...
          ++iters;
          rpc=o.get_rpc();
          total_rpc+=rpc;
        }else
          throw;
      }
    }while(rpc>0);
  }else
   throw; // re-throw the exception to the outer catch block.
 }

 db.commit(); // commit transaction

}

int main()
{
 otl_connect::otl_initialize(); // initialize ODBC environment
 try{

  db.rlogon("scott/[email protected]");

  otl_cursor::direct_exec
   (
    db,
    "drop table test_tab",
    otl_exception::disabled // disable OTL exceptions
   ); // drop table

   db.commit();

  otl_cursor::direct_exec
   (
    db,
    "create table test_tab(f1 int, f2 bool)"
    );  // create table

  db.commit();

  insert(); // insert records into the table
  update(10); // update records in the table
  select(8); // select records from the table

 }

 catch(otl_exception& p){ // intercept OTL exceptions
  cerr<<p.msg<<endl; // print out error message
  cerr<<p.stm_text<<endl; // print out SQL that caused the error
  cerr<<p.sqlstate<<endl; // print out SQLSTATE message
  cerr<<p.var_info<<endl; // print out the variable that caused the error
 }

 db.logoff(); // disconnect from ODBC

 return 0;

}

int main()
{
 otl_connect::otl_initialize(); // initialize ODBC environment
 try{

  db.rlogon("UID=scott;PWD=tiger;DSN=postgresql"); // connect to ODBC
//  db.rlogon("scott/[email protected]"); // connect to ODBC, alternative format
                                    // of connect string

  otl_cursor::direct_exec
   (
    db,
    "drop table test_tab",
    otl_exception::disabled // disable OTL exceptions
   ); // drop table
  db.commit();
  otl_cursor::direct_exec
   (
    db,
    "create table test_tab(f1 int, f2 date, f3 time, f4 timestamp)"
    );  // create table
  db.commit();
  insert(); // insert records into table
  select(); // select records from table

 }

 catch(otl_exception& p){ // intercept OTL exceptions
  cerr<<p.msg<<endl; // print out error message
  cerr<<p.stm_text<<endl; // print out SQL that caused the error
  cerr<<p.sqlstate<<endl; // print out SQLSTATE message
  cerr<<p.var_info<<endl; // print out the variable that caused the error
 }

 db.logoff(); // disconnect from ODBC

 return 0;

}

void insert()
// insert rows into table
{ 
 otl_long_unicode_string f2(6000); // define long unicode string variable

 otl_stream o;

 o.set_lob_stream_mode(true); // set the "lob stream mode" flag
 o.open(1, // buffer size has to be set to 1 for operations with CLOBs
        "insert into test_tab values(:f1<int>,:f2<varchar_long>)",
           // SQL statement
         db // connect object
        );

 o.set_commit(0); // setting stream "auto-commit" to "off". It is required
                  // when LOB stream mode is used.

 otl_lob_stream lob; // LOB stream for reading/writing unlimited number
                     // of bytes regardless of the buffer size.

 int i,j;

 for(i=1;i<=20;++i){
  o<<i;
  o<<lob; // Initialize otl_lob_stream by writing it
          // into otl_stream.

  for(j=0;j<5000;++j)
   f2[j]=(unsigned short) '*'; // ASCII to Unicode.
  f2[5000]= (unsigned short) '?'; // ASCII to Unicode
  f2.set_len(5001);

  lob.set_len(5001+2123); // setting the total  size of
                          // the CLOB to be written.
  
  lob<<f2; // writing first chunk of the CLOB into lob

  f2[2122]= (unsigned short) '?';
  f2.set_len(2123); // setting the size of the second chunk

  lob<<f2; // writing the second chunk of the CLOB into lob
  lob.close(); // closing the otl_lob_stream

 }

 db.commit(); // committing transaction.

}

void insert()
// insert rows into table
{otl_long_string f2(60000); // define long string variable
 otl_stream o(1, // buffer size has to be set to 1 for operations with LOBs
              "insert into test_tab values(:f1<int>,empty_clob()) "
              "returning f2 into :f2<clob> ",
                 // SQL statement
              db // connect object
             );
 o.set_commit(0); // setting stream "auto-commit" to "off". It is required
                  // when LOB stream mode is used.

 otl_lob_stream lob; // LOB stream for reading/writing unlimited number
                     // of bytes regardless of the buffer size.

 for(int i=1;i<=20;++i){
  for(int j=0;j<50000;++j)
   f2[j]='*';
  f2[50000]='?';
  f2.set_len(50001);

  o<<i;

  o<<lob; // Initialize otl_lob_stream by writing it
          // into otl_stream. Weird, isn't it?

// OTL 4.0.138 and higher does not require the LOB's total length to be 
// set beforehand. Instead, otl_long_string::last_piece can be used.
// lob.set_len(50001+23123); // setting the total  size of
//                            // the CLOB to be written.
//   // It is required for compatibility
//   // with earlier releases of OCI8: OCI8.0.3, OCI8.0.4.

  f2.set_last_piece(false);
  lob<<f2; // writing first chunk of the CLOB into lob

  f2[23122]='?';
  f2.set_len(23123); // setting the size of the second chunk

  f2.set_last_piece(true);
  lob<<f2; // writing the second (last) chunk of the CLOB into lob
  lob.close(); // closing the otl_lob_stream
 }

 db.commit(); // committing transaction.
}

void update()
// insert rows in table
{ 

 otl_long_string f2(6200); // define long string variable

 otl_stream o(1, // buffer size has to be set to 1 for operations with LOBs
              "update test_tab "
              "   set f2=empty_clob() "
              "where f1=:f1<int> "
              "returning f2 into :f2<clob> ",
                 // SQL statement
              db // connect object
             );

  otl_lob_stream lob;

  o.set_commit(0); // setting stream "auto-commit" to "off". 


 for(int j=0;j<6000;++j){
  f2[j]='#';
 }

 f2[6000]='?';
 f2.set_len(6001);

 o<<5;
 o<<lob; // Initialize otl_lob_stream by writing it
         // into otl_stream.

// OTL 4.0.138 and higher does not require the LOB's total length to be 
// set beforehand. Instead, otl_long_string::last_piece can be used.
// lob.set_len(6001*4); // setting the total size of of the CLOB to be written

 for(int i=1;i<=4;++i){
  f2.set_last_piece(i==4);
  lob<<f2; // writing chunks of the CLOB into the otl_lob_stream
}

 lob.close(); // closing the otl_lob_stream

 db.commit(); // committing transaction

}

void select()
{ 
 otl_stream i(50, // buffer size
              "select * from test_tab where f1>=:f11<char[40]> and f1<=:f12<char[40]>*2",
                 // SELECT statement
              db // connect object
             ); 
   // create select stream
 
 float f1;
 char f2[31];
 
 try{
  cout<<"====> Starting a fetch sequence...."<<endl;
  i<<"xxx8"<<"xxx8"; // assigning :f11 = "xxx8", :f12 = "xxx8". It's obviously 
                     // invalid for a numeric column
    // SELECT automatically executes when all input variables are
    // assigned. First portion of output rows is fetched to the buffer

  while(!i.eof()){ // while not end-of-data
   i>>f1>>f2;
   cout<<"f1="<<f1<<", f2="<<f2<<endl;
  } 
 }catch(otl_exception& p){
  cerr<<"===> A database exception is caught: "<<endl;
  cerr<<p.msg<<endl; // print out error message
  cerr<<p.stm_text<<endl; // print out SQL that caused the error
  cerr<<p.var_info<<endl; // print out the variable that caused the error
  cerr<<"===> Cleaning up the stream's error flags"<<endl;
  i.clean();
  db.commit();
 }

 cout<<"====> Starting another fetch sequence..."<<endl;
 i<<"4"<<"4"; // assigning :f11 = :"4, :f12 = "4"
   // SELECT automatically executes when all input variables are
   // assigned. First portion of output rows is fetched to the buffer

 while(!i.eof()){ // while not end-of-data
  i>>f1>>f2;
  cout<<"f1="<<f1<<", f2="<<f2<<endl;
 }

}