I have a database and application in FileMaker Pro 9 that I'm trying to recreate using ColdFusion and Oracle, and I'm having some weird trouble exporting the raw data:
I have this one table (well, actually several tables in the source, named 'Jan', 'Feb', 'Mar', etc... but that's not the real WTF) with a string field--we'll call it "bananas"--that, for most records, happens to be null. When it's not null sometimes it's a short string of letters ("DS") and sometimes a number recorded as a string ("5").
So I exported these tables via ODBC and was really confused when "bananas" had a lot more nulls than it should have. Furthermore, the only non-null values were numbers... wait a minute... The ODBC interface claims the field is a number! I threw that out and then tried the "Export to CSV" command and got the same result: "bananas" was converted to a number with non-numeric strings munged into nulls, and then written to the output file as text.
Solution: I had to temporarily create a Layout (what Access or VB would call a Form) and then export the data from that Layout, ensuring that I enabled the "Apply current layout's data formatting to exported data" switch, which was disabled by default.
So, in conclusion, if you create a layout (and then export the layout), data types are determined by the underlying datatype in the source table. If you export via ODBC or text files, datatypes are determined by (as far as I can tell--remember I said most values were null) by reading some records and guessing! WTF?!