List:MySQL ODBC« Previous MessageNext Message »
From:Jerry Schwartz Date:February 3 2011 3:23pm
Subject:RE: RE: Retrieving last_insert_id
View as plain text  
 

From: pwr@stripped [mailto:pwr@stripped] 
Sent: Thursday, February 03, 2011 4:59 AM
To: Al McNicoll
Cc: 'Jerry Schwartz'; myodbc@stripped
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: jerry@stripped 

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

 

 






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

Fra: Al McNicoll <al@stripped>
Til: 'Jerry Schwartz' <jerry@stripped>, myodbc@stripped
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: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





--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=1



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