9 Replies Latest reply on Dec 11, 2013 7:32 AM by gdurniak

    Great new Stuff for ESS fans in FM13

    LSNOVER

      Hi Folks:

       

      I've been very eager to share this info. One of the "issues" we have had with ESS is the inability to call Stored Procedures in our SQL Databases directly from Filemaker. This can be done with the old "Execute SQL" SCRIPT STEP, not to be confused with the ExecuteSQL function. The only problem was that the Execute SQL script step requires a LOCAL ODBC driver. This is a big pain in the butt, to say the least, and defeats one of the nicest features of ESS, which is centralizing all the ODBC setup stuff to the server.

       

      Well, with FM13 and the "Perform Script On Server", our problems are solved! We can now use the Execute SQL Script step ON the server, called from a "Perform Script on Server" script! This is a fantastic capability. No more kludges using utility tables and table triggers to call Store Procs! In addition, if you finesse your Error routines in SQL, you can pass back some pertinent info. directly to Filemaker by way of the Error handler. Let's not forget FMGO and Webdirect, for which it is otherwise IMPOSSIBLE to install a ODBC driver. You can now execute SQL stored procs on FMGo and in WebDirect!

       

      NEXT, hidden in FM13, is a new Summary function. This one is a little obtuse because of how it has been implemented. As you know, one "issue" for many of us in the FM community has been finding an "easy" way to get all of the Field IDs for the records IN THE FOUND SET. Should be simple, but it's not. Many kludges abound. This is something many Filemaker developers want to do, regardless of whether they use ESS. But for us ESS fans, it's particularly important, because we have no other way of "communicating" what the found set records are, to the SQL database, when we want to run Server side procedures.

       

      So in FM13, you can now define a new type of Summary field. The summary type is "List of", and you specify the Field from the table you want a "list of". Once defined, this field will hold the values for all of the records in the found set, for the field you specify. Wala, you now have a nice relatively fast list of your record IDs for the found set. This can now be passed to a Stored Procedure, or saved in a utility record to provide the SQL database with context for your users found set. This is also quite handy when using Perform Script on Server in Filemaker. Remember, when you use Perform Script on Server, your called Filemaker Script has NO CONTEXT of your current Filemaker Client session (which means Pro, FMGo and WebDirect). You must think of these scripts like a user firing up a fresh copy of your application from scratch, and program what needs to happen from there. When the script finishes running, that "special" user session closes out and is done. The nice things about "List of" summaries.... no need to go off to a special layout, loop through your found set, etc.

       

      These are two seemingly "trivial" features in FM13 that open up all kinds of new capabilities and flexibility and really make SQL database calls much more convenient.

       

      Enjoy ESS Minions! ;-)

       

      Regards,

      Lee Snover

        • 1. Re: Great new Stuff for ESS fans in FM13
          mikebeargie

          Thanks for pointing out the summary type, I didn't notice that option but definitely see it's potential (IE building virtual lists without the need to loop through records? yes please!)

          • 2. Re: Great new Stuff for ESS fans in FM13
            jrenfrew

            Already put the summary to use today for soemone who wanted a really quick set of line item summaries... I agree it s a great feature..

            • 3. Re: Great new Stuff for ESS fans in FM13
              IanWilson

              I like this summary type. I thought I might be able to put it to good use when a Perform Script on Server script step is used. I could use it to make sure the server has exactly the same found set as the client calling the PSS.

               

              Anyway, I decided to test it, but my attempt failed miserably. I'm sure I've seen people use WHERE ... IN in the ExecuteSQL function before. My test is pretty well self-explanatory ... contacts::s_List is the summary field which provides the list on contactIDs:

               

              Let ( 

                        ls =  "( " & Substitute ( contacts::s_List ; "¶" ; ", " ) & " )" ;

                ExecuteSQL ("

                        SELECT \"first_name\", \"last_name\"

                        FROM contacts 

                        WHERE  contactID in  ? "   ;    " | "   ;   ¶ ; ls )

                )

               

              Can anyone shed any light on this please?

               

              Cheers

               

              Ian

              • 4. Re: Great new Stuff for ESS fans in FM13
                jrenfrew

                Doesn't work as a variable at the end. This will.

                 

                ... WHERE contacts IN ( " & Substitute ( contacts::s_List ; ¶ ; "," ) & " ) ...

                • 5. Re: Great new Stuff for ESS fans in FM13
                  coherentkris

                  Yea jrenfrew got it ...WHERE IN is requires a parenthesized argument that can't be a variable but can be a sub query...

                   

                  http://www.w3schools.com/sql/sql_in.asp

                  • 6. Re: Great new Stuff for ESS fans in FM13
                    IanWilson

                    Wonderful ... thanks so much!

                     

                    Ian

                    • 7. Re: Great new Stuff for ESS fans in FM13
                      medioag

                      thank you Lee!

                       

                      Cheers Dani

                      • 8. Re: Great new Stuff for ESS fans in FM13
                        LSNOVER

                        Oh, one other thing....

                         

                        Many folks are griping about the "scalability" of Filemaker.   It you are using ESS as your primary data source, you can have MULTIPLE FIlemaker servers all connected to a central single SQL Database.    There's still performance issues with ESS, especially with large tables.  These can be programmed around to SOME degree.   But we all need to keep up the pressure on FMI to work on the deficiencies in ESS.   ESS with WebDirect is a KILLER feature set IMHO.   But FMI needs to step up to the plate and fix, SORTING speed, SEARCH speed (especially across relations), and they need to remove the size limits on character data.   Ideally, I would like to see BLOB support.   In the mean time, we can use the new Image conversion functions to convert container fields into text and save them in a SQL Character field as long as we don't exceed the character buffer size impossed by ESS.

                         

                        PLEASE, PLEASE, PLEASE, if you use ESS, write to FMI and let them know you would like to see these issues addressed.   The squeaky wheels get the oil.   ESS is great, but it needs some OIL!  

                         

                        Regards,

                        Lee

                        • 9. Re: Great new Stuff for ESS fans in FM13
                          gdurniak

                          Ouch !  That is quite a Kludge

                           

                           

                          Note: Servoy has a similar problem.  You need the primary keys to operate on the Found Set, but if the Found Set is very large, this can be slow

                           

                          greg

                           

                          > ESS with WebDirect is a KILLER feature set IMHO