1 2 Previous Next 17 Replies Latest reply on Dec 3, 2016 6:34 AM by fmpdude

    Post to MySQL using "Insert from URL" - loop

    weirded

      Hi to all,

      I'm absolutely newbie to php/MySQL and was trying to see how to manage data transfer from Filemaker (ideally GO) to other databases using "Insert from URL" script step with httppost.

      I've put up a locally working sample following a tutorial.

      FM basically calls a php script via "Insert from URL" passing the record field values, then the script opens the connection, inserts the data and closes the connection.

      I can put this step in a loop, to call the php script for every record I want to post, but then the script opens and closes the connection for every record. Is there a more efficient way to do this (in order to open the connection only once at the beginning of the push process and close it at the end) ? Am I forced to push the whole dataset as a sigle variable (sort of list) and then unwrap it in MySQL ? Any ideas ?

      Thanks.

        • 1. Re: Post to MySQL using "Insert from URL" - loop
          bigtom

          You will need a plugin with a cURL function. Base Elements (Goya), 360 Works, and MBS all have something to use for this. Base Elements is free. Insert from URL currently only supports GET.

          • 2. Re: Post to MySQL using "Insert from URL" - loop
            CarlSchwarz

            Actually FileMaker has a special URL scheme to support post: Support for HTTP POST operations in FileMaker Pro | FileMaker

             

            "httppost" and "httpspost" are custom schemes defined by FileMaker Pro and behave the same as "http" and "https" respectively except that the text before the first "?" character is used as the network address / path and all the text after the first "?" character is sent as the POST data.

            • 3. Re: Post to MySQL using "Insert from URL" - loop
              bigtom

              @Carl I have not gotten that to work well in all cases and this is why I moved to a plugin. I would prefer to not use a plugin, but no real success there.

              • 4. Re: Post to MySQL using "Insert from URL" - loop
                CarlSchwarz

                Okay - I haven't tried using POST from FileMaker.

                • 5. Re: Post to MySQL using "Insert from URL" - loop
                  weirded

                  httppost seems to work for me (however I only did a sort of "proof of concept" with an INSERT and 2 fields).

                  I followed this tutorial: Filemaker meets PHP Aus URL einfügen Insert From URL http request Post mysql - YouTube

                  I was wondering about the best way to use it while posting multiple records.

                  Plugins don't work with Filemaker GO, do they ?

                  • 6. Re: Post to MySQL using "Insert from URL" - loop
                    bigtom

                    With PSOS you can get it done with Go. I think some of the security authentication stuff is not here insert from URL and httppost did not work well.

                    • 7. Re: Post to MySQL using "Insert from URL" - loop
                      weirded

                      Hi bigtom. In order to use PSOS I would need Filemaker Server (while I'm trying to practice with a MySQL server).

                      • 8. Re: Post to MySQL using "Insert from URL" - loop
                        bigtom

                        You are trying to go directly from FM Go to do an httppost without FMS? As far as I know parameters pass easily enough, but some things you might need in a header won't make it.

                         

                        You should look at the FDS to work with FMS for development. It opens a lot of options. Plugins work lol not currently run on iOS directly. I think a few plugin developers have worked out a way to get things working with the iOS SDK from FM. Again you are looking at the FDS.

                        • 9. Re: Post to MySQL using "Insert from URL" - loop
                          fmpdude

                          Using FMP's free JDBC driver, and just a little Java, you can avoid the INSERT FROM URL script step (which is actually quite useful and I use it a lot) and just write data directly from FM to MySQL (or vice versa). All for free.

                           

                          Updated...

                          However, if you really want to use the POST and GET stuff in FMP via INSERT FROM URL, which is cool, then you can still easily do this in Java. It's a little more complicated "sounding", but it's really not difficult.

                           

                          Two straightforward ways, in Java, to use HTTPPOST OR GET:

                           

                          1. Create a Servlet (TONS of examples online)

                          2. Create a (REST) Web Service (TONS of examples online)

                           

                          (again, both these are free options as well.)

                           

                          I won't elaborate on these two options unless you ask, but once you created either one, then you could write to MySQL (or any database) from either the Servlet or from the Web Service.

                           

                          HOPE THIS HELPS.

                          • 10. Re: Post to MySQL using "Insert from URL" - loop
                            bigtom

                            xDBC has its own advantages and shortcomings.

                             

                            That being said I think you could accomplish the POST with JS through a web viewer.

                            • 11. Re: Post to MySQL using "Insert from URL" - loop
                              weirded

                              Hi,

                              thank you for your suggestions. Are these 2 options available using Filemaker GO or only Pro ?

                              Can you explain which is the advantage of using this combination "Java+Insert from URL" instead of httppost ?

                              Thank you for your help.

                              • 12. Re: Post to MySQL using "Insert from URL" - loop
                                bigtom

                                My suggestion would be to use only JS in a web viewer and not use insert

                                from URL if you want this to operate without FMS or a packaged FIAS App.

                                JS is available in web viewers with Pro, Go and Web Direct.

                                 

                                From general understanding cURL support for POST will be available

                                natively in the future in some way, but not right now.

                                • 13. Re: Post to MySQL using "Insert from URL" - loop
                                  fmpdude

                                  bigtom makes good points but I'll answer your questions.

                                   

                                  To me, the biggest advantage of using Java is that, unlike Web Direct and FMS which can get extremely expensive before you get too far, Java is free. Servlets are free. Web Services are free. And Java is rocket fast. But, in fairness, Java is a bit more involved.

                                   

                                  Best yet, FileMaker has excellent support for POST and GET via the INSERT FOR URL (POST support was added in FMP 13). I've never had any issues with it and I use it almost every week.

                                   

                                  I think I confused you a bit, however. Sorry about that...You would use INSERT FROM URL (assuming you have at least FMP 13), "httppost". Then, in the INSERT FROM URL script step, you'd need a "target" of that POST. The target could be anything that accepts POST (in this case) Requests (like a Java Servlet or a REST Web Services or other web server).

                                   

                                  I would try to keep things as simple as possible, however. If you don't know Java or are not a developer, then my suggestions are probably not that useful and you should maybe just stick with FMP-only (or using FM plug-in) methods.

                                   

                                  The MBS plug-in is really good and Christian, the author, is amazingly helpful and nice.

                                   

                                  HOPE THIS HELPS.

                                  • 14. Re: Post to MySQL using "Insert from URL" - loop
                                    weirded

                                    OK fmpdude, thank you for the clarification.

                                     

                                    Using "Insert from URL", the "target" of my post is a php script, which receives the parameters, authenticates to the MySQL server, load them via SQL (insert ...) then closes the server connection. I think is alternative to using java as you are suggesting, am I right ?

                                     

                                    This brings me back to my original question, which was if there is a possibility to connect/disconnect to/from the MySQL server ONLY ONCE (at the beginning/end of the process of uploading the records, when upload multiple records). For now, I call the "Insert from URL" script step (which calls the php script passing the single record) in a loop -> MySQL connect/disconnect happens for every single record.

                                    I hope I made myself clear...

                                     

                                    I put below a sample of the script I'm testing:

                                     

                                    <?php

                                    $servername = "127.0.0.1";

                                    $username = "user";

                                    $password = "pwd";

                                    $dbname = "Test_FM";

                                     

                                    //retrieve parameters

                                    @$pkid = $_POST ["pkid"];

                                    @$name = $_POST ["name"];

                                     

                                    // Create connection

                                    $conn = mysqli_connect ($servername, $username, $password, $dbname);

                                    // Check connection

                                    if (!$conn) {

                                        die("Connection failed: " . mysqli_connect_error());

                                    } else {

                                        echo "Connection OK !\r";

                                    }

                                     

                                    //query

                                    $sql = "INSERT INTO test (pkid, name)

                                    VALUES ('".$pkid."', '".$name."')";

                                     

                                    if (mysqli_query($conn, $sql)) {

                                        echo "New record created successfully";

                                    } else {

                                        echo "Error: " . $sql . "<br>" . mysqli_error($conn);

                                    }

                                     

                                    $conn->close();

                                    ?>

                                    1 2 Previous Next