Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]
Groups > comp.databases.ms-sqlserver > #458
| From | white_ideal <whiteideal@gmail.com> |
|---|---|
| Newsgroups | comp.databases.ms-sqlserver |
| Subject | Re: problem from proc call and autoincreasing key in using otl |
| Date | 2011-06-14 17:34 -0700 |
| Organization | http://groups.google.com |
| Message-ID | <eda6fdd7-5ee6-403d-9c06-041442fdb655@34g2000pru.googlegroups.com> (permalink) |
| References | <3c4bb9a9-97ed-4a95-9982-2afd993017bd@34g2000pru.googlegroups.com> |
On 6月13日, 上午8时08分, white_ideal <whiteid...@gmail.com> wrote:
> hi, everyone, thanks for your reading. I have met with some problem
> when using otl to program for the database. And my development
> environment is sqlserver2000 plus vs2008.
> 1. how to get the autoincreasing identity immediately after inserting
> a row? I how you can give me the answer within mysql and sqlserver.
>
> 2. why the following code is error with "invalid cursor state"?
> the source code is like this.
>
> //////////////////////////////////
> #include <iostream>
> using namespace std;
> #include <stdio.h>
>
> #define OTL_ODBC // Compile OTL 4/ODBC, MS SQL 2008
> #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(f1 int)"
> ); // create table
>
> otl_cursor::direct_exec(db, "DROP PROCEDURE my_insert", 0);
> otl_cursor::direct_exec(db,
> "CREATE PROCEDURE my_insert "
> "@F1 int "
> "as "
> "set nocount on "
> "insert into test_tab(f1) values(@F1) "
> );
>
> otl_stream i(50, // buffer size
> " exec my_insert :f1<int,in> ",
> db, // connect object
> otl_implicit_select // implicit SELECT statement
> );
>
> int f1=20;
>
> try{
> i<<f1;
> }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;
> }
>
> and the result after running is shown in the following.
>
> ===> A database exception is caught:
> [Microsoft][ODBC SQL Server Driver]Invalid cursor state
> exec my_insert ?
>
> ===> Cleaning up the stream's error flags
> Press any key to continue . . .
>
> I hope these two problems can be resoved as soon as possible. And
> very thanks for your helps in advance.
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 | Find similar
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