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

List:       mysql-odbc
Subject:    ODBC and DATETIME
From:       Michael Widenius <monty () monty ! pp ! sci ! fi>
Date:       1999-09-14 11:51:56
[Download RAW message or body]

> > > > > "Geocrawler" == Geocrawler com <archiver@db.geocrawler.com> writes:

Geocrawler> This message was sent from Geocrawler.com by "Maarten Boekhold" \
<maarten.boekhold@tibcofinance.com> Geocrawler> Be sure to reply to that address.

Geocrawler> Hi there,
                     
Geocrawler> I'm trying to migrate an application from using the MS Jet DB engine on \
.mdb files to directly using ODBC (the app uses DAO and I just want to tell it to use \
an ODBC data source instead of a file).  
Geocrawler> This gave me some problems, which I've solved for most part (for example \
the app opened tables in dbOpenTable mode, which I had to change to dbOpenDynaset). \
I'm left now with the following:  
Geocrawler> I have a table that has a DATETIME column. I want to do a select like \
'SELECT...FROM table WHERE ... and BETWEEN t1 AND t2' where t1 and t2 are some \
representation of a datetime. If I specify t1/t2 as '1999-09-09 11:00:00' I get an \
error back from (I think) the ODBC driver that there's a data type mismatch. The \
application originally used float numbers to specify the Geocrawler> datetime, but \
that apparently only works with MS products. I also tried other representations of a \
date/time, to no avail. I tried changing the column definition to timestamp, but \
MySQL defines this differently from the SQL92 standard (Date/Darwen, 3rd edition, \
page 252).  
Geocrawler> Does anybody have any tips on how to get this query to work?
                     
Geocrawler> Maybe it was not obvious from the preceding story, but if I execute the \
query directly in mysql it works, so the problem has to be somewhere in the \
communication between the app and mysql, that's why I figured it was the ODBC driver. \
 Hi!

Everything depends on how you try to generate the query?  In some
context checking of the argument types for placeholders '?' doesn't
work properly;  MyODBC always returns 'STRING' for all parameters
as MySQL can convert a string to any type.

What type is your 't1', 't2' variables?  Try changing these to string
instead of datetime. 

In the worst case you can always try to generate a MyODBC trace and
take a look at this to get a clue of what is happening..

Regards,
Monty

---------------------------------------------------------------------
Please check "http://www.mysql.com/Manual_chapter/manual_toc.html" before
posting. To request this thread, e-mail myodbc-thread691@lists.mysql.com

To unsubscribe, send a message to the address shown in the
List-Unsubscribe header of this message. If you cannot see it,
e-mail myodbc-unsubscribe@lists.mysql.com instead.


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