Bookmarks

Yahoo Gmail Google Facebook Delicious Twitter Reddit Stumpleupon Myspace Digg

Search queries

nrao wwwxxx, xxxxxdup, procmail change subject header, wwwXxx not20, Wwwxxx.doks sas, linux raid resync after reboot, bind-address mysql multiple, sanibleone xxxx, ftp://192.168.100.100/, www.xxxcon

Links

XODOX
Impressum

#1: Retrieving last_insert_id

Posted on 2011-01-21 20:56:32 by Jerry Schwartz

------=_NextPart_000_009B_01CBB97B.65201B30
Content-Type: text/plain;
charset="utf-8"
Content-Transfer-Encoding: quoted-printable

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

=20

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

=20

Any ideas?

=20

Regards,

=20

Jerry Schwartz

Global Information Incorporated

195 Farmington Ave.

Farmington, CT 06032

=20

860.674.8796 / FAX: 860.674.8341

E-mail: <mailto:jerry@gii.co.jp> jerry@gii.co.jp=20

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

=20


------=_NextPart_000_009B_01CBB97B.65201B30--

Report this message

#2: RE: Retrieving last_insert_id

Posted on 2011-02-02 23:23:52 by John.Bonnett

If I understand you correctly you already have the the last inserted ID
in another column in the same table so you just need to copy it. You
need to make sure you only update the row you just added though. This
should do it -

UPDATE <table> SET <other column> =3D <primary ID column>
WHERE <primary ID column> =3D LAST_INSERT_ID();

You need to do that immediately after the insert on the same connection
for the value of the last insert ID to be correct.

John Bonnett

-----Original Message-----
From: Jerry Schwartz [mailto:jerry@gii.co.jp]=20
Sent: Saturday, 22 January 2011 6:27 AM
To: myodbc@lists.mysql.com
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.)

=20

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().

=20

Any ideas?

=20

Regards,

=20

Jerry Schwartz

Global Information Incorporated

195 Farmington Ave.

Farmington, CT 06032

=20

860.674.8796 / FAX: 860.674.8341

E-mail: <mailto:jerry@gii.co.jp> jerry@gii.co.jp=20

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

=20


--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=3Dgcdmo-myodbc@m.gmane.o rg

Report this message

#3: RE: Retrieving last_insert_id

Posted on 2011-02-03 02:45:38 by Jerry Schwartz

>-----Original Message-----
>From: Bonnett, John [mailto:John.Bonnett@vision.zeiss.com]
>Sent: Wednesday, February 02, 2011 5:24 PM
>To: jerry@gii.co.jp; myodbc@lists.mysql.com
>Subject: RE: Retrieving last_insert_id
>
>If I understand you correctly you already have the the last inserted ID
>in another column in the same table so you just need to copy it. You
>need to make sure you only update the row you just added though. This
>should do it -
>
>UPDATE <table> SET <other column> = <primary ID column>
>WHERE <primary ID column> = LAST_INSERT_ID();
>
>You need to do that immediately after the insert on the same connection
>for the value of the last insert ID to be correct.
>
[JS] Unfortunately, trying to SELECT LAST_INSERT_ID() as a pass-through query
from Access gave me results I did not understand. I don't know if your way
would work. I basically don't know what constitutes a "connection" in this
particular environment.

In any case, I just did away with this redundant field. That might not have
been elegant, but it was effective and efficient.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: jerry@gii.co.jp
Web site: www.the-infoshop.com



>John Bonnett
>
>-----Original Message-----
>From: Jerry Schwartz [mailto:jerry@gii.co.jp]
>Sent: Saturday, 22 January 2011 6:27 AM
>To: myodbc@lists.mysql.com
>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@gii.co.jp> jerry@gii.co.jp
>
>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=gcdmo-myodbc@m.gmane.org

Report this message

#4: RE: Retrieving last_insert_id

Posted on 2011-02-03 10:44:36 by Al McNicoll

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=3Dautoidfield) and then simply run an =
UPDATE tbl SET autoidfield=3Dautoidfield 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@gii.co.jp]=20
Sent: 21 January 2011 19:57
To: myodbc@lists.mysql.com
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âÂ=99t ask why.)

=20

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

=20

Any ideas?

=20

Regards,

