14 Replies Latest reply on May 10, 2012 7:47 AM by jrenfrew

    FileMaker 12: Why SQL?

    JohnSindelar

      First in a series of articles on why we think the new ExecuteSQL function is important, and how we're using it.

       

      SeedCodeNext: Why SQL?

       

      Enjoy!

       

      John Sindelar

      SeedCode LLC

      FileMaker Pro Calendar Templates

      tel. (855) 733-3263

      http://www.seedcode.com

      Start Making Software

        • 1. Re: FileMaker 12: Why SQL?
          csodemann

          Hi John.

           

          I've been using Chris Dragons SQL Runner and other methods, so ExecuteSQL is a bit a disapointment.

           

          Why block updating of data? Can you explain that too ? :-)

           

          Regards,

           

          Christian

          • 2. Re: FileMaker 12: Why SQL?
            Oliver_Reid

            I look forward to making good use of this function. So far I have been using of the List functiom to list ID values via various reltiosnhips and using unions and intersections of those lists (with custom functions) to do complex queries and calculate aggregate values over same.

             

            I am also anticipatiing some of the enterprising among us will create some custom fuctions built on Excute SQL that will make this esier to use for the rest of us.

            • 3. Re: FileMaker 12: Why SQL?
              LSNOVER

              This feature is a great start, especially for those of us who avoid plugins.  It is disappointing that the functionality is limited, especially since the Plugins that do more have been around for quite some time now.   The function needs to be expanded, and it also needs to be modified to run the SQL queries against the native ESS tables in the originating SQL database. 

               

              WARNING:DO NOT USE THIS FUNCTION ON LARGE ESS TABLES!  The data must all be read into Filemaker and THEN Filemaker evaluates the SQL and returns the data.  If you do this on a large SQL Database it WILL KILL you system.  It does work fine with small ESS Tables.

               

              The one other thing this (and Filemaker SQL in general) needs, is some type of Pseudo column for selecting records that are in the Current Found Set.  I've been begging for this capability, as there is no fast way of getting all the record IDs (or any other column for that matter) in the scope of the current table's found set.  This is critical for reporting and also for synching up records between Filemaker and SQL databases.  I hope FMI will put something like this in soon.

               

              But it's certainly a good first step in the right direction!

               

              Cheers!

              Lee

              • 4. Re: FileMaker 12: Why SQL?
                worldcloud

                Like many others, I have been using various plug-ins for this functionality for years. My number one reason for doing so is so that I can create a record in any table and push one or more value pairs without any Table Occurence or layout dependencies. I actually have a fairly complex script to do this, so I never look at the SQL, I simply pass script parameters and a single script does all my record creation.

                 

                The new FileMaker function does not premit UPDATE or INSERT commands, so this new version does not completely replace the plug-in functionality, but the issue I have had over the past two years is that FileMaker Go has no way to utilize this method. I have considered a bot machine, running something like 24U SimpleTalk and the CNS Query plug-in. The thing I like about the CNS plug-in is that you can execute SQL on any FileMaker database, not just the one your script is running from, which was the reason I move from the Execute SQL function in FMdataguard.

                 

                I have also considered triggering a script via a hidden Web Viewer to force the server to handle the call, but PHP calls to FileMaker require a layout and table occurence, which contradicts the 'content free' promise of this method. Another thought would be to utilize the ODBC functionality with FIleMaker as a source, but that requires Server Advanced to host and requires that each machine setup a DSN, which would kill the iPad (again).

                 

                My last thought was to use 360Works Web Services plug-in and and a hidden Web Viewer/ scrap, but I would be interested in getting some other opinions.

                 

                The functionaility worked so well that I used it all my solutions until FileMaker Go was released and now I would really like to see some start people find a new solution. I was hopeful when I heard the rumors of SQL in FMP12. It certainly will clean up the TO which I use for calcs, but there's a piece missing.

                 

                Thanks,

                 

                 

                Joe

                • 5. Re: FileMaker 12: Why SQL?
                  csodemann

                  Thanks all.

                   

                  It seems we're all in the same boat. Everybody would like more funtionality in Go, where we lack plugins.

                   

                  What a shame Filemaker doesn't allow modification of data via SQL. In a function I can understand it, but why not in a script step?

                   

                  Christian

                  • 6. Re: FileMaker 12: Why SQL?
                    kaostika

                    I am thinking that is would be a major problem for the Non professional developers to have this Mass update feature in Filemaker.  I am myself am afraid of the replace feature since it is so dangerous at times to use.  I can imagine making a mistake with the Where clause of a update and mess up thousands of records.

                     

                    Oreste

                    • 7. Re: FileMaker 12: Why SQL?
                      beverly

                      And this is the case when working with a SQL database. One does all one can to avoid an UPDATE or DELETE without that very important WHERE clause.

                       

                      remove the functions that you deem dangerous from the users' menus and don't allow access to the scripts and don't use the functions and script steps that shouldn't be used by non-professionals.

                       

                      oh, and backup, BackUp, BACKUP....

                       

                      Beverly

                      • 8. Re: FileMaker 12: Why SQL?
                        kaostika

                        FileMaker seems to put some 'developer' features in each release and some laymen features.   I am curious why they didn't put in SQL the whole way.  

                         

                        Sent from my iPhone

                        • 9. Re: FileMaker 12: Why SQL?
                          LSNOVER

                          Keep sending feature requests. ;-)  The feature has so much more potential. 

                          • 10. Re: FileMaker 12: Why SQL?
                            worldcloud

                            FileMaker Go is my guess. Third party products (plug-ins) already do this on the Mac and Windows platform, but I an aware of a iOS program which handles ODBC natively. From what I have heard that there is deliberate effort to make sure that FileMake Go is as feature enabled as the other FileMaker products.

                            • 11. Re: FileMaker 12: Why SQL?
                              JohnSindelar

                              Well, I'm not sure why it's just SELECT--and only a subset of that--what happened to 'replace'?--but even just SELECT is very nice for the virtual list stuff we're doing.

                               

                              For folks who may need some help writing SQL queries, we've released a free SQL Explorer that helps you write queries using the FileMaker vocabulary you already know, and copy those queries out of the explorer in FileMaker's calculation syntax.

                               

                              Check it out: http://seedcode.com/cp-app/ste_cat/sqlxfree

                               

                              Screenshot: http://seedcode.com/screenshots/sqlx/master.jpg

                               

                              John Sindelar

                              SeedCode LLC

                              FileMaker Pro Calendar Templates

                              tel. (855) 733-3263

                              http://www.seedcode.com

                              • 12. Re: FileMaker 12: Why SQL?
                                jbante

                                I don't think lack of user expertise is the thinking behind limiting ExecuteSQL() to SELECT statements. FileMaker has other potentially destructive or advanced-user-only features: Replace Field Contents, Delete All Records, apply XSLT to imported and exported data, the Execute SQL script step (which does support CREATE, UPDATE, and DELETE, but only through ODBC), etc. It's not because it's hard to implement; several plugins already do it, and some of those were easy enough to put together to be distributed for free.

                                 

                                I think supporting other SQL statements in the ExecuteSQL function may violate FileMaker's vision of what the calculation engine is for: returning a result. Somebody has a crush on functional programming. (This is only what I suspect; I haven't heard FileMaker's vision from their own mouths, only inferred it through observation.) If that's the case, though, it's not a very consistent vision, since the Let function can set $variables that persist outside the calculation, and calculations are how we access plugin functionality, which frequently includes state-modifying behavior.

                                • 13. Re: FileMaker 12: Why SQL?
                                  csodemann

                                  Yes, there is no valid data security or other technical argument against data manipulation with SQL. I can understand why you wouldn't allow a function to perfom this, but why not a script step?

                                   

                                  Just imange how much we could save by not jumping between layouts and looping through records?

                                   

                                  Christian