FreeTDS Development Group

["Travis LaWall" <tlawall AT peakpeak.com>]

Could someone comment on the following issue I have been having with
trying to retrieve the id of the primary key(PK) of the row I just
inserted. It seems the one's place of the returned value is being dropped
when I try to do a regular select @@IDENTITY. However I am able to get the
proper result when I cast the result of the @@IDENTITY as an INT. Am I
doing something wrong here or is this how it has to be done in the first
place or do people not get the PK this way to begin with?

Below I have tried to provide as much information as possible in this
order Linux Version, Perl Version, DBD Version, Database Version, Table
DDL, FreeTDS Version and protocol Version, My Code, Code Results with
freetds packet log for the packets in question.

Any help will be appreciated and my thanks in advance.

Travis

Linux myhost.peakpeak.com 2.4.2-2 #1 Sun Apr 8 20:41:30 EDT 2001 i686
unknown

----------------------------------------------------------------------------------------------------------------------


This is perl, v5.6.0 built for i686-linux

Copyright 1987-2000, Larry Wall

Perl may be copied only under the terms of either the Artistic License or
the
GNU General Public License, which may be found in the Perl 5.0 source kit.

Complete documentation for Perl, including FAQ lists, should be found on
this system using `man perl' or `perldoc perl'.  If you have access to the
Internet, point your browser at http://www.perl.com/, the Perl Home Page.

----------------------------------------------------------------------------------------------------------------------


Version of DBD/Sybase.pm

bash-2.04$ head -30
/usr/local/lib/perl5/site_perl/5.6.0/i686-linux/DBD/Sybase.pm
# -*-Perl-*-
# $Id: Sybase.pm,v 1.25 2000/11/15 00:56:12 mpeppler Exp $

# Copyright (c) 1996,1997,1998,1999,2000   Michael Peppler
#
#   You may distribute under the terms of either the GNU General Public
#   License or the Artistic License, as specified in the Perl README file,
#   with the exception that it cannot be placed on a CD-ROM or similar
media
#   for commercial distribution without the prior approval of the author.
#
# Based on DBD::Oracle Copyright (c) 1994,1995,1996,1997 Tim Bunce

{
    package DBD::Sybase;

    use DBI ();
    use DynaLoader ();
    use Exporter ();
    @ISA = qw(DynaLoader Exporter);

    @EXPORT = qw(CS_ROW_RESULT CS_CURSOR_RESULT CS_PARAM_RESULT
                 CS_STATUS_RESULT CS_MSG_RESULT CS_COMPUTE_RESULT);


    $VERSION = '0.91';
    my $Revision = substr(q$Revision: 1.25 $, 10);

    require_version DBI 1.02;

    bootstrap DBD::Sybase $VERSION;

----------------------------------------------------------------------------------------------------------------------


select @@VERSION

'Microsoft SQL Server  7.00 - 7.00.842 (Intel X86)      
 Mar  2 2000 06:49:37    
 Copyright (c) 1988-1998 Microsoft Corporation       
 Enterprise Edition on Windows NT 4.0 (Build 1381: Service Pack 6) '

----------------------------------------------------------------------------------------------------------------------

PotentialCustomers table from my MS SQL Database.


| universe | dbo                | PotentialCustomers      | UserID        
               | 1  | No  | int           |
| universe | dbo                | PotentialCustomers      | CRID          
               | 2  | YES | int           |
| universe | dbo                | PotentialCustomers      | CustomerName  
               | 3  | YES | nvarchar      |
| universe | dbo                | PotentialCustomers      |
CustomerCompanyName           | 4  | YES | nvarchar      |
| universe | dbo                | PotentialCustomers      | StreetAddress 
               | 5  | YES | nvarchar      |
| universe | dbo                | PotentialCustomers      | StreetAddress2
               | 6  | YES | nvarchar      |
| universe | dbo                | PotentialCustomers      | City          
               | 7  | YES | nvarchar      |
| universe | dbo                | PotentialCustomers      | State         
               | 8  | YES | nvarchar      |
| universe | dbo                | PotentialCustomers      | ZIP           
               | 9  | YES | nvarchar      |
| universe | dbo                | PotentialCustomers      | QwestName     
               | 10 | YES | nvarchar      |
| universe | dbo                | PotentialCustomers      |
QwestStreetAddress            | 11 | YES | nvarchar      |
| universe | dbo                | PotentialCustomers      |
CustomerEmailAddress          | 12 | YES | nvarchar      |
| universe | dbo                | PotentialCustomers      |
CustomerSitePassword          | 13 | YES | nvarchar      |
| universe | dbo                | PotentialCustomers      | CustomerType  
               | 14 | YES | int           |



----------------------------------------------------------------------------------------------------------------------

/usr/src/freetds-0.51/config.status

s%@CFLAGS@%-g -O2 -DTDS50%g

----------------------------------------------------------------------------------------------------------------------


use DBI;
use Data::Dumper

$dbh = DBI->connect($datasource, $username, $auth );

### insert a row
$sqlstmt = "insert into PotentialCustomers(CustomerName,StreetAddress)
values('oiuouououou','ytytytytyt')";
$sth = $dbh->do($sqlstmt);
$sth = $dbh->prepare($sqlstmt);
$rv = $sth->execute;

### fetch the last id inserted

### the offending piece of code
#$sqlstmt = 'select @@IDENTITY';


### the work around
#$sqlstmt = 'select CAST( @@IDENTITY AS INT )';

$sth = $dbh->prepare($sqlstmt);
$rv = $sth->execute;
$ary_ref  = $sth->fetchall_arrayref;
print Dumper $ary_ref;

###
$sqlstmt = "select * from PotentialCustomers";
$sth = $dbh->prepare($sqlstmt);
$rv = $sth->execute;
$ary_ref  = $sth->fetchall_arrayref;
#print Dumper $ary_ref;
print join("   ", @{$$ary_ref[-1]});
print "\n";


Results with the $sqlstmt = 'select @@IDENTITY'; uncommented.

$VAR1 = [
          [
            '11'
          ]
        ];
112      oiuouououou      ytytytytyt      

oy! the one's place is missing! 

FreeTDS dump facility provides:

Sending packet @ 2001-07-23 14:56:18
0000  01 01 00 2c 00 00 01 00 73 00 65 00 6c 00 65 00   |...,....s.e.l.e.|
0010  63 00 74 00 20 00 40 00 40 00 49 00 44 00 45 00   |c.t. .@.@.I.D.E.|
0020  4e 00 54 00 49 00 54 00 59 00 20 00               |N.T.I.T.Y. .|


2001-07-23 14:56:18 inside ct_results()
2001-07-23 14:56:18 processing result tokens.  marker is  81
2001-07-23 14:56:18 processing result tokens.  marker is  d1
2001-07-23 14:56:18 inside ct_res_info()
2001-07-23 14:56:18 inside ct_describe()
2001-07-23 14:56:18 inside _ct_get_client_type()
2001-07-23 14:56:18 inside ct_describe() datafmt->datatype = 7 server type
108
2001-07-23 14:56:18 inside ct_bind()
2001-07-23 14:56:18 inside ct_bind() item = 1 datafmt->datatype = 1
2001-07-23 14:56:18 inside ct_fetch()
2001-07-23 14:56:18 processing row tokens.  marker is  d1
2001-07-23 14:56:18 setting column 0 to NULL
2001-07-23 14:56:18 inside _ct_bind_data()
2001-07-23 14:56:18 inside _ct_get_server_type()
2001-07-23 14:56:18 inside _ct_bind_data() converting column 0 from type
108 to type 47
2001-07-23 14:56:18 inside _ct_bind_data() setting source length for 0 =
17
2001-07-23 14:56:18 inside _ct_bind_data() conversion done len = 3 bindfmt
= 0
2001-07-23 14:56:18 inside _ct_bind_data() length binding len = 2
2001-07-23 14:56:18 inside ct_fetch()
2001-07-23 14:56:18 processing row tokens.  marker is  fd
2001-07-23 14:56:18 inside ct_results()
2001-07-23 14:56:18 inside ct_res_info()
2001-07-23 14:56:18 inside ct_results()
2001-07-23 14:56:18 inside ct_cmd_drop()
2001-07-23 14:56:18 inside ct_cmd_alloc()
2001-07-23 14:56:18 inside ct_command()
2001-07-23 14:56:18 inside ct_send()


Results with the work around $sqlstmt = 'select CAST( @@IDENTITY AS INT
)'; uncommented

