1 2 Previous Next 22 Replies Latest reply on Oct 22, 2015 11:36 AM by KenHoopes

    How to executesql for stored procedure with parameters?

    KenHoopes

      I am basically trying to use filemaker as the frontend to an sql database, since filemaker is easy to deploy and I have no front end experience. To accomplish this, I plan on coding as much of it as I can in MS SQL, and was hoping to get assistance from the community on executing stored procedures with variables. See below.

       

      The following is my executesql command in my script:

       

      Execute SQL [ ODBC Data Source: “sampledb” ; SQL Text: EXECUTE sampleSP @parameter1,@parameter2 ]

       

      I have a layout that is tied to a global table with two fields, and a button that executes the script.

       

      How can I pass what is in the global fields to the two parameters?

       

      A followup(less important), my SP is designed to "PRINT" feedback based on certain scenarios. Is there a way to pass that message back to filemaker?

        • 1. Re: How to executesql for stored procedure with parameters?
          nicolai

          In the "Specify SQL" dialogue box select "Calculated SQL text" option

           

          E.g. for the FileMaker fields myParameter1 and myParameter2 enter

           

          "EXEC sampleSP @parameter1 = "  &  myParameter1 &  ", @parameter2 =" & myParameter2


          On my experience Print does not work (as far as I remember it is used for debugging and not the output).




          • 2. Re: How to executesql for stored procedure with parameters?
            KenHoopes

            That worked perfectly, thank you so much.

             

            Any way to get it to not show the specify sql dialog box, and require log in to the database each time the script is executed?

            • 3. Re: How to executesql for stored procedure with parameters?
              beverly

              part 2 of the question: The ODBC set up should have a way to LOG the queries. See what results you get from the logs after a query is run.

               

              beverly

              • 4. Re: How to executesql for stored procedure with parameters?
                beverly

                post your script so that we may see what you're currently doing?

                 

                beverly

                • 5. Re: How to executesql for stored procedure with parameters?
                  KenHoopes

                  It isn't a query, it is an insert statement. I haven't actually written the script yet, but give me like an hour and I'll throw it up here.

                  • 6. Re: How to executesql for stored procedure with parameters?
                    beverly

                    sorry, I'm using 'query' as in Structured Query Language. Any SELECT, INSERT, UPDATE, DELETE (or other SQL statement) to me is a "query".

                     

                    The stored procedure that executes on the SQL db and does not have any result for FM, will not have any results for FM. You're using the statements from FM and passing parameters, but there's nothing in an FM table that needs to be updated, so where would it show.

                     

                    If, OTOH, you actually perform an INSERT, are you pulling values from FM to INSERT?

                     

                    Thanks!

                    beverly

                    • 7. Re: How to executesql for stored procedure with parameters?
                      KenHoopes

                      The filemaker script is as follows:

                       

                      Show Custom Dialog [ Title: "Confirmation"; Message: "Are you sure you wish to add this record?"; Default Button: “Yes” , Commit:“No”; Button 2: “No”, Commit: “No” ]

                      If [ Get ( LastMessageChoice ) = 2 ]

                      Exit Script [ ]

                      End If

                      If [ IsEmpty ( gtbl_createrecord::parameter1 ) or IsEmpty ( gtbl_createrecord::parameter2 ) ]

                      Show Custom Dialog [ Title: "Empty Fields"; Message: "A required field has been left empty."; Default Button: “OK”, Commit:

                      “No” ]

                      Exit Script [ ]

                      End If

                      --SEE (1) BELOW

                      Execute SQL [ ODBC Data Source: “sampledb” ; Calculated SQL Text: "EXECUTE sampleSP @parameter1 = " &

                      gtbl_createrecord::parameter1 & ",@parameter2 = " & gtbl_createrecord::parameter2 ]

                      --SEE (2) BELOW

                      Show Custom Dialog [ Title: "Success"; Message: "Your record was successfully created."; Default Button: “OK”, Commit: “No”]

                      Set Field [ gtbl_addincidentcode::code ; "" ]

                      Set Field [ gtbl_addincidentcode::description ; "" ]

                       

                       

                      (1)-------------Since I haven't found a way to return "PRINT" output from the database back, I may create an additional stored procedure, that will query the table and if a match is found to return a "1" then use that 1 to generate a custom dialog or something, and then end the script.

                      (2)--------------SQL connection boxes come up here, and I would prefer them not to. I have saved the UN and pass, but it still asks for the DSN, even though it is already configured in the line above.

                       

                       

                       

                      The stored procedure looks like:

                       

                      CREATE PROCEDURE sampleSP(

                      @parameter1 nvarchar(3),

                      @parameter2 nvarchar(40)

                      )

                       

                      AS

                      BEGIN

                       

                      IF @parameter1 IN (SELECT field1 FROM sampletable)

                      PRINT 'record already exists.'

                       

                      ELSE

                       

                      INSERT INTO sampletable (field1,field2,createdate)

                      VALUES (@parameter1,@parameter2,getdate())

                       

                      END

                       

                       

                      So that is kinda where I am.

                      • 8. Re: How to executesql for stored procedure with parameters?
                        beverly

                        very good! If you wanted to break this out:

                         

                        1. query to find the record & if does NOT exist using SELECT:

                         

                        2. INSERT

                         

                        3. query to find the record again to verify (using IMPORT script step and SELECT in FM)?

                         

                        otherwise, you don't get a "feedback" on the record being actually created, as you have found.

                         

                        beverly

                        • 9. Re: How to executesql for stored procedure with parameters?
                          KenHoopes

                          Awesome Beverly. I totally dig your step 3. That makes so much more sense than simply hoping it worked fine, while promising on the front end that it did. Any chance you can answer in this thread, the script steps I would use to perform the "warn if in use behavior" and the "report its success behavior" if I return a 1 from a query when the record exists in the table?

                           

                          Also, I found out that I can suppress the database dialog boxes when the script runs by flagging "perform without dialog". Lol on missing it, I was kinda pulling my hair out.

                          • 10. Re: How to executesql for stored procedure with parameters?
                            beverly

                            Howdy, Ken! there is a function called "Get(LastODBCError)"

                            https://www.filemaker.com/help/14/fmp/en/html/func_ref1.32.111.html#1067564

                            that comes in handy when using ExecuteSQL.

                             

                            If I recall, I believe we used something like these steps:

                             

                            Execute SQL

                             

                                 "UPDATE sampletable SET field1 = " & gtbl_createrecord::parameter1 & " , field1 = '" & gtbl_createrecord::parameter2 & "' WHERE field1 = '" & gtbl_createrecord::parameter1 & "'"

                             

                            Then test for Get(LastODBCError), if there's an error that the record does NOT exist, for example:

                             

                            Execute SQL

                             

                                 "INSERT INTO sampletable ( field1, field1 ) VALUES = ('" & gtbl_createrecord::parameter1 & "' , '" & gtbl_createrecord::parameter2"

                             

                            End If

                             

                            IMPORT (using SQL)

                             

                                 "SELECT field1, field2 FROM sampletable WHERE field1 = '" & gtbl_createrecord::parameter1 & "'"

                             

                            If the record comes through, you can test for the correct values and then send a message to the user that all is a-ok. delete this record if desired.

                             

                            It seems overkill, but is a way to check.

                             

                            beverly

                            • 11. Re: How to executesql for stored procedure with parameters?
                              nicolai

                              +1 to Beverly

                               

                              I would use TRY CATCH to throw a custom exception, if you are going to use Get(LastODBCError) - this will give you more control.

                               

                              You can try something else:

                               

                              Use RETURN instead of PRINT. This allows to return a different value depending on a condition, e.g. successful insert. I think I did see it coming through to FileMaker.


                              Regarding password:


                              In "Specify SQL" dialogue Under "ODBC Data Source" click on "Specify".

                              On the next popup window select your data source, it should prompt for Username and password.

                              At the bottom of this dialogue there is a checkbox to save them permanently.

                              These username and password are solution based, not user based. I usually add a fm_user to the sql database as a database user. If you need to log user, you would have to grab FileMaker user and pass it to the SP as another parameter.


                              Good luck


                              Nicolai



                              • 12. Re: How to executesql for stored procedure with parameters?
                                beverly

                                apologies, I used "ExecuteSQL" in the first para when I should have said "Execute SQL" the Get ( LastODBCerror ) works with Execute SQL script step not the ExecuteSQL() function.

                                • 13. Re: How to executesql for stored procedure with parameters?
                                  okramis

                                  (1)-------------Since I haven't found a way to return "PRINT" output from the database back, I may create an additional stored procedure, that will query the table and if a match is found to return a "1" then use that 1 to generate a custom dialog or something, and then end the script.

                                  (2)--------------SQL connection boxes come up here, and I would prefer them not to. I have saved the UN and pass, but it still asks for the DSN, even though it is already configured in the line above.

                                   

                                   

                                   

                                  The stored procedure looks like:

                                   

                                  CREATE PROCEDURE sampleSP(

                                  @parameter1 nvarchar(3),

                                  @parameter2 nvarchar(40)

                                  )

                                   

                                  AS

                                  BEGIN

                                   

                                  IF @parameter1 IN (SELECT field1 FROM sampletable)

                                  PRINT 'record already exists.'

                                   

                                  ELSE

                                   

                                  INSERT INTO sampletable (field1,field2,createdate)

                                  VALUES (@parameter1,@parameter2,getdate())

                                   

                                  END

                                   

                                  You could modify your stored procedure to return the result, instead of printing it and execute it with the import script step into a global:

                                   

                                  CREATE PROCEDURE sampleSP(

                                  @parameter1 nvarchar(3),

                                  @parameter2 nvarchar(40)

                                  )

                                   

                                  AS

                                  BEGIN

                                   

                                  IF @parameter1 IN (SELECT field1 FROM sampletable)

                                   

                                  SELECT -1

                                   

                                  ELSE

                                   

                                  INSERT INTO sampletable (field1,field2,createdate)

                                  VALUES (@parameter1,@parameter2,getdate())

                                   

                                  SELECT COUNT(*) FROM sampletable WHERE field1=@parameter1

                                   

                                  END

                                   

                                  this would import "-1" if the record already exists, "1" if it has been created succesfully, "0" if creation failed...

                                   

                                  Otmar

                                  • 14. Re: How to executesql for stored procedure with parameters?
                                    nicolai

                                    One more thing, it it keeps asking you to select DNS, make sure you select "Perform Without dialogue" on the Execute SQl script step

                                    1 2 Previous Next