1 2 Previous Next 15 Replies Latest reply on Jul 5, 2012 8:29 AM by greglane

    Issue with ExecuteSQL calculation

    jamest

      I'm wondering if I'm doing something wrong, I'm running the ExecuteSQL calculation on an external ODBC data source. I'm not using the script step, perhaps I should be. Upon running the following statement, FileMaker 12 A hangs indefinately. Access to the data source is not an issue as the layout shows the data with no issues.

       

      Is this mearly a case of the calculation not being able to be run on external data sources?

       

       

      ExecuteSQL(

      "SELECT *

      FROM External_STUDENTS

      WHERE STU_ID = ?";

      ""; ""; External_STUDENTS::STU_ID)

       

       

      Regards,

      James

        • 1. Re: Issue with ExecuteSQL calculation
          BowdenData

          jamest wrote:

           

          I'm running the ExecuteSQL calculation on an external ODBC data source. I'm not using the script step, perhaps I should be.

           

          James,

           

          Correct. The new ExecuteSQL function is for internal FM querying. The "Execute SQL" script step is the one to use to query other, external databases running on MySQL, MSSQL, etc., usin an ODBC connection.

           

          HTH.

           

          Doug

          1 of 1 people found this helpful
          • 2. Re: Issue with ExecuteSQL calculation
            jamest

            Ok, I do have table occurances of the external tables in the database, that was why I was supprised they did not work as I thought this had worked in the past. Thanks for your help Doug.

             

            FileMaker Inc. should look into the hanging issue though. If the calc function can't reference external tables, there should be more documentation regarding this.

            • 3. Re: Issue with ExecuteSQL calculation
              DrewTenenholz

              James --

               

              I've been told by at least two FMI service engineers that the new ExecuteSQL() should be able to work on data sources other than an FMPro database.  They said that as long as you have a TO on your relationship graph for the external file (which implies an item under File>Manage>External Data Sources, which further implies an entry in your ODBC manager setup to create the DSN for the data object), then you should be able to perform queries with this step.  I'm think they even demonstrated it to a combined MIT/Harvard/Boston Area Developers group a couple of weeks ago.

               

              Try a couple of simpler tests (even though your calculation looks very, very simple).  Maybe substitute a hard-coded student ID for a field value.  Also check that your DSN has a relatively short timeout.  Maybe specify your cell & row delimiters.  (I recall hearing that results from external data sources across platforms may not play well inside FileMaker.) Maybe contact FMI support?

               

              I haven't tested this myself, but my local engineers certainly have.

               

              -- Drew Tenenholz

              • 4. Re: Issue with ExecuteSQL calculation
                jamest

                Thanks very much for that Drew. I've tried many different options, I started off with the file served from FMSA, moved it to local, tried hard coding things (as I was doing much of the testing in the data viewer, though I originally noticed it when a script hung), separate files, only thing I haven't tried yet is reinstalling FMA. That might have to be my next step.

                 

                I've made the mistake of rushing off to get FMIs help before, I'll do some more testing before anything else, maybe I'll notice something along the way.

                 

                Thanks again for looking into this, in the end I may have to send the file off to FMI.

                 

                James

                • 5. Re: Issue with ExecuteSQL calculation
                  beverly

                  James, I wonder if your query is correct?

                  It appears that the EXTERNAL SQL SOURCE (table) is "External_STUDENTS" (the FROM clause). If so, why are you using the same table to supply the "?". Can you give more information on the tables (local and/or external) that you are using and where are the fields you are trying to match?

                   

                  The FileMaker 12 ODBC/JDBC Guide on ExecuteSQL (the function) does, indeed, say it works on FileMaker and on ESS sources.

                  And the Help says that the function: "Executes an SQL query statement for the specified table occurrence within a FileMaker Pro database."

                   

                  The 'table occurrence' (the name of the "table alias", if you will) on the Relationship Graph is the name that should be used in the function.

                   

                  Beverly

                   

                  ExecuteSQL(

                   

                      "SELECT *

                   

                          FROM External_STUDENTS

                   

                          WHERE STU_ID = ?";

                   

                      ""; ""; External_STUDENTS::STU_ID)

                   

                  • 6. Re: Issue with ExecuteSQL calculation
                    jamest

                    Hi Beverly,

                     

                    External_STUDENTS is the fictional name I gave the actual table occurance for this. It was a simple statement I was testing with to ensure there weren't issues with my SQL that were crashing the program. I was supplying the same table because I was using a layout with that table backend to select a student from and have it return the whole row. It was a test as the argument in the original attempt was a variable as part of a set field script step.

                     

                    The SQL tables hold student and subject data with about 200,000 rows at least together, 10,000 or so in one, many more in students. About 20 columns each. The field I'm matching in this case is a primary key for the student table. The local tables for the most part mimic the SQL and going to be used to create a copy of the SQL with additional calculations.

                     

                    Below is an exact copy of something I just tested with. This statement runs with no problems in MSSQL where the table names are changed to those in the SQL DB. In FM though the table occurances exist as written and FM hangs indefinately.

                     

                    ExecuteSQL (

                        "SELECT *

                        FROM ISIS_SPack SP

                        JOIN ISIS_SPackAvails SPA on SP.SPK_NO = SPA.SPK_NO

                        WHERE SPA.Avail_YR = '2012';

                    "-"; "¶")

                     

                    For both of these examples though, I'm only interested in the tables specified, there's nothing else in play. Much of the testing now I'm doing through the Data Viewer.

                     

                     

                    James

                    • 7. Re: Issue with ExecuteSQL calculation
                      beverly

                      How many columns in the tables? Have you tested with specific columns instead of "*"? There may be a limit.

                       

                      -- sent from my iPhone4 --

                      Beverly Voth

                      --

                      • 8. Re: Issue with ExecuteSQL calculation
                        jamest

                        I have tried testing with specific columns yes. Unfortunately it had the same effect. Below is one of the specific column tests I have performed. This should return a single name, 'Taylor'. Unfortunately, it still hangs the program.

                         

                        ExecuteSQL (

                            "SELECT STU_FAMILY_NM

                                FROM ISIS_Students

                                WHERE STU_ID = '295591'";

                            ","; "¶")

                         

                        I've been in contact with FMI, I'm awaiting to hear back from some of their Systems Engineers regarding the issue. Happy to keep exploring though.

                         

                        Thanks for your help Beverly, I understand you're one of the most experienced in FM and ESS.

                        • 9. Re: Issue with ExecuteSQL calculation
                          BowdenData

                          James,

                           

                          Sorry for the erroneous information in my earlier reply to you about the ExecuteSQL function vs. the Execute SQL script step. I learned something as well. That's one of the cool things about this forum, incorrect information will usually get corrected by someone pretty quick.

                           

                          Out of curiosity, have you tried your queries using a 3rd party SQL plugin? I know that this is not the answer to your issue, but it would be interesting to know the result. If you don't already have a plugin, there are a couple of them that are free.

                           

                          The SQL Runner plugin is SQL only and is available at:

                          http://dracoventions.com/products/2empowerFM/family/sql.php

                           

                          The BaseElements plugin has a SQL function (among many others), and is available at:

                          http://www.goya.com.au/baseelements/plugin

                           

                          HTH.

                           

                          Doug

                          • 10. Re: Issue with ExecuteSQL calculation
                            Peter Wagemans

                            Hello James,

                            The new SQL engine is picky about data types. Could it be that the datatype of STU_ID is not a string?

                            My 5¢...

                            • 11. Re: Issue with ExecuteSQL calculation
                              beverly

                              Yes! Peter has a gold-plated Ni...

                               

                              WHERE STU_ID = '295591'

                              does imply that it's a STRING rather than INTEGER.

                              What about testing without the single quotes? But you've probably testing directly in MS SQL, right James?

                               

                              Beverly

                              • 12. Re: Issue with ExecuteSQL calculation
                                beverly

                                "I've been in contact with FMI, I'm awaiting to hear back from some of their Systems Engineers regarding the issue. Happy to keep exploring though."

                                 

                                     Good to hear they are on the case!

                                 

                                "Thanks for your help Beverly, I understand you're one of the most experienced in FM and ESS."

                                 

                                     LOL. I just get to play in FileMaker, MS SQL & MySQL dbs nearly everyday. ;D

                                 

                                Beverly

                                • 13. Re: Issue with ExecuteSQL calculation
                                  jamest

                                  Yes I've tried both. In MSSQL Server Management Studio, it doesn't matter. In FM is I remove the quotes it doesn't crash FM but is doesn't work either, ie. shows '?'. I should say, the SQL server is feed built from the production environment. I'm saying this because I believe every field is typed as a string for the feed.

                                  • 14. Re: Issue with ExecuteSQL calculation
                                    jamest

                                    I haven't tried with a plugin, I will look into these today and let you know. For our environment, unless there is no other option it would be preferable not to use one. But I will try, thanks Doug.

                                    1 2 Previous Next