ODBC/SQL Import problem

[Tundraboy]

I have been attempting to import data from a set of .dbf files that are served out via ODBC. All is well with straight forward pulls but I have a problem if I want to use a date column in a WHERE statement. The following is created using the SQL Query builder during the import process.

SELECT `STUDCAL`.`IDSTUDENT`, `STUDCAL`.`SDAYSTATUS`, `STUDCAL`.`SDAYACTIVE`, `STUDCAL`.`DAYDATE`

FROM `STUDCAL`

WHERE `STUDCAL`.`DAYDATE` = 2007/09/28

AND `STUDCAL`.`SDAYACTIVE` = 1

The DAYDATE is a value which was taken from the Query builders pull down list, so FileMaker seems to reecognize the value at that point. When the query is run an empty set is returned with no errors. I know that there are matches (I did an import from one of the smaller datasets (18232 records) and found many matches.

Any idea as to why this statement did not work?

[Allison]

Hi there,

I use SQL in another environment where I would put quotes around the date - maybe this might work here too? Maybe give this a go? Also, I'm not familiar with seeing quotes around each of the table and field names though. I would normally have used just studcal.idstudent etc. Give the former a try first.

Cheers

a

[LaRetta]

Hmmm, for SQL Server, I wrap with double- quotes But I’m finding that each driver handles it differently. As for the date, there are two issues: date format and interpretation of ‘equals’ as applying to a number. You need to use a date function. Here is one I know for sure works:

To_date(‘2007/09/28’,’yyyy/mm/dd’)

If you don’t need to switch around the date format, I’ve heard that date() function works. However, I question why you are hard-coding the date within the SQL command. It would seem to make more sense to use a FileMaker calculation here so that date can pull dynamically, ie, change every day? If you want specific User control over that date, you can also let a User populate a global which is then referenced within the calc. If you wish to use a calculation, then will need to quote out the quotes (as in ") and then wrap the whole calculation in quotes.

LaRetta

[Tundraboy]

Allison,

Yes that is true of SQL queries but this is what is displayed in the FMP SQL Query builder when you use their tools to build the query. I would guess that FM adds the missing punctuation. I used the Query builder because when I used a regular SQL statement with punctuation et al the query failed.

Bob

[Tundraboy]

LaRetta,

It is a proof of concept test. The query is being built to test if it is possible to take only the necessary data from a .dbf file (dBASE IV format). The file can contain up to 100,000 records and I would rather not pull all of that in if possible. This is the reason for the hard coding of a date.

FileMaker seems to recognize that the field being used in the query is of type date and displays the date values in the query builder, so it should work in theory.

Maybe the ODBC driver is the problem. Currently the ODBC connection is made using the Microsoft dBase driver.

Does the date translation usually happen in the driver? Or is FileMaker doing some sort of translation on the fly?

Bob

[LaRetta]

I don't know those answers, Bob. FM itself sees dates as numbers unless wrapped with quotes to signify text. I only know that I ran into the identical problem you did with dates. And I tried everything, as you have. And it SHOULD have worked as expected but did not, even when wrapping with quotes. And I went onto SQL and Oracle forums. And they all gave me the two conversions to using Date() or to_date. I needed to_date because our FM dates were mm/dd/yyyy but the ESS table where the dates were queried were structured as yyyy-mm-dd so I had to format it specifically.

My question is ... did you try to_date() to even see if you could make it work? If there is ANY problem with interpretation between ESS and FM, then this should solve it. Sometimes it takes a process of elimination. Logic might dictate that, as indicated in the prior suggestion by Allison, you wrap it in quotes which forces it to text. But I'm not finding that kind of consistent logic. Even when I received suggestions from four 'gurus' of sql and oracle, they each gave a different suggestion on proper syntax. I found that a bit humourous and an indication that trial and error appears to be the best approach when linking different types of data through different types of structures and query engines.

Edited October 5, 2007 by Guest

[Tundraboy]

LaRetta,

I'm not using ESS and I'm using 8.5Adv with just a local DSN. Sorry, more info I forgot to give! That might be why I can't find any reference to the to_date() function. I am also unclear if this is to be used inside the SQL statement or elsewhere. This particular test was being done just as a straight import via ODBC not as a script.

Bob