Hello fellow Monks,
I am trying to get MSSQL and FreeTDS to return the value of
an autoincrement of a primary key after an insert. This is
not the first time this has been asked here: $code or die
asked in DBI and primary keys, and was correctly informed that the
system variable @@IDENTITY contained the desired value, and
then indicated in a followup to that thread (here)
that SQL of the form
insert into table .......... select @@IDENTITY
would get the desired value (MSSQL allows you to put multiple
statements on one line like that).
All of this is well and good, but I believe $code or die was
working on Perl in a Windows environment; I want to make
it just that more difficult and use FreeTDS from a linux box
to do the work. Here is the test code I have written:
#!/usr/local/bin/perl -w
use DBI;
use strict;
$ENV{'SYBASE'} = '/usr/local/freetds';
$ENV{'TDSVER'} = 70;
my $server = "server";
my $db = "db";
my $user = "sqluser";
my $passwd = "sqluserpasswd";
my $dbh = DBI->connect("dbi:Sybase:server=$server;database=$db",$user,
+$passwd)
or die "--$!--\n";
my $sth = $dbh->prepare(' insert into sctemp (scratch) values (42) sel
+ect @@IDENTITY as foo');
$sth->execute;
my $data = $sth->fetchrow_hashref;
my $seqid= $$data{foo};
my $err = $sth->errstr;
print "$seqid, $err\n";
In addition to
fetchrow_hashref, I have tried
fetchrow_array and
fetch, changing
the assignment to $seqid to the right syntax. So here's the problem:
the value of $seqid seems to be getting truncated. The value
of @@IDENITY is currently on the order of 20, but when I print
$seqid, I get 2 (until I got the ID up to 20, I thought I was
getting a return value of 1). The error string is empty, as
it should be, since nothing seems to be going wrong on the database
side (when I run the command in Query Analyzer, it works fine).
So it now looks like a bug in FreeTDS, but I am not sure. Does
anyone see any other problems in my code that could explain
what I am seeing?
Thanks,
Scott
