[prev in list] [next in list] [prev in thread] [next in thread] 

List:       dbi-dev
Subject:    Re: DBD::ODBC, Access, and WHERE Date = ?
From:       Jaime Metcher <J.Metcher () spmed3 ! herston ! uq ! edu ! au>
Date:       1998-11-27 0:55:13
[Download RAW message or body]

    *** From dbi-users -- To unsubscribe, see the end of this message. ***

This works for me:

  use DBI qw(:sql_types);  # <--- IMPORTANT!!

  my $dbh = DBI->connect ("DBI:ODBC:GHSPDB");

  $sth = $dbh->prepare ("select id from [subject enrolments] where [date] ?");
  $sth->bind_param (1, "{d '1997-01-01'}", SQL_DATE);

  $sth->execute() || die "Execute failed ", $sth->errstr;



The date literal format is from the Microsoft ODBC SDK documenation, but I
presume it's documented in the SQL standard.

BTW, this took me about two hours to work out.  How about we add some more
examples to the docs?  (I'll graciously donate this one ;->)

--
Jaime Metcher

At 01:45  26/11/98 -0500, Dave Paris wrote:
>    *** From dbi-users -- To unsubscribe, see the end of this message. ***
>
>When attempting to do a SELECT statement via DBD::ODBC to an Access 7
database
>as follows:
>
>SQL:
>SELECT field1, field2, field3
>   FROM table
>      WHERE field0 = ?
>      AND date_field1 = ?
>      AND date_field2 = ?
>
>DBD:
>$dbh->prepare(q{SELECT field1, field2, field3
>	FROM table
>	WHERE field0 = ?
>	AND date_field1 = ?
>	AND date_field2 = ?}) or die $dbh->errstr;
>
>The prepare works fine, but the execute gags with an inconclusive error
>message:  "data type mismatch"  (see trace at the end of this mail)
>
>I've tried dates in the following forms:
>CCYY-MM-DD HH24:MI:SS	  (std. SQL)
>#MM/DD/YY HH:MM:SS [AP]M#  (Access SQL with leading zeros)
>#M/D/YY HH:MM:SS [AP]M#	  (Access SQL w/o leading zeros)
>
>.. to no avail.
>
>Any and all suggestions are welcome.  I've lost more hair over this than I
care
>to admit.
>
>Trace snippet:
>    -> prepare for DBD::ODBC::db (DBI::db=HASH(0xd2c860)~0xd2c824 'SELECT
Rate
>FROM Seasons WHERE PropID = ? and StartDate = ? and EndDate = ?')
>    dbd_preparse scanned 3 distinct placeholders
>    dbd_st_prepare'd sql f18882184
>	SELECT Rate FROM Seasons WHERE PropID = ? and StartDate = ? and EndDate = ?
>    <- prepare= DBI::st=HASH(0xd1cf28) at
>c:\perl\site\5.00502\lib/ResNet/Rates.pm line 173.
>
>bind 1 <== '0911764416208234201206000118' (attribs: )
>bind 1 <== '0911764416208234201206000118' (size 28/29/0, ptype 4, otype 1)
>    bind 1: CTy=1, STy=VARCHAR, CD(, Sc(, VM(.
>bind 2 <== '#6/7/98#' (attribs: )
>bind 2 <== '#6/7/98#' (size 8/9/0, ptype 4, otype 1)
>    bind 2: CTy=1, STy=VARCHAR, CD=8, Sc=8, VM=8.
>bind 3 <== '#8/2/98 11:59:59 PM#' (attribs: )
>bind 3 <== '#8/2/98 11:59:59 PM#' (size 20/21/0, ptype 4, otype 1)
>    bind 3: CTy=1, STy=VARCHAR, CD , Sc , VM .
>    dbd_st_execute (for sql f18882184 after)...
>    ERROR EVENT -1 '[Microsoft][ODBC Microsoft Access 97 Driver] Data type
>mismatch in criteria expression. (SQL-22005)(DBD: st_execute/SQLExecute
>err=-1)' (Handlers: undef)
>st_execute/SQLExecute error -1 recorded: [Microsoft][ODBC Microsoft Access
97
>Driver] Data type mismatch in criteria expression. (SQL-22005)(DBD:
>st_execute/SQLExecute err=-1)
>    <- execute= undef at c:\perl\site\5.00502\lib/ResNet/Rates.pm line 195.
>    !! ERROR: -1 '[Microsoft][ODBC Microsoft Access 97 Driver] Data type
>mismatch in criteria expression. (SQL-22005)(DBD: st_execute/SQLExecute
>err=-1)'
>
>
>TIA.,
>dsp
>
>dparis@w3works.com  -+-<|>-+-  DParis@GestaltTechnology.com
>#include <disclaimer.h>
>The two most oft overlooked motor vehicle laws:  Inertia and Tonnage
>
>
>
>
>---------------------------------------------------------------------------
---
>To unsubscribe from this list, please visit http://www.fugue.com/dbi
>If you are without web access, or if you are having trouble with the web
page,
>please send mail to dbi-request@fugue.com.   Please try to use the web
>page first - it will take a long time for your request to be processed by
hand.
>---------------------------------------------------------------------------
---
>


------------------------------------------------------------------------------
To unsubscribe from this list, please visit http://www.fugue.com/dbi
If you are without web access, or if you are having trouble with the web page,
please send mail to dbi-request@fugue.com.   Please try to use the web
page first - it will take a long time for your request to be processed by hand.
------------------------------------------------------------------------------

[prev in list] [next in list] [prev in thread] [next in thread]