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.
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"?
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
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 )]
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?
Business Systems Developer
82 Murray Road
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
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&"'"]
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 < firstname.lastname@example.org
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.