Re: Re: Re: Class::DBI && Sybase

Note that this was discussed recently on the dbi-dev mailing list (Tim wants to add a method to DBI to get the "last id" for auto-incremented values).

That's when I found some interesting properties of DBD::Sybase that I didn't think would work...

First, if you use placeholders then you can't get at the @@identity value, as is documented.

However, if AutoCommit is off you can do the following (all error handling ommitted!):

$sth = $dbh->prepare("insert foo(...) values(?, ?, ...)");

# insert first row:
$sth->execute('foo', 'bar', ...);

# fetch the id value (assuming "foo_id" is an identity column)
$sth2 = $dbh->prepare("select max(foo_id) from foo");
$sth2->execute;
while($data = $sth2->fetch) {
    $last_id = $data->[0];
}

# insert second row
$sth->execute('baz', ...);

# fetch the id value
$sth2 = $dbh->prepare("select max(foo_id) from foo");
$sth2->execute;
while($data = $sth2->fetch) {
    $last_id = $data->[0];
}
# etc.
[download]

This surprised me greatly, to say the least, because I was sure that Sybase wouldn't let me execute the second prepare() while the original $sth was still active.

You need to run this in AutoCommit off mode to make sure that your select max(id) statement will get your last id, not some other processes...

Note that there are locking issues with this technique, as your process holds locks on the table until you execute the commit, which may be a problem for a high volume site.

Michael

Comment on Re: Re: Re: Class::DBI && Sybase Select or Download Code

Replies are listed 'Best First'.
Re: Re: Re: Re: Class::DBI && Sybase by kal (Hermit) on Apr 03, 2003 at 17:55 UTC
Note that this was discussed recently on the dbi-dev mailing list (Tim wants to add a method to DBI to get the "last id" for auto-incremented values). Thanks, I'll look that up - I hope Tim gets his way, though, because this is a bit useful ;) Just having something standard to override in a subclass would be good. As for the select max(), I have to say that I did consider it, but thought that it wouldn't work (when others are accessing the DB, for example), or that it would be unreliable. I did also see some stuff about commit() being troublesome if you had many processes, which I probably will have. However, I didn't realise that you held table locks in non-auto mode.. I guess I need to read up a bit more. To be honest, I'm really hoping to move to PostgreSQL soon, which would negate all these silly problems. For those who are interested, my current solution (which is working) is to overload _insert_row() to use a non-placeholder method to insert the data, which then allows me to select @@identity. However, it does require help from the Class::DBI table instances to give schema information, since I need to quote some columns and not quote others (basically doing the work of the placeholders, in a way), which is a bit of a pain. But, the code is actually fairly tidy, and I think in this simple scenario I can get away with it. It's a nice clean separation, so if I ever need to implement something more sophisticated (like the above), I won't need to change too much, other than the _insert_row() implementation. I will have probably moved off of Sybase by that stage, though.	[reply]

Replies are listed 'Best First'.

Re: Re: Re: Re: Class::DBI && Sybase
by kal (Hermit) on Apr 03, 2003 at 17:55 UTC

Note that this was discussed recently on the dbi-dev mailing list (Tim wants to add a method to DBI to get the "last id" for auto-incremented values).

Thanks, I'll look that up - I hope Tim gets his way, though, because this is a bit useful ;) Just having something standard to override in a subclass would be good.

As for the select max(), I have to say that I did consider it, but thought that it wouldn't work (when others are accessing the DB, for example), or that it would be unreliable. I did also see some stuff about commit() being troublesome if you had many processes, which I probably will have. However, I didn't realise that you held table locks in non-auto mode.. I guess I need to read up a bit more. To be honest, I'm really hoping to move to PostgreSQL soon, which would negate all these silly problems.

For those who are interested, my current solution (which is working) is to overload _insert_row() to use a non-placeholder method to insert the data, which then allows me to select @@identity. However, it does require help from the Class::DBI table instances to give schema information, since I need to quote some columns and not quote others (basically doing the work of the placeholders, in a way), which is a bit of a pain. But, the code is actually fairly tidy, and I think in this simple scenario I can get away with it. It's a nice clean separation, so if I ever need to implement something more sophisticated (like the above), I won't need to change too much, other than the _insert_row() implementation.

I will have probably moved off of Sybase by that stage, though.

[reply]