After something that happened in my code recently, I suspect there may be a
more general problem. May I suggest you replace the prepare/executes with
the basic 'do' function for the two insertions. Does this make any
difference?
My problems involved a rather long-winded trigger, which (among other
things) logged insertions to a table. When I checked the audit table after
the insertion, there was no sign of it until I reconnected. The problem
disappears using $db->do("INSERT ...blah...") - Or maybe I have lulled
myself into a false sense of security...
TIM
> -----Original Message-----
> From: Andrew Powell [SMTP:[EMAIL PROTECTED]]
> Sent: Monday, March 12, 2001 1:52 PM
> To: [EMAIL PROTECTED]
> Subject: DBI/DBD::Sybase @@IDENTITY Bug??
>
> 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