[freetds] Return the InsertID after an Insert?

craigs s.craig at andronics.com
Thu Mar 3 06:33:58 EST 2005


This is proving abit harder than i thought! Im new enough to freetds and 
so far i've only had to do basic querys and inserts which is easy enough.

my problem now is i can get the value i want but dont know how to pass 
it into a variable, eg...

INSERT INTO Table(column1,column2,etc...) VALUES (etc..........) SELECT 
@@IDENTITY AS 'Identity'

when i run this query in tsql it returns the value i want under the 
column heading Identity.  So how do i get the value and pass it into a 
variable?

i use dbsqlexec to execute the query, i tried calling dbresults, dbbind, 
and dbnextrow after as you would for a query but cant get the value 
stored in Identity.

any ideas?

Shaun.


craigs wrote:

> Thanks guys, this is what i'm after.
>
> Shaun.
>
> Thompson, Bill D (London) wrote:
>
>> Shaun, Rob,
>>
>> It looks like MySQL has a feature called an "AUTO_INCREMENT" column on
>> tables.
>> This looks very much like an identity column in SQL Server, AFAICT from
>> a quick google.
>> If this is what you're after, I'd do as Rob suggests.
>>
>> You'd have to "select @@IDENTITY" after your insert and then bring the
>> value back into your program by binding and fetching, as you would in a
>> normal select.
>>
>> Bill
>> -----Original Message-----
>> From: freetds-bounces at lists.ibiblio.org
>> [mailto:freetds-bounces at lists.ibiblio.org] On Behalf Of Owen, Robert
>> Sent: 03 March 2005 10:06
>> To: FreeTDS Development Group
>> Subject: RE: [freetds] Return the InsertID after an Insert?
>>
>>
>> Do you mean the identity value from a table?
>>
>> If so suggest RETURN @@IDENTITY and use the return value in db-lib
>>
>> Rob.
>>
>> -----Original Message-----
>> From: freetds-bounces at lists.ibiblio.org
>> [mailto:freetds-bounces at lists.ibiblio.org] On Behalf Of craigs
>> Sent: 03 March 2005 10:02
>> To: FreeTDS Development Group
>> Subject: Re: [freetds] Return the InsertID after an Insert?
>>
>>
>> I should have explained it better! what i mean is the row number the 
>> Insert was inserted into, in MySQL its a function called 
>> mysql_insert_id, eg..
>>
>> row no.       data
>> 3               Aston Villa
>> 4               Man Utd.
>>
>> if i inserted Arsenal next the row would be 5, thats what i want to 
>> return.  Is this possible in freetds or will i just have to do 
>> another query?
>>
>> Thanks,
>> Shaun.
>>
>> Thompson, Bill D (London) wrote:
>>
>>  
>>
>>> Shaun
>>>
>>> AFAIK, there's no such concept as an "insert ID" in sql server.
>>> What does it represent in MySQL ? You can get a "number of rows 
>>> affected" value...
>>>
>>> Bill
>>>
>>> -----Original Message-----
>>> From: freetds-bounces at lists.ibiblio.org
>>> [mailto:freetds-bounces at lists.ibiblio.org] On Behalf Of craigs
>>> Sent: 03 March 2005 09:55
>>> To: FreeTDS Development Group
>>> Subject: [freetds] Return the InsertID after an Insert?
>>>
>>>
>>> Is it possible in db-lib to return the insertid of a record when its 
>>> inserted, eg. insertid = dbsqlexec("Insert into .....etc") I know this
>>> can be done in mysql but dont know about ms sql, any info would be
>>> great.
>>>
>>> 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
>>>
>>>
>>>   
>>
>>
>> _______________________________________________
>> FreeTDS mailing list
>> FreeTDS at lists.ibiblio.org
>> http://lists.ibiblio.org/mailman/listinfo/freetds
>>
>> ______________________________________________________________________
>> This email has been scanned by the MessageLabs Email Security System.
>> For more information please visit http://www.messagelabs.com/email 
>> ______________________________________________________________________
>>
>>
>> _____________________________________________________________________
>> CONFIDENTIALITY NOTICE
>>
>> The contents of this e-mail are confidential to the ordinary user of
>> the e-mail address to which it was addressed and may also be
>> privileged.  If you are not the addressee of this e-mail you may not
>> copy, forward, disclose or otherwise use it or any part of it in any
>> form whatsoever.  If you have received this e-mail in error please
>> e-mail the sender by replying to this message.
>>
>> A list of the partners of Norton Rose, Solicitors, can be inspected at
>> www.nortonrose.com
>>
>> Please refer to www.nortonrose.com/regulation for the firm's regulatory
>> position.
>>
>> Regulated by the Law Society.
>> ______________________________________________________________________
>>
>> _______________________________________________
>> 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
>>  
>>
>
> _______________________________________________
> FreeTDS mailing list
> FreeTDS at lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds





More information about the FreeTDS mailing list