FreeTDS Development Group

[Brian Bruns <camber AT ais.org>]

It appears you are using 0.51? Try the latest CVS snapshot.


On Mon, 23 Jul 2001, Travis LaWall wrote:

> 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()
> 
> 
> ---
> You are currently subscribed to freetds as: [camber AT ais.org]
> To unsubscribe, forward this message to $subst('Email.Unsub')
>