 | |
problem from proc call and autoincreasing key in using otl
comp.databases.ms-sqlserver comp.databases.ms-sqlserver
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
problem from proc call and autoincreasing key in using otl -
06-12-2011
, 07:08 PM
#2
| |||
| |||
|
|
1. how to get the autoincreasing identity immediately after inserting a row? I how you can give me the answer within mysql and sqlserver. |
INSERT statement.
For MySQL, you need to ask in an MySQL forum.
Quote:
|
2. why the following code is error with "invalid cursor state"? the source code is like this. |
--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
#3
| |||
| |||
|
|
white_ideal (whiteideal (AT) gmail (DOT) 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. ^^^ |
Sincerely,
Gene Wirchenko
#4
| |||
| |||
|
|
On Mon, 13 Jun 2011 07:23:03 +0000 (UTC), Erland Sommarskog esquel (AT) sommarskog (DOT) se> wrote: I will have to admit that I have never seen this OTL before. ^^^ What is this abbrev, please? |
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, esquel (AT) sommarskog (DOT) 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
#5
| |||
| |||
|
|
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:  tl_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. |
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.
Quote:
|
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 |
implement in otl. And I am a little regret to use otl in my project,
but i have no choice at present.
Quote:
|
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 |
Thanks very much.
heiyun
#6
| |||
| |||
|
|
Hey, if, you do, can you report your findings, unless white_ideal comes back and tell us? |
#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
#7
| |||
| |||
|
|
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.
--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) 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
#8
| |||
| |||
|
|
white_ideal (whiteid... (AT) gmail (DOT) 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... (AT) sommarskog (DOT) 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 |
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.
#9
| |||
| |||
|
|
On 615, 5ʱ39, Erland Sommarskog <esq.... (AT) sommarskog (DOT) se> wrote: white_ideal (whiteid... (AT) gmail (DOT) 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... (AT) sommarskog (DOT) 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. |
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:
tl_initialize(); // initialize ODBC environmenttry{
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:
tl_initialize(); // initialize ODBC environmenttry{
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.
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.