$VAR1 = [
          [
            116
          ]
        ];
116      oiuouououou      ytytytytyt     

seems to work!

FreeTDS dump facility provides:

Sending packet @ 2001-07-23 15:00:34
0000  01 01 00 48 00 00 01 00 73 00 65 00 6c 00 65 00   |...H....s.e.l.e.|
0010  63 00 74 00 20 00 43 00 41 00 53 00 54 00 28 00   |c.t. .C.A.S.T.(.|
0020  20 00 40 00 40 00 49 00 44 00 45 00 4e 00 54 00   | .@.@.I.D.E.N.T.|
0030  49 00 54 00 59 00 20 00 41 00 53 00 20 00 49 00   |I.T.Y. .A.S. .I.|
0040  4e 00 54 00 20 00 29 00                           |N.T. .).|


2001-07-23 15:00:34 inside ct_results()
2001-07-23 15:00:34 processing result tokens.  marker is  81
2001-07-23 15:00:34 processing result tokens.  marker is  d1
2001-07-23 15:00:34 inside ct_res_info()
2001-07-23 15:00:34 inside ct_describe()
2001-07-23 15:00:34 inside _ct_get_client_type()
2001-07-23 15:00:34 inside ct_describe() datafmt->datatype = 2 server type
38
2001-07-23 15:00:34 inside ct_bind()
2001-07-23 15:00:34 inside ct_bind() item = 1 datafmt->datatype = 2
2001-07-23 15:00:34 inside ct_fetch()
2001-07-23 15:00:34 processing row tokens.  marker is  d1
2001-07-23 15:00:34 setting column 0 to NULL
2001-07-23 15:00:34 inside _ct_bind_data()
2001-07-23 15:00:34 inside _ct_get_server_type()
2001-07-23 15:00:34 inside _ct_bind_data() converting column 0 from type
38 to type 56
2001-07-23 15:00:34 inside _ct_bind_data() setting source length for 0 = 4
2001-07-23 15:00:34 inside _ct_bind_data() conversion done len = 4 bindfmt
= 0
2001-07-23 15:00:34 inside _ct_bind_data() length binding len = 4
2001-07-23 15:00:34 inside ct_fetch()
2001-07-23 15:00:34 processing row tokens.  marker is  fd
2001-07-23 15:00:34 inside ct_results()
2001-07-23 15:00:34 inside ct_res_info()
2001-07-23 15:00:34 inside ct_results()
2001-07-23 15:00:34 inside ct_cmd_drop()
2001-07-23 15:00:34 inside ct_cmd_alloc()
2001-07-23 15:00:34 inside ct_command()
2001-07-23 15:00:34 inside ct_send()