Skip to Content.
Sympa Menu

freetds - Re: Data format/cast issue involving select @@IDENTITY?

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Brian Bruns <camber AT ais.org>
  • To: TDS Development Group <freetds AT franklin.oit.unc.edu>
  • Subject: Re: Data format/cast issue involving select @@IDENTITY?
  • Date: Mon, 23 Jul 2001 22:05:18 -0400 (EDT)



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')
>





Archive powered by MHonArc 2.6.24.

Top of Page