List:MySQL ODBC« Previous MessageNext Message »
From:Lawson K. Cronlund Date:February 3 2011 3:59pm
Subject:RE: Retrieving last_insert_id
View as plain text  
My experience also is that a BEFORE INSERT allows you to modify the record
associated with the INSERT.  Don't try it via ODBC though.

Regards,


Lawson Cronlund
lawson@stripped
+1(480)308-0641


-----Original Message-----
From: Al McNicoll [mailto:al@stripped] 
Sent: Thursday, February 03, 2011 8:33 AM
To: 'Jerry Schwartz'; myodbc@stripped
Subject: RE: Retrieving last_insert_id

>> [JS] A trigger won't let you modify the table that is associated with the
trigger. For example, you can't UPDATE the table you did an INSERT into.
[ARM] Is that definitely right? I thought with a BEFORE INSERT you could use
the NEW table variable to change values. From the MySQL docs:

"A column named with NEW can be referred to if you have the SELECT privilege
for it. In a BEFORE trigger, you can also change its value with SET
NEW.col_name = value if you have the UPDATE privilege for it. This means you
can use a trigger to modify the values to be inserted into a new row or that
are used to update a row."

I don't know whether NEW.autoidfield will have a value at this stage though?

Al


-----Original Message-----
From: Jerry Schwartz [mailto:jerry@stripped] 
Sent: 03 February 2011 15:23
To: 'Al McNicoll'; myodbc@stripped
Subject: RE: Retrieving last_insert_id

>-----Original Message-----
>From: Al McNicoll [mailto:al@stripped]
>Sent: Thursday, February 03, 2011 4:45 AM
>To: 'Jerry Schwartz'; myodbc@stripped
>Subject: 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.
>
[JS] A trigger won't let you modify the table that is associated with the 
trigger. For example, you can't UPDATE the table you did an INSERT into.

>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.
>
[JS] I wasn't using an ADO connection. I'm using a form's events. The
logical 
place to put this was in the AfterInsert event handler. I think that the 
form's inner workings open and close the connection.

Unfortunately, the MyODBC traces only capture the actual queries, so I'm not

sure what is really going on. The workings of Access are mysterious indeed, 
and it watches everything you do very closely. I didn't do the experiment,
but 
I think that trying to update that field with anything would trigger the 
form's update events, and I could get endless recursion.

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

[JS] I will if I have time. As I recall, I got back 0 in this particular
case.
>
>Hope that helps,
>
[JS] Thanks for the thoughts.

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



>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