12 Replies Latest reply on Jul 27, 2015 2:02 AM by rrrichie

    Delay In ExecuteSQL Response

    twelvetens

      Hi, I'm seeing weird behaviour when querying an SQL (MS SQL2005) source using an ExecuteSQL function.

       

      First, I create a record using the ExecuteSQL script step. All goes well, I can see the entry that's created in the destination table.

       

      Then, I try and query the SQL source for a UUID associated with the entry I just created:

       

      Let (

       

       

      [

      sku = SQLQuote ( Items::SKU ) ;

      query =

      "SELECT " & SQLfield ( Items | dbo.StockItem_ALL_TEST::pkStockItemID )

      &

      " FROM " & SQLTable ( Items | dbo.StockItem_ALL_TEST::pkStockItemID )

      &

      " WHERE " & SQLfield ( Items | dbo.StockItem_ALL_TEST::ItemNumber ) & " = " &  sku

      &

      " AND " & SQLfield ( Items | dbo.StockItem_ALL_TEST::bLogicalDelete ) & " = 0"

      ]

      ;

      ExecuteSQL ( query ; "" ; ""  )

      )


      And I don't get any results, which I know is odd, because there IS a record which matches the above. So then I try:


      Commit Records (Skip/No Dialog)

      Refresh Window (Flush/Flush)

      Flush Cache To Disk


      Still no joy.

       

      Then, I go into Manage Database, double check things, and when In exit, hey presto, the calculation returns the result I was expecting. This happens everytime, It's as though something is being refreshed, just by opening and closing the Manage Database window, which isn't refreshed by any of the script steps above. Am I missing something here? Is there some sort of refresh external data sources script step I'm missing?...

        • 1. Re: Delay In ExecuteSQL Response
          coherentkris

          How do you "I create a record using the ExecuteSQL script step" do this with FM ExecuteSQL?

           

          ExecuteSQL in FM only supports SELECT statements which is the R part of CRUD

           

          Create, read, update and delete - Wikipedia, the free encyclopedia

          • 2. Re: Delay In ExecuteSQL Response
            twelvetens

            I query the data source using the ExecuteSQL Function, but write to it using the Execute SQL script step, which supports the INSERT function (amongst others)...

            • 3. Re: Delay In ExecuteSQL Response
              beverly

              The IMPORT script step will also SELECT from a SQL source. The data comes in, you can massage and then use Execute SQL script step, as you said, to INSERT, UPDATE or DELETE.

               

              The use of ExecuteSQL() - the function - may indeed be your delay. Try the IMPORT into a temporary table, if needed (a new table the first time.

               

              FileMaker does not create "virtual temporary" tables as SQL dbs do. So have a table for the import.

               

              Beverly

               

              On Jul 24, 2015, at 1:16 PM, James Glendinning <noreply@filemaker.com> wrote

              • 4. Re: Delay In ExecuteSQL Response
                user19752

                Where did you see?

                I can see the entry that's created in the destination table.

                 

                Execute SQL script have target as SQL Server table, but ExecuteSQL() function has ESS table in FM.

                 

                Check "query" value in Let function.

                • 5. Re: Delay In ExecuteSQL Response
                  twelvetens

                  I'm sorry, I'm not sure I understand your response. The query in the Let funciton is not the problem - it works fine, it's just that the results seemed to be getting cached or delayed somewhere...

                  • 6. Re: Delay In ExecuteSQL Response
                    twelvetens

                    Thanks Beverly, seems like a bit of a pain to have to run an import just for a query on a single entry, but I'll give it a go...

                    • 7. Re: Delay In ExecuteSQL Response
                      twelvetens

                      Hi Beverly, me again, Just realised, this problem only seems to occur when I run the script as a Perform Script On Server (PSoS) script. Are you aware of any caching nuances I need to be aware of? I seem to remember reading some posts on PSoS scripts and deletions of data, but wasn't aware of anything ODBC specific....

                      • 8. Re: Delay In ExecuteSQL Response
                        beverly

                        The FM DevCon is just over, let's have the folks take a weekend rest and hopefully someone can give you an answer on that.

                         

                        Keep in mind that you are:

                             1. making an FM function call on the full data set

                             2. the data set is already an external sql source (ESS)

                             3. the call is being made on the FMServer

                         

                        While # 3 may or may not be a factor, #'s 1 & 2 certainly can be slower than than molasses in the winter at times. Especially if you are dealing with a LARGE data set*. Ways to get a smaller data set:

                             1. get a view (smaller data set from being pre-narrowed by a SQL query), if possible - may require access to the SQL db or have someone set this up

                             2. Import a (smaller data set with a SQL query), then use FM functions on it.

                         

                        • A large data set is the number of rows/records AND the number of columns/fields.

                         

                        This is true for even "internal" FM tables whether you are making queries with eSQL or using other means to get a value (finds, relationships or calculations). There are ways, but you must find the best of both worlds: Use the tools (whatever they may be) to optimize the data being called and use the method(s) that works the fastest.

                         

                        beverly

                         

                        On Jul 25, 2015, at 6:23 AM, James Glendinning wrote

                         

                        Hi Beverly, me again, Just realised, this problem only seems to occur when I run the script as a Perform Script On Server (PSoS) script. Are you aware of any caching nuances I need to be aware of? I seem to remember reading some posts on PSoS scripts and deletions of data, but wasn't aware of anything ODBC specific....

                         

                        • 9. Re: Delay In ExecuteSQL Response
                          wimdecorte

                          The response is about what the target is:

                          - the ExecutesQL script step will target the external database directly through ODBC

                          - the ExecuteSQL() function (as Beverly explained as well) targets a FM TO - which in this case is the representation of an ESS external table.

                           

                          So I would expect a certain amount of delay when using ExecuteSQL() - the function not the script step - against an ESS TO since there is more "translation" to do for FM.

                          • 10. Re: Delay In ExecuteSQL Response
                            twelvetens

                            I agree Wim, that's exactly what I'd guessed, but the thing that's been getting me is what appears to be a disconnect between what I see when the script steps involving the ODBC connection (be it Execute SQL or ExecuteSQL) run as PSoS, compared to how they run in FMP.

                             

                            Anyway, I think I've got this working now, as pulling the information down through an ESS relationship between TO's, rather than an ExecuteSQL Function call, seems to have solved things.

                             

                            I think I'll pencil this down to a learning curve, but this does kind of highlight to me how it would be useful to be able to perform more debugging on PSoS scripts than is currently offered.

                            • 11. Re: Delay In ExecuteSQL Response
                              gdurniak

                              As you explain, this is not really a "delay",  but possibly a failure to refresh

                               

                              and there isn't much to debug,  since it all happens under the hood

                               

                              if it is repeatable,  you might consider a bug report

                               

                              greg

                               

                              > it would be useful to be able to perform more debugging on PSoS scripts than is currently offered

                              • 12. Re: Delay In ExecuteSQL Response
                                rrrichie

                                I think it's inherent to the ESS functionality.  I have the same issue with mySQL.  Stuff is inserted externally, but it won't show up in Filemaker.  I solved it by doing a search.  It does show up eventually, but if you need it straight away it's an issue.

                                 

                                In your case you know the key.  Do a FileMaker search script step and the result will be updated.   There is an article somewhere on what triggers refreshing of ESS sources...  I'll put it here if I find it.