last insert id

Hello,
I am using DBI Version 1.42 on OSX, accessing a MySQL database on
Linux (Mandrake)

Previously I have been using DBI with mysql on Linux, I always used
$sth=$dbh->prepare("INSERT INTO ......");
$sth->execute() or die();
$ID = $sth->{insertid};
To obtain the last insert id.


But this does not seem to work on OSX with DBI 1.42 there is no error
it just Returns undef.
The documentation has
$ID = $dbh->last_insert_id();

The parameters are ignored for MySQL, but this does not work either

I found on the net
$ID = $dbh->{'mysql_insertid'};

Why dont the other two work, and is there a more portable method to
obtain the last insert id.

Thank you
Stuart Gall

Re: last insert id

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


> I am using DBI Version 1.42 on OSX, accessing a MySQL database on
....

The last_insert_id() method was broken prior to 1.43, so at the very
least, you should install a newer version of DBI.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200603231124
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B90 6714964AC8
-----BEGIN PGP SIGNATURE-----

iD8DBQFEIsvNvJuQZxSWSsgRAmsKAJ4ingxlHpxQGZ/OYDuhdjJdOI9YbACg 74M4
a5DK5VqKK6Pw45eVMWrRI20=
=ZyTK
-----END PGP SIGNATURE-----

Re: last insert id

On 23 Mar 2006, at 18:24, Greg Sabino Mullane wrote:

>
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
>
>> I am using DBI Version 1.42 on OSX, accessing a MySQL database on
> ...
>
> The last_insert_id() method was broken prior to 1.43, so at the very
> least, you should install a newer version of DBI.

Good Call, I have upgraded to 1.50, but alas *still* only
$dbh->{'mysql_insertid'} works.


Stuart.

Re: last insert id

On Thu, Mar 23, 2006 at 11:22:12PM +0200, Stuart Gall wrote:
> On 23 Mar 2006, at 18:24, Greg Sabino Mullane wrote:
>
> >>I am using DBI Version 1.42 on OSX, accessing a MySQL database on
> >...
> >
> >The last_insert_id() method was broken prior to 1.43, so at the very
> >least, you should install a newer version of DBI.
>
> Good Call, I have upgraded to 1.50, but alas *still* only
> $dbh->{'mysql_insertid'} works.

The DBI doesn't implement the last_insert_id() method, the driver needs
to do that.

Tim.

Re: last insert id

Stuart Gall wrote:
>
> On 23 Mar 2006, at 18:24, Greg Sabino Mullane wrote:
>
>>
>> -----BEGIN PGP SIGNED MESSAGE-----
>> Hash: SHA1
>>
>>
>>> I am using DBI Version 1.42 on OSX, accessing a MySQL database on
>> ...
>>
>> The last_insert_id() method was broken prior to 1.43, so at the very
>> least, you should install a newer version of DBI.
>
> Good Call, I have upgraded to 1.50, but alas *still* only
> $dbh->{'mysql_insertid'} works.

I've never been able to get it working with MySQL either ( tried many
different DBI & DBD::mysql versions ). If you want something a *little*
more portable than $dbh->{'mysql_insertid'}, then you can use:

select @@IDENTITY

... which SQL Server ( and maybe even others ) understands as well.

Dan

Re: last insert id

On Sun, 16 Apr 2006 22:15:22 +1000, Daniel Kasak wrote:

Hi Daniel

DBI: V 1.47
DBD::mysql: V 3.002

