4 Replies Latest reply on Jun 20, 2014 7:48 AM by LSNOVER

    Execute SQL using Filemaker Server

    richard@itech

      Hi

       

      I have setup a Filemaker Development Server. In the same computer the server is loaded onto, I have setup a System DSN for an SQL database, and I can add tables from this database onto the relationship tab of Manage Database. My issue is that the search I am performing is a bit slow, so I figured I would delve into the Execute SQL script step as I read that this can be quicker.

       

      My issue is that when I try and specify the ODBC Data Source the 'Select ODBC Data Source' window has no entries. If I go to File>Manage>External Data Sources I can see the DSN from the server ok. I am I missing something obvious here, or am I unable to use the DSN set in external data sources in the execute SQL scripts?

        • 1. Re: Execute SQL using Filemaker Server
          wimdecorte

          I figured I would delve into the Execute SQL script step as I read that this can be quicker.

           

          In what respect?  What did you read?

          The Execute SQL script step can only be used to insert and update data in an ODBC source.  Is that what you are trying to do?

          (Want to make sure your expectations are correct).

           

           

          In the same computer the server is loaded onto, I have setup a System DSN for an SQL database

          [...]

          My issue is that when I try and specify the ODBC Data Source the 'Select ODBC Data Source' window has no entries. If I go to File>Manage>External Data Sources I can see the DSN from the server ok.

           

          I'm assuming you are on Windows.

          FMS uses 64-bit DSNs for the ESS feature (to make the live connection and put tables on the graph), but FMP uses 32-bit DSNs for the "Execute SQL" and "Import from ODBC source" script steps.  That's why you do not see any DSNs when you work with those script steps.

          Find the 32-bit ODBC control panel and add a system DSN there too.

           

          Chapter 9 of the FTS training book describes this well: http://www.filemaker.com/support/training/fts.html

          • 2. Re: Execute SQL using Filemaker Server
            richard@itech

            Thanks, I had obviously got the wrong end of the stick. I am not trying to write to the SQL DB, in fact I cannot as I have read only access to it.

             

            I am searching through approximately 27,000 records using a number field (Patient ID). Normally I would index this field, however this does not seem possible with an SQL table, which is why I was looking into using the script step.

             

            I will live with the slight delay for now, it's not a deal breaker at the moment.

             

            Thanks for the prompt response.

            • 3. Re: Execute SQL using Filemaker Server
              Mike_Mitchell

              You can't index the SQL table from the FileMaker side, but the SQL administrator can create an index on his side.

               

              Don't know if that helps or not.

               

              Mike

              • 4. Re: Execute SQL using Filemaker Server
                LSNOVER

                27000 records in a SQL DB is barely worth indexing.  It should be nearly instant with or without.

                 

                If you have proper privs on the table, you CAN issue a command through Filemaker to Index fields in most SQL DBs. 

                 

                 

                 

                The Execute SQL Script Step can be used for retrieving data as well, in a limited way.  You can use the Error handling mechanism to return limited values.  

                 

                You can also call Stored Procedures which in turn can populate tables that you can then make available through the ESS feature, assuming the DB is one of the supported SQL databases.