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)



Subject: String comparison on integer PK fails
Date: Thu, 20 Jan 2011 15:23:29 -0600
To: bug-DBD-ODBC [...] rt.cpan.org
From: Wes Malone <wesdmalone [...] gmail.com>
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
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
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.