3 Replies Latest reply on May 17, 2012 8:49 PM by steve_ssh

    Using SQL to create records from FileMaker 12 and Go 12

    worldcloud

      We all realize that FileMaker 12 does not support the INSERT or UPDATE SQL statements. Many of us have been using plug-ins to create records, push values, etc.

       

      We can continue to use plug-ins with FileMaker 12, but FileMaker Go still creates a problem- as Go does not support any plug-in architecture. My concept is to enable Web Publishing on a FileMaker Server and post a PHP page which will take a SQL statement as a script parameter, trigger a script (on the server) via the Web Publishing engine, and then return a result. The result would be a 'error code' from the plug-in.

       

      I realize that I would have to dedicate a layout and table occurence to access this PHP page, as all PHP pages access FileMaker within the context of a specific layout, but this method would prevent me from having to create a 'Utility' layout for record creation for each table in the solution. I hesitate to call it a Web Service, because that implies that the result would be returned as XML.

       

      To utilize this server-side script, a 'hidden' web viewer could be used to trigger the script and the GetObjectAttribute function could be used to recover the error code as a test or number. The script could 'loop' until a result code is generated as the client script would no way of knowing when the server had completed the request.

       

      On a more grand scale, I could see using this method to trigger any function which renders a text or number result; but at this time, creating records in a context free environment is my highest priority.

       

      1. Has anyone tried anything like this?

      2. Any reason this can't work?

      3. Does anyone who which of the SQL plug-ins are capable of running through web publishing engine?

       

       

      Thanks!!!

       

      Joe

        • 1. Re: Using SQL to create records from FileMaker 12 and Go 12
          jbante

          My biggest concern when planning the under-the-hood portions of any FileMaker Go solution is this: How does it work when the network fails? This is less of a concern for solutions running on on-site WiFi, but I haven't personally met one of those yet. Without the network, there's no access to the Web Publishing Engine. If I were looking for the way to create records in an unrelated table — or the way to do anything else, for that matter — I'd be looking for a way to make it work on an iPhone in airplane mode sitting inside a Faraday cage. For performance-sensitive situations, I'm fond of creating records through temporary relationships to avoid layout-switching; but that requires more table occurrences and fields, which defeats half of your purpose.

           

          Hordes of developers would love a FileMaker-native feature for creating records through SQL, but I wonder if FileMaker would? It seems to me that the UI as the default API/mental model for scripted manipulation of data is somehow fundamental to their vision of the product. If so, I can see why INSERT, UPDATE, and DELETE SQL operations wouldn't be big on the to-do list. FileMaker does natively support some "external" technologies like XLST, PHP and ODBC for the sake of integration; but natively using SQL, a syntax that must be learned outside of FileMaker documentation, for use within FileMaker solutions is qualitatively different. Mass adoption of these commands by advanced developers would make their code less accessible to others who don't already know and don't care to learn SQL, thereby fragmenting the community. Or maybe not. Perhaps introducing the ExecuteSQL function in version 12 is FileMaker's way of dipping their toes in the water. A prior example for comparison might be web viewers... but I digress from the problem at hand, which I don't have a satisfactory answer to.

          • 2. Re: Using SQL to create records from FileMaker 12 and Go 12
            worldcloud

            Issue 1:

             

            I treat a WAN solution differently than a network solution. As a hosting provider, most of our users are aware that without a network connection that they are going to have issues. Certianly Zync or similar coding can provide offline support. We also have a Spyglass solution which features 'smart' push-pull syncs which is less complex (and free). We designed it a proof of concept for FIleMaker Go, but in context of this discussion, I am assuming that the database is on a FileMaker Server and if they lose connectivity, then they will get an error and will be forced to re-connect.

             

            Issue 2:

             

            I aggree that FileMaker's ommission of INSERT, UPDATE, and DELETE and revealing about how they see there product, but I suspect that FileMaker Go was the limiting factor in SQL support, as there are no native SQL drivers for iOS to utilize. I understand that they are very hesistant to have features in Pro which are not present in Go. I hope that you are correct and they are 'dipping their toes' and we may get added support in the future, but for the next 18 months or so, it is unlikely that we are going to see a new release from FileMaker.

             

            Issue 3:

             

            I think my original concept will work for a network/ hosted solution; but I hate having to add layouts to a solution to make a technique work. If FileMaker where to make small changes in New Record and Set Field then much of this talk about SQL would go away. More specifically. If they allowed you to specify a table to create a record into without using the context of the current layout, and/or the allowed Set Field dialog to allow context free access to records- maybe with code similar to what we use with the Web Publishing engine or SQL.

            • 3. Re: Using SQL to create records from FileMaker 12 and Go 12
              steve_ssh

              Hello Joe,

               

              I can not offer any information regarding SQL plugins, however I do have a few comments related to a topic that seems to be of interest to both of us:

               

                The potential for transmitting data from FM Go to a "main" FM system via a webviewer on Go.

               

              I began my experimentation with this as sort of a hobby, back when Go was first released and have worked on this off and on through three different versions of Go.

               

              I'd like to share some observations/ideas that I have about this particular endeavor, in case any of it may save you or anyone else some time/effort.  As far as I am concerned, gathering this knowledge is a work in progress -- I'll do my best to share this information as I understand it, but I nonetheless suggest taking it with a grain of salt.

               

              Best of luck with this,

               

              -steve

               

               

               

              1) Consider a two-part protocol for transmission

               

              Given the ever-present possibility that the network connection could break in mid-transaction, I've started gravitating towards a request/response protocol which allows:

               

                - the client to send a request message to be processed by the server

                - the client to send a request message asking the server to resend a response for a previous request

               

              This would, of course, result in a slightly more complex protocol and backend system than just a simple one-time request/response paradigm, but I feel that the added potential for the client to recover from a network break would be worth it.

               

               

               

              2) When appropriate, consider encapsulating multiple transactions within a single request/response transmission.

               

              This has the potential for complexifying the actual request/response message format, but my thinking is that each HTTP exchange will incur enough overhead such that it would be more efficient to have larger payloads with fewer transmissions versus many transmissions of smaller payload size.  Even if the processing time on the backend increases, the two part protocol mentioned above should alleviate concerns about possible timeouts (though my encouragement would be to try to establish some metrics which will allow you to determine a comfortable number of transactions which can be bundled within a single transmission without causing a timeout).  I realize that you may already have had this in mind - perhaps the SQL statement that you intend to transmit could have been for a batch insert.

               

               

               

              3) GET and POST from Go

               

              Sometimes when folks get started with using the Go webviewer as a HTTP client, they think mostly in terms of requesting data via a GET request by pointing the webviewer at a carefully crafted URL.  If this works out well, I think it is worth sticking with this methodology.  If however, you start bumping into the limit to the amount of data that you can include as query parameters in the URL, or if for any other reason it would be advantageous to perform a POST request, don't rule out the possibility of performing a POST request.  A POST request can be accomplished in the Go webviewer by constructing the HTML for a simple page that includes a form element with input elements (type='hidden') that encapsulate all of the data you wish to post (as well as the proper action URL).  Include an onload handler in the page that submits the form once the page is fully loaded. This will allow you to POST data, thus opening doors for communicating with servers that require a POST, or handling larger amounts of data.

               

               

               

              4) Reliability of grabbing response content from the Go webviewer

               

              I reiterate that, if a GET request is working fine, I would stick with it.  I say this because my experience suggests to me that pulling returned page content from the webviewer (via GetLayoutObjectAttribute ) is more reliable when the page has been loaded by loading a URL that immediately implies to FM Go that it is making an HTTP connection, versus loading a data URL which does not have that obvious implication. Even though the technique I describe above will post data and return a page that displays in the webviewer, the GetLayoutObjectAttribute function does not always recognize that the page content has changed, and it may try to hand you back your original data url content that you supplied -- then it feels like you are playing games with the webviewer to get the *true* response content.  I've tried variations of the POST technique with three different releases of Go, and I am sorry to say that, with each successive version, it seems like it has become a little bit more difficult to get at the true response content that you want.  I am not saying that it can not be done - I believe that it can; it just may take a little bit of trial and error and patience to get this to happen reliably.  One thing that I've found with version 12 of Go is that it helps to make sure that you script in a reset of the webviewer object in-between a series of multiple transmissions - this seems to kick the application sufficiently to keep it returning you a fresh server response.  Additionally, this quirkiness is another reason why I gravitate to the two-part protocol mentioned above:  Even if the webviewer is recalcitrant with respect to returning the response from my POST, I could always utilize the "resend-the-response" message (presumably through a GET request that supplies only the parameters necessary to identify the target request) to overcome this and get at what I need.

               

               

               

              5) Keeping perspective

               

              With regard to the quirkiness of getting response content from the webviewer that I describe above, it helps me to keep the following perspective: I try to keep in mind that I am endeavoring to use something (the webviewer) for a purpose for which it is not really intended.  I don't think that FM built the webviewer with me in mind, thinking that it would be great to give me a means to perform pseudo-web-service transactions.  I keep this in mind when I run into a bump in the road with the webviewer.  As I've said, with each successive version of Go, I've found it more difficult to use the webviewer in this way, and I try to maintain the perspective that most of the bumps I've hit are likely the result of FM trying optimize the behavior of the webviewer as they actually intended it to be used.

               

               

               

              6) Position of the response payload

               

              Assuming that you are controlling the back end that sends the response to your Go client, I'd suggest putting the payload of your response within the head element of your returned HTML content, and moreover, within HTML comment begin/end tags.  When I first started doing this kind of work, I started off by having the server simply return an XML document because I was accustomed to thinking in terms of web-services, and XML, etc..  I seem to remember (this was back in 2010, so I'm no longer certain of details), that I switched to returning an HTML page instead of XML, because there was something about sending XML that confused the situation on the client end.  It may be that the webviewer just "didn't get it" when I returned an XML document -- like I say, I can't remember, but I did make the switch to HTML for some such reason.  As for returning your payload content within HTML comments in the head element:  I suggest this because this should protect your data from being munged by the tags that the iOS webkit may insert into the response content. (The names of these tags escape me at the moment, but, essentially, in between the time the server returns your data, and the time that you retrieve it via GetLayoutObjectAttribute, there may be tags introduced into the response data that try to identify data that appears to be in the pattern of a phone number, address, etc.).  To avoid this from mangling your response content, you can put the meaningful portion of your response content between HTML comments in the head portion of your returned HTML -- that should provide it with immunity from this interference.

               

               

               

              7) Knowing when to grab the response payload

               

              You mentioned using a timed loop in the client script to know when the response payload has arrived from the server.  I have always gone this route, but lately I have been intrigued by the idea of having the response content include some javascript which modifies the document.location property in order to trigger a FM script in Go.  This was something that I played with when the FM Url protocol was introduced in Go (second version of Go, I believe?), but at the time, the functionality was not quite there to make this happen.  Apparently, with Go 12, this technology has taken a nice step forward, and it now may be possible that the response content from the server could actively trigger a script in Go 12.

               

              For reference, I recommend this blog entry on the Beezwax site:

               

              http://buzz.beezwax.net/2012/04/21/native-web-2-0-controls-in-filemaker-12-layouts

               

              It's a fun and provocative article.  The main point I'm getting at, though, is that the claim is that a loaded web page can trigger a script in Go 12 via javascript.  I haven't tested this for myself yet, and, even if it did work, I'm not sure that I would completely abandon a looped script on the client side, since I'd still want to catch timeout and error conditions where I might not receive the standard response content.  Nonetheless, this is something to keep in mind as potentially useful for our purposes.