Skip to Content.
Sympa Menu

freetds - RE: [freetds] Return the InsertID after an Insert?

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: "FreeTDS Development Group" <freetds AT lists.ibiblio.org>
  • Subject: RE: [freetds] Return the InsertID after an Insert?
  • Date: Thu, 3 Mar 2005 11:46:39 -0000

Shaun,

You need a bit of db-library education!
I'd suggest you look at:

http://sybooks.sybase.com/onlinebooks/group-cn/cng1250e/dblib

or SQL server books online.

Also a good place to start would be to look at some of the freetds
db-library unitttest programs:

<freetds>/src/dblib/unittests

many of these go through the basic dblibrary API calls to select data
into your program,

t0002.c looks good.

Then lookup the various dblibrary API functions in SQL server BOL..

dbresults()
dbbind()
dbnextrow()


HTH,

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 11:34
To: FreeTDS Development Group
Subject: Re: [freetds] Return the InsertID after an Insert?


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


_______________________________________________
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/
--------------------------------------------------------





Archive powered by MHonArc 2.6.24.

Top of Page