AnsweredAssumed Answered

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

Question asked by Claw'd on Aug 20, 2015
Latest reply on Sep 22, 2015 by user19752

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.

Outcomes