sub last_insert_id
{
=09my($self, $table_name) =3D @_;

=09my($id);

=09if ($self -> db_vendor() =3D~ /(?:mysql|Pg)/)
=09{
$id =3D $self -> dbh() -> last_insert_id(undef, undef, $table_name, undef);
=09}
=09else # Oracle.
=09{
my($sth) =3D $self -> dbh() -> prepare("select ${table_name}_id_seq.currval=
from
dual");

$sth -> execute();

$id =3D ${$sth -> fetch()}[0];

$sth -> finish();
=09}

=09return $id;

}=09# End of last_insert_id.

--
Cheers
Ron Savage, ron@savage.net.au on 17/04/2006
http://savage.net.au/index.html
Let the record show: Microsoft is not an Australian company

Re: last insert id

Ron Savage schreef:

> sub last_insert_id [...]
> if ($self -> db_vendor() =~ /(?:mysql|Pg)/)

That regex can be written as /mysql|Pg/.

Notice that 'CertainlyNotPgAtAll' matches, so maybe you were looking for
/\A(?:mysql|pg)\z/i

MySQL's native LAST_INSERT_ID() will return the *first* id from a multi
row insert. I don't now what DBI does with that.

DBI doc last_insert_id() "For some drivers the $catalog, $schema,
$table, and $field parameters are required (e.g., Pg), for others they
are ignored (e.g., mysql)."

--
Affijn, Ruud

"Gewoon is een tijger."

Re: last insert id

On Mon, 17 Apr 2006 15:29:03 +0200, Dr.Ruud wrote:

Hi

>> sub last_insert_id [...]
>> if ($self -> db_vendor() =3D~ /(?:mysql|Pg)/)
>>
> That regex can be written as /mysql|Pg/.

Correct.

> Notice that 'CertainlyNotPgAtAll' matches, so maybe you were
> looking for /\A(?:mysql|pg)\z/i

Nope. Method db_vendor() extracts the vendor's name from the connect string=

passed to DBI, so I don't see how it could return that :-))).

> MySQL's native LAST_INSERT_ID() will return the *first* id from a
> multi row insert. I don't now what DBI does with that.

Good point. But then my code doesn't come with a written guarantee to work=
with
multi-row inserts...

> DBI doc last_insert_id() "For some drivers the $catalog, $schema,
> $table, and $field parameters are required (e.g., Pg), for others
> they are ignored (e.g., mysql)."

Right. That's why they are undef for MySQL, and it seems the last time I=
tested
it under Pg, undef worked there too, but that was months ago. Proof to the
contrary should be published here.

--
Cheers
Ron Savage, ron@savage.net.au on 18/04/2006
http://savage.net.au/index.html
Let the record show: Microsoft is not an Australian company

Re: last insert id

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


>> DBI doc last_insert_id() "For some drivers the $catalog, $schema,
>> $table, and $field parameters are required (e.g., Pg), for others
>> they are ignored (e.g., mysql)."
>
> Right. That's why they are undef for MySQL, and it seems the last time I tested
> it under Pg, undef worked there too, but that was months ago. Proof to the
> contrary should be published here.

Yes, undef is fine for Pg: the only required attribute is the tablename, and even
that can be left off if a sequence name is passed in via the %attrib. I've been
meaning to get that part of the doc updated for a while, so thanks for the
reminder. A patch would be overkill: could someone with commit access simply
remove the "(e.g., Pg)" bit from that line in DBI.pm? Thanks much.

- --
Greg Sabino Mullane greg@turnstep.com
PGP Key: 0x14964AC8 200604172137
http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B90 6714964AC8

-----BEGIN PGP SIGNATURE-----

iD8DBQFERELdvJuQZxSWSsgRAn7sAKDGdWPleJJjRevHBSxe71+/NLRAjQCg 11/W
O61U4cWfQXrAufsUtWoK350=
=p7GB
-----END PGP SIGNATURE-----

Re: last insert id

Ron Savage schreef:
> Dr.Ruud:

>> [ /mysql|Pg/ ]
>> Notice that 'CertainlyNotPgAtAll' matches, so maybe you were
>> looking for /\A(?:mysql|pg)\z/i
>
> Nope. Method db_vendor() extracts the vendor's name from the connect
> string passed to DBI, so I don't see how it could return that :-))).

Is it legal to pass 'MySQL'? How about ' mysql '?


>> MySQL's native LAST_INSERT_ID() will return the *first* id from a
>> multi row insert. I don't [k]now what DBI does with that.
>
> Good point. But then my code doesn't come with a written guarantee to
> work with multi-row inserts...

But then I don't claim that you claim.

--
Affijn, Ruud

"Gewoon is een tijger."

Re: last insert id

On Tue, Apr 18, 2006 at 01:37:47AM -0000, Greg Sabino Mullane wrote:
>
> >> DBI doc last_insert_id() "For some drivers the $catalog, $schema,
> >> $table, and $field parameters are required (e.g., Pg), for others
> >> they are ignored (e.g., mysql)."
> >
> > Right. That's why they are undef for MySQL, and it seems the last time I tested
> > it under Pg, undef worked there too, but that was months ago. Proof to the
> > contrary should be published here.
>
> Yes, undef is fine for Pg: the only required attribute is the tablename, and even
> that can be left off if a sequence name is passed in via the %attrib. I've been
> meaning to get that part of the doc updated for a while, so thanks for the
> reminder. A patch would be overkill: could someone with commit access simply
> remove the "(e.g., Pg)" bit from that line in DBI.pm? Thanks much.

Done. Thanks.

Tim.

Re: last insert id

On Tue, 18 Apr 2006 12:56:24 +0200, Dr.Ruud wrote:

Hi

>> Nope. Method db_vendor() extracts the vendor's name from the
>> connect string passed to DBI, so I don't see how it could return
>> that :-))).
> Is it legal to pass 'MySQL'? How about ' mysql '?

