FileMaker Pro exports a string field as a number



  • 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?!
     



  • @Brendan Kidwell said:

    I have a database and application in FileMaker Pro 9 that I'm trying to recreate using ColdFusion and Oracle ...

    Stop right there.  You've got enough WTF material already. 

    @Brendan Kidwell said:


    I have this one table (well, actually several tables in the source, named 'Jan', 'Feb', 'Mar', etc... but that's not the real WTF) ...

    What did I just say?



  • I think you are 100% correct about the guessing. SAS uses a very similar approach and you literally set an option called GUESSINGROWS when using the lazy option to import a file.

    Based on my experience with Filemaker, I would say the WTF in your case is that it uses the guessing approach (which can be a legitimate solution to some problems) but does not provide the user with a method to configure or disable the option.

     



  • @djork said:

    @Brendan Kidwell said:

    I have a database and application in FileMaker Pro 9 that I'm trying to recreate using ColdFusion and Oracle ...

    Stop right there.  You've got enough WTF material already. 

    Let's just say I didn't pick the technology.



  • @antonrojo said:

    I think you are 100% correct about the guessing. SAS uses a very similar approach and you literally set an option called GUESSINGROWS when using the lazy option to import a file.

     

    Guessing is okay on import.  It's not your native file type, and the file may not have inherent type information.  On export, however, you ought to know better.  



  • I read that too quickly--that approach is indefensible. From a guide on porting from Filemaker files: "ODBC and SQL have come relatively late to FileMaker". It sounds like they haven't gotten around to creating a decent ODBC driver.

     Apparently, it takes some extra ODBC programming, at least according to MySQL's painful porting process:

    Perl DBD::ODBC module reads data from FileMaker

    Lots of ODBC junk here<lots of="" odbc="" junk="">

    $mysql_sth->bind_param( 1,$rowdata[0],SQL_INTEGER); # asset_id - integer column

    $mysql_sth->bind_param( 2,$rowdata[1],SQL_VARCHAR); # model - varchar column</lots>




  • I used to work for an ODBC driver vendor. The stock Filemaker ODBC has some interesting eccentricities.  Example,  if you don't create a layout,  it reports each string as the maximum size it could be,  and the driver thoughtfully pads that out with spaces.  Makes for pretty big recordsets in memory.  It stores everything as a string,  and converts to other datatypes on the fly,  it seems.You can't trust what the driver thinks is the datatype unless you have that layout.

     Some apps will look at the data and make a guess.  Sometimes that works.
     



  • I ran into the same sort of WTFery with Excel to MS SQL via DTS earlier this year.  Google "IMEX=1" for the gory details.

     



  • @antonrojo said:

     Apparently, it takes some extra ODBC programming, at least according to MySQL's painful porting process:

    Sorry, but the author of the porting scripts may be a FileMaker expert (and the article may be a good step by step description of the process) but you can easily see perl -- and thus DBI -- is not his native language. The scripts can be much simpler, at least on the perl and MySQL-sides. For example the whole
    $mysql_sth->bind_param()-block can be replaced by just passing @rowdata as an argument to
    $mysql_sth->execute(), because MySQL (like Oracle) accepts every data item in SQL statements as a string, converting it when parsing the string, of course.



  • @Brendan Kidwell said:

    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.

     

    I think from just the first two sentences that you have a whole lot more wtf's in store for you before the project is out, good luck



  • I'm fairly sure filemaker pro was the application my old high-school had their student progress report database written in.

    There were 3 real users. "Student" "Staff" and "Admin". Guess how I know that?

    Students were restricted to only viewing/editing their own record comments through the use of a "login page" which was actually a disguised DB search. Pressing login without anything entered would trigger a script complaining about it, but you could disable find mode and access anyone's records.

    Thanks to guessing the Staff account password (shock horror, it was "staff") we slightly modified the teacher side of some peoples' records...

    Not ours though, that would have been too obvious. I was devious enough at that age to actually improve someone elses' record :P. Unfortunately, I don't think they noticed.

    They stopped using the system and went back to paper pretty quickly.



  • I remember running into a bug like this in MySQL (this was version 4.0 here, and it definately got fixed), where if you do a union join select, the first value will determine the type and length of the whole column. So a query like...

    SELECT 'foo' UNION SELECT 'delicious'

    would return

    foo
    del

    Annoying WTF to work around. 


Log in to reply