Re: Datetime overflow with DBI ODBC setting 19th century dates with placeholders

From: James Willmore (jwillmore_at_remove.adelphia.net)
Date: 05/14/04 

Date: Fri, 14 May 2004 11:18:25 -0400

On Fri, 14 May 2004 08:29:41 +0100, iain wrote:

>
> "James Willmore" <jwillmore@remove.adelphia.net> wrote in message
> news:pan.2004.05.14.04.44.19.763862@remove.adelphia.net...
>> > $sth->execute('1900-01-01'); # works OK
>> > $sth->execute('1799-12-31'); # gives error
>>
>> First ... try to insert the date using the command line utility for the
>> RDBMS. If it works, then it *should* work in the script. If it doesn't,
>> then the issue lies with the RDBMS.
>>
>> Next, you could use the 'debug' function in the DBI module to see what the
>> "conversation" is between the script and the database. That will shed
>> some light on why it's not working as expected.
>>
>> There are more ideas, but give these a try first :-)

>
> Thanks for the suggestion, but I've tried both those - $dbh->trace(5) shows
> the date is passed to SQL Server ODBC driver in the same way for both 1901
> and 1799.
> BTW - I meant to show 1899 not 1799 in my original post - neither work
> anyway.
> The command line utility (SQL Query) only allows you to use complete SQL DML
> statements, not placeholders with parameters (as far as I know). And the
> former works fine for 1899 with perl DBI anyway.
>
> It seems to be something to do with ODBC not binding the date parameter
> correctly when it would be held as a negative number on the database.
>
> I've also tried the SQL Server profiler on the server side, but haven't got
> it to show sufficient detail for
> this kind of update.

One more thing to try - you could tell the server what kind of data you're
using. After reading Brian's post and the issue he had, it occured to me
that you could try using the 'bind_param' function to set the type of data
being used. Not quite as easy as the sample you posted, but may fix the
issue.

For example (untested and off top of head):

use DBI qw(:sql_types);

$sth = $dbh->prepare(<<SQL);
insert into table (date, ...) values(?, ...)
SQL

$sth->bind_param(1, $date, SQL_DATE);
...

$sth->execute();

In this way, you're defining what the data type is for the database, not
letting it try and figure it out for itself (which may be what it's trying
to do).

Again, just something to try and may not work.

HTH 

-- 
Jim
Copyright notice: all code written by the author in this post is
 released under the GPL. http://www.gnu.org/licenses/gpl.txt 
for more information.
a fortune quote ...
 The 80's -- when you can't tell hairstyles from chemotherapy.

Relevant Pages