6 Replies Latest reply on Sep 22, 2015 4:29 PM by user19752

    ExecuteSQL difference between Let() and Import using ExecuteSQL()

    Claw'd

      Summary

      ExecuteSQL difference between Let() and Import using ExecuteSQL()

      Product

      FileMaker Pro

      Version

      13,14

      Operating system version

      All

      Description of the issue

      when using ExecuteSQL() in a calculation where Let() was used, that referenced a remote server with a log in the credentials that were saved in the DSN against the ODBC connection were queried after the connection to the server was initiated. the result was that instead of using the stored credentials the connection would ask for a user name and password.

      Steps to reproduce the problem

      Walking this through the 'Let' statement created the connection always before the DSN was queried.

      Expected result

      Stored password in DSN to be used

      Actual result

      Asks for password/username to connect to remote server

      Exact text of any error message(s) that appear

      User name and password dialogue

      Configuration information

      the Let() was used in a script to update a field

      The import was used in a script to update a field.

      Both used the DSN setup in the OS wether Windows, IOS or OSX

      First the let:

      ExecuteSQL No Dialogue; Calculated SQL Text;[

      Let (

         [ $query = "SELECT qty FROM inventory



            WHERE barcode = '"&$BarCode&"'"



         ; $result = ExecuteSQL( $query

            ; ",  "; "" )

      ]; $result

      )]

      Then the Import: Import Records no dialogue; DSN StoredData; Calculated SQL Text[

      "SELECT `order_details`.`barcode`, `order_details`.`order_id`, `order_details`.`order_qty`, `order_details`.`entity_id`, `order_details`.`product_id`, `order_details`.`name`, `order_details`.`sku`

      FROM `order_details`]

      WHERE order_details.entity_id"  &  ">"  & $$TimeStamp



      Working on different columns but the result should be comparable

      Workaround

      Doing away with the let and using  import to get the data into a field as an update using the stored connection details.

        • 1. Re: ExecuteSQL difference between Let() and Import using ExecuteSQL()
          TSGal

          Patrick Kenlock:

           

          Thank you for your post.

           

          I have sent your entire post to our Development and Testing departments for review.  When I receive any feedback, I will let you know.

           

          TSGal
          FileMaker, Inc.

          • 2. Re: ExecuteSQL difference between Let() and Import using ExecuteSQL()
            TSGal

            Claw'd:

             

            A tester mentioned that in the Execute SQL script step, the calculation result should be a native SQL or calculated SQL statement instead of a query result.  The format for the Execute SQL script:

             

            Execute SQL [ No Dialog ; ODBC: <datasource name> ; <native SQL or calculated SQL> ]

             

            The tester also mentioned that the credential storage has nothing to do with the calculated result, and there is nothing wrong with the stored credentials.  The tester asked if you forgot to check the option "save user name and password"?

             

            TSGal

            FileMaker, Inc.

            • 3. Re: ExecuteSQL difference between Let() and Import using ExecuteSQL()
              Claw'd

              Hi

               

              Thanks for the input.

               

              Please see the screenshots taken from a freshly opened file after putting in the

              normal account user name and password for the user.

               

              As you can see running the "QueryQOH" script to query the MySql server the

              password is asked for although (of course) the 'remember password' box is

              checked.

               

              Set Variable

              Set Error Capture

              Execute SQL [No dialog; DSN: Menelaus; Calculated SQL Text: Let (    [ $query =

              "SELECT qty FROM menelaus_inventory        WHERE barcode = '"&$BarCode&"'"     ;

              $result = ExecuteSQL( $query        ; ",  "; "" ) ]; $result )]

              Set Field

               

               

              The close up is of the ? which results from not putting in the user name and

              password asked for, this means that the saved credentials are being bypassed.

              The query works as expected if the credentials are put in manually.

               

              Does your tester have a different way to format this query that doesn't bypass

              the stored credentials?

               

              Kind Regards

               

              Patrick Kenlock

               

              Business Systems Developer

              ABSsoftware Ltd

              82 Murray Road

              Ipswich

              IP3 9AQ

              TEL: 07985571670

              www.abssoftware.co.uk <http://www.abssoftware.co.uk/>

              patrick@abssoftware.co.uk <mailto:patrick@abssoftware.co.uk>

              This message (including any attachments) is intended only for the use of the

              individual or entity to which it is addressed and may contain information that

              is non-public, proprietary, privileged, confidential, and exempt from disclosure

              under applicable law or may constitute as attorney work product. If you are not

              the intended recipient, you are hereby notified that any use, dissemination,

              distribution, or copying of this communication is strictly prohibited. If you

              have received this communication in error, please notify us immediately by

              telephone (+44 1473 410819)and (1) destroy this message if a facsimile or (2)

              delete this message immediately if this is an electronic communication. Thank

              you.

              • 4. Re: ExecuteSQL difference between Let() and Import using ExecuteSQL()
                user19752

                As TsGal mentioned, you need "query" in Execute SQL script step. Try

                Execute SQL [No dialog; DSN: Menelaus; Calculated SQL Text:"SELECT qty FROM menelaus_inventory        WHERE barcode = '"&$BarCode&"'"]

                • 5. Re: ExecuteSQL difference between Let() and Import using ExecuteSQL()
                  Claw'd

                  Hi

                   

                  Thanks. Doesn't explain why let doesn't work without manually putting in the

                  login details and I think I tried this approach first but it reported an error

                  with the SQL, also, how does the 'Result' Field get filled?.

                   

                  "SELECT qty FROM menelaus_inventory        WHERE barcode = '"&$BarCode&"'" does

                  the query but doesn't do anything with it which is why the 'Let' variable

                  approach is used.

                   

                  Isn't 'Let' the official route to forming SQL queries?

                   

                  Or am I missing something.

                   

                  Sent from my iPad

                   

                  On 22 Sep 2015, at 10:06, user19752 < noreply@filemaker.com

                  • 6. Re: ExecuteSQL difference between Let() and Import using ExecuteSQL()
                    user19752

                    You can set the result with one step

                    Set Field [ someFiled ; ExecuteSQL("your query" ; "" ; "" ) ]

                     

                    If you need to use Execute SQL[] script step for comparing to Import Records step in bug report, you can use it with "SQL query" parameter, but it never return result, you can't see the result in FM in the step. So the step is not use with SELECT statement usually. INSERT/DELETE/UPDATE result can be seen in FM after done the step.

                     

                    Let() function is useful to compose calculation clearly, but nothing special with ExecuteSQL().

                    If you use Let() in Execute SQL script step, it need to return SQL query string, not result of ExecuteSQL()

                    Execute SQL [

                    Let ( $query = some calculation ; $query )

                    ]

                     

                    BTW, of course you can use ExecuteSQL() result to compose SQL query string. But this is not your purpose now.