Sigh. DBI -> parse_dsn() does not accept something like 'dbi: mysql :test'.

And anyway, I control the config file this data comes from, so spaces won't=

appear there...

This thread is now closed.
--
Cheers
Ron Savage, ron@savage.net.au on 20/04/2006
http://savage.net.au/index.html
Let the record show: Microsoft is not an Australian company

Re: last insert id

Ron Savage schreef:
> Dr.Ruud:
>> [attribution repaired] Ron Savage:

>>> Nope. Method db_vendor() extracts the vendor's name from the
>>> connect string passed to DBI, so I don't see how it could return
>>> that :-))).
>>
>> Is it legal to pass 'MySQL'? How about ' mysql '?
>
> Sigh. DBI -> parse_dsn() does not accept something like 'dbi: mysql
> :test'.
>
> And anyway, I control the config file this data comes from, so spaces
> won't appear there...

OK, so your "/(?:mysql|Pg)/" should have been more like
"/:(?:mysql|pg):/i".

You posted code that others might use (parts of). It is just good to
have comments on it mentioned in the same thread, so that people who hit
on it later, can profit from that.


> This thread is now closed.

Is it that hard?

--
Affijn, Ruud

"Gewoon is een tijger."

Re: last insert id

Hi

>>> Nope. Method db_vendor() extracts the vendor's name from the
>>> connect string passed to DBI, so I don't see how it could return
>>> that :-))).
>> And anyway, I control the config file this data comes from, so spaces
>> won't appear there...
>OK, so your "/(?:mysql|Pg)/" should have been more like
>"/:(?:mysql|pg):/i".

Nope, wrong again.

You are evading the implications of the above explanation of the method,=
which I
repeat below:
"Method db_vendor() extracts the vendor's name from the connect string"

I did not say it extracts the colons, and I did not say it returns the=
connect
string with the embedded colons, and I did not say ... etc.

Permit me to politely suggest it's time for you to stop fabricating=
irrelevant
code.
--
Cheers
Ron Savage, ron@savage.net.au on 21/04/2006
http://savage.net.au/index.html
Let the record show: Microsoft is not an Australian company

Re: last insert id

Ron Savage schreef:

> [attribution repaired] Ruud:
>> [attribution repaired] Ron:
>>> [attribution repaired] Ron:

>>>> Nope. Method db_vendor() extracts the vendor's name from the
>>>> connect string passed to DBI, so I don't see how it could return
>>>> that :-))).
>>>
>>> And anyway, I control the config file this data comes from, so
>>> spaces won't appear there...
>>
>> OK, so your "/(?:mysql|Pg)/" should have been more like
>> "/:(?:mysql|pg):/i".
>
> Nope, wrong again.
>
> You are evading the implications of the above explanation of the
> method, which I repeat below:
> "Method db_vendor() extracts the vendor's name from the connect
> string"
>
> I did not say it extracts the colons, and I did not say it returns
> the connect string with the embedded colons, and I did not say ...
> etc.

