8 Replies Latest reply on Nov 6, 2014 3:38 PM by ayescas

    Execute SQL function


      Hello All,


      So far I been able to set up the ODBC connector on FM server and now I’m starting the testing phase and learning the sql statements and have the following two basic questions:


      1.) Is this function capable of getting records and displaying them on layout, like when you perform a find in FileMaker ?


      2.) Is the intent of this function to only display a result in in a web-viewer, portal, and/or field ?



        • 1. Re: Execute SQL function

          Alejandro, let's clarify:



          • ExecuteSQL() is the function that produces TEXT result. Where it is displayed depends on what you want to do with it. Only the SELECT statement is available. no ODBC drivers needed.



          • Execute SQL is the script step used when FMP is making a query to an ODBC database and uses the INSERT, UPDATE or DELETE. the Import script step is use with ODBC database source to SELECT data.



          • if you use ESS (an ODBC external sql source), you can VIEW live data in FMP layouts.



          • if you use FMP as an ODBC source, other applications can make queries to it.





          Please make sure you read these documents:






          Can you define more of what you need to do, please?



          • 2. Re: Execute SQL function

            The ExecuteSQL function, will not return Records.  It will return a calculated result (typically a list of string data you set in the formula.), which can be loaded into a Virtual List approach, which can then be treated like records.  Depending on how you code the result, you could also display the result in a Web Viewer.

            1 of 1 people found this helpful
            • 3. Re: Execute SQL function

              The picture is getting clearer and thank you. 


              Thank you Beverly,


              In other words:


              If I use the ExecuteSQL in script maker it’s for the purpose of communicating with other NON-FM datasources.


              If I use it in a calculation field the result is ways be text and the ODBC driver will not be needed.


              In script maker under the “Specify SQL statement using”  you have 2 options:

              “Calculated SQL text” (calculation option) and the other is “SQL text”.  I’m assuming this is only for communication with other datasources, but what’s the difference ?


              BTW  I have your book on a developer's guide to XML, love it !!!

              • 4. Re: Execute SQL function

                Awesome, do you have any sample code on how to get the result in a virtual list and web viewer ?

                • 5. Re: Execute SQL function

                  A virtual list is really a bit more than code, it is generally set up with a Table which will have a "Global" "DATA" Field to hold the result from the ExecuteSQL formula, Which would typically be separated by carriage returns. The Table would in turn have an ID field, which should be set as a serialized number for each record. There would also be a calculating field "LINE" which would have a formula something like:


                  GetValue ( DATA ; ID )


                  The Virtual List table will need to have a preset number of records that would fit the needs of the result,  Could be anywhere from a few hundred to tens of thousands.  As each record will have a number for each value, the data pulled by the GetValue function will represent that line.


                  This is a very simplified explanation as many more complex things can be done.

                  1 of 1 people found this helpful
                  • 6. Re: Execute SQL function

                    Execute SQL script step (note the space in the name!) is for communicating (thru an ODBC Data Source) to non-fmp sql db. this requires a driver


                    ESS can use ODBC data source, but is limited to particular sql dbs, uses ODBC driver(s) but is live data

                    • 7. Re: Execute SQL function

                      Got it thank you

                      • 8. Re: Execute SQL function

                        Thank you can't wait to try it out....