=20

Jerry Schwartz

Global Information Incorporated

195 Farmington Ave.

Farmington, CT 06032

=20

860.674.8796 / FAX: 860.674.8341

E-mail: <mailto:jerry@gii.co.jp> jerry@gii.co.jp=20

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

=20



--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=3Dgcdmo-myodbc@m.gmane.o rg

Report this message

#5: Re: RE: Retrieving last_insert_id

Posted on 2011-02-03 10:59:15 by PeterWR

--1296727155.7f4af5F1.8544
Date: Thu, 3 Feb 2011 10:59:15 +0100
MIME-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable

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





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

> Fra: Al McNicoll <al@integritec.co.uk>
> Til: 'Jerry Schwartz' <jerry@gii.co.jp>, myodbc@lists.mysql.com
> Dato: Tor, 03. feb 2011 10:44
> Emne: RE: Retrieving last_insert_id
>=20
> Hi Jerry,
>=20
> 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=3Dautoidfield) and
> then simply run an UPDATE tbl SET autoidfield=3Dautoidfield 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.
>=20
> 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.
>=20
> If not, could you post back what you do get?
>=20
> Hope that helps,
>=20
> Al
>=20
>=20
> -----Original Message-----
> From: Jerry Schwartz [mailto:jerry@gii.co.jp]
> Sent: 21 January 2011 19:57
> To: myodbc@lists.mysql.com
> Subject: Retrieving last_insert_id
>=20
> 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.)
>=20
>=20
>=20
> 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().
>=20
>=20
>=20
> Any ideas?
>=20
>=20
>=20
> Regards,
>=20
>=20
>=20
> Jerry Schwartz
>=20
> Global Information Incorporated
>=20
> 195 Farmington Ave.
>=20
> Farmington, CT 06032
>=20
>=20
>=20
> 860.674.8796 / FAX: 860.674.8341
>=20
> E-mail: <mailto:jerry@gii.co.jp> jerry@gii.co.jp
>=20
> Web site: <http://www.the-infoshop.com/> www.the-infoshop.com
>=20
>=20
>=20
>=20
>=20
> --
> MySQL ODBC Mailing List
> For list archives: http://lists.mysql.com/myodbc
> To unsubscribe: http://lists.mysql.com/myodbc?unsub=3Dpwr@post4.tele.dk


--1296727155.7f4af5F1.8544--

Report this message

#6: RE: RE: Retrieving last_insert_id

Posted on 2011-02-03 16:23:00 by Jerry Schwartz

------=_NextPart_000_0053_01CBC38C.56284AB0
Content-Type: text/plain;
charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

=20

From: pwr@post4.tele.dk [mailto:pwr@post4.tele.dk]=20
Sent: Thursday, February 03, 2011 4:59 AM
To: Al McNicoll
Cc: 'Jerry Schwartz'; myodbc@lists.mysql.com
Subject: Re: RE: Retrieving last_insert_id

=20

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âÂ=99t be trusted in a =
multi-user environment, or even a single-user environment if it is =
complex enough. ThatâÂ=99s why the function last_insert_id() exists.

=20

Regards,

=20

Jerry Schwartz

Global Information Incorporated

195 Farmington Ave.

Farmington, CT 06032

=20

860.674.8796 / FAX: 860.674.8341

E-mail: jerry@gii.co.jp=20

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

=20

=20






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

Fra: Al McNicoll <al@integritec.co.uk>
Til: 'Jerry Schwartz' <jerry@gii.co.jp>, myodbc@lists.mysql.com
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=3Dautoidfield) and then simply run an =
UPDATE tbl SET autoidfield=3Dautoidfield 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@gii.co.jp]=20
Sent: 21 January 2011 19:57
To: myodbc@lists.mysql.com
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@gii.co.jp> jerry@gii.co.jp=20

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=3Dpwr@post4.tele.dk


------=_NextPart_000_0053_01CBC38C.56284AB0--

Report this message

#7: RE: Retrieving last_insert_id

Posted on 2011-02-03 16:23:00 by Jerry Schwartz

>-----Original Message-----
>From: Al McNicoll [mailto:al@integritec.co.uk]
>Sent: Thursday, February 03, 2011 4:45 AM
>To: 'Jerry Schwartz'; myodbc@lists.mysql.com
>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@gii.co.jp
Web site: www.the-infoshop.com



