Blast, I think you've cracked it :( (Thanks to zengargoyle, incidentally - I had already thought of that solution, and it didn't work, but it was a good idea ;)

Looking back at my previous code that pulls back @@identity (there's not all that much), I've realised that one consistent feature is that I don't use placeholders, so I think I've probably discovered this problem in the past and either got around it subconciously, or just forgotton the doc. I'm fairly sure I hadn't read that before, though.

I guess the thing to do will be to re-write _insert_row() completely so that it generates the SQL without a placeholder syntax, which won't be too bad, but has implications in terms of inheritance that I haven't quite got my brane around yet. I think it should be okay, but it probably needs warning signs on it :/ Thanks very much though!

(This also explains neatly why Class::DBI wouldn't support this for Sybase, even though the docs suggest it works - I might send an email to the author to make it clearer, because I wouldn't have thought to look to DBD::Sybase for the 'bug')

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

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.

i'm sorry my right before bedtime half-idea didn't work. FWIW, Class::DBI doesn't work with DBD::CSV either. DBD::CSV doesn't like to have the table id prepended to the column spec in queries (SELECT table.col FROM table). i'm now the proud owner of a Class::DBI subclass that loads a DB via DBD::CSV into a DBD::SQLite DB so the regular Class::DBI methods work, then on DESTROY the SQLite DB is copied back over the DBD::CSV DB so i can have my simple flat file format cake and eat it with Class::DBI.