[freetds] SELECT @@IDENTITY AS InsertID

Thompson, Bill D (London) bill_d_thompson at ml.com
Thu Oct 6 11:48:23 EDT 2005


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


More information about the FreeTDS mailing list