RE: RE: Retrieving last_insert_id

 

From: [email protected] [mailto:[email protected]] 
Sent: Thursday, February 03, 2011 4:59 AM
To: Al McNicoll
Cc: 'Jerry Schwartz'; [email protected]
Subject: Re: RE: Retrieving last_insert_id

 

hi,

Why not just use ...

SELECT record_ID FROM table ORDER BY record_ID DESC

that will work out no matter tech. setup

Best regards
Peter

[JS] You should never do that! It can’t be trusted in a multi-user environment, or even a single-user environment if it is complex enough. That’s why the function last_insert_id() exists.

 

Regards,

 

Jerry Schwartz

Global Information Incorporated

195 Farmington Ave.

Farmington, CT 06032

 

860.674.8796 / FAX: 860.674.8341

E-mail: [email protected] 

Web site: www.the-infoshop.com <http://www.the-infoshop.com/> 

 

 






----- Original meddelelse -----

Fra: Al McNicoll <[email protected]>
Til: 'Jerry Schwartz' <[email protected]>, [email protected]
Dato: Tor, 03. feb 2011 10:44
Emne: RE: Retrieving last_insert_id

Hi Jerry,

There is always the possibility of using a trigger on INSERT - would that solve your problem? I think that prior to MySQL 5.1 you need SUPER privileges to work with triggers, after that it's a separate permission. I see no reason why an INSERT trigger wouldn't pick up the correct value from the autoincremented column. Alternatively, you could put the trigger on UPDATE (on UPDATE SET b=autoidfield) and then simply run an UPDATE tbl SET autoidfield=autoidfield after your INSERT - that should still throw the trigger, and because the row's already been inserted, the autoincremented value will be set and retrievable.

Otherwise, what isn't working when you SELECT LAST_INSERT_ID() via MyODBC? I code a global function into all my VBA/MyODBC projects that takes a ADODB connection and returns the last insert id just by running that select. As long as you maintain the same connection object for the INSERT and the subsequent SELECT (or in your case UPDATE) then last_insert_id should be retrieved correctly.

If not, could you post back what you do get?

Hope that helps,

Al


-----Original Message-----
From: Jerry Schwartz [mailto:[email protected]] 
Sent: 21 January 2011 19:57
To: [email protected]
Subject: Retrieving last_insert_id

I have an auto_increment field in my table, but I need to set another field to the same value. (Please don?t ask why.)



It seems I would need to insert the record, and then update it; but I haven?t figured out how to get the value of last_insert_id().



Any ideas?



Regards,



Jerry Schwartz

Global Information Incorporated

195 Farmington Ave.

Farmington, CT 06032



860.674.8796 / FAX: 860.674.8341

E-mail: <mailto:[email protected]> [email protected] 

Web site: <http://www.the-infoshop.com/> www.the-infoshop.com





--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/[email protected]