Paul,
I think this is in the driver. The problem is, in the case where you are
not binding the parameters, I believe the Server itself is handling the data
and converting it. In the case where you are binding, I believe the driver
is attempting to parse along the ODBC rules (not the SQLServer rules) and
indicating that it can't parse the date with the T.
That's what I *think*, at this point. You might want to try DBD::ODBC
0.45_18, in case they updated it in their ODBC 3.0 support and left it off
for 2.0. .43 still uses the ODBC 2.0 API and the driver definitely behaves
differently -- ESPECIALLY Microsoft's SQL Server.
Another thing that you can try is to use prepare() and bind_param and
actually try binding either the convert(datetime, ?, ...) as a VARCHAR,
which should let the server do the parsing.
Regards,
Jeff
>
>
> 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
>
>
>
>
