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