It is not possible to retrieve the last IDENTITY value after an insert done with ?-style placeholders. This is a Sybase limitation/bug, not a DBD::Sybase problem.

--
Ilya Martynov, ilya@iponweb.net
CTO IPonWEB (UK) Ltd
Quality Perl Programming and Unix Support UK managed @ offshore prices - http://www.iponweb.net
Personal website - http://martynov.org

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

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.

With DBD::Pg and PostgreSQL, I do the following:

# some standard DBI code doing an INSERT here..
my $dbh = ...;
my $sth = $dbh->prepare('...');
$sth->execute(@values);

my $seq = 'SOME_SEQUENCE_NAME';
my $sth = $dbh->prepare("SELECT currval('$seq')");
$sth->execute() or die $dbh->errstr;
if (my @row = $sth->fetchrow_array) {
  return $row[0];
}
[download]

The reason this works (I think) is that I am using the same $dbh as for an INSERT statement earlier in the code. That and PostgreSQL's way of doing this is better than TSQL's. I hated @@identity when I used to work with SQL Server.

I realise the difference here is that in Sybase (and MS SQL Server) TSQL you have to use @@identity, rather than just SELECT on a sequence. I hope this gives you some insight all the same. Maybe you should try and do this in 'raw DBI' first, and then use that to patch Class::DBI. If you get something that works please post it on this thread; I don't have access to a Sybase instance but I would still be interested.

That line in Class::DBI looks like it could use a polymorphic get_id method. I haven't used Class::DBI (yet) though so I won't comment further.

try this

package MyDB;
use base 'Class::DBI';
__PACKAGE__->set_db(...);
# other table junk setup
__PACKAGE__->set_sql('getlastid', 'SELECT @@identity',...);   # see pe
+rldoc Ima::DBI
...
1;

# then where you need the last id you can try

my ($id) = MyDBI->sql_getlastid()->execute();

# or something close (again see Ima::DBI)
[download]