I want to report a problem with the automatic typing of statement
parameters that is now the default. The database is SQL Server 2000
and the ODBC SQL Server Driver is from MDAC 2.7.
C:\>perl -MDBI -MDBD::ODBC -e "print qq{$DBI::VERSION
$DBD::ODBC::VERSION\n}"
1.30 0.43
Consider the table foo:
create table foo (x int, y datetime, z text);
I can do
$db->do("insert into foo values (3, '2002-10-01 10:05:30', 'x')");
or
$db->do("insert into foo values (3, '2002-10-01T10:05:30', 'x')");
without a problem. The second of these using the XML format for
datetime.
I can also do this:
$db->do(<<THIS, undef, '2002-10-01 10:05:30');
insert into foo values
(3, ?, 'x')
THIS
but the following yields an error:
$db->do(<<THIS, undef, '2002-10-01T10:05:30');
insert into foo values
(3, ?, 'x')
THIS
Here is the error:
[Microsoft][ODBC SQL Server Driver]
Invalid character value for cast specification (SQL-22005)
The relevant part of the DBI_TRACE=3 output is:
-> execute for DBD::ODBC::st (DBI::st=HASH(0x1d96868)~0x1d96a0c
'2002-10-01T10:05:30') thr#01ACF3C8
bind 1 <== '2002-10-01T10:05:30' (attribs: ), type 0
bind 1 <== '2002-10-01T10:05:30' (size 19/20/0, ptype 4, otype 1, sqltype 0)
bind 1 <== '2002-10-01T10:05:30' (len 19/19, null 0)
SQLDescribeParam 1: SqlType=TIMESTAMP, ColDef=23
bind 1: CTy=1, STy=TIMESTAMP, CD=19, Sc=19, VM=19.
dbd_st_execute (for hstmt 31791792 before)...
dbd_error: err_rc=-1 rc=0 s/d/e: 31791792/31789896/31789728
dbd_error: SQL-22005 (native 0): [Microsoft][ODBC SQL Server Driver]Invalid
char
acter value for cast specification (SQL-22005)
So where is the problem? DBD::ODBC, the ODBC driver, or SQL Server
itself?
If this works:
$db->do("insert into foo values (3, '2002-10-01T10:05:30', 'x')");
then shouldn't it allow '2002-10-01T10:05:30' to be coerced into a
datetime at the bind level too?
By the way, lest you suggest that I explicitly work around it by putting
a convert in the query, that also fails:
$db->do(<<THIS, undef, '2002-10-01T10:05:30');
insert into foo values
(3, convert(datetime,?,126), 'x')
THIS
in the exact same way, i.e. SqlDescribeParam still thinks that the
parameter should be bound to a datetime.
I noticed this problem when I upgraded DBD::ODBC to the latest version
to solve a different problem:
$db->do(<<THIS, undef, $int, $date, $text)
insert into foo values
(?,?,?)
THIS
was failing when length($text) > 8192, because it was being bound as a
varchar and not as text. The new DBD::ODBC solves that problem but
introduces a new one.
-Paul Weiss
