Skip Menu |
 

This queue is for tickets about the DBD-ODBC CPAN distribution.

Report information
The Basics
Id: 64968
Status: rejected
Priority: 0/
Queue: DBD-ODBC

People
Owner: Nobody in particular
Requestors: wesdmalone [...] gmail.com
Cc:
AdminCc:

Bug Information
Severity: (no value)
Broken in: (no value)
Fixed in: (no value)



From wesdmalone [...] gmail.com Thu Jan 20 16: 23:37 2011
MIME-Version: 1.0
X-Spam-Status: No, score=-6.21 tagged_above=-99.9 required=10 tests=[BAYES_00=-1.9, DKIM_SIGNED=0.1, DKIM_VALID=-0.1, DKIM_VALID_AU=-0.1, FREEMAIL_FROM=0.001, RCVD_IN_DNSWL_HI=-5, SPF_NEUTRAL=0.779, T_TO_NO_BRKTS_FREEMAIL=0.01] autolearn=ham
X-Spam-Flag: NO
content-type: text/plain; charset="utf-8"
Message-ID: <AANLkTi=9OmNqw6Y+=O++Cp7MXMLoE65nwEjxOCeZnkxA [...] mail.gmail.com>
X-Virus-Scanned: Debian amavisd-new at bestpractical.com
X-Spam-Score: -6.21
Received: from localhost (localhost [127.0.0.1]) by hipster.bestpractical.com (Postfix) with ESMTP id 2699424147A for <cpan-bug+DBD-ODBC [...] hipster.bestpractical.com>; Thu, 20 Jan 2011 16:23:37 -0500 (EST)
Received: from hipster.bestpractical.com ([127.0.0.1]) by localhost (hipster.bestpractical.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id Ry7kKgFdLHEE for <cpan-bug+DBD-ODBC [...] hipster.bestpractical.com>; Thu, 20 Jan 2011 16:23:35 -0500 (EST)
Received: from la.mx.develooper.com (x1.develooper.com [207.171.7.70]) by hipster.bestpractical.com (Postfix) with SMTP id 3FC6824136B for <bug-DBD-ODBC [...] rt.cpan.org>; Thu, 20 Jan 2011 16:23:35 -0500 (EST)
Received: (qmail 28837 invoked by uid 103); 20 Jan 2011 21:23:34 -0000
Received: from x16.dev (10.0.100.26) by x1.dev with QMQP; 20 Jan 2011 21:23:34 -0000
Received: from mail-iy0-f178.google.com (HELO mail-iy0-f178.google.com) (209.85.210.178) by 16.mx.develooper.com (qpsmtpd/0.80) with ESMTP; Thu, 20 Jan 2011 13:23:32 -0800
Received: by iyi42 with SMTP id 42so1005319iyi.9 for <bug-DBD-ODBC [...] rt.cpan.org>; Thu, 20 Jan 2011 13:23:30 -0800 (PST)
Received: by 10.42.225.129 with SMTP id is1mr3323716icb.17.1295558609930; Thu, 20 Jan 2011 13:23:29 -0800 (PST)
Received: by 10.42.226.132 with HTTP; Thu, 20 Jan 2011 13:23:29 -0800 (PST)
Authentication-Results: hipster.bestpractical.com (amavisd-new); dkim=pass header.i= [...] gmail.com
Authentication-Results: hipster.bestpractical.com (amavisd-new); domainkeys=pass header.from=wesdmalone [...] gmail.com
Delivered-To: cpan-bug+DBD-ODBC [...] hipster.bestpractical.com
Subject: String comparison on integer PK fails
Return-Path: <wesdmalone [...] gmail.com>
Domainkey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:date:message-id:subject:from:to:content-type :content-transfer-encoding; b=Tct4T3d9e+bpI6mH01Bey5B72t43QnoN2Fs/TSc1EydYsUaDS4kapOyoPFdAe7JZTp Z6EEF8gzz7yKovsdIM6Ke5eq1lPAz9fI5gAPDxYCngvFzIY7IWTNC7nIToosI9iKq9l6 SFPfu0vgKL8SuRUqlZ8ZhnW5EQxW1x/39Qot4=
X-RT-Mail-Extension: dbd-odbc
X-Original-To: cpan-bug+DBD-ODBC [...] hipster.bestpractical.com
X-Spam-Check-BY: 16.mx.develooper.com
Dkim-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:date:message-id:subject:from:to :content-type:content-transfer-encoding; bh=ZIOpKyZqAlO2g8iXkHdTl8VoOgNvEZp9RmNyB2GDkfY=; b=k1R80SGaqiNddsED0BceJaOGN7Lpp+oZ1jQ0HYkryZx43YwLVcGJ+C+564ZJkcUksL cZemReGtXYasraWVFu1ShGaAMVfLB+xgm/WXzbRauMYGIZDYSU/SMM93HGKJvlaEUlmo bXo1Lo9HKILtkrwNNHysjHYlgEnN30Cop4v2Y=
Date: Thu, 20 Jan 2011 15:23:29 -0600
X-Spam-Level:
To: bug-DBD-ODBC [...] rt.cpan.org
Content-Transfer-Encoding: quoted-printable
From: Wes Malone <wesdmalone [...] gmail.com>
X-RT-Original-Encoding: ISO-8859-1
Content-Length: 1807
Download (untitled) / with headers
text/plain 1.7k
I have a table like this: --Table Def from SSMS (MS SQL Server 10.0.1600) CREATE TABLE [dbo].[WorkScopeMaterials]( [work_order_id] [int] NOT NULL, [work_scope_id] [int] NOT NULL, [id] [int] NOT NULL, [quantity] [int] NULL, [part_id] [varchar](20) NULL, [is_in_stock] [bit] NULL, [is_order] [bit] NULL, [description] [varchar](23) NULL, [quantity_in_stock] [int] NULL, CONSTRAINT [PK_tblWsMaterialsList] PRIMARY KEY CLUSTERED ( [work_order_id] ASC, [work_scope_id] ASC, [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY] ) ON [PRIMARY] When I run: $dbh->selectall_arrayref(    'SELECT *       FROM [WorkScopeMaterials] [me]       WHERE( [me].[work_order_id] LIKE ? )', undef, '100%'); it works as expected. But this fails: $dbh->selectall_arrayref( 'SELECT * FROM [WorkScopeMaterials] [me] WHERE ( [me].[work_order_id] LIKE ? )', undef, '100%'); #space after WHERE With this error: DBIx::Class::Storage::DBI::__ANON__(): DBI Exception: DBD::ODBC::db selectall_arrayref failed: [Microsoft][SQL Server Native Client 10.0]Invalid character value for cast specification (SQL-22018) [for Statement "SELECT * FROM [WorkScopeMaterials] [me] WHERE ( [me].[work_order_id] LIKE ? )"] at script\workbench.pl line 38 I'm using DBIx::Class, so it would be difficult for me to work around this by just not putting a space after the where keyword. I can provide more info if necessary. Show quoted text
---Platform Info--- C:\code\acdri>perl -v This is perl, v5.10.1 (*) built for MSWin32-x86-multi-thread C:\code\acdri>perl -E "use DBD::ODBC 100" DBD::ODBC version 100 required--this is only version 1.27 at -e line 1. ODBC driver: sqlncli 2009.100.1600.01 x64
MIME-Version: 1.0
In-Reply-To: <AANLkTi=9OmNqw6Y+=O++Cp7MXMLoE65nwEjxOCeZnkxA [...] mail.gmail.com>
X-Mailer: MIME-tools 5.427 (Entity 5.427)
Content-Disposition: inline
References: <AANLkTi=9OmNqw6Y+=O++Cp7MXMLoE65nwEjxOCeZnkxA [...] mail.gmail.com>
Content-Type: text/plain; charset="UTF-8"
Message-ID: <rt-3.8.HEAD-19313-1295982428-560.64968-0-0 [...] rt.cpan.org>
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
Content-Length: 2154
Download (untitled) / with headers
text/plain 2.1k
On Thu Jan 20 16:23:37 2011, wesdmalone@gmail.com wrote: Show quoted text
> I have a table like this: > > --Table Def from SSMS (MS SQL Server 10.0.1600) > CREATE TABLE [dbo].[WorkScopeMaterials]( > [work_order_id] [int] NOT NULL, > [work_scope_id] [int] NOT NULL, > [id] [int] NOT NULL, > [quantity] [int] NULL, > [part_id] [varchar](20) NULL, > [is_in_stock] [bit] NULL, > [is_order] [bit] NULL, > [description] [varchar](23) NULL, > [quantity_in_stock] [int] NULL, > CONSTRAINT [PK_tblWsMaterialsList] PRIMARY KEY CLUSTERED > ( > [work_order_id] ASC, > [work_scope_id] ASC, > [id] ASC > )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY > = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) > ON [PRIMARY] > ) ON [PRIMARY] > > When I run: > > $dbh->selectall_arrayref( >    'SELECT * >       FROM [WorkScopeMaterials] [me] >       WHERE( [me].[work_order_id] LIKE ? )', undef, '100%'); > > it works as expected. > > But this fails: > $dbh->selectall_arrayref( > 'SELECT * > FROM [WorkScopeMaterials] [me] > WHERE ( [me].[work_order_id] LIKE ? )', undef, '100%'); #space > after WHERE > > With this error: > > DBIx::Class::Storage::DBI::__ANON__(): DBI Exception: DBD::ODBC::db > selectall_arrayref failed: [Microsoft][SQL Server Native Client > 10.0]Invalid character value for cast specification (SQL-22018) [for > Statement "SELECT * > FROM [WorkScopeMaterials] [me] > WHERE ( [me].[work_order_id] LIKE ? )"] at script\workbench.pl > line 38 > > I'm using DBIx::Class, so it would be difficult for me to work around > this by just not putting a space after the where keyword. > > I can provide more info if necessary. > > ---Platform Info--- > C:\code\acdri>perl -v > This is perl, v5.10.1 (*) built for MSWin32-x86-multi-thread > > C:\code\acdri>perl -E "use DBD::ODBC 100" > DBD::ODBC version 100 required--this is only version 1.27 at -e line > 1. > > ODBC driver: sqlncli 2009.100.1600.01 x64
Just to let you know I've reproduced this now. Sorry it has taken so long to get back to it but I've been extremely busy. Martin -- Martin J. Evans Wetherby, UK
MIME-Version: 1.0
In-Reply-To: <rt-3.8.HEAD-19313-1295982428-560.64968-0-0 [...] rt.cpan.org>
X-Mailer: MIME-tools 5.427 (Entity 5.427)
Content-Disposition: inline
References: <AANLkTi=9OmNqw6Y+=O++Cp7MXMLoE65nwEjxOCeZnkxA [...] mail.gmail.com> <rt-3.8.HEAD-19313-1295982428-560.64968-0-0 [...] rt.cpan.org>
Content-Type: text/plain; charset="UTF-8"
Message-ID: <rt-3.8.HEAD-19313-1295983360-453.64968-0-0 [...] rt.cpan.org>
Content-Transfer-Encoding: binary
X-RT-Original-Encoding: utf-8
Content-Length: 4134
On Tue Jan 25 14:07:09 2011, MJEVANS wrote: Show quoted text
> On Thu Jan 20 16:23:37 2011, wesdmalone@gmail.com wrote:
> > I have a table like this: > > > > --Table Def from SSMS (MS SQL Server 10.0.1600) > > CREATE TABLE [dbo].[WorkScopeMaterials]( > > [work_order_id] [int] NOT NULL, > > [work_scope_id] [int] NOT NULL, > > [id] [int] NOT NULL, > > [quantity] [int] NULL, > > [part_id] [varchar](20) NULL, > > [is_in_stock] [bit] NULL, > > [is_order] [bit] NULL, > > [description] [varchar](23) NULL, > > [quantity_in_stock] [int] NULL, > > CONSTRAINT [PK_tblWsMaterialsList] PRIMARY KEY CLUSTERED > > ( > > [work_order_id] ASC, > > [work_scope_id] ASC, > > [id] ASC > > )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY > > = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) > > ON [PRIMARY] > > ) ON [PRIMARY] > > > > When I run: > > > > $dbh->selectall_arrayref( > >    'SELECT * > >       FROM [WorkScopeMaterials] [me] > >       WHERE( [me].[work_order_id] LIKE ? )', undef, '100%'); > > > > it works as expected. > > > > But this fails: > > $dbh->selectall_arrayref( > > 'SELECT * > > FROM [WorkScopeMaterials] [me] > > WHERE ( [me].[work_order_id] LIKE ? )', undef, '100%'); #space > > after WHERE > > > > With this error: > > > > DBIx::Class::Storage::DBI::__ANON__(): DBI Exception: DBD::ODBC::db > > selectall_arrayref failed: [Microsoft][SQL Server Native Client > > 10.0]Invalid character value for cast specification (SQL-22018) [for > > Statement "SELECT * > > FROM [WorkScopeMaterials] [me] > > WHERE ( [me].[work_order_id] LIKE ? )"] at script\workbench.pl > > line 38 > > > > I'm using DBIx::Class, so it would be difficult for me to work around > > this by just not putting a space after the where keyword. > > > > I can provide more info if necessary. > > > > ---Platform Info--- > > C:\code\acdri>perl -v > > This is perl, v5.10.1 (*) built for MSWin32-x86-multi-thread > > > > C:\code\acdri>perl -E "use DBD::ODBC 100" > > DBD::ODBC version 100 required--this is only version 1.27 at -e line > > 1. > > > > ODBC driver: sqlncli 2009.100.1600.01 x64
> > Just to let you know I've reproduced this now. > > Sorry it has taken so long to get back to it but I've been extremely busy. > > Martin
In the first case where the space is missing SQLDescribeParam fails with: 07009 [Microsoft][SQL Server Native Client 10.0]Invalid parameter number get_param_type: modified value type to SQL_C_WCHAR and as you can see DBD::ODBC defaulted the parameter type to SQL_C_WCHAR because SQLDescribeParam failed. In the case where the space exists SQLDescribeParam worked and said the parameter was: SQLDescribeParam 1: SqlType=INTEGER(4) param_size=10 Scale=0 Nullable=0 Binding '100%' as an integer is bound to return the error you are seeing as work_order_id is an integer and '100%' cannot be cast to an integer. Fundamentally, this is a bug in the Native Client Driver as your parameter is an integer and should be reported as one but when the space is missing the SQLDescribeParam fails so DBD::ODBC falls back on SQL_WCHAR type. However, attempting to bind '100%' to an integer is flawed anyway so I could argue your code is wrong (I know you are using DBIx::Class really but don't compare '100%' with an integer - use '100' instead). What you have to understand about the driver is that it takes your SQL and attempts to rearrange it into a select on the table to find out details of the column you are binding a parameter to. Quite often it fails to rearrange the SQL sucessfully and SQLDescribeParam will fail. There is no bug in DBD::ODBC since it does what the driver tells it - if it says the parameter is an integer, it binds it as an integer. The proper way to avoid this is not to try and bind a string which cannot convert into a number but a (horrible) workaround is to specify the parameter bind type as SQL_WCHAR. I hope you won't be offended if I reject this as a bug in DBD::ODBC but I will leave it open for a while to see if any more comments come in. Martin -- Martin J. Evans Wetherby, UK


This service is sponsored and maintained by Best Practical Solutions and runs on Perl.org infrastructure.

Please report any issues with rt.cpan.org to rt-cpan-admin@bestpractical.com.