5 Replies Latest reply on Mar 7, 2013 7:39 AM by BowdenData

    Sql Query function and Script step



      i'm working on a filemaker solution that use ODBC connection to manage data stored in a mySQL server.

      I have not local table in filemaker, just the instance of the tables that exist in mySQL server.

      My problem is on a script that simply wants copy many fileds from a relatd record to an other.

      So i made a script that require input from the user, than i need to chek the input before perform the UPDATE with the SQL function.

      the script step "Execute SQL [No Dialog; ODBC: <datasource name>; <native SQL or calculated SQL>]" is not a problem because i can choose my ODBC connection and find out the right table called in my SQL script.

      before this step i have an IF instance that chek if the origin record and the destination record already exist and if i can copy the data from one to the other.


      So, the quick way for me is use an other SQL script called inside the IF condition like "SELECT id_a from table2 WHERE categories='test'"


      ExecuteSQL(sqlQuery; fieldSeparator; rowSeparator {;arguments...})


      the problem is that when i use the "table2" as istance of an ODBC table this function returs always "?" as an error because i think the function not recognize witch ODBC connection it should be use or because the name of the table in this case is not correct.

      i tried to apply this function to a local filemaker table an it works without problems

      don't work on external table like it not exist.


      how i can resolve this??

      Thanks a lot


        • 1. Re: Sql Query function and Script step

          Hi Vins,


          What is the actual name of the table occurence on the relationships graph that you're referring to as "table2"? If the name is a reserved word or if it contains spaces, you'll need to enclose it in double quotes in your SELECT query.


          Another approach you could take would be to use scalar subqueries in an UPDATE via the Execute SQL script step.


          Since it sounds like you already have your MySQL tables on the relationships graph using ESS, you could also use a more traditional FileMaker approach without writing any SQL. You'd need to script a find in each table and then use a group of Set Variable and Set Field script steps to copy the values from one record to the other. Or, you could script the finds and do an import with the "Update matching records" option.


          One advantage of not using the Execute SQL script step is that you could run the script on a workstation that doesn't have the DSN configured. Only the host computer requires the DSN when using ESS.



          • 2. Re: Sql Query function and Script step

            Hi Greg,

            thanks a lot for your solutions,


            actually the name table in my relationship Graph is


            and the name of EES ODBC commection is


            tha is connected by DSN system on a mysql server 5.1


            i was reading somewhere that my table should not have relation in my relationship graph but i'm gonna check this teory.


            in the mean time i have also made your second solution that use the script step to chek the oring and destinetion record setting up 2 flag variables. after this first part of the script i use the flags to manage

            the Execute SQL script step that works perfectly.

            this stuff work but is not what i want looking for, becaus i was thinking to perform all the initial-task in back.ground. in this way i can see when the form change state and perform the find etc etc...


            in this case i have not that advantage because all my tables are on a my sql server and all my clients use the file solution db.fm12 with the local DSN ( same for all clients ).

            there are no data stored in the db.fm12, just 1 local table with global variable.

            in this way the client can work without problem when i update the solution.


            i was thinking if the proble is the name of the table that i have to specify in the query, because instead than in the Execute SQL step , in the function i can't specify the ODBC connection name.

            i have tried also something sputid like this as table name :


            but of course doesn't work.

            i have tried also some external plug-in... same issues.


            thanks a lot



            • 3. Re: Sql Query function and Script step

              HI Greg,

              i'm running some test and it seems that there is a bug regarding the name table

              if i rename the table in pratiche_ditte it works!

              if i put the numers at the end works either like pratiche_ditte_3_1


              the function doesn't worl if i start the table name with a number


              you was talking about encolse the name table in double quote '' in the query but ''3_1_pratiche_ditte'' doesn't work either.


              have you some idea??


              i would like leave the number_text notation for my tables is very useful



              • 4. Re: Sql Query function and Script step


                the problem was the name table, when it start with a number you have to add the double quote, like Greg said.

                my problem was in the editor because i have to use \ in the string before the double quote:


                runSQL ( "SELECT * FROM \"3_1_pratiche_ditte\"";",";";" )


                work perfectly, very fast in this way i can cut my first script of over 10 steps

                • 5. Re: Sql Query function and Script step



                  In the Help files, they talk about not naming fields to start with a number. I don't seen anything specific about doing the same with tables, but it kind of makes sense that this would carry through to tables.




                  Glad to see that you resolved the issue by quoting your table names.