List:MySQL ODBC« Previous MessageNext Message »
From:Michael Widenius Date:September 14 1999 11:51am
Subject:ODBC and DATETIME
View as plain text  
>>>>> "Geocrawler" == Geocrawler com <archiver@stripped>
> writes:

Geocrawler> This message was sent from Geocrawler.com by "Maarten Boekhold"
> <maarten.boekhold@stripped>
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
Thread
ODBC and DATETIMEGeocrawler.com9 Sep
  • Re: ODBC and DATETIMEChristian Mack13 Sep
  • ODBC and DATETIMEMichael Widenius14 Sep