Whatever anchors are there, should be used, as I already pointed at in
my first reaction. Since, you haven't supplied a correction to your
code. So we're back at:

/\A(?:mysql|pg)\z/i
/\A(?i:mysql|pg)\z/

--
Affijn, Ruud

"Gewoon is een tijger."

RE: last insert id

> -----Original Message-----
> From: Dr.Ruud [mailto:rvtol+news@isolution.nl]=20
> Sent: Friday, April 21, 2006 6:37 AM
> To: dbi-users@perl.org
> Subject: Re: last insert id
>=20
[snip]
> > I did not say it extracts the colons, and I did not say it returns
the=20
> > connect string with the embedded colons, and I did not say ...
> > etc.
>=20
> Whatever anchors are there, should be used, as I already pointed at in
my first reaction. Since, you haven't supplied a correction to your
code. So we're back at:
>=20
> /\A(?:mysql|pg)\z/i
> /\A(?i:mysql|pg)\z/

If you insist on carrying on this ridiculous off-topic conversation,
please take it off list.

Thanks,
Philip

RE: last insert id

Garrett, Philip (MAN-Corporate) [mailto:Philip.Garrett@manheim.com] wrote:

> > Whatever anchors are there, should be used, as I already pointed at in
> > my first reaction. Since, you haven't supplied a correction to your
> > code. So we're back at:
> >
> > /\A(?:mysql|pg)\z/i
> > /\A(?i:mysql|pg)\z/
>
> If you insist on carrying on this ridiculous off-topic conversation,
> please take it off list.

I must have missed a memo... It's now off-topic to discuss code that uses
DBI on the dbi-users list?

Ronald

RE: last insert id

> -----Original Message-----
> From: Ronald J Kimball [mailto:rjk-dbi@focalex.com]=20
> Sent: Friday, April 21, 2006 10:38 AM
> To: Garrett, Philip (MAN-Corporate); dbi-users@perl.org
> Subject: RE: last insert id
>=20
> Garrett, Philip (MAN-Corporate) [mailto:Philip.Garrett@manheim.com]
wrote:
> =20
> > > Whatever anchors are there, should be used, as I already pointed
at=20
> > > in my first reaction. Since, you haven't supplied a correction to=20
> > > your code. So we're back at:
> > >
> > > /\A(?:mysql|pg)\z/i
> > > /\A(?i:mysql|pg)\z/
> >=20
> > If you insist on carrying on this ridiculous off-topic conversation,

> > please take it off list.
>=20
> I must have missed a memo... It's now off-topic to discuss code that
> uses DBI on the dbi-users list?

(Grudgingly replies to "closed" thread)

This has nothing to do with DBI. It's regular expression pedantry.

Philip

Re: last insert id

On Fri, 21 Apr 2006 12:36:43 +0200, Dr.Ruud wrote:

Hi

> your code. So we're back at:
>
> /\A(?:mysql|pg)\z/i
> /\A(?i:mysql|pg)\z/

Nope. Wrong again. Notice a pattern developing here?

DSNs 101: A Tutorial Based on My Crude Level of Understanding

My CGI programs normally have up to 3 distinct types of DSNs:
o An 'ODBC DSN'
o A 'DBI DSN'. This is also known as a 'DBI connexion string'
o A 'CGI::Session DSN'

When discussing DBI matters, we can ignore those of type 'CGI::Session=
DSN'.

Further, for simplicity let's confine ourselves to Postgres. MySQL would do=
just
as well.

For a database called 'staff', say, typical values for these DSNs might be:
o 'ODBC DSN' =3D> 'pg-staff'
o 'DBI DSN' =3D> 'dbi:Pg:dbname=3Dstaff' or 'dbi:ODBC:pg-staff'

