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.
Re: last insert id
Time to wind up this thread now guys. Tim.
RE: last insert id
Ron Savage [mailto:[EMAIL PROTECTED] 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
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+Status&selfund=102 Variant of 68 chars: http://donate.perlfoundation.org/?node=Fund+Drive+Status&selfund=102 (tested with Firefox 1.5.0.2 on a Win2K system) -- Affijn, Ruud "Gewoon is een tijger."
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."
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' => 'pg-staff'
o 'DBI DSN' => 'dbi:Pg:dbname=staff' 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' => 'dbi:Pg:dbname=staff', I store 'Pg'.
In the case of 'DBI DSN' => '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:
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') )
{
...
}
but that contains 2 method calls instead of the regexp, so we could have
written:
my($vendor) = $self -> db_vendor();
if ( ($vendor eq 'mysql') || ($vendor eq 'Pg') )
{
...
}
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=Fund%20Drive%20Status&selfund=10
2
o This thread is closed, ended, finished, shut down, terminated, etc, as
previously noted
--
Cheers
Ron Savage, [EMAIL PROTECTED] on 22/04/2006
http://savage.net.au/index.html
Let the record show: Microsoft is not an Australian company
RE: last insert id
> -Original Message- > From: Ronald J Kimball [mailto:[EMAIL PROTECTED] > Sent: Friday, April 21, 2006 10:38 AM > To: Garrett, Philip (MAN-Corporate); dbi-users@perl.org > Subject: RE: last insert id > > Garrett, Philip (MAN-Corporate) [mailto:[EMAIL PROTECTED] 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? (Grudgingly replies to "closed" thread) This has nothing to do with DBI. It's regular expression pedantry. Philip
RE: last insert id
Garrett, Philip (MAN-Corporate) [mailto:[EMAIL PROTECTED] 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: Dr.Ruud [mailto:[EMAIL PROTECTED] > Sent: Friday, April 21, 2006 6:37 AM > To: dbi-users@perl.org > Subject: Re: last insert id > [snip] > > 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/ If you insist on carrying on this ridiculous off-topic conversation, please take it off list. Thanks, Philip
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
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, [EMAIL PROTECTED] 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: > 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
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, [EMAIL PROTECTED] on 20/04/2006 http://savage.net.au/index.html Let the record show: Microsoft is not an Australian company
Re: last insert id
On Tue, Apr 18, 2006 at 01:37:47AM -, 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
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
-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 [EMAIL PROTECTED] PGP Key: 0x14964AC8 200604172137 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFERELdvJuQZxSWSsgRAn7sAKDGdWPleJJjRevHBSxe71+/NLRAjQCg11/W O61U4cWfQXrAufsUtWoK350= =p7GB -END PGP SIGNATURE-
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() =~ /(?: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, [EMAIL PROTECTED] on 18/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 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
{
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.
--
Cheers
Ron Savage, [EMAIL PROTECTED] on 17/04/2006
http://savage.net.au/index.html
Let the record show: Microsoft is not an Australian company
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 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
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
-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 [EMAIL PROTECTED] PGP Key: 0x14964AC8 200603231124 http://biglumber.com/x/web?pk=2529DF6AB8F79407E94445B4BC9B906714964AC8 -BEGIN PGP SIGNATURE- iD8DBQFEIsvNvJuQZxSWSsgRAmsKAJ4ingxlHpxQGZ/OYDuhdjJdOI9YbACg74M4 a5DK5VqKK6Pw45eVMWrRI20= =ZyTK -END PGP SIGNATURE-
