Skip Menu |
 

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

Report information
The Basics
Id: 2102
Status: resolved
Priority: 0/
Queue: DBD-ODBC

People
Owner: Nobody in particular
Requestors: Jenda [...] Krynicky.cz
Cc:
AdminCc:

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



Subject: Passing dates to MS SQL via DBD::ODBC doesn't work under 5.8
In a script I have something like this: $db = DBI->connect('dbi:ODBC:'.$DSN, $UID, $PWD, {PrintError => 0, RaiseError => 1,LongReadLen => 65536}); ... $FetchPagesWithUpdatedTranslations = $db->prepare('exec FetchPagesWithUpdatedTranslations ?, ?'); # product_id, time ... my $res = $FetchPagesWithUpdatedTranslations->execute( $prodId, SQLtime($FILES{$section})); ... sub SQLtime { my $time = shift() || 0; my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime($time); $year+=1900; $mon++; return "$year-$mon-$mday $hour:$min:$sec"; } The stored procedure is like this: CREATE PROCEDURE FetchPagesWithUpdatedTranslations ( @ProductId Int, @Date datetime = NULL ) AS ... This code works fine with Perl 5.6.1. (ActiveState build 631), but reports DBD::ODBC::st execute failed: [Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification (SQL-22018)(DBD: st_execute/SQLExecute err=-1) at ... with Perl 5.8 (ActiveState build 804). Both running under Win2k server with MS SQL 2000. I tried it with DBI 1.30 (from PPM) and DBI 1.32 (compiled by myself) and with DBD::ODBC 0.43 (from PPM), 1.02 and 1.04 (compiled by myself) and it reported this error every time under 5.8 and worked fine under 5.6.1. I then tried to change the stored procedure to CREATE PROCEDURE FetchPagesWithUpdatedTranslations ( @ProductId Int, @DateS varchar(20) = NULL ) AS ... and that worked with both 5.6.1 and 5.8. Do you have any idea what could be wrong? In this case I can change the stored procedure, but in the future this could be a problem. I attach a test script. It creates a CREATE PROCEDURE Test__DBD_ODBC_Date_Problem ( @date datetime) AS SELECT @date, @date+1 procedure and tries to call it. I've tried to use "{d '$year-$mon-$mday $hour:$min:$sec'}"; "{d '$year-$mon-$mday $hour:$min'}"; "{d '$year-$mon-$mday'}"; "$year-$mon-$mday $hour:$min:$sec" as the date, and either go the Invalid character value for cast specification or Can't rebind placeholder 1 no matter what. Thanks, Jenda
Download zk_db.pl
text/x-perl 1k
use DBI; $DSN = ''; $username = ''; $password = ''; $db = DBI->connect('dbi:ODBC:' . $DSN, $username, $password, {PrintError => 0,RaiseError => 1,LongReadLen => 65536}); $db->do('CREATE PROCEDURE Test__DBD_ODBC_Date_Problem ( @date datetime) AS SELECT @date, @date+1'); $exec = $db->prepare( q<exec Test__DBD_ODBC_Date_Problem ?>); $time = time(); eval { print "Test__DBD_ODBC_Date_Problem '" . SQLtime($time) . "'\n\t\t->\n"; my $res = $exec->execute( SQLtime($time)); if ($res != 0) { my $rows = $exec->fetchall_arrayref(); foreach my $row (@$rows) { print "Returned: $row->[0], $row->[1]\nIt's OK.\n"; } } else { print "FAILED!!!\n"; } }; print STDERR $@ if $@; $db->do('DROP PROCEDURE Test__DBD_ODBC_Date_Problem'); sub SQLtime { my $time = shift() || 0; my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) = localtime($time); $year+=1900; $mon++; # return "{d '$year-$mon-$mday $hour:$min:$sec'}"; # return "{d '$year-$mon-$mday $hour:$min'}"; # return "{d '$year-$mon-$mday'}"; return "$year-$mon-$mday $hour:$min:$sec"; } 1;
From: "Jeff Urlwin" <jurlwin [...] bellatlantic.net>
To: <bug-DBD-ODBC [...] rt.cpan.org>, <"AdminCc ofcpanTicket#2102:" [...] fsck.com>
Subject: RE: [cpan #2102] Passing dates to MS SQL via DBD::ODBC doesn't work under 5.8
Date: Fri, 14 Feb 2003 07:36:13 -0500
RT-Send-Cc:
Download (untitled) / with headers
text/plain 2.9k
Try binding '{ts etc}' (see the tests in DBD::ODBC which have date and time values. Grep ts would do it.) Also, you can change the bind type to character by: $sth->bind_param(n, undef, SQL_VARCHAR); Read the section in the DBD::ODBC POD regarding odbc_default_bind_type, as that changed around .43. Jeff Show quoted text
> -----Original Message----- > From: Guest via RT [mailto:bug-DBD-ODBC@rt.cpan.org] > Sent: Thursday, February 13, 2003 4:58 PM > To: AdminCc of cpan Ticket #2102 : > Subject: [cpan #2102] Passing dates to MS SQL via DBD::ODBC > doesn't work under 5.8 > > > > This message about DBD-ODBC was sent to you by guest <> via > rt.cpan.org > > Full context and any attached attachments can be found at: > <URL: https://rt.cpan.org/Ticket/Display.html?id=2102 > > > In a script I have something like this: > > $db = DBI->connect('dbi:ODBC:'.$DSN, $UID, $PWD, > {PrintError => 0, RaiseError => 1,LongReadLen => 65536}); > ... > $FetchPagesWithUpdatedTranslations > = $db->prepare('exec FetchPagesWithUpdatedTranslations ?, ?'); > # product_id, time > ... > my $res = $FetchPagesWithUpdatedTranslations->execute( > $prodId, SQLtime($FILES{$section})); > ... > sub SQLtime { > my $time = shift() || 0; > my ($sec,$min,$hour,$mday,$mon,$year,$wday,$yday,$isdst) > = localtime($time); > $year+=1900; > $mon++; > return "$year-$mon-$mday $hour:$min:$sec"; > } > > The stored procedure is like this: > CREATE PROCEDURE FetchPagesWithUpdatedTranslations ( > @ProductId Int, > @Date datetime = NULL > ) AS > ... > > This code works fine with Perl 5.6.1. (ActiveState build > 631), but reports DBD::ODBC::st execute failed: > [Microsoft][ODBC SQL Server Driver]Invalid character value > for cast specification (SQL-22018)(DBD: st_execute/SQLExecute > err=-1) at ... > > with Perl 5.8 (ActiveState build 804). Both running under > Win2k server with MS SQL 2000. > > I tried it with DBI 1.30 (from PPM) and DBI 1.32 (compiled by > myself) and with DBD::ODBC 0.43 (from PPM), 1.02 and 1.04 > (compiled by myself) and it reported this error every time > under 5.8 and worked fine under 5.6.1. > > I then tried to change the stored procedure to > > CREATE PROCEDURE FetchPagesWithUpdatedTranslations ( > @ProductId Int, > @DateS varchar(20) = NULL > ) AS > ... > > and that worked with both 5.6.1 and 5.8. Do you have any idea > what could be wrong? In this case I can change the stored > procedure, but in the future this could be a problem. > > I attach a test script. It creates a > CREATE PROCEDURE Test__DBD_ODBC_Date_Problem ( @date > datetime) AS SELECT @date, @date+1 procedure and tries to call it. > > I've tried to use > "{d '$year-$mon-$mday $hour:$min:$sec'}"; > "{d '$year-$mon-$mday $hour:$min'}"; > "{d '$year-$mon-$mday'}"; > "$year-$mon-$mday $hour:$min:$sec" > as the date, and either go the > Invalid character value for cast specification > or > Can't rebind placeholder 1 > no matter what. > > Thanks, Jenda >
From: Jenda [...] Krynicky.cz
Download (untitled) / with headers
text/plain 611b
[jurlwin@bellatlantic.net - Fri Feb 14 07:36:31 2003]: Show quoted text
> Try binding '{ts etc}' > (see the tests in DBD::ODBC which have date and time values. Grep ts > would do it.)
Same results as with {d etc}. Either Can't rebind placeholder 1 or Invalid character value for cast specification Show quoted text
> Also, you can change the bind type to character by: > $sth->bind_param(n, undef, SQL_VARCHAR);
This works. Show quoted text
> Read the section in the DBD::ODBC POD regarding
odbc_default_bind_type, Show quoted text
> as that changed around .43. > > Jeff
I stil wonder why does it behave differently under 5.6.1 and 5.8. Never mind. Thanks, Jenda
From: "Jeff Urlwin" <jurlwin [...] bellatlantic.net>
To: <bug-DBD-ODBC [...] rt.cpan.org>, <"AdminCc ofcpanTicket#2102:" [...] fsck.com>
Subject: RE: [cpan #2102] Passing dates to MS SQL via DBD::ODBC doesn't work under 5.8
Date: Fri, 14 Feb 2003 18:20:19 -0500
RT-Send-Cc:
Download (untitled) / with headers
text/plain 1.1k
I'll have to look at it. Jeff Show quoted text
> -----Original Message----- > From: Guest via RT [mailto:bug-DBD-ODBC@rt.cpan.org] > Sent: Friday, February 14, 2003 10:23 AM > To: AdminCc of cpan Ticket #2102 : > Subject: [cpan #2102] Passing dates to MS SQL via DBD::ODBC > doesn't work under 5.8 > > > > This message about DBD-ODBC was sent to you by guest <> via > rt.cpan.org > > Full context and any attached attachments can be found at: > <URL: https://rt.cpan.org/Ticket/Display.html?id=2102 > > > [jurlwin@bellatlantic.net - Fri Feb 14 07:36:31 2003]:
> > Try binding '{ts etc}' > > (see the tests in DBD::ODBC which have date and time
> values. Grep ts
> > would do it.)
> > Same results as with {d etc}. Either > Can't rebind placeholder 1 > or > Invalid character value for cast specification >
> > Also, you can change the bind type to character by: > > $sth->bind_param(n, undef, SQL_VARCHAR);
> > This works. >
> > Read the section in the DBD::ODBC POD regarding
> odbc_default_bind_type,
> > as that changed around .43. > > > > Jeff
> > I stil wonder why does it behave differently under 5.6.1 and > 5.8. Never mind. > > Thanks, Jenda >
Download (untitled) / with headers
text/plain 1.7k
Just out of curiosity, were you using 0.28 under 5.6.1? By default, odbc_bind_type is set to SQL_VARCHAR in that version. That would explain a difference between 5.6.1 and 5.8 -- I changed the default bind type to autodetect, from forcing a character conversion every time. I'll have to try this with a stored proc, since it may be that the driver doesn't detect, correctly, that the stored proc parameter is datetime... what version of the ODBC driver and server are you using? THanks, Jeff [jurlwin@bellatlantic.net - Fri Feb 14 18:20:39 2003]: Show quoted text
> I'll have to look at it. > > > Jeff >
> > -----Original Message----- > > From: Guest via RT [mailto:bug-DBD-ODBC@rt.cpan.org] > > Sent: Friday, February 14, 2003 10:23 AM > > To: AdminCc of cpan Ticket #2102 : > > Subject: [cpan #2102] Passing dates to MS SQL via DBD::ODBC > > doesn't work under 5.8 > > > > > > > > This message about DBD-ODBC was sent to you by guest <> via > > rt.cpan.org > > > > Full context and any attached attachments can be found at: > > <URL: https://rt.cpan.org/Ticket/Display.html?id=2102 > > > > > [jurlwin@bellatlantic.net - Fri Feb 14 07:36:31 2003]:
> > > Try binding '{ts etc}' > > > (see the tests in DBD::ODBC which have date and time
> > values. Grep ts
> > > would do it.)
> > > > Same results as with {d etc}. Either > > Can't rebind placeholder 1 > > or > > Invalid character value for cast specification > >
> > > Also, you can change the bind type to character by: > > > $sth->bind_param(n, undef, SQL_VARCHAR);
> > > > This works. > >
> > > Read the section in the DBD::ODBC POD regarding
> > odbc_default_bind_type,
> > > as that changed around .43. > > > > > > Jeff
> > > > I stil wonder why does it behave differently under 5.6.1 and > > 5.8. Never mind. > > > > Thanks, Jenda > >
> >
From: Jenda [...] Krynicky.cz
Download (untitled) / with headers
text/plain 598b
[JURL - Sat Feb 15 05:50:48 2003]: Show quoted text
> Just out of curiosity, were you using 0.28 under 5.6.1? > > By default, odbc_bind_type is set to SQL_VARCHAR in that version. > That would explain a difference between 5.6.1 and 5.8 -- I changed the > default bind type to autodetect, from forcing a character conversion > every time.
OH MY GOD I'M STUPID. Yes I was. I think I did install a newer version under 5.6.1 as well, but it seems I did not. When I looked there now, I did have 0.28. And once I've installed 1.04 it started reporting the very same problem. Sorry to waste your time. Thanks, Jenda
From: "Jeff Urlwin" <jurlwin [...] bellatlantic.net>
To: <bug-DBD-ODBC [...] rt.cpan.org>, <"AdminCc ofcpanTicket#2102:" [...] fsck.com>
Subject: RE: [cpan #2102] Passing dates to MS SQL via DBD::ODBC doesn't work under 5.8
Date: Mon, 17 Feb 2003 08:31:48 -0500
RT-Send-Cc:
Download (untitled) / with headers
text/plain 1.1k
Show quoted text
> > This message about DBD-ODBC was sent to you by guest <> via > rt.cpan.org > > Full context and any attached attachments can be found at: > <URL: https://rt.cpan.org/Ticket/Display.html?id=2102 > > > [JURL - Sat Feb 15 05:50:48 2003]:
> > Just out of curiosity, were you using 0.28 under 5.6.1? > > > > By default, odbc_bind_type is set to SQL_VARCHAR in that
> version. That
> > would explain a difference between 5.6.1 and 5.8 -- I changed the > > default bind type to autodetect, from forcing a character
> conversion
> > every time.
> > OH MY GOD I'M STUPID.
Actually, I've used enough of your modules and seen your posts to know that's not the case. I've been a happy user of Mail::Sender to e-mail myself log files and send out production reports to end users...without Mail::Sender, it's a lot more work. Show quoted text
> > Yes I was. I think I did install a newer version under 5.6.1 as well, > but it seems I did not. When I looked there now, I did have 0.28. And > once I've installed 1.04 it started reporting the very same problem. > > Sorry to waste your time. > > Thanks, Jenda >
Been there, done that. No problem. Now you are aware of the differences :) Jeff


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.