Re: Time to standardize the ``last insert id'' concept...
On Mon, May 26, 2003 at 10:31:36PM -0700, Jonathan Leffler wrote: What should you return if no value has been generated (yet)? undef seems most reasonable. I think undefined would be better (as in, don't do that). Databases are likely to do various things, for example Sybase seems to give 0. I don't think the Sybase driver wants to translate 0 to undef (perhaps 0 is legitimate sometimes) or try to keep track of whether an ID has been generated yet. Ideally, the last-inserted-id would be returned in the same step as the insert, maybe in the form of $id = $dbh-insertrow(INSERT INTO ...), but I understand you are tailoring the interface to the standard practice of stateful handles. -John
Re: Time to standardize the ``last insert id'' concept...
On Tue, May 27, 2003 at 09:33:21AM -0400, [EMAIL PROTECTED] wrote: On Mon, May 26, 2003 at 10:31:36PM -0700, Jonathan Leffler wrote: What should you return if no value has been generated (yet)? undef seems most reasonable. I think undefined would be better (as in, don't do that). I agree. I'll tweak the docs. Tim. Databases are likely to do various things, for example Sybase seems to give 0. I don't think the Sybase driver wants to translate 0 to undef (perhaps 0 is legitimate sometimes) or try to keep track of whether an ID has been generated yet. Ideally, the last-inserted-id would be returned in the same step as the insert, maybe in the form of $id = $dbh-insertrow(INSERT INTO ...), but I understand you are tailoring the interface to the standard practice of stateful handles. -John
Re: Time to standardize the ``last insert id'' concept...
On Tue, Mar 11, 2003 at 01:30:30PM -0800, Michael Peppler wrote:
It looks like it pays to question things.
The following works, although I was sure it wouldn't:
my ($sth, $sth2);
$sth = $dbh-prepare(insert dbi_insert(c, i) values(?,?));
for(my $i = 0; $i 2; $i++) {
$sth-execute(foo, 1);
$sth2 = $dbh-prepare(select max(id) from dbi_insert);
$sth2-execute;
while(my $d = $sth2-fetch) {
print identity = @$d\n;
}
}
Both $sth and $sth2 are on the same connection, and $sth *can* be reused
after $sth2 is done.
Which means that fetching the values will be a little easier (although
there are locking issues, of course!)
Right.
So this will work reliably for DBD::Sybase:
sub last_insert_id {
my ($dbh, $table, $field) = @_;
$_ = $dbh-quote_identifier($_) foreach ($table, $field);
return $dbh-selectrow_array(select max($field) from $table);
}
*if* AutoCommit is off. If AutoCommit is on then there's a risk
that the returned value will be incorrect if some other process has
performed an insert after yours. Right?
Thanks for prodding me on this Tim!
A pleasure :)
Tim.
Re: Time to standardize the ``last insert id'' concept...
On Wed, 2003-03-12 at 03:11, Tim Bunce wrote:
On Tue, Mar 11, 2003 at 01:30:30PM -0800, Michael Peppler wrote:
It looks like it pays to question things.
The following works, although I was sure it wouldn't:
my ($sth, $sth2);
$sth = $dbh-prepare(insert dbi_insert(c, i) values(?,?));
for(my $i = 0; $i 2; $i++) {
$sth-execute(foo, 1);
$sth2 = $dbh-prepare(select max(id) from dbi_insert);
$sth2-execute;
while(my $d = $sth2-fetch) {
print identity = @$d\n;
}
}
Both $sth and $sth2 are on the same connection, and $sth *can* be reused
after $sth2 is done.
Which means that fetching the values will be a little easier (although
there are locking issues, of course!)
Right.
So this will work reliably for DBD::Sybase:
sub last_insert_id {
my ($dbh, $table, $field) = @_;
$_ = $dbh-quote_identifier($_) foreach ($table, $field);
return $dbh-selectrow_array(select max($field) from $table);
}
*if* AutoCommit is off. If AutoCommit is on then there's a risk
that the returned value will be incorrect if some other process has
performed an insert after yours. Right?
Yes. Though I'd probably do this instead:
return $dbh-selectrow_array(qq(
declare [EMAIL PROTECTED] numeric
select [EMAIL PROTECTED] = [EMAIL PROTECTED]@identity
if([EMAIL PROTECTED] = 0)
select [EMAIL PROTECTED] = max($field) from $table
select 'last_id' = [EMAIL PROTECTED]
));
which will return the @@identity value if it has been set, and the max()
otherwise.
Michael
--
Michael Peppler Data Migrations, Inc.
[EMAIL PROTECTED] http://www.mbay.net/~mpeppler
Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short or
long term contract positions - http://www.mbay.net/~mpeppler/resume.html
Re: Time to standardize the ``last insert id'' concept...
On Mon, 2003-03-10 at 15:50, Tim Bunce wrote:
On Mon, Mar 10, 2003 at 02:50:08PM -0800, Michael Peppler wrote:
On Mon, 2003-03-10 at 12:36, Tim Bunce wrote:
Taking a different tack... *if* @@identity is a sequence and AutoCommit
is off then then select max($column) from $table would get it. No?
Yes, that would work.
However, the following won't:
$sth = $dbh-prepare('insert foo(...) values(?,...)');
$sth-execute('bar', ...);
$id_sth = $dbh-prepare('select max(id) from foo');
because now $id_sth resides on a different physical connection. You'd
have to finish() the original $sth,
Can't DBD::Sybase tell it's an insert and automatically finish()
after the execute?
Yes.
But then you won't be able to reuse that handle to make a second insert
(as you know, of course).
I just feel that whatever method I use to try to solve this it'll be
rather hackish :-(
Michael
--
Michael Peppler Data Migrations, Inc.
[EMAIL PROTECTED] http://www.mbay.net/~mpeppler
Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short or
long term contract positions - http://www.mbay.net/~mpeppler/resume.html
Re: Time to standardize the ``last insert id'' concept...
On Tue, Mar 11, 2003 at 09:20:48AM -0800, Michael Peppler wrote:
On Mon, 2003-03-10 at 15:50, Tim Bunce wrote:
On Mon, Mar 10, 2003 at 02:50:08PM -0800, Michael Peppler wrote:
On Mon, 2003-03-10 at 12:36, Tim Bunce wrote:
Taking a different tack... *if* @@identity is a sequence and AutoCommit
is off then then select max($column) from $table would get it. No?
Yes, that would work.
However, the following won't:
$sth = $dbh-prepare('insert foo(...) values(?,...)');
$sth-execute('bar', ...);
$id_sth = $dbh-prepare('select max(id) from foo');
because now $id_sth resides on a different physical connection. You'd
have to finish() the original $sth,
Can't DBD::Sybase tell it's an insert and automatically finish()
after the execute?
Yes.
But then you won't be able to reuse that handle to make a second insert
(as you know, of course).
I just feel that whatever method I use to try to solve this it'll be
rather hackish :-(
Michael
--
Michael Peppler Data Migrations, Inc.
[EMAIL PROTECTED] http://www.mbay.net/~mpeppler
Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short or
long term contract positions - http://www.mbay.net/~mpeppler/resume.html
Re: Time to standardize the ``last insert id'' concept...
On Tue, 2003-03-11 at 12:50, Tim Bunce wrote:
[ignore the previous 'empty' reply, finger trouble]
On Tue, Mar 11, 2003 at 09:20:48AM -0800, Michael Peppler wrote:
On Mon, 2003-03-10 at 15:50, Tim Bunce wrote:
On Mon, Mar 10, 2003 at 02:50:08PM -0800, Michael Peppler wrote:
On Mon, 2003-03-10 at 12:36, Tim Bunce wrote:
Taking a different tack... *if* @@identity is a sequence and AutoCommit
is off then then select max($column) from $table would get it. No?
Yes, that would work.
However, the following won't:
$sth = $dbh-prepare('insert foo(...) values(?,...)');
$sth-execute('bar', ...);
$id_sth = $dbh-prepare('select max(id) from foo');
because now $id_sth resides on a different physical connection. You'd
have to finish() the original $sth,
Can't DBD::Sybase tell it's an insert and automatically finish()
after the execute?
Yes.
But then you won't be able to reuse that handle to make a second insert
(as you know, of course).
I'm probably just not familar enough with how DBD::Sybase works,
but that seems odd. I thought the only reason for using separate
connections was for when one $sth still had results to be fetched.
It looks like it pays to question things.
The following works, although I was sure it wouldn't:
my ($sth, $sth2);
$sth = $dbh-prepare(insert dbi_insert(c, i) values(?,?));
for(my $i = 0; $i 2; $i++) {
$sth-execute(foo, 1);
$sth2 = $dbh-prepare(select max(id) from dbi_insert);
$sth2-execute;
while(my $d = $sth2-fetch) {
print identity = @$d\n;
}
}
Both $sth and $sth2 are on the same connection, and $sth *can* be reused
after $sth2 is done.
Which means that fetching the values will be a little easier (although
there are locking issues, of course!)
Thanks for prodding me on this Tim!
Michael
--
Michael Peppler Data Migrations, Inc.
[EMAIL PROTECTED] http://www.mbay.net/~mpeppler
Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short or
long term contract positions - http://www.mbay.net/~mpeppler/resume.html
Re: Time to standardize the ``last insert id'' concept...
On Sun, Mar 09, 2003 at 12:33:10PM -0800, Jonathan Leffler wrote: Tim Bunce wrote: Jonathan Leffler wrote: Tim Bunce wrote: Can you tell if the last insert generated a SERIAL or SERIAL8 id? Or would the application need to give you a hint? I don't need an application hint, though I can't readily tell either. The SERIAL I can guess pretty reliably simply by looking at the field in the SQLCA -- if it isn't zero, chances are a serial was inserted. So the SQLCA is cleared for each statement and only set non-zero for an insert into a table with a SERIAL column? The SQLCA is the SQL Communication Area. It is logically cleared [...] With SERIAL8, I have no information readily available. But if the above is true, then if the application asks for $dbh-last_insert_id and the SQLCA field is 0 then you could reasonably assume that the SERIAL8 should be used. Not necessarily. Consider: CREATE TABLE Rubbish(s4 SERIAL UNIQUE, s8 SERIAL8 UNIQUE); INSERT INTO Rubbish VALUES(0, 0); Not sensible - but legal. In this case, I'd have both the SERIAL and the SERIAL8 to worry about. Also, there are plenty of INSERT operations where neither type of SERIAL is involved. Certainly, I could decide that if the SERIAL value is zero, I should try the SERIAL8 instead. That would be feasible. It might generate spurious data on occasion - if someone asked for the last_insert_id inappropriately - but at least it is some sort of algorithm. Yeap. It doesn't deal with the pathological case shown above, of course. It does in as much as it says if a table has both SERIAL and SERIAL8 columns then the SERIAL column is given priority. :) Since PostgreSQL, Oracle, DB2 and Informix (and maybe others) all have sequences, I think the DBI specification should specifically exclude them from the discussion of last_insert_id -- just to prevent misunderstandings. I think we have to discuss them together for now. We'll wait and see how the DBI specification ends up, but it will need to clarify auto-values vs sequences. Tim.
Re: Time to standardize the ``last insert id'' concept...
On Sat, 2003-03-08 at 13:11, Tim Bunce wrote: On Fri, Mar 07, 2003 at 04:28:33PM -0800, Michael Peppler wrote: On Fri, 2003-03-07 at 14:15, Tim Bunce wrote: I'd appreciate a summary of which drivers support some form of ``last insert id'' and details of the interface they provide. Specifically... via an attribute or method? at the sth or dbh level, or both? any other details that might be relevant... Also, for databases that do _not_ support a ``last insert id'' concept in the client API, are there any SQL mechanisms for finding the last insert id? Note that you *can't* get at the @@identity value if you insert data using placeholders. This is because Sybase also localizes the @@variables within each stored procedure, and when using placeholders Sybase creates a temporary stored procedure for you on the fly. Could the generated temporary stored procedure copy @@identity to a non-localized session variable (where it could then be read by DBD::Sybase if the application wants the last_insert_id)? Unfortuantely no. YOu have no control over the content of the temp. stored proc beyond the actual insert statement. Michael -- Michael Peppler Data Migrations, Inc. [EMAIL PROTECTED] http://www.mbay.net/~mpeppler Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short or long term contract positions - http://www.mbay.net/~mpeppler/resume.html
Re: Time to standardize the ``last insert id'' concept...
On Mon, 2003-03-10 at 08:27, Tim Bunce wrote: On Mon, Mar 10, 2003 at 07:41:19AM -0800, Michael Peppler wrote: On Sat, 2003-03-08 at 13:11, Tim Bunce wrote: On Fri, Mar 07, 2003 at 04:28:33PM -0800, Michael Peppler wrote: On Fri, 2003-03-07 at 14:15, Tim Bunce wrote: I'd appreciate a summary of which drivers support some form of ``last insert id'' and details of the interface they provide. Specifically... via an attribute or method? at the sth or dbh level, or both? any other details that might be relevant... Also, for databases that do _not_ support a ``last insert id'' concept in the client API, are there any SQL mechanisms for finding the last insert id? Note that you *can't* get at the @@identity value if you insert data using placeholders. This is because Sybase also localizes the @@variables within each stored procedure, and when using placeholders Sybase creates a temporary stored procedure for you on the fly. Could the generated temporary stored procedure copy @@identity to a non-localized session variable (where it could then be read by DBD::Sybase if the application wants the last_insert_id)? Unfortuantely no. YOu have no control over the content of the temp. stored proc beyond the actual insert statement. Which 'you'? Doesn't DBD::Sybase created those temporary stored procedures itself? No - the Sybase server does. DBD::Sybase just uses the Sybase API (ct_dynamic() and friends). This sends the query to be prepared to the server, which creates the temp proc. This makes it fast because the procs are never recorded in any of the system tables. If DBD::Sybase had to create the procs on the fly the process would be rather slow, and there would be a lot of lock contention between clients wanting to execute dynamic requests. Michael -- Michael Peppler Data Migrations, Inc. [EMAIL PROTECTED] http://www.mbay.net/~mpeppler Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short or long term contract positions - http://www.mbay.net/~mpeppler/resume.html
Re: Time to standardize the ``last insert id'' concept...
On Mon, 2003-03-10 at 12:36, Tim Bunce wrote:
On Mon, Mar 10, 2003 at 08:37:45AM -0800, Michael Peppler wrote:
Note that you *can't* get at the @@identity value if you insert data
using placeholders. This is because Sybase also localizes the
@@variables within each stored procedure, and when using placeholders
Sybase creates a temporary stored procedure for you on the fly.
So the @@variables can't even be read by another stored procedure?
If so, that seems remarkably unhelpful.
The idea is to be able to do nested procedure calls and not have a
called proc affect the @@variables in the caller (such as @@error,
@@identity, @@rowcount, etc). It makes sense in most situations, but
it's a problem here.
Previously you said:
Off-hand I'm not sure how a last_insert_id() method would work for
DBD::Sybase, but there may be a way to build something that is at least
semi-reliable.
So, any ideas?
Taking a different tack... *if* @@identity is a sequence and AutoCommit
is off then then select max($column) from $table would get it. No?
Yes, that would work.
However, the following won't:
$sth = $dbh-prepare('insert foo(...) values(?,...)');
$sth-execute('bar', ...);
$id_sth = $dbh-prepare('select max(id) from foo');
because now $id_sth resides on a different physical connection. You'd
have to finish() the original $sth, and then do the second prepare() to
get the max() value, which is of course rather annoying if you're doing
a bunch of inserts (though it's maybe unlikely that you really need the
last_id value if you're doing a batch of inserts).
Michael
--
Michael Peppler Data Migrations, Inc.
[EMAIL PROTECTED] http://www.mbay.net/~mpeppler
Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short or
long term contract positions - http://www.mbay.net/~mpeppler/resume.html
Re: Time to standardize the ``last insert id'' concept...
On Mon, Mar 10, 2003 at 02:50:08PM -0800, Michael Peppler wrote:
On Mon, 2003-03-10 at 12:36, Tim Bunce wrote:
Taking a different tack... *if* @@identity is a sequence and AutoCommit
is off then then select max($column) from $table would get it. No?
Yes, that would work.
However, the following won't:
$sth = $dbh-prepare('insert foo(...) values(?,...)');
$sth-execute('bar', ...);
$id_sth = $dbh-prepare('select max(id) from foo');
because now $id_sth resides on a different physical connection. You'd
have to finish() the original $sth,
Can't DBD::Sybase tell it's an insert and automatically finish()
after the execute?
Tim.
and then do the second prepare() to
get the max() value, which is of course rather annoying if you're doing
a bunch of inserts (though it's maybe unlikely that you really need the
last_id value if you're doing a batch of inserts).
Re: Time to standardize the ``last insert id'' concept...
On Sat, Mar 08, 2003 at 09:44:16PM -0800, Jonathan Leffler wrote:
Tim Bunce wrote:
Can you tell if the last insert generated a SERIAL or SERIAL8 id?
Or would the application need to give you a hint?
I don't need an application hint, though I can't readily tell either.
The SERIAL I can guess pretty reliably simply by looking at the field
in the SQLCA -- if it isn't zero, chances are a serial was inserted.
So the SQLCA is cleared for each statement and only set non-zero for
an insert into a table with a SERIAL column?
With SERIAL8, I have no information readily available.
But if the above is true, then if the application asks for
$dbh-last_insert_id and the SQLCA field is 0 then you could
reasonably assume that the SERIAL8 should be used.
[I agree that dbh is better than sth, and the DBI spec will say
that the info is potentially very short lived and should be got
immediately after the insert.]
For the special case of SERIAL8, I can provide a suitable access
method - probably $dbh-ix_last_serial8() via the nice new mechanism
you've exposed for adding methods to the DBI. Or as an attribute such
as $dbh-{ix_last_serial8} -- it's much the same.
But that implies that the application *knows* it was a serial8.
I can imagine that a table might be altered from serial to serial8,
but you wouldn't want to have to change all the applications.
Interesting observation, but existing applications can't fish 64-bit
integer values out of 32-bit storage reliably -- so they'd have to
change. That, in conjunction with various other observations about
the (lack of) usage of features that are not as operational as I'd
like tends to suggest that such features are not very widely used anyway.
Sure. Today. I'd hate you or the DBI spec to get boxed into a corner
so it's worth exploring further even if SERIAL8 support ends up
not being implemented in the short term.
Tim.
Re: Time to standardize the ``last insert id'' concept...
On Sat, Mar 08, 2003 at 07:24:59PM -0500, John Siracusa wrote: Just a quick question on the topic. How far are you willing to go (or willing to let DBD authors go) to support the API you're planning? | this far | :) For example, Postgres has a SERIAL psuedo-type that really just creates a sequence behind the scenes for you. I currently get the last insert id using DBD::Pg by querying what I know is the auto-generated sequence name (tablename_columnname_seq) Would it be reasonable for DBD::Pg to do this for me behind the scenes in order to service the proposed new API, or would that be frowned upon (because it has to run another query, or because it guesses the name of the sequence, or some other reason)? It's for just this reason that I suggested the last_insert_id method may take $table_name and $column_name parameters. For postgres the tablename_columnname_seq name is part of the core system so there's really no guessing going on (as I understand it). DBD::Oracle isn't so lucky. Tim.
Re: Time to standardize the ``last insert id'' concept...
Tim Bunce wrote: Jonathan Leffler wrote: Tim Bunce wrote: Can you tell if the last insert generated a SERIAL or SERIAL8 id? Or would the application need to give you a hint? I don't need an application hint, though I can't readily tell either. The SERIAL I can guess pretty reliably simply by looking at the field in the SQLCA -- if it isn't zero, chances are a serial was inserted. So the SQLCA is cleared for each statement and only set non-zero for an insert into a table with a SERIAL column? The SQLCA is the SQL Communication Area. It is logically cleared for each statement, and the part (one element of one of the arrays) that records the last serial column is set non-zero for an insert into a table with a serial column (certainly if you insert the special value, 0, which indicates that the actual value should be auto-generated; if you insert a (non-zero) value, I don't know whether it is returned). The SQLCA also contains information such as the SQL error number (sqlca.sqlcode), and data for the error message, and the number of rows affected by the last operation, and costs, and warnings, and so on. (There's also a totally different set of meanings for the fields in the SQLCA when you connect to a database, but that's not material to this discussion). With SERIAL8, I have no information readily available. But if the above is true, then if the application asks for $dbh-last_insert_id and the SQLCA field is 0 then you could reasonably assume that the SERIAL8 should be used. Not necessarily. Consider: CREATE TABLE Rubbish(s4 SERIAL UNIQUE, s8 SERIAL8 UNIQUE); INSERT INTO Rubbish VALUES(0, 0); Not sensible - but legal. In this case, I'd have both the SERIAL and the SERIAL8 to worry about. Also, there are plenty of INSERT operations where neither type of SERIAL is involved. Certainly, I could decide that if the SERIAL value is zero, I should try the SERIAL8 instead. That would be feasible. It might generate spurious data on occasion - if someone asked for the last_insert_id inappropriately - but at least it is some sort of algorithm. It doesn't deal with the pathological case shown above, of course. [I agree that dbh is better than sth, and the DBI spec will say that the info is potentially very short lived and should be got immediately after the insert.] Yes, the information is volatile and must be retrieved quickly, before it is trampled. Since PostgreSQL, Oracle, DB2 and Informix (and maybe others) all have sequences, I think the DBI specification should specifically exclude them from the discussion of last_insert_id -- just to prevent misunderstandings. -- Jonathan Leffler ([EMAIL PROTECTED], [EMAIL PROTECTED]) #include disclaimer.h Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/
RE: Time to standardize the ``last insert id'' concept...
On Sat, Mar 08, 2003 at 07:24:59PM -0500, John Siracusa wrote: Just a quick question on the topic. How far are you willing to go (or willing to let DBD authors go) to support the API you're planning? | this far | :) For example, Postgres has a SERIAL psuedo-type that really just creates a sequence behind the scenes for you. I currently get the last insert id using DBD::Pg by querying what I know is the auto-generated sequence name (tablename_columnname_seq) Would it be reasonable for DBD::Pg to do this for me behind the scenes in order to service the proposed new API, or would that be frowned upon (because it has to run another query, or because it guesses the name of the sequence, or some other reason)? It's for just this reason that I suggested the last_insert_id method may take $table_name and $column_name parameters. For postgres the tablename_columnname_seq name is part of the core system so there's really no guessing going on (as I understand it). DBD::Oracle isn't so lucky. Neither is DBD::ODBC, I think...unless I'm missing something obvious...(need to re-read ODBC specs on this, because I don't believe the select $column_name from $table_name where $column_name IS NULL works...) Jeff
Re: Time to standardize the ``last insert id'' concept...
On 3/9/03 3:21 PM, Tim Bunce wrote: For postgres the tablename_columnname_seq name is part of the core system so there's really no guessing going on (as I understand it). That's only true for the SERIAL pseudo-type's auto-generated sequences. A table could have its primary key column default to the next value from any sequence under any name, in which case the DBD would really have no way of knowing which sequence contains the last insert id (unless it wants to get funky with Postgres's table introspection abilities, I guess). Anyway, I'd be happy even if only the common case of a SERIAL primary key was handled, but I don't see a good general-purpose solution to the problem. -John
Re: Time to standardize the ``last insert id'' concept...
On Fri, Mar 07, 2003 at 04:28:33PM -0800, Michael Peppler wrote: On Fri, 2003-03-07 at 14:15, Tim Bunce wrote: I'd appreciate a summary of which drivers support some form of ``last insert id'' and details of the interface they provide. Specifically... via an attribute or method? at the sth or dbh level, or both? any other details that might be relevant... Also, for databases that do _not_ support a ``last insert id'' concept in the client API, are there any SQL mechanisms for finding the last insert id? For Sybase you issue a select @@identity right after the insert. I believe that this will also work with MS-SQL. The @@identity value is a global Transact-SQL variable that is localized to the connection. Note that you *can't* get at the @@identity value if you insert data using placeholders. This is because Sybase also localizes the @@variables within each stored procedure, and when using placeholders Sybase creates a temporary stored procedure for you on the fly. Off-hand I'm not sure how a last_insert_id() method would work for DBD::Sybase, but there may be a way to build something that is at least semi-reliable. Could the generated temporary stored procedure copy @@identity to a non-localized session variable (where it could then be read by DBD::Sybase if the application wants the last_insert_id)? Tim.
Re: Time to standardize the ``last insert id'' concept...
Tim Bunce wrote:
I'd appreciate a summary of which drivers support some form of
``last insert id'' and details of the interface they provide.
Specifically...
via an attribute or method?
at the sth or dbh level, or both?
any other details that might be relevant...
DBD::Informix has it for SERIAL columns. It is a value in the SQLCA
which is accessed via either the statement or the database handle -
same space (there is an SQLCA record per database handle).
It is volatile - that is, the next SQL operation on the same database
handle destroys the information. It would not be desparately hard to
provide a one-element cache per statement handle, but no-one using
DBD::Informix has ever suggested it.
Now, there is also a SERIAL8 type. I had to go manual bashing to find
out how to get the last insert ID for that, but there is a function
that provides the information at the C interface -- I'd have to work
out a way of handling that (not least because, although it is
pointless, a single table can have both a SERIAL and a SERIAL8 column).
Finally, in IDS 9.40, there are sequences as found in Oracle and DB2.
You manipulate those with seqname.currval and seqname.nextval and
SELECT statements.
Also, for databases that do _not_ support a ``last insert id''
concept in the client API, are there any SQL mechanisms for finding
the last insert id?
I'm currently (very vagely) thinking that it'll be a dbh level
method something like this:
$id = $dbh-last_insert_id();
$id = $dbh-last_insert_id($table_name, $column_name);
I'm neutral between $dbh and $sth. Obviously, it is a $sth that
'sets' the value, but there's typically just one place that stores it,
effectively at the $dbh level, so there's room for arguing either way.
For the special case of SERIAL8, I can provide a suitable access
method - probably $dbh-ix_last_serial8() via the nice new mechanism
you've exposed for adding methods to the DBI. Or as an attribute such
as $dbh-{ix_last_serial8} -- it's much the same.
For sequences, I am not sure whether it is necessary -- are you
planning to provide access to sequences for DBD::Oracle? If so, does
the table name map to the sequence name? If not (as I suspect), then
am I right in thinking that you'd expect people to do an explicit
SELECT seqname.currval FROM dual or some equivalent?
where many drivers will ignore the parameters, but some might need them,
for example to do:
select $column_name from $table_name where $column_name IS NULL
which is, I believe, the 'standard' ODBC way to get the last insert id.
But I'm working from memory so could easily be wrong or confused here.
(And, of course, the select would only reliably return the right value
if AutoCommit is off so the table would still be locked after the insert.)
Speak up!
Thanks.
Tim.
p.s. I'm hoping DBI 1.35, which I've just released, will be fairly
stable and I can aim to put new things like this, and 'take_imp_data'
for Stas, into development releases leading up to a DBI 1.36.
Did my patch to data_sources make it? Curiosity is killing this cat,
that's all.
--
Jonathan Leffler ([EMAIL PROTECTED], [EMAIL PROTECTED])
#include disclaimer.h
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/
Re: Time to standardize the ``last insert id'' concept...
On Fri, Mar 07, 2003 at 11:12:00PM -0800, Jonathan Leffler wrote:
Tim Bunce wrote:
I'd appreciate a summary of which drivers support some form of
``last insert id'' and details of the interface they provide.
Specifically...
via an attribute or method?
at the sth or dbh level, or both?
any other details that might be relevant...
DBD::Informix has it for SERIAL columns. It is a value in the SQLCA
which is accessed via either the statement or the database handle -
same space (there is an SQLCA record per database handle).
It is volatile - that is, the next SQL operation on the same database
handle destroys the information. It would not be desparately hard to
provide a one-element cache per statement handle, but no-one using
DBD::Informix has ever suggested it.
Now, there is also a SERIAL8 type. I had to go manual bashing to find
out how to get the last insert ID for that, but there is a function
that provides the information at the C interface -- I'd have to work
out a way of handling that (not least because, although it is
pointless, a single table can have both a SERIAL and a SERIAL8 column).
Can you tell if the last insert generated a SERIAL or SERIAL8 id?
Or would the application need to give you a hint?
Finally, in IDS 9.40, there are sequences as found in Oracle and DB2.
You manipulate those with seqname.currval and seqname.nextval and
SELECT statements.
Also, for databases that do _not_ support a ``last insert id''
concept in the client API, are there any SQL mechanisms for finding
the last insert id?
I'm currently (very vagely) thinking that it'll be a dbh level
method something like this:
$id = $dbh-last_insert_id();
$id = $dbh-last_insert_id($table_name, $column_name);
I'm neutral between $dbh and $sth. Obviously, it is a $sth that
'sets' the value, but there's typically just one place that stores it,
effectively at the $dbh level, so there's room for arguing either way.
Yeap. That's pretty common.
For the special case of SERIAL8, I can provide a suitable access
method - probably $dbh-ix_last_serial8() via the nice new mechanism
you've exposed for adding methods to the DBI. Or as an attribute such
as $dbh-{ix_last_serial8} -- it's much the same.
But that implies that the application *knows* it was a serial8.
I can imagine that a table might be altered from serial to serial8,
but you wouldn't want to have to change all the applications.
For sequences, I am not sure whether it is necessary -- are you
planning to provide access to sequences for DBD::Oracle? If so, does
the table name map to the sequence name? If not (as I suspect), then
am I right in thinking that you'd expect people to do an explicit
SELECT seqname.currval FROM dual or some equivalent?
I'll probably duck supporting named sequence for the time being.
But it would be worth looking at at some point in the future.
Tim.
Re: Time to standardize the ``last insert id'' concept...
Just a quick question on the topic. How far are you willing to go (or willing to let DBD authors go) to support the API you're planning? For example, Postgres has a SERIAL psuedo-type that really just creates a sequence behind the scenes for you. I currently get the last insert id using DBD::Pg by querying what I know is the auto-generated sequence name (tablename_columnname_seq) Would it be reasonable for DBD::Pg to do this for me behind the scenes in order to service the proposed new API, or would that be frowned upon (because it has to run another query, or because it guesses the name of the sequence, or some other reason)? -John
Re: Time to standardize the ``last insert id'' concept...
Tim Bunce wrote:
On Fri, Mar 07, 2003 at 11:12:00PM -0800, Jonathan Leffler wrote:
Tim Bunce wrote:
I'd appreciate a summary of which drivers support some form of
``last insert id'' and details of the interface they provide.
Specifically...
via an attribute or method?
at the sth or dbh level, or both?
any other details that might be relevant...
DBD::Informix has it for SERIAL columns. It is a value in the SQLCA
which is accessed via either the statement or the database handle -
same space (there is an SQLCA record per database handle).
It is volatile - that is, the next SQL operation on the same database
handle destroys the information. It would not be desparately hard to
provide a one-element cache per statement handle, but no-one using
DBD::Informix has ever suggested it.
Now, there is also a SERIAL8 type. I had to go manual bashing to find
out how to get the last insert ID for that, but there is a function
that provides the information at the C interface -- I'd have to work
out a way of handling that (not least because, although it is
pointless, a single table can have both a SERIAL and a SERIAL8 column).
Can you tell if the last insert generated a SERIAL or SERIAL8 id?
Or would the application need to give you a hint?
I don't need an application hint, though I can't readily tell either.
The SERIAL I can guess pretty reliably simply by looking at the field
in the SQLCA -- if it isn't zero, chances are a serial was inserted.
With SERIAL8, I have no information readily available.
However, the important thing is simply that the client code asks for
the information, and I give it to them. They know that it an insert
occurred, so 'the application gives me a hint' by asking for the
information. This, along with Paul DuBois's observation that $dbh is
better than $sth (at least, $sth only) because of $dbh-do() where
there is no visible $sth to interrogate, suggests that $dbh is
preferable to $sth. Trying to track SERIAL8 per statement handle
would be a pain.
[...]
I'm currently (very vagely) thinking that it'll be a dbh level
method something like this:
$id = $dbh-last_insert_id();
$id = $dbh-last_insert_id($table_name, $column_name);
I'm neutral between $dbh and $sth. Obviously, it is a $sth that
'sets' the value, but there's typically just one place that stores it,
effectively at the $dbh level, so there's room for arguing either way.
Yeap. That's pretty common.
For the special case of SERIAL8, I can provide a suitable access
method - probably $dbh-ix_last_serial8() via the nice new mechanism
you've exposed for adding methods to the DBI. Or as an attribute such
as $dbh-{ix_last_serial8} -- it's much the same.
But that implies that the application *knows* it was a serial8.
I can imagine that a table might be altered from serial to serial8,
but you wouldn't want to have to change all the applications.
Interesting observation, but existing applications can't fish 64-bit
integer values out of 32-bit storage reliably -- so they'd have to
change. That, in conjunction with various other observations about
the (lack of) usage of features that are not as operational as I'd
like tends to suggest that such features are not very widely used anyway.
[...]
--
Jonathan Leffler ([EMAIL PROTECTED], [EMAIL PROTECTED])
#include disclaimer.h
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/
Time to standardize the ``last insert id'' concept...
I'd appreciate a summary of which drivers support some form of ``last insert id'' and details of the interface they provide. Specifically... via an attribute or method? at the sth or dbh level, or both? any other details that might be relevant... Also, for databases that do _not_ support a ``last insert id'' concept in the client API, are there any SQL mechanisms for finding the last insert id? I'm currently (very vagely) thinking that it'll be a dbh level method something like this: $id = $dbh-last_insert_id(); $id = $dbh-last_insert_id($table_name, $column_name); where many drivers will ignore the parameters, but some might need them, for example to do: select $column_name from $table_name where $column_name IS NULL which is, I believe, the 'standard' ODBC way to get the last insert id. But I'm working from memory so could easily be wrong or confused here. (And, of course, the select would only reliably return the right value if AutoCommit is off so the table would still be locked after the insert.) Speak up! Thanks. Tim. p.s. I'm hoping DBI 1.35, which I've just released, will be fairly stable and I can aim to put new things like this, and 'take_imp_data' for Stas, into development releases leading up to a DBI 1.36.
Re: Time to standardize the ``last insert id'' concept...
On Fri, 2003-03-07 at 14:15, Tim Bunce wrote: I'd appreciate a summary of which drivers support some form of ``last insert id'' and details of the interface they provide. Specifically... via an attribute or method? at the sth or dbh level, or both? any other details that might be relevant... Also, for databases that do _not_ support a ``last insert id'' concept in the client API, are there any SQL mechanisms for finding the last insert id? For Sybase you issue a select @@identity right after the insert. I believe that this will also work with MS-SQL. The @@identity value is a global Transact-SQL variable that is localized to the connection. Note that you *can't* get at the @@identity value if you insert data using placeholders. This is because Sybase also localizes the @@variables within each stored procedure, and when using placeholders Sybase creates a temporary stored procedure for you on the fly. Off-hand I'm not sure how a last_insert_id() method would work for DBD::Sybase, but there may be a way to build something that is at least semi-reliable. Michael -- Michael Peppler Data Migrations, Inc. [EMAIL PROTECTED] http://www.mbay.net/~mpeppler Sybase T-SQL/OpenClient/OpenServer/C/Perl developer available for short or long term contract positions - http://www.mbay.net/~mpeppler/resume.html
