Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #457

Re: problem from proc call and autoincreasing key in using otl

Show all headers | View raw

On 6月15日, 上午6时36分, white_ideal <whiteid...@gmail.com> wrote:
> On 6月15日, 上午5时39分, Erland Sommarskog <esq...@sommarskog.se> wrote:
>
>
>
>
>
>
>
>
>
> > white_ideal (whiteid...@gmail.com) writes:
> > > I know how to get the identity of course by using sql. But don't know
> > > how to get it using the interface from otl. It's so difficult to
> > > implement in otl. And I am a little regret to use otl in my project,
> > > but i have no choice at present.
>
> > So the question boils down to "how do I receive a result set through OTL" or
> > "how do I receive an output parameter through OTL". There is nothing
> > special with regards to the IDENTITY value.
>
> > I see that they have a discussion board on the OTL web site, you may
> > find better answers there.
>
> > --
> > Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
>
> > Links for SQL Server Books Online:
> > SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
> > SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
>
> Yeah, that discussion board is create by myself. and the great SKuchin
> has emailed with me about this problem, but there is no answer at
> present. I'll tell you all guys the results if it's resolved.

hi, all guys, our great Mr. SKuchin has give me the correct results. I
have test his example in my environment(vs2008+sqlserver2000), and
gotten the correct results.

his code shown as follows.
////////////////////////////////////////////
				#include <iostream>
				using namespace std;

				#include <stdio.h>

				#define OTL_ODBC_MSSQL_2008 // Compile OTL 4/ODBC, MS SQL 2008
				//#define OTL_ODBC // Compile OTL 4/ODBC. Uncomment this when used
with MS SQL 7.0/ 2000
				#include <otlv4.h> // include the OTL 4.0 header file

				otl_connect db; // connect object

				void insert()
				// insert rows into table
				{
				 otl_stream o(1, // buffer size
				              "{call prc_1( "
				              "  :f1<int,out>, "
				              "  :f2<char[31],in> "
				              ")}",
				                 // SQL statement
				              db
				             );
				 o.set_commit(0); // set stream auto-commit to OFF
				 char f2_in[32];
				 int f1;

				 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;
				     cout<<"f1="<<f1<<", f2="<<f2_in<<endl;
				   }
				 }
				 db.commit(); // commit transaction

				}

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

				  db.rlogon("scott/tiger@mssql2008"); // connect to ODBC

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

				  db.direct_exec
				   (
				    "create table test_tab(f1 int not null identity(1000,1), f2
varchar(30))"
				    );  // create table

				  db.direct_exec
				   (
				    "drop procedure prc_1",
				    otl_exception::disabled // disable OTL exceptions
				   ); // drop table

				  db<<
				    "create procedure prc_1 "
				    " @f1 int out, "
				    " @f2 varchar(30) "
				    "as "
				    "set nocount on "
				    "insert into test_tab (f2) values(@f2) "
				    "set @f1=scope_identity() ";
				  insert(); // insert records into 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 Oracle

				 return 0;

				}
///////////////////////////////////////////////////////////////////////////////

and at the same time, i have test my previous code by cancel the
otl_implicit_select in otl_stream and gotten the correct result too.
So, I think the problem is from this parameter. the documents from otl
have said about it like this.
		otl_implicit_select -- to indicate that the stream is a stored
procedure call that returns a result set.
		If the stream returns a result set via a stored procedure call, this
parameter needs to be set to otl_implicit_select.
I think this is an important parameter that must be careful to use.

and the next is the correct code
/////////////////////////////////////////////////////////////
				#include <iostream>
				using namespace std;
				#include <stdio.h>

				#define OTL_ODBC
				#include "otlv4.h" // include the OTL 4.0 header file

				otl_connect db; // connect object

				int main()
				{
					otl_connect::otl_initialize(); // initialize ODBC environment
					try{
						db.rlogon("dev/11111111@mssql_dev"); // connect to ODBC

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

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

						otl_cursor::direct_exec(db, "DROP PROCEDURE my_insert", 0);
						otl_cursor::direct_exec(db,
							" create procedure my_insert "
							" @f2 int  "
							"as "
				//			"set nocount on " // whether using it or not, the results is
correct.
							"insert into test_tab (f2) values(@f2) "
							);

						otl_stream i(50, // buffer size
							" exec  my_insert :f2<int,in> ",
							db
				// 			, otl_implicit_select // cancel it
							);
						i.set_commit(0);

						int f1=20;

						try{
							i<<f1;
							db.commit();
						}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();
						}
					}
					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 ODBC

					return 0;
				}
/////////////////////////////////////////////////////////


I am so glad that this problem can be resolved so quickly. it's so
important to my project. SKuchin is so great man, he give us otl, give
me the method to resolve my problem. Thank him very much here.

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

problem from proc call and autoincreasing key in using otl white_ideal <whiteideal@gmail.com> - 2011-06-12 17:08 -0700
  Re: problem from proc call and autoincreasing key in using otl Erland Sommarskog <esquel@sommarskog.se> - 2011-06-13 07:23 +0000
    Re: problem from proc call and autoincreasing key in using otl Gene Wirchenko <genew@ocis.net> - 2011-06-13 07:28 -0700
      Re: problem from proc call and autoincreasing key in using otl Erland Sommarskog <esquel@sommarskog.se> - 2011-06-13 23:59 +0200
        Re: problem from proc call and autoincreasing key in using otl Henk van den Berg <me@myplace.net> - 2011-06-14 08:35 +0200
  Re: problem from proc call and autoincreasing key in using otl white_ideal <whiteideal@gmail.com> - 2011-06-13 15:13 -0700
    Re: problem from proc call and autoincreasing key in using otl Erland Sommarskog <esquel@sommarskog.se> - 2011-06-14 23:39 +0200
      Re: problem from proc call and autoincreasing key in using otl white_ideal <whiteideal@gmail.com> - 2011-06-14 15:36 -0700
        Re: problem from proc call and autoincreasing key in using otl white_ideal <whiteideal@gmail.com> - 2011-06-14 17:33 -0700
  Re: problem from proc call and autoincreasing key in using otl white_ideal <whiteideal@gmail.com> - 2011-06-14 17:34 -0700

csiph-web