1 2 3 Previous Next 36 Replies Latest reply on Jul 31, 2013 3:14 PM by RogerWirt

    FileMaker to SAS via ODBC?

    RogerWirt

      Summary

      FileMaker to SAS via ODBC?

      Product

      FileMaker Pro

      Version

      11 (Advanced)

      Operating system version

      Win XP SP3

      Description of the issue

      With a confirmed Windows ODBC DSN and FileMaker internal privileges & permissions set, clients on the same PC can 'see' header information for FileMaker table occurrences but can't import data.  One exception occurred when Excel 2007 successfully imported a flat file of 2 columns and 3 rows;  but for a complex multi-table FileMaker system, no data could be obtained over an identical connection with either Excel, SAS 9.2, or SPSS [PASW] 18.  SAS and SPSS displayed table information about the FileMaker system, but attempts to display data from any given table with SAS yielded message that the table did not exist;  SPSS displayed a blank table with some header information that appeared to correspond to FileMaker variable list. 

      In this office, the same methods have routinely worked to link SPSS and SAS with such database systems as Oracle, SQL Server, and Sybase.

      Steps to reproduce the problem

      Open a database in FileMaker
      Create Windows DSN
      Open client (e.g. SAS)
      Instruct client to open FileMaker database

      Expected result

      Obtain access to data

      Actual result

      Client software reports a successful connection, displays FileMaker table-occurrence names, but does not provide access to data.

      Exact text of any error message(s) that appear

      SPSS: none.  (displays an empty table, with header info clearly obtained from within the FileMaker database)

      SAS: "Libref was successfully assigned" (i.e. SAS can "see" the FileMaker database, recognizes a working ODBC connection), and FileMaker table occurrences are recognized (displayed with a series of little icons in SAS Explorer) .. but no data can be extracted.  Sample error messages:
        1. SAS Explorer: "The table ... either does not exist or cannot be displayed."
        2. SAS SQL: "File ... does not exist."

      Configuration information

      FM 11.0.2.0, FM ODBC driver 11.0.61
      SAS 9.2 M0 with associated (SAS 9.2) ODBC driver
      SPSS ('PASW') 18.0.0
      XP Pro SP3

      FileMaker's internal configuration settings were confirmed by an expert FileMaker developer who replicated the above steps with Excel and SPSS.  Essentially similar procedures with SAS on the same PC work fine to connect to a remote SQL Server database.

      Workaround

      Manual solution (create a series of exports for each table, save separately, write programs to read & relate, check results, write programs to link tables).

        • 1. Re: FileMaker to SAS via ODBC?
          TSGal

          Roger Wirt:

          Thank you for your post.

          Since you were able to access the FileMaker data from Excel, the ODBC driver was set up properly.

          What SQL commands were sent from Excel that do not work from SPSS or SAS?  Since you are also trying to access related tables, what commands are you using (inner join?  outer join?) to extract the FileMaker data?

          TSGal
          FileMaker, Inc.

          • 2. Re: FileMaker to SAS via ODBC?
            RogerWirt

            Thanks for your note.   Since initial tests mentioned above, we have obtained a more recent FileMaker ODBC driver. Installing it didn't change anything; we get the same results with driver # 11.2.70

            With FileMaker open, SPSS makes some sort of connection in response to the following menu-driven commands:  Open Database > New Query > Database Wizard > ODBC Login screen.  Once the ODBC connection is established, SPSS's 'wizard' lists all the available FileMaker tables;  dlicking an icon for any given table brings up a list of fields, and clicking one or more fields causes the selected fields to be included in a query.   Given dataset filename.FM7 with a Table1 having variables named var1, var2, var3, etc.:     If var1, var2, and var3 are selected from Table1, the SPSS 'wizard' generates the following SQL code (embedded in an SPSS command to create a new data file named DataSet1):

               GET DATA   /TYPE=ODBC  /CONNECT= 'DSN=filename; UID=username; PWD= xxxxxxx  /SQL='SELECT var1, var2, var3 from "Table1"'  /ASSUMEDWIDTH=255.      CACHE.   EXECUTE.   DATASET NAME DataSet1  WINDOW=FRONT.

            SPSS then goes through the motions of opening the file, displays proper column headings (e.g. var1, var2, var3), but does not populate the working data file (except for some miscellaneous entries in the very first row which may or may not represent something meaningful; in today's test the first field displayed ".0", the others were blank). 

            Is some FileMaker-specific connection string necessary to get FileMaker to forward data?  I haven't found relevant discussion of that point.  Without clarification of that detail, there didn't seem to be any point in trial-and-error programming with SAS;  the SAS 'Explorer' seems to indicate that SAS isn't getting any more information from FileMaker than SPSS.  Of course, if some FileMaker-specific connection string would make a difference, additional testing would be of interest.

            I don't know what SQL commands (if any) may have been sent by Excel when its menu  opened a 'new data source' with 'Microsoft Query'.   Would there be a way to capture such information?  [Excel 2003 help files suggest that the protocol has changed among versions; apparently the SQL_REQUEST command in Excel 2002 used to allow sending an SQL connection string, but they dropped that command for Excel 2003, whose help screens elicit discussions of 'Microsoft Query'.  We are using Excel 2007 on the system in question; more recent developer references e.g.  http://msdn.microsoft.com/en-us/library/bb257085(office.12).aspx  and http://support.microsoft.com/kb/306125  apparently recommend using ADO along with a DBMS-specific connection string.]   Again, using a default, menu-driven connection, Excel wouldn't deliver any data from our multi-table FileMaker file, although it can 'see' the data and display a list of tables.  Since installing the new (11.2.70) FM ODBC driver, Excel 2007 on Windows XP now replaces the hourglass with the following error message:  FQL0001/(1:8): There is an error in the syntax of the query."   Perhaps this is a clue of some sort, since Excel successfully read our simpler file as we expected in response to an identical series of mouseclicks.

            • 3. Re: FileMaker to SAS via ODBC?
              TSGal

              Roger Wirt:

              Thank you for the update.  It sounds like the DSN is now working properly.

              The driver between FileMaker 11 and previous versions is quite different.  If something worked with the previous driver, it may not work with the current driver.  Let's try and narrow it down further...

              I'm assuming var1, var2 and var3 are Text fields.  If so, then be sure to set the validation Maximum number of characters from 1 to 255.  To do this, go into Manage -> Database..., select var1, click Options, select the Validation tab, under "Require" check the box for "Maximum number of characters", and change the value from 1 to 255.  This would then ensure you get more than one character.

              Try this again from SPSS and see what is the result.

              TSGal
              FileMaker, Inc.

              • 4. Re: FileMaker to SAS via ODBC?
                RogerWirt

                Thanks for your comment.   I followed the instructions for the test database, with two columns and three rows.  Each column contained single-character text information (despite SPSS's default assignment of 255 characters which would accommodate arbitrarily wider text fields up to the limit of 255).

                Changing FileMaker's "maximum number of characters"  for the file in question, per the instructions had no effect on SPSS's ability to read the data.  I tried it with the FileMaker setting at the default value of 1 (which correctly described the data in question); also with the recommended setting of 255, and an intermediate setting of 5.  Behavior in each case was the same as before: SPSS recognized the column headings but did not display any data.  Input was the same FileMaker test file that Excel had originally been able to read, correctly, through the original DSN connection 3 weeks ago (with the FileMaker "Maximum number of characters" setting at 1).   The SQL instructions were generated by the SPSS 'wizard'  that has worked fine for years with various other databases.   

                So:  the Excel tests yielded identical results with two different versions of the FileMaker ODBC drivers (11.0.61 and 11.2.70), but SPSS wouldn't read the same FileMaker data over the same DSN connection with either of those drivers.

                • 5. Re: FileMaker to SAS via ODBC?
                  TSGal

                  Roger Wirt:

                  Thank you for testing this out.

                  We did change our ODBC driver in FileMaker Pro 11, so there is obviously something that is causing SPSS to fail and "that has worked fine for years with various other databases."

                  The maximum number of characters setting is only necessary if you want to update the FileMaker data from SPSS.  Although your original post was only referring to reading the data, I wanted to cover all possibilities in case you were updating the file before reading the data.

                  I'm a little unclear when you say "the Excel tests yielded identical results with two different versions..".  Does that mean the identical results of SPSS with no data?  Or, does this work properly?

                  TSGal
                  FileMaker, Inc.

                  • 6. Re: FileMaker to SAS via ODBC?
                    RogerWirt

                    Thanks.  The initial post states the problem, which remains unsolved.

                    The reference to the "two different versions of the FileMaker ODBC drivers (11.0.61 and 11.2.70)" was simply intended to mention that we got the same results with both.  Neither SPSS nor SAS could fetch relational data from FileMaker with either FM ODBC driver, although both SPSS and SAS are able to obtain lists of the FileMaker data tables via the ODBC connection. 

                    Here's a bit more detail on the same thing.  If we export tables from FileMaker one-at-a-time into Excel 2007 format, SPSS seems to have no trouble accessing the resulting data via ODBC.   The SPSS 'wizard' produces the following syntax for the [flat, single-table] Excel file:

                         GET DATA  /TYPE=ODBC  /CONNECT='DSN=XLFile;DBQ=C:\pathname\filename.xlsx; DriverId=1046;FIL=excel 12.0;MaxBufferSize=2048;pageTimeout=5;' /SQL='SELECT Var1, Var2, Var3,  ... FROM `filename`'  /ASSUMEDSTRWIDTH=255.

                    If we try to use SPSS to obtain the same information via ODBC directly from the FileMaker database, once the FileMaker login step is completed over the ODBC connection SPSS can 'see' the FileMaker tables and its 'wizard' can display the fields which comprise each table.   But selecting just a single FileMaker table (whose data SPSS successfully read via ODBC after those data had been exported to Excel), produced a mostly empty SPSS data file with correct column headings: a sprinkling of seemingly meaningless characters in row 1, and nothing populating any other rows.  That operation produced the following SPSS syntax:

                         GET DATA  /TYPE=ODBC  /CONNECT='DSN=FMFile;UID=username;PWD=password;'  /SQL='SELECT Var1, Var2, Var3,  ... FROM 'tablename`'  /ASSUMEDSTRWIDTH=255.

                    I would assume that the differences in syntax have to do with SPSS's interpretation of what ODBC tells it about FileMaker's requirements. 

                    Please let me know if any other information would be useful.   If there is some specific connection string that FileMaker needs to receive, it would be helpful to know exactly what that should be.

                    • 7. Re: FileMaker to SAS via ODBC?
                      TSGal

                      Roger Wirt:

                      Thanks for clearing up my confusion with the Excel portion.  I just wanted to make sure something wasn't overlooked.

                      Since I don't have access to SPSS, I have sent all the information to our Development and Software Quality Assurance (Testing) departments for review.  When I receive any kind of information/feedback, I will let you know.

                      TSGal
                      FileMaker, Inc.

                      • 8. Re: FileMaker to SAS via ODBC?
                        RogerWirt

                        thanks for your help

                        • 9. Re: FileMaker to SAS via ODBC?
                          RogerWirt

                          Incidentally, subsequent to various attempts to use FileMaker's ODBC capability, the Windows XP system has sometimes balked at shutting down correctly.  When it occurs, this intermittent problem seems to be associated with persistence of the FileMaker ODBC 'listener' process, despite one's having logged out of a FileMaker database and closing the FileMaker program.  If Windows 'task manager' is used to shut down the fmxdbc_listner.exe program, Windows will then shut down normally. 

                          I am mentioning this here on the chance that someone reviewing ODBC functioning (in connection with above issues) might want to know about it.

                          Thanks again for looking into this.

                           

                          • 10. Re: FileMaker to SAS via ODBC?
                            TSGal

                            Roger Wirt:

                            Thank you for this information.

                            I have also forwarded this information to our Development and Testing departments for review.

                            Since this problem occurs intermittently, it makes it more difficult to find the cause.  When this did fail, did you remember what you did prior to this happening?  I'm looking for any clues or commonality that may make it easier for our Testers to reproduce the problem.

                            TSGal
                            FileMaker, Inc.

                            • 11. Re: FileMaker to SAS via ODBC?
                              RogerWirt

                              My impression is that the Windows shut-down problem typically occurs when the FileMaker ODBC connection has been queried.  This is consistent with the observation that terminating the fmxdbc_listner.exe process seems to allow Windows to complete a normal shutdown. 

                              If the original problem can be replicated with one or more ODBC client applications (e.g. SPSS, SAS, etc),  a pertinent test of the latter question would be to try to shut down Windows subsequent to an ODBC query to FileMaker.   A further test could explore whether different outcomes resulted from successful vs. unsuccessful ODBC queries.

                              • 12. Re: FileMaker to SAS via ODBC?
                                TSGal

                                Roger Wirt:

                                Thank you for the additional information.  Your last comments have also been forwarded to the appropriate people.

                                Testing further commented that it seems the listener is still processing some SQL even when FileMaker Pro is closed.  Do you know if the listener ever terminates on its own?

                                TSGal
                                FileMaker, Inc.

                                • 13. Re: FileMaker to SAS via ODBC?
                                  RogerWirt

                                  Thanks.   Inspection has shown that the 'listener' often does terminate on its own. My impression is that it has failed to terminate when we've tried to make use of the ODBC connection, particularly when that effort failed to extract data (as described above).  On the other hand, if I open FileMaker and then simply browse around various screens and tables (i.e. without having any external apps query ODBC), the listener seems to terminate reliably if I (a) log out of a menu system which was created by an expert developer, (b) close all the windows one at a time, (c) pause briefly, and (d) close the FileMaker application.

                                  For comparision, the problematic situation in which the listener keeps on listening after FM has been closed has typically involved (a) opening FileMaker, (b) opening a complex data file without logging into any menu systems, (c) using an external program to query FileMaker via ODBC, (d) closing the FileMaker data file, (e) pausing briefly, and (f) closing FileMaker.   To find out what's going on with the listener program, one has to monitor the Windows Task Manager;  to find out whether it's going to interfere with shutdown, one must initiate a shutdown.  If and when the computer shuts down normally, there is no way to retroactively check on what it was doing just before it shut down.

                                  When paying attention to the above issues (perhaps 20-30 trials) I have not so far noticed an instance of the Windows shutdown problem subsequent to a confirmed closing of the listener program.  Obviously, these are subjective impressions; resources don't permit carrying out a systematic empirical program testing all the possible combinations of data files, client software, sequences of actions, etc.

                                  • 14. Re: FileMaker to SAS via ODBC?
                                    TSGal

                                    Roger Wirt:

                                    Thank you for the information.

                                    Our Testers would like to know the exact steps you take when you test with SPSS/SAS.  Also, if you have FileMaker Pro 10 available to you, please try it the same procedure.

                                    While going through the steps of attempting to import with SPSS/SAS, keep the task manager open and watch the xdbc listener file.  Close FileMaker Pro as soon as you've completed the test, and see if the listener file hangs out.

                                    TSGal
                                    FileMaker, Inc.

                                    1 2 3 Previous Next