scain has asked for the wisdom of the Perl Monks concerning the following question:

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

[download]
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";

[download]
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

Replies are listed 'Best First'.
Re: Getting identity after autoincr in FreeTDS/MSSQL/DBI
by mpeppler (Vicar) on Nov 09, 2001 at 22:47 UTC
    What is the definition of the sctemp table?

    Also, run your script with the DBI->trace(3) flag, to see what DBD::Sybase thinks it's getting. For example, when I ran your script against a Sybase 12.0 server, I got: 

        -> fetchrow_hashref in DBD::_::st for DBD::Sybase::st (DBI::st=HAS
+H(0x81c6114)~0x8106cb0)
2   -> fetch for DBD::Sybase::st (DBI::st=HASH(0x8106cb0)~INNER)
    dbih_setup_fbav for 1 fields => 0x8106d64
2   <- fetch= [ '26' ] row1 at scratch.pl line 22
2   -> FETCH for DBD::Sybase::st (DBI::st=HASH(0x8106cb0)~INNER 'NAME'
+)
2   <- FETCH= [ 'foo' ] at scratch.pl line 22
    <- fetchrow_hashref= HASH(0x8106db8)1keys row1 at scratch.pl line 
+22

    [download]
    Michael
[reply]
[d/l]
[select]
      Michael,

      First, welcome! I didn't know you were here. Also, thanks for answering on the FreeTDS mailing list; I didn't think there was much overlap between here and there.

      I ran with trace on and got the following results, which indicate to me that truncation is taking place somewhere. Time to spend the afternoon installing a newer version of FreeTDS. 

          -> fetchrow_hashref in DBD::_::st for DBD::Sybase::st (DBI::st=HAS
+H(0x81d9a58)~0x81d99c8)
2   -> fetch for DBD::Sybase::st (DBI::st=HASH(0x81d99c8)~INNER)
    dbih_setup_fbav for 1 fields => 0x81d9b84
2   <- fetch= [ '2' ] row1 at test3.pl line 23
2   -> FETCH for DBD::Sybase::st (DBI::st=HASH(0x81d99c8)~INNER 'NAME'
+)
2   <- FETCH= [ 'foo' ] at test3.pl line 23
    <- fetchrow_hashref= HASH(0x80fd484)1keys row1 at test3.pl line 23
    -> errstr in DBD::_::common for DBD::Sybase::st (DBI::st=HASH(0x81
+d9a58)~0x81d99c8)
    <- errstr= undef at test3.pl line 25

      [download]
      Thanks again,
      Scott
[reply]
[d/l]
        Yeah, the returned data is clearly getting truncated.

        I don't think that it's specific to the @@identity value - it's probably related to numeric data getting truncated by FreeTDS.

        Michael

[reply]
Re: Getting identity after autoincr in FreeTDS/MSSQL/DBI
by Anonymous Monk on Nov 09, 2001 at 22:43 UTC
    I would bet that this is happening because you're trying to do both in one statement. try doing the insert, then grab the identity with a second call.. cooincidentally, my first post here spoke to this issue, albeit from Win32. Identity nabber for MSSQL
[reply]
      hmmm, I guess I should login before posting, if I was wrong about the above blame anonymous monk...
[reply]
        wardk,

        I think it is different when running over freeTDS on linux, because when I tried to do it as two seperate commands, @@IDENTITY was empty. I think it has something to do with the concept of "current session", but I don't really know.

        Nice work in Identity nabber for MSSQL, by the way.

        Thanks,
        Scott

[reply]

Back to Seekers of Perl Wisdom