problem from proc call and autoincreasing key in using otl
white_ideal
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.
Erland Sommarskog
> 1. how to get the autoincreasing identity immediately after inserting
> a row? I how you can give me the answer within mysql and sqlserver.
In SQL Server, use scope_identity(), which must be in the same batch as the
INSERT statement.
For MySQL, you need to ask in an MySQL forum.
> 2. why the following code is error with "invalid cursor state"?
> the source code is like this.
I will have to admit that I have never seen this OTL before.
--
Erland Sommarskog, SQL Server MVP, esq...@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Gene Wirchenko
<esq...@sommarskog.se> wrote:
>white_ideal (white...@gmail.com) writes:
>> 1. how to get the autoincreasing identity immediately after inserting
>> a row? I how you can give me the answer within mysql and sqlserver.
>
>In SQL Server, use scope_identity(), which must be in the same batch as the
>INSERT statement.
>
>For MySQL, you need to ask in an MySQL forum.
>
>> 2. why the following code is error with "invalid cursor state"?
>> the source code is like this.
>
>I will have to admit that I have never seen this OTL before.
^^^
What is this abbrev, please?
Sincerely,
Gene Wirchenko
Erland Sommarskog
walk over to Google, you can find out. Hey, if, you do, can you report
your findings, unless white_ideal comes back and tell us?
--
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
white_ideal
hi, guys, very thanks for your excellent answer.
this is the website for otl: http://otl.sourceforge.net/. It's a c++
template lib for odbc. I use it in my project to access databases.
And the following is my reply about your answer.
> >> 1. how to get the autoincreasing identity immediately after inserting
> >> a row? I how you can give me the answer within mysql and sqlserver.
>
> >In SQL Server, use scope_identity(), which must be in the same batch as the
> >INSERT statement.
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.
> >> 2. why the following code is error with "invalid cursor state"?
> >> ? ? ? ? ? the source code is like this.
>
> >I will have to admit that I have never seen this OTL before.
As i said above, it's a c++ template lib for odbc. and the website is
otl.sourceforge.net.
Thanks very much.
heiyun
Henk van den Berg
> Hey, if, you do, can you report
> your findings, unless white_ideal comes back and tell us?
In the OP there was a line
#define OTL_ODBC // Compile OTL 4/ODBC, MS SQL 2008
so I did a Google search on "otl odbc"
Hit #4 has this description:
15 Feb 2010 ... OTL is a C++ template library. It is a wrapper around
the Oracle, ODBC and DB2-CLI database APIs. When you use the library, it
is expanded ...
Best,
Henk
Erland Sommarskog
> 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.
"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.
white_ideal
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.
white_ideal
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.
white_ideal
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()
}
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;
}