Skip to Content.
Sympa Menu

freetds - Re: [freetds] SELECT @@IDENTITY AS InsertID

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: "Thompson, Bill D (London)" <bill_d_thompson AT ml.com>
  • To: <s.craig AT andronics.com>, "FreeTDS Development Group" <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] SELECT @@IDENTITY AS InsertID
  • Date: Thu, 6 Oct 2005 16:48:23 +0100

Hi Shaun,

in theory then you should be OK, if the program works as you say.

the sequence of dblibrary calls would be:

dbcmd() /* insert into table... */
dbsqlexec()
dbresults()

dbcmd() /* select @@identity */
dbsqlexec()
dbresults()
dbbind()
dbnextrow()

which, although you are cutting corners, *should* work.

1) have you defined a dblibrary error handler ?
If you are not checking the return of each dblibrary call,
and you have no error/message handlers, then you could be missing
a dblibrary error.

2) you really should call dbresults in a loop until it returns
NO_MORE_RESULTS.
You can get away with not doing it, but it's not ideal practice.

A TDSDUMP log will tell us whats going on pretty quickly. perhaps you
could produce one and mail it.

Bill

-----Original Message-----
From: freetds-bounces AT lists.ibiblio.org
[mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of Shaun Craig
Sent: 06 October 2005 16:37
To: FreeTDS Development Group
Subject: Re: [freetds] SELECT @@IDENTITY AS InsertID


Right i'm trying to get my head round this, i'm doing things slightly
different in my code, as follows

Query = 'Insert Into Table .......etc Select @@Identity as InsertId'

I break the query string up depending on how many statements are in it,
so for above i have 2, 1 for insert and 1 for select

for ( x = 0; x < amount of statments + 1; x++ )
{
//first time around it will be the insert which inserts ok
dbcmd(dbconn, statements[x])
dbsqlexec(dbconn)
dbresults(dbconn)

if we find that the statement is a 'Select @@Identity..etc' we go in

here, we wont the first time but when we go around the second time we
will go in.
{
// try bind select to variable
dbbind(dbconn, 1, INTBIND, (DBINT)0, (BYTE *)& InsertID); //its
says its binding ok but what its binding i cant be sure!

if (dbnextrow(dbconn) != NO_MORE_ROWS)
journey_insert_id = InsertID;
}
}

So in theory im calling dbresults for each statment, cant think of way
around this yet!

Thompson, Bill D (London) wrote:

>Hi shaun,
>
>you may need to call dbresults more than once before binding and
>nextrow-ing
>
>you would get a dbresults return for the insert statemnt and then
>another for the select
>
>you can check it with DBROWS as per this bit from the sybase manual:
>
>while ((return_code = dbresults(dbproc)!=NO_MORE_RESULTS)
> {
> if ((return_code == SUCCEED) && (DBROWS(dbproc) == SUCCEED))
> {
> /* Bind column data to program variables */
> dbbind(dbproc, 1, INTBIND, (DBINT) 0,
> (BYTE *) &xvariable);
> dbbind(dbproc, 2, STRINGBIND, (DBINT) 0,
> yvariable);
>
>
> /* Now process each row */
> while (dbnextrow(dbproc) != NO_MORE_ROWS)
> {
>
>
>HTH,
>
>Bill
>
>
>-----Original Message-----
>From: freetds-bounces AT lists.ibiblio.org
>[mailto:freetds-bounces AT lists.ibiblio.org] On Behalf Of Shaun Craig
>Sent: 06 October 2005 15:50
>To: FreeTDS Development Group
>Subject: [freetds] SELECT @@IDENTITY AS InsertID
>
>
>Hi all,
>
>I've got an insert statement with SELECT @@IDENTITY AS InsertID at the
>end as I want to know what row the record was inserted into. I know
the
>
>line I use works as i've tested it through tsql and its gives me back
>the correct row number. The problem is in my C code i cant get the
>value passed back to me, this is my code so far.
>
>DBINT InsertID;
>journey_id //global variable of type int
>Query //which contains my insert statement and SELECT @@IDENTITY AS
>InsertID
>
>dbfcmd(dbconn, Query)
>dbsqlexec(dbconn)
>dbresults(dbconn)
>
>//Now i'm trying to bind the returned row number into InsertID
>dbbind(dbconn, 1, INTBIND, (DBINT)0, (BYTE *)& InsertID);
>
>if (dbnextrow(dbconn) != NO_MORE_ROWS)
>{
> journey_id = InsertID;
>}
>
>Unfortunately my program crashes either at the bind or dbnextrow() as
>i'm doing something wrong.
>
>Any help would be appreciated as this is holding me back from other
>stuff, thanks.
>
>Shaun.
>_______________________________________________
>FreeTDS mailing list
>FreeTDS AT lists.ibiblio.org
>http://lists.ibiblio.org/mailman/listinfo/freetds
>--------------------------------------------------------
>
>If you are not an intended recipient of this e-mail, please notify the
sender, delete it and do not read, act upon, print, disclose, copy,
retain or redistribute it. Click here for important additional terms
relating to this e-mail. http://www.ml.com/email_terms/
>--------------------------------------------------------
>_______________________________________________
>FreeTDS mailing list
>FreeTDS AT lists.ibiblio.org
>http://lists.ibiblio.org/mailman/listinfo/freetds
>
>


--
Shaun Craig
Andronics Ltd.
Tel: 02871273118
Web: http://www.andronics.co.uk

_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds




Archive powered by MHonArc 2.6.24.

Top of Page