>Al
>
>
>-----Original Message-----
>From: Jerry Schwartz [mailto:jerry@gii.co.jp]
>Sent: 21 January 2011 19:57
>To: myodbc@lists.mysql.com
>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@gii.co.jp> jerry@gii.co.jp
>
>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=gcdmo-myodbc@m.gmane.org

Report this message

#8: RE: Retrieving last_insert_id

Posted on 2011-02-03 16:32:41 by Al McNicoll

>> [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@gii.co.jp]
Sent: 03 February 2011 15:23
To: 'Al McNicoll'; myodbc@lists.mysql.com
Subject: RE: Retrieving last_insert_id

>-----Original Message-----
>From: Al McNicoll [mailto:al@integritec.co.uk]
>Sent: Thursday, February 03, 2011 4:45 AM
>To: 'Jerry Schwartz'; myodbc@lists.mysql.com
>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@gii.co.jp
Web site: www.the-infoshop.com



>Al
>
>
>-----Original Message-----
>From: Jerry Schwartz [mailto:jerry@gii.co.jp]
>Sent: 21 January 2011 19:57
>To: myodbc@lists.mysql.com
>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@gii.co.jp> jerry@gii.co.jp
>
>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=gcdmo-myodbc@m.gmane.org

Report this message

#9: RE: Retrieving last_insert_id

Posted on 2011-02-03 16:59:57 by Lawson Cronlund

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@vrtinc.com
+1(480)308-0641


-----Original Message-----
From: Al McNicoll [mailto:al@integritec.co.uk]
Sent: Thursday, February 03, 2011 8:33 AM
To: 'Jerry Schwartz'; myodbc@lists.mysql.com
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@gii.co.jp]
Sent: 03 February 2011 15:23
To: 'Al McNicoll'; myodbc@lists.mysql.com
Subject: RE: Retrieving last_insert_id

>-----Original Message-----
>From: Al McNicoll [mailto:al@integritec.co.uk]
>Sent: Thursday, February 03, 2011 4:45 AM
>To: 'Jerry Schwartz'; myodbc@lists.mysql.com
>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@gii.co.jp
Web site: www.the-infoshop.com



>Al
>
>
>-----Original Message-----
>From: Jerry Schwartz [mailto:jerry@gii.co.jp]
>Sent: 21 January 2011 19:57
>To: myodbc@lists.mysql.com
>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@gii.co.jp> jerry@gii.co.jp
>
>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=lawson@vrtinc.com


--
MySQL ODBC Mailing List
For list archives: http://lists.mysql.com/myodbc
To unsubscribe: http://lists.mysql.com/myodbc?unsub=gcdmo-myodbc@m.gmane.org

Report this message

#10: RE: Retrieving last_insert_id

Posted on 2011-02-03 20:31:43 by Jerry Schwartz

>-----Original Message-----
>From: Al McNicoll [mailto:al@integritec.co.uk]
>Sent: Thursday, February 03, 2011 10:33 AM
>To: 'Jerry Schwartz'; myodbc@lists.mysql.com
>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
>
[JS] No, it won't.

Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: jerry@gii.co.jp
Web site: www.the-infoshop.com



>
>-----Original Message-----
>From: Jerry Schwartz [mailto:jerry@gii.co.jp]
>Sent: 03 February 2011 15:23
>To: 'Al McNicoll'; myodbc@lists.mysql.com
>Subject: RE: Retrieving last_insert_id
>
>>-----Original Message-----
>>From: Al McNicoll [mailto:al@integritec.co.uk]
>>Sent: Thursday, February 03, 2011 4:45 AM
>>To: 'Jerry Schwartz'; myodbc@lists.mysql.com
>>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@gii.co.jp
>Web site: www.the-infoshop.com
>
>
>
>>Al
>>
>>
>>-----Original Message-----
>>From: Jerry Schwartz [mailto:jerry@gii.co.jp]
>>Sent: 21 January 2011 19:57
>>To: myodbc@lists.mysql.com
>>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@gii.co.jp> jerry@gii.co.jp
>>
>>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=gcdmo-myodbc@m.gmane.org

Report this message