It appears that selecting @@IDENTITY returns only the first IDENTITY
result for the database connection. This is with SyBase 11.0.3.3, DBI
1.13 and DBD:Sybase 0.91.
For example, I created a test table as such:
create table test (id numeric(10,0) identity, blah char(10) null)
And then ran a Perl script:
...
# connect to the database
$db = DBI->connect("dbi:Sybase:", $dbuser, $dbpass);
# test the first insertion
$qd = $db->prepare("insert into test (blah) values ('one')");
$qd->execute;
$qd = $db->prepare('select @@identity');
$qd->execute;
$row = $qd->fetchrow_arrayref;
print "Identity: $row->[0]\n";
# test the second insertion
$qd = $db->prepare("insert into test (blah) values ('two')");
$qd->execute;
$qd = $db->prepare('select @@identity');
$qd->execute;
$row = $qd->fetchrow_arrayref;
print "Identity: $row->[0]\n";
print "done\n";
Which produces the output:
Identity: 1
Identity: 1
Where the correct result should be:
Identity: 1
Identity: 2
The only way I can get around the problem is to disconnect and then
reconnect to the database before inserting into a table where I need to
get the IDENTITY value. This problem did not occur in the SyBase
command-line interface - only in the Perl script as discussed.
I would rather not use the workaround, so if anyone knows if I am
missing a setting or command, your help will be greatly appreciated.
Sincerely,
Andrew Powell
aap
