6 Replies Latest reply on Jul 13, 2009 1:04 AM by phpProSG

    Filemaker as ODBC data source, strange behavior

    phpProSG

      Title

      Filemaker as ODBC data source, strange behavior

      Your post

      I am connecting to Filemaker Server 9 through ODBC, trying to use FM as a data source.

       

      I have a table called "attorneyDatabase" with 6 fields that are relevant to me (I don't know how many fields total).  The

      entire table holds 775 records.

       

      When I select one or two fields (select "Client Number", "Name" from attorneyDatabase), I get the result in about 5 seconds

      with values filled in each field.

       

      Sample section of result set:
      Client Number = 14366
      Name = Michael Me***

       

      Client Number = 14367
      Name = Tanja Ma***

       

      Client Number = 14368
      Name = Andrew Ro***

       

      Client Number = 14369
      Name = Wendy Wa***

       

      However, when I start adding other fields (select "Company", "Client Number", "Name" from attorneyDatabase), the query takes around 3 and a half minutes to run, and returns results with values missing.

       

      Same sample section of result set:
      Company =
      Client Number = 0
      Name =

       

      Company =
      Client Number = 0
      Name =

       

      Company =
      Client Number = 14368
      Name = Andrew Ro***

       

      Company = Alst***
      Client Number = 14369
      Name = Wendy Wa***

       

      When I just select the Company (select "Company" from attorneyDatabase), I get full results again, no missing data.

       

      Same sample section of result set:
      Company = McDe***

      Company = Fole***

      Company = Staa***

      Company = Alst***

       

      In addition to the blank rows issue (perhaps related to it), the more fields that I try to select, the longer the query takes

      in giving back a response, so that when I try to select all 6 fields, the query can take around 15 minutes to respond, and

      then comes back with about 2/3 blank records, and the rest correct.

       

      The only thing I can think of is that perhaps something - odbc or filemaker - is timing out on the query and returning blank

      results after a certain time.  But I don't know of any such limit.  Also, I am surprised that a db of only 775 records is

      taking this long in general to return results.  I'm stumped.  Ideas, anyone?

       



        • 1. Re: Filemaker as ODBC data source, strange behavior
          kapitaen_1
            

          funny problem.

          Why do you set the fields in " ?

           

          try a 

          select Company, Client Number, Name from attorneyDatabase

          instead of 

          select "Company", "Client Number", "Name" from attorneyDatabase 

           

          AND you should

          rename field Client Number to ClientNumber (without blank) or Client_Number. Only characters and numbers as table or field specifier, no spezial characters (the only exception is underscore _) such as blank, $, %, & and many many others.

           

          Let me know if this helps. 

           

          greetings from germany

          Chris 

          • 2. Re: Filemaker as ODBC data source, strange behavior
            phpProSG
              

            Thanks for responding. 

             

            I need the quotes because of the multi-word field name.  I can't change the field name because the db is set up by my

            client and is the center of his operation ... I am just trying to access the data to display on the web.

            • 3. Re: Filemaker as ODBC data source, strange behavior
              philmodjunk
                 Note: One of the nice things about Filemaker, as compared to other DB apps, is that changing a field's name doesn't break the database in most cases. A few layout's may show the changed field name and you'd have to check for any Import records scripts that use Matching Names to align the fields, but that's about it.
              • 4. Re: Filemaker as ODBC data source, strange behavior
                phpProSG
                  

                I have determined that I can change the field names if necessary.

                 

                However, I just tested a query without the quotes and got the following result:

                 

                Warning: odbc_exec() [function.odbc-exec]: SQL error: [DataDirect][ODBC SequeLink driver][ODBC Socket][DataDirect][ODBC FileMaker driver][FileMaker]Parse Error in SQL, SQL state S1000 in SQLExecDirect in C:\web\test.php on line 9
                [DataDirect][ODBC SequeLink driver][ODBC Socket][DataDirect][ODBC FileMaker driver][FileMaker]Parse Error in SQL
                select Company, Name, Email, Position from attorneyDatabase

                 

                When I add quotes around the fields in the same query, it seems to run.  (It was taking so long that I stopped it in the middle, but it did appear to be running.)

                • 5. Re: Filemaker as ODBC data source, strange behavior
                  kapitaen_1
                    

                  just this minute i did a look into the jdbc odbc guide for filemaker, page 44, reserved words.

                   

                  position is a reserved word. Try your query once without position and let me know if it works.

                   

                  greetings from germany

                  Chris

                   

                   

                  http://www.filemaker.com/downloads/pdf/fm10_odbc_jdbc_guide_en.pdf

                   

                  • 6. Re: Filemaker as ODBC data source, strange behavior
                    phpProSG
                      

                    I'm still having trouble.

                     

                    The query

                    select Name from attorneyDatabase

                    returns 780 results, almost all filled in (a few scattered blanks throughout)

                     

                    The query

                    select Name, Email from attorneyDatabase

                    also returns 780 results, with the first one containing a name and a blank email, and the next 63 records with blank name and blank email!

                     

                    I can't figure out what the problem is.