So, 'Convention # 1' is that 'ODBC DSN's contain a string to represent the
database vendor, here 'pg', and a string to represent to database, here=
'staff'.

Now, if we wish to write code conditional upon on the vendor, as in my=
earlier
post of this now over-extended thread, i.e. as in my now infamous 'sub
last_insert_id()', what do we do?

What /I/ do is to break the problem down into simple steps.

Step 1: Abstract out the 'DBI DSN' processing code into a method, let's call=
it
'sub parse_dsn()', which stores data, including a vendor-identifier, into a=

place where other code, let's call it 'sub vendor()', can access it.

In the case of 'DBI DSN' =3D> 'dbi:Pg:dbname=3Dstaff', I store 'Pg'.
In the case of 'DBI DSN' =3D> 'dbi:ODBC:pg-staff', I store 'Pg'.
Notice a pattern developing here?

So, 'Convention # 2' is that 'DBI DSN' processing code is done in one place,=

inside 'sub parse_dsn()'.

And, 'Convention # 3' is that Postgres is uniquely identified, independently=
of
the 'DBI DSN', as 'Pg'.

And, 'Convention # 4' is that the return value of 'sub vendor()', will be=
such
an identifier, here 'Pg'.

Step 2: Now, when yet other code, let's call it 'sub last_insert_id()',=
wants to
execute the aforesaid and understated conditional code, it can do this:

=09if ($self -> db_vendor() =3D~ /(?:mysql|Pg)/)
=09{
...
=09}

Neat, huh?

This uses non-capturing parentheses to emulate:

=09if ( ($self -> db_vendor() eq 'mysql') || ($self -> db_vendor() eq 'Pg') )
=09{
...
=09}

but that contains 2 method calls instead of the regexp, so we could have
written:

=09my($vendor) =3D $self -> db_vendor();
=09
=09if ( ($vendor eq 'mysql') || ($vendor eq 'Pg') )
=09{
...
=09}

That's the funny thing about Perl, there can be more than one way to do=
things.

So, Dr. Ruud, I'd like to think that by this time you have seen that your
fundamental mistake was to persist with code suggestions, including colons=
no
less!, which were based on the delusion that 'sub last_insert_id()' was=
parsing
a 'DBI DSN', when in fact there was no such DSN within the scope of that=
sub.

Specifically, 'sub vendor()', by virtue of its well-chosen name (but I'm
biased), could reasonably be expected to limit its activities to vendor=
names.

If I had called it 'sub dbi_connexion_string()', understandable confusion=
may
have followed. Confusion did follow, but I can't really say it's=
understandable.

Permit me to finish with 3 suggestions:

o For more on 'DBI connexion strings', the DBI docs, and:
http://www.connectionstrings.com/

o Fell free to donate large quantities of money to the
Perl DBI Development Fund, at (watch wrap):
https://donate.perlfoundation.org/index.pl?node=3DFund%20Dri ve%20Status&selfun=
d=3D10
2

o This thread is closed, ended, finished, shut down, terminated, etc, as
previously noted

--
Cheers
Ron Savage, ron@savage.net.au on 22/04/2006
http://savage.net.au/index.html
Let the record show: Microsoft is not an Australian company

Re: last insert id

Ron Savage schreef:
> Dr.Ruud:

>> So we're back at:
>> /\A(?:mysql|pg)\z/i
>> /\A(?i:mysql|pg)\z/
>
> [db_vendor()]
> In the case of 'DBI DSN' => 'dbi:Pg:dbname=staff', I store 'Pg'.
> In the case of 'DBI DSN' => 'dbi:ODBC:pg-staff', I store 'Pg'.
> [...]
> if ($self -> db_vendor() =~ /(?:mysql|Pg)/)
> [...]
> my($vendor) = $self -> db_vendor();
>
> if ( ($vendor eq 'mysql') || ($vendor eq 'Pg') )

That last variant is what I prefer as well, although I would write it a
bit different:

my $vendor = $self -> db_vendor();

if ( $vendor eq 'mysql' or $vendor eq 'Pg' )

