5 Replies Latest reply on Jan 19, 2016 9:14 AM by beverly

    Execute SQL and INSERT statements using local field data

    bcmillerway

      1. I have an FMP database set up as an ODBC datasource.

       

      2. I want to put local field data into the ODBC datasource via the 'Execute SQL' script step using the INSERT command. By 'local field data' I mean data from the FMP file from which the script is being executed.  So FMPfile1 is sending field data to FMPfile2 -which is the ODBC datasource.

       

      3. If I hardcoded the INSERT command it would look like this:

       

        INSERT INTO data (city,state,zip) VALUES ('Chattanooga','TN','37407')

       

      and that works successfully.

       

      4. But I don't want to have to hardcode the values which are being drawn from my current FMP database. I want to use values either directly from fields or from values returned via the 'ExecuteSQL' function.

       

      5. I've tried various query syntaxes typically resulting in:

       

        [FileMaker][FileMaker] FQL0001/(1:159463082): There is an error in the syntax of the query.

       

      I know there are alternate, more round about methods to accomplish this but the Execute SQL script step method seems so direct and simple.

       

      Has anyone done this successfully?

       

       

      Thanks

        • 1. Re: Execute SQL and INSERT statements using local field data
          planteg

          Hi,

           

          when you pull values from a field for example, do you add the ' (single quotes) around the values since SQL expects them (strings) ?

          • 2. Re: Execute SQL and INSERT statements using local field data
            bcmillerway

            Thanks planteg

            I wanted to avoid having to manually 'pull' values from fields and build them into the INSERT statement. Would rather just reference them.  That would allow me, for instance, to move from record to record clicking a button to execute a scripted EXECUTE SQL command to INSERT fieldvalues as I go.

             

            I've tried referencing the fully qualified field names, i.e.:

             

                 INSERT INTO ODBCtbl (city, state, zip) VALUES ( FMsrcTbl::city, FMsrcTbl::state, FMsrcTbl::zip)

             

            Definitely didn't work.

             

            Need to replace the 'FMsrcTbl::city' part in someway that FMP will consider syntactically acceptable.

             

            I've also tried putting the field values in variables like this:

             

                 INSERT INTO ODBCtbl (city, state, zip) VALUES ( $valCity, $valState, $valZip)

             

            ...which also got the syntax error.

            • 3. Re: Execute SQL and INSERT statements using local field data
              beverly

              How about 'calculated':

              "INSERT INTO

                   ODBCtbl (city, state, zip)

                   VALUES ('" & FMsrcTbl::city & "','" & FMsrcTbl::state & "','" & FMsrcTbl::zip & "')"

               

              beverly

              • 4. Re: Execute SQL and INSERT statements using local field data
                bcmillerway

                Thanks Beverly, truly psychodelic!!   It works!

                 

                Now I'm going to have to figure out the logic behind the single/double quotes and ampersands.

                 

                Barry

                • 5. Re: Execute SQL and INSERT statements using local field data
                  beverly

                  in the SQL 'matches', text must have single-quotes & numbers need not have quotes.

                   

                       WHERE xya = "abc"

                   

                       WHERE xyb = 123

                   

                  The ampersand is FileMaker's calculation dialog for text concatenation.

                   

                       table::field & " abc " & 123

                   

                  The SQL query is a calculation (text result) and combining fields (or static text or static numbers) follows all of the above:

                   

                  " SELECT a, b, c, d, 'constant'

                  FROM table1

                  WHERE a = " & table1:field3

                   

                  but if our match is a TEXT field, not a number field, it must be single-quoted:

                   

                  " SELECT a, b, c, d, 'constant'

                  FROM table1

                  WHERE a = '" & table1:field3 & "'"

                   

                  this will take the value of field3 and the query when evaluated becomes (whitespace ignored):

                   

                  SELECT a, b, c, d, 'constant' FROM table1 WHERE a = 'bob'

                   

                  So, you are writing queries, but you just concatenate with field references (to get the values).

                   

                   

                  HTH,

                  beverly