1 2 3 Previous Next 33 Replies Latest reply on Nov 27, 2015 9:12 AM by beverly

    Execute SQL ( )

    deninger

      I have thoroughly explored the ExecuteSQL() script step and have put it to a lot of use.

       

      NOW I am trying to get my head around the potential uses of the EXECUTE SQL () step. In looking thru the script steps reference, I can see how it can used to add or delete data directly on the SQL source. I imagine that I could run stored procedures as well...

       

      What would make things more interesting, and what I am not seeing (and am hopefully missing) is how to return values from the executed SQL call without having to round-trip the whole data batch into FileMaker Pro. Is it possible to use Execute SQL ( ) on SQL Server and then grab (save to a variable) the return a value(s) for further processing?


        • 1. Re: Execute SQL ( )
          greglane

          No, you can't return a value from a SQL query using the Execute SQL script step. If your query can be expressed as a SELECT, consider using the Import Records (from ODBC data) script step instead.

          • 2. Re: Execute SQL ( )
            monkeybreadsoftware

            Well, you could use a plugin.

            e.g. with MBS Plugin, you can query selects to the FileMaker database:

            http://www.mbsplugins.eu/FMExecuteSQL.shtml

             

            or use plugin functions to connect to a SQL Server directly:

            http://www.monkeybreadsoftware.de/filemaker/guide/sql.shtml

            • 3. Re: Execute SQL ( )
              beverly

              First, deninger,  lets clarify what you are trying to do.

               

              1. Execute SQL [] - Script step is for INSERT, UPDATE & DELETE when connected to an ODBC source through a driver and DSN set up. You communicate with the External SQL using SQL calls to perform the above statements. Note that IMPORT [], script step, is for the SELECT calls to the same source. At no time are you "live" connected to the SQL db. see:

              Execute SQL - note the space between the two "words" for the script step.

               

              2. ESS is a way to "live connect" to select SQL dbs (with permissions, of course) through a DSN set up and no SQL calls are needed. You simply place the source on the Relationship Graph and make finds, edits, deletes as you would FMP native tables. Read more about ESS:  FileMaker Software Deployment, Business Software Solution - FileMaker

               

              3. ExecuteSQL() is a FUNCTION that is used to query (SELECT only) any table/table occurance whether FileMaker or ESS source. ExecuteSQL

               

              for more information about this function, see my reference:

              The Missing FM 12 ExecuteSQL Reference | FileMakerHacks (get the PDF as it's more complete than the blog and get the example file). There are links to other articles about ExecuteSQL and even links to SQL plug-ins for FileMaker. Yes, there are a few new features in FM13 for this function.

               

              4. Also look at this documentation:

                   https://fmhelp.filemaker.com/docs/13/en/fm13_odbc_jdbc_guide.pdf

                   https://fmhelp.filemaker.com/docs/13/en/fm13_sql_reference.pdf

               

               

              So.... it seems like you need to research the ESS and the function rather than the script steps. Performing the ExecuteSQL on an External SQL source may be overkill. But yes, you can make an ExecuteSQL() - SELECT only - call to and External SQL source and you can "parse" the text in a way that may be more useful. Again, it really depends on what you need to do with it. Perhaps the IMPORT[] script step would do the same thing more elegantly?

               

              Beverly

              1 of 1 people found this helpful
              • 4. Re: Execute SQL ( )
                deninger

                Just to be clear, I already know and understand ExecuteSQL(), ESS, and IMPORT(), and I leverage all three heavily in my projects.

                 

                In my projects, I am working with a third party back-end hosted on SQL Server. There are some calculations that I need made that would be MUCH easier to perform in SQL on the host DB because I don't actually need the data in FM.

                 

                ExecuteSQL()  can be run on an ESS connected datasource and DOES work, it can be very slow thru the ESS channel, though.

                 

                consider an ESS table DISPENSE:

                Let (

                 

                [

                $mySQL= "SELECT  SUM (dispense.DAYS_SUPPLY)

                     FROM ITEM LEFT JOIN NDCGPI ON ITEM.NDC_CODE = NDCGPI.NDC_CODE, DISPENSE, PATIENT, RX

                     WHERE PATIENT.PATIENT_ID = RX.PATIENT_ID

                     AND RX.RX_ID = DISPENSE.RX_ID

                     AND ITEM.ITEM_ID = DISPENSE.DISPENSED_ITEM_ID

                     AND PATIENT.PATIENT_ID = ?

                     AND NDCGPI.GPI_CLASS_CODE = ?

                     AND NDCGPI.GPI_GROUP_CODE = ?"

                ];

                ExecuteSQL ( $mySQL ; "" ; "" ; $patientID ; $gpiClass ; $gpiGroup )

                )

                 

                This is very slow over ESS

                 

                My desire is to be able run this on the SQL Server and just return the result. Obviously Execute SQL () is not designed for this as is appears to be limited to just INSERT, UPDATE and DELETE type calls.

                 

                I will look into the plugin

                • 5. Re: Execute SQL ( )
                  beverly
                  • 6. Re: Execute SQL ( )
                    Mike Duncan

                    I just recently completed a project that integrated with MS SQL where we used this. Execute SQL was used to run a stored procedure... even better, with FMS 13, used with the perform script on server functionality so this could run from users on ipads.

                     

                    There is nothing returned besides an error code when using the Execute SQL function. However, ESS to the same datasource allowed me to check what I needed to.

                     

                    Hope this helps.

                    Mike

                    • 7. Re: Execute SQL ( )
                      beverly

                      my reply by email (above) appears to be empty.

                       

                      What I posted was that the info for d, was for all readers. And I also asked about views. I see that Mike Duncan mentioned "stored procedures". Calling views and stored procedures may be different.  A view could narrow down your found set and even have joined results. It's like calling a table, but a query is set up on the SQL server to return a differrent set.

                       

                      Beverly

                      • 8. Re: Execute SQL ( )
                        beverly
                        • 9. Re: Execute SQL ( )
                          taylorsharpe

                          You can run a Perform Script on Server and when you exit that script, make sure the last script step is the Exit Script Step and click the option on the Exit Script Step and you can have the exiting script return the value of the variable you put in the Exit Script Step to the first script.  Then in the first script, you'll have a Get ( ScriptResult ) script step to make use of the value of the SQL call on the server side script.  Is that what you are asking for?

                           

                          If it is, this is what I do a lot to get the performance of server side processing and only returning the result to the client application.  It really does speed things up when used properly. 

                          • 10. Re: Execute SQL ( )
                            beverly

                            Again, the content does NOT seem to be appearing when reply-by-email. matthew_odell - something to research?

                             

                            Here's what I said:

                             

                            Here's a good explanation of the differences in Stored Procedures and Views (to SQL sources):

                             

                            http://databases.aspfaq.com/database/should-i-use-a-view-a-stored-procedure-or-a-user-defined-function.html

                             

                            also this from Mike is excellent:

                             

                            "There is nothing returned besides an error code when using the Execute SQL function. However, ESS to the same datasource allowed me to check what I needed to."

                             

                            I'm sure he meant the "ExecuteSQL() function" - the spelling is the problem with this function vs. script step! The same can be said for a slight advantage at times to use the "Execute SQL []" script step at times (for INSERT, UPDATE, DELETE) and the "Import []" script step (for SELECT). They can all have advantages and disadvantages. That's why it's good to understand how they all work.

                             

                            And sometimes plug-ins are the necessary connection for SQL<=>FM...

                             

                            Beverly

                            • 11. Re: Execute SQL ( )
                              Mike Duncan

                              Beverly,

                               

                              Yes, I meant the "Execute SQL" script step. Thanks for the clarification

                               

                              Mike

                              • 12. Re: Execute SQL ( )
                                danielkellett@mac.com

                                Does anyone have an example script of how to create a view or table or layout using ExecuteSQL() ?

                                i.e. I want to query an existing table, and use a button-triggered script to fetch, e.g.

                                 

                                [pseudo code]

                                CREATE TABLE "CUSTOMERS" (INT PRIMARY KEY "CUSTOMER_ID")

                                ;

                                INSERT INTO CUSTOMERS  (CUSTOMER_ID (SELECT DISTINCT CUSTOMER_ID FROM TRANSACTIONS)

                                 

                                So a script to create a table from WITHIN existing FM tables only, populate it with a select statement and display it somewhere (portal?)

                                 

                                I'm new to FM - my experience is in SQL Server, Oracle, Teradata and C#.NET

                                 

                                Ideally, my client wants a layout with an edit box into which you can type:

                                SELECT x from y WHERE etc....

                                and that gets assigned as a variable, e.g. $SQL for use in a script that then puts the search results in a new table, or simply displays is on a layout.

                                 

                                FM is very user friendly, but for people used to simply writing code (C/SQL style) it is pretty awkward, especially as I don't have Advanced with the debugger facility.

                                 

                                Anyone have any solutions to hand?

                                 

                                I just want a layout, like in TOAD or SQL Server, where the user can write their own SQL select statement, and if it is syntactically correct, it displays data. If not, that's their problem

                                 

                                Thanks in advance

                                 

                                Dan

                                • 13. Re: Execute SQL ( )
                                  beverly

                                  Daniel, let's be clear:

                                   

                                  1. ExecuteSQL() is a function that works on tables IN FileMaker (as seen on the graph):

                                   

                                  https://www.filemaker.com/help/14/fmp/en/html/func_ref1.32.185.html

                                   

                                  The result is TEXT and cannot be use to change data or schema in FileMaker

                                   

                                  2. Execute SQL is a script step that works on EXTERNAL SQL sources to INSERT, UPDATE, DELETE

                                   

                                  https://www.filemaker.com/help/14/fmp/en/html/scripts_ref1.35.169.html#1113277

                                   

                                  3. FileMaker can be used as an ODBC/JDBC source:

                                  https://www.filemaker.com/help/14/fmp/en/html/odbc.20.1.html#1027500

                                  &

                                  https://community.filemaker.com/docs/DOC-5772

                                  &

                                  https://community.filemaker.com/docs/DOC-5773

                                   

                                  Perhaps the last three links will help the most.

                                  beverly

                                  • 14. Re: Execute SQL ( )
                                    okramis

                                    You could pack your query into a stored procedure or a function on the SQL Server and call it with the ODBC import script step into a global field as mentioned in this thread:

                                     

                                    Re: How to executesql for stored procedure with parameters?

                                     

                                    regards

                                    Otmar

                                    1 2 3 Previous Next