List:MySQL ODBC« Previous MessageNext Message »
From:Al McNicoll Date:February 3 2011 9:44am
Subject:RE: Retrieving last_insert_id
View as plain text  
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:jerry@stripped] 
Sent: 21 January 2011 19:57
To: myodbc@stripped
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:jerry@stripped> jerry@stripped 

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

 


Thread
Retrieving last_insert_idJerry Schwartz21 Jan
  • RE: Retrieving last_insert_idJohn Bonnett2 Feb
    • RE: Retrieving last_insert_idJerry Schwartz3 Feb
  • RE: Retrieving last_insert_idAl McNicoll3 Feb
    • RE: Retrieving last_insert_idJerry Schwartz3 Feb
      • RE: Retrieving last_insert_idAl McNicoll3 Feb
        • RE: Retrieving last_insert_idLawson K. Cronlund3 Feb
        • RE: Retrieving last_insert_idJerry Schwartz3 Feb
  • Re: RE: Retrieving last_insert_idpwr3 Feb
    • RE: RE: Retrieving last_insert_idJerry Schwartz3 Feb