3 Replies Latest reply on Jan 18, 2013 5:53 AM by Gingernut

    Execute SQL single double quotes confusing

    Gingernut

      Filemaker 12 WIndows 7

      I have this in a Script to set a variable

       

      ExecuteSQL (

      "SELECT "Date", "_InvNo", "GrossValue"

      FROM "Invoice_Customer"

      WHERE "Balance Due" >0 AND "_AccNo" =?" ;

      "|" ;

      ; '5737' )

       

      Without the Select statement preceding by quotes I get "Specified Table could not be found" but am getting the same thing for "Date" which is a field in the Invoice_Customer table occurence

       

      What is confusing me is if I use fields to set the query it does work

       

      The query field contains

      SELECT "Date", "_InvNo", "GrossValue"

      FROM "Invoice_Customer"

      WHERE "Balance Due" >0 AND "_AccNo" =? (no quotes)

       

      The Parameters FIELD has 5737 in the first box (no quotes)

       

      and there are also fields for each of the field and row separators

       

      Result field is calculation that has no quotes or double quotes (borrowed this from another persons file - cannot recall who now)

       

      ExecuteSQL(

      query;

      field separator;

      row separator;

      parameters[1];
      parameters[2];
      parameters[3];
      parameters[4];
      parameters[5])

       

      I am clearly missing something in the syntax, can anybody please help me

      Cathy

        • 1. Re: Execute SQL single double quotes confusing
          wimdecorte

          Pretty sure that "date" is reserved keyword.  You should not use field names that are the same as data types.

          • 2. Re: Execute SQL single double quotes confusing
            beverly

            Date is a reserved word (it's a function in SQL and in FMP)! the field names beginning with "_" have to be quoted, too.

             

            Cathy, you need to quote the entire query:

             ExecuteSQL ( "query" ; "" ; "" ; argExpr ; argExpr ) 

             

            IF the query is in a field then this works:

             ExecuteSQL ( Quote(table::queryField) ; "" ; "" ; argExpr ; argExpr ) 

             

            IF the query is not in a field, then you probably need to escape your inner quotes:

            =======

            ExecuteSQL (

             

            "SELECT \"Date\", \"_InvNo\", \"GrossValue\"

             

            FROM \"Invoice_Customer\"

             

            WHERE \"Balance Due\" >0 AND \"_AccNo\" =?" ;

             

            "|" ;

             

            "¶" ;

             

            ; 5737 )

            ======

            You ONLY need to escape reserved words and words beginning with "_" (and a few others). So GrossValue and Invvoice_Customer are ok as is. If it helps to quote all fields and tables, then do so. The Quote(field) function takes care of the escaping for you.

             

            + The arguments (5737, in this case) are in DOUBLE QUOTES, too, unless they are numbers. FileMaker will take care of the single quotes as needed.

             

            Did you see my article and example file? "The Missing FM 12 ExecuteSQL Reference"

                 http://www.filemakerhacks.com/?p=6406

            There are references to all kinds of other resources, too.

            Beverly

            • 3. Re: Execute SQL single double quotes confusing
              Gingernut

              Hi Beverley and thanks

              I knew I had seen a reference and after my post I did manage to find it and have saved it

              It also saved me as I could see immediately from that I was missing the \ characters.

              I have now got it workiing and am reading through the rest of your reference which is excellent. I had downloaded previously which is how I knew of its existence but I lost it

               

              Thank you VERY much

              Cathy