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

    Sql Query function and Script step

    vnp1977

      Hi,

      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'"

      using

      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

      Vins

        • 1. Re: Sql Query function and Script step
          greglane

          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.

           

          Greg

          • 2. Re: Sql Query function and Script step
            vnp1977

            Hi Greg,

            thanks a lot for your solutions,

             

            actually the name table in my relationship Graph is

            3_1_pratiche_ditte

            and the name of EES ODBC commection is

            mySQL-SHARK

            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 :

            mySQL-SHARK.3_1_pratiche_ditte

            but of course doesn't work.

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

             

            thanks a lot

             

            Vin

            • 3. Re: Sql Query function and Script step
              vnp1977

              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

               

              thanks

              • 4. Re: Sql Query function and Script step
                vnp1977

                SOLVED

                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
                  BowdenData

                  Hi,

                   

                  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.

                   

                  http://www.filemaker.com/12help/html/create_db.8.9.html#1081048

                   

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

                   

                  HTH.

                   

                  Doug