10 Replies Latest reply on Sep 21, 2015 4:44 AM by beverly

    Execute SQL / Stored Procedure format?

    user5571

      I have been given the following stored procedure that has 2 parameters.....

       

      Exec iSM_Query6 ‘CTDS’, ‘01/09/2015’


      How would I translate this in FileMaker's Execute SQL script step using the 'Calculated SQL text"? 

       

      I'm assuming the results can only come into a single field within FileMaker (which isn't ideal).

       

      Being a SQL novice I'm just not sure of the formatting.

       

      Any advice would be greatly appreciated.

        • 1. Re: Execute SQL / Stored Procedure format?
          mikebeargie

          I haven't dealt with stored procedures at all directly with Execute SQL, but this blog post covers it well:

          http://timdietrich.me/blog/filemaker-sql-stored-procedures/

           

          What might be better for you is using the "import records" script step to pull the data result of your SQL over to filemaker.

           

          Traditionally I've used the ExecuteSQL script step whenever I need to write data FROM FileMaker back to SQL and don't want to deal with ESS via the relationship graph.

          • 2. Re: Execute SQL / Stored Procedure format?
            wimdecorte

            user5571 wrote:

             

            I'm assuming the results can only come into a single field within FileMaker (which isn't ideal).

             

             

            It is important to note that the Execute SQL script step will not return a result.  It is meant to send / update data.  Only the Import script step can return data from an ODBC data source.

            • 3. Re: Execute SQL / Stored Procedure format?
              beverly

              It has been a while... does the driver have "logging" and that shows "something" after INSERT, UPDATE or DELETE?

               

              However, the script step has nowhere to place any results, so this is a true statement.

              • 4. Re: Execute SQL / Stored Procedure format?
                ronhofius

                You treat it like straight text concatenation with the single quotes being just text in your string.

                 

                if there are no variables, but you have given the exact syntax needed, then just put it in quotes just as it appears. If you need to feed it Filemaker data as variables each time you run it then do the following:

                 

                For example let's say the CTDS was stored in your filemaker data as mytable::field1 and the date was mytable::field2. You would send this calculated value to the ExecuteSQL script step:


                "Exec iSM_Query6 ‘" & mytable::field1 & "’, ‘" & mytable::field2 & "’"


                I left the single quotes formated as you had them because it's easier to see in my example, but you should probably type them to avoid wierdness with SQL misinterpreting them.   To validate your syntax, if you create the string just like I did there and look at it in your data viewer on FM Advanced while positioned on a particular record, you should see the exact SQL command you're looking for.

                • 5. Re: Execute SQL / Stored Procedure format?
                  ronhofius

                  You talked about returning results. As others have said, if the SQL stored procedure is a select statement which is going to return a set of results, then you use the same syntax as I just gave you, but rather than using the ExecuteSQL script step, you use the Import Records script step.  Pull down the drop-down that says "File..." and choose "ODBC Data Source."  You have to have the login credentials to use that ODBC data source, and you have to have set up that ODBC data source on the client you're on.  Unlike Filemaker's ESS feature, a server side ODBC driver will not work for running SQL stored procedures.  So assuming you have set up your computer this way, then you can import records using the syntax I gave you above.

                   

                  As far as getting the results into discrete fields, your assumption is not correct that they would have to go into a single field. If the stored procedure contains a select statement, it would return fields with field names just as though you were importing from a Filemaker table, and you can map them to their proper destination in your Filemaker table. 

                   

                  I often test the query the first time by importing not to my existing data but to a "Create New Table" destination. Then I can examine everything before deciding whether to just use this new table in FM and create relationships to it or to import the SQL data directly into existing tables.

                  • 6. Re: Execute SQL / Stored Procedure format?
                    rrrichie

                    Construct the SQL as ronhofius stated.  Then depending on what your stored procedure does, just import the result with an odbc.

                     

                    I have used the technique do let MS-SQL collect time-sheets entries and use a transaction to test and set a "flag" field, which I used later in an ODBC to import the collected records from MS-SQL.  So the stored procedure did not return any results itself (you can put a lot in a stored procedure, which is pretty powerful, cause FileMaker has nothing to do with it.)

                     

                    If your stored procedure is just a query, you might be able to put that into the import from ODBC source and skipt the stored procedure.  You can also make the stored procudure create a new table with the ids you need from other tables and use that in the FileMaker SQL query using a SELECT ... FROM ... WHERE id IN (SELECT id FROM tempTable ...)

                     

                    Point is the FIleMaker SQL is a small subset of that mighty SQL language :-)

                     

                    Happy Coding!

                    • 7. Re: Execute SQL / Stored Procedure format?
                      user5571

                      Thank you everyone for your help!  It is very much appreciated.

                       

                      I trying ronhofius' suggestion to use a Import records script step, but I'm receiving a syntax error.  I think I'm close but obviously something isn't formatted correct.

                       

                      Let me post some screen shots......screen 1.png

                      screen 2.png

                      screen 3.png

                      • 8. Re: Execute SQL / Stored Procedure format?
                        user19752

                        You use different chars at opening and closing. Try use same one.

                        ' = char(39)

                        • 9. Re: Execute SQL / Stored Procedure format?
                          user5571

                          Thanks!   Closer but still not quite there.  I think the syntax is now correct but now I'm receiving this error.screen 4.png

                          • 10. Re: Execute SQL / Stored Procedure format?
                            beverly

                            What result do you get when this is performed ON the SQL server? is the stored procedure a SELECT, and could you simply use the correct query 'as is' with the Import script step?

                             

                            If it has some other steps (unseen by us), then you might see if your DBA can make it a "View" upon which you can SELECT with the Import script step.  Tables and Views can be used by ESS or ODBC and the Import script step.

                             

                            beverly