Re: Datetime overflow with DBI ODBC setting 19th century dates with placeholders
From: iain (irhm_at_clara.net)
Date: 05/17/04
- Next message: gomerpyl3: "Using Perl in Windows / Dreamweaver"
- Previous message: Herr Hardy: "Re: simple problem w/ opening files"
- In reply to: James Willmore: "Re: Datetime overflow with DBI ODBC setting 19th century dates with placeholders"
- Next in thread: Brian McCauley: "Re: Datetime overflow with DBI ODBC setting 19th century dates with placeholders"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Date: Mon, 17 May 2004 09:29:17 +0100
"James Willmore" <jwillmore@remove.adelphia.net> wrote in message
news:pan.2004.05.14.15.18.23.72592@remove.adelphia.net...
> 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.
>
>
Yes, I'd tried that as well!
Having scoured the DBI and DBD/ODBC docs, I am fairly convinced that the
odbc driver queries the server for the data type of the columns being
updated.
My best guess is that some of the code which deals with the parameters to
pre-prepared statements is treating the date as an unsigned integer, whereas
it needs to be signed (negative) for dates before 1900. If I had time I'd
re-code in VB, and then complain to either Microsoft or DBD::ODBC author.
But a more practical approach is to do a workaround which checks for old
dates (there are a small proportion) and uses a single unprepared execute.
Iain
- Next message: gomerpyl3: "Using Perl in Windows / Dreamweaver"
- Previous message: Herr Hardy: "Re: simple problem w/ opening files"
- In reply to: James Willmore: "Re: Datetime overflow with DBI ODBC setting 19th century dates with placeholders"
- Next in thread: Brian McCauley: "Re: Datetime overflow with DBI ODBC setting 19th century dates with placeholders"
- Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]
Relevant Pages
|