But that difference is mainly at the source level, see:
perl -MO=Deparse,x=7 -e 'if ( ($vendor eq q{mysql}) || ($vendor eq
q{Pg}) ){}'



> o This thread is closed, ended, finished, shut down, terminated, etc,
> as previously noted

Welcome to usenet.

--
Affijn, Ruud

"Gewoon is een tijger."

DBI Development Fund (was: Re: last insert id)

Ron Savage schreef:

> o Fell free to donate large quantities of money to the
> Perl DBI Development Fund, at (watch wrap):
>
https://donate.perlfoundation.org/index.pl?node=Fund%20Drive %20Status&selfund=10
> 2

That is really a nasty wrap: the 10 in stead of 102 hides the DBI
Development Fund.

This one is 76 chars:
http://donate.perlfoundation.org/index.pl?node=Fund+Drive+St atus&selfund=102


Variant of 68 chars:
http://donate.perlfoundation.org/?node=Fund+Drive+Status&sel fund=102

(tested with Firefox 1.5.0.2 on a Win2K system)

--
Affijn, Ruud

"Gewoon is een tijger."

RE: last insert id

Ron Savage [mailto:ron@savage.net.au] wrote:

> Step 2: Now, when yet other code, let's call it 'sub last_insert_id()',
> wants to
> execute the aforesaid and understated conditional code, it can do this:
>
> if ($self -> db_vendor() =~ /(?:mysql|Pg)/)
> {
> ...
> }
>
> Neat, huh?
>
> This uses non-capturing parentheses to emulate:
>
> if ( ($self -> db_vendor() eq 'mysql') || ($self -> db_vendor() eq
> 'Pg') )
> {
> ...
> }

> That's the funny thing about Perl, there can be more than one way to do
> things.

The point you seem to be missing is that these two code snippets do not mean
precisely the same thing.

The second expression evaluates to true if and only if $self->db_vendor()
returns either 'mysql' or 'Pg'.

The first expression evaluates to true if $self->db_vendor() returns 'mysql'
or 'Pg', or any string that contains 'mysql' or 'Pg'. For example, it would
match a vendor called 'NotPg', which may not be the desired behavior

In order to match the eq behavior, you could use the regex
/^(?:mysql|Pg)\z/, which is what Dr. Ruud was suggesting.


On the one hand, this level of caution is probably not necessary in this
case. From your explanation, we know that the simpler regex is sufficient
given $db->vendor()'s possible return values.

On the other hand, this level of caution is certainly not harmful, so I'm
not sure why you were so annoyed by the suggestion.


Ronald

Re: last insert id

Time to wind up this thread now guys.

Tim.

Re: last insert id

Ron Savage wrote:
> On Sun, 16 Apr 2006 22:15:22 +1000, Daniel Kasak wrote:
>
> Hi Daniel
>
> DBI: V 1.47
> DBD::mysql: V 3.002
>

There are critical bugs in the $dbh->column_info() method in
DBD::mysql-3.x that prevent us from upgrading :(
I've tried a line identical to your:

> $id = $self -> dbh() -> last_insert_id(undef, undef, $table_name, undef);
and I didn't get anything useful from it. Ah well. 'select @@IDENTITY'
works well for me, and appears to be the most compatible way of doing
things.

Is last_insert_id() supposed to work in versions prior to 3.x? I know
it's only a relatively recent addition. That means that the code below
requires more checks on the version of DBD::mysql to see if
last_insert_id() is supported ( and working ).
> sub last_insert_id
> {
> my($self, $table_name) = @_;
>
> my($id);
>
> if ($self -> db_vendor() =~ /(?:mysql|Pg)/)
> {
> $id = $self -> dbh() -> last_insert_id(undef, undef, $table_name, undef);
> }
> else # Oracle.
> {
> my($sth) = $self -> dbh() -> prepare("select ${table_name}_id_seq.currval from
> dual");
>
> $sth -> execute();
>
> $id = ${$sth -> fetch()}[0];
>
> $sth -> finish();
> }
>
> return $id;
>
> } # End of last_insert_id.
>
>