where is $_id getting set?
You use it in your let and its empty then _id is empty and the select will fail.
For me Debugging a SELECT in FM is all about breaking it down in to the simplest possible term that works and build out from there.
ExecuteSQL ( "SELECT \"First Name\" , \"Last Name\" FROM SQLContacts" ; "" ; "" ) does not throw an error so start from there.
First add in a known good id i.e,
ExecuteSQL ( "SELECT \"First Name\" , \"Last Name\" FROM SQLContacts WHERE id=9878" ; "" ; "" )
If that works then this should work...
_id = 9878;
ExecuteSQL ( "SELECT \"First Name\" , \"Last Name\" FROM SQLContacts WHERE id=?" ; "" ; "" ; _id )
Then add the fields variable to the mix...
Let ( [
_id = 9878;
_fields = "\"First Name\" , \"Last Name\""
ExecuteSQL ( "SELECT " & _fields & " FROM SQLContacts WHERE id=?" ; "" ; "" ; _id )
Correct me if I am wrong, I feel the syntax of the "_qry ="SELECT " & field.list.sub & " FROM SQLContacts WHERE id=? "" just makes everything more problematic. I personally like to avoid escaping characters/words if I can.
maybe modify it to something along the lines of
_id = $_id ;
Fname = \"First Name\"
Lname= \"Last Name\" ;
ExecuteSQL ("SELECT Fname,Lname FROM SQLContacts WHERE id=? "; "" ;""; _id )
Many thanks coherent kris, brianb and nicolai,
... for your speedy responses. Please allow me to respond collectively -
The query itself works - when the field names are hard coded / written out. The $_id is set earlier on the the script and is also correct.
However, what I did not make clear in the text of my question (only in the Title - sorry about that) was that the field names may vary ... user selected - they can actually select 4 fields from 8, I trimmed it down to 2 for debugging ...
So creating an alias of the Field name would not work as the routine does not know before hand what fields will be contained in the field.list.sub variable. The correct TOC is available.
Hope that adds further clarity ...
i tested your exact query in a duplicate schema and it worked perfectly unless $_id was empty or not set
This is fine, but now you would need to post actual code. It looks like you posted working code and asked why it does not work.
does the order of selected fields matter and how will the user be selecting the fields. I assume checkbox?
I would ask HOW are you letting the users choose which fields to SELECT?
Checkbox?, edit box?, ??
You should be able to write this "dynamically", but I'd like to know how you let them choose. I've been writing dynamic sql SELECTs for more than a decade.
Your 'field.list.sub' does not seem correct.
I also suspect syntax at the point of assigning field list to 'field.list.sub.
make sure your field list does not end up with coma
I am not sure, but \"Field.Name\" could be causing a problem as well. Try to escape any SQL unsafe characters
the order is not important
the users select per a wizard - showing drop down Value List ... After the wizard has run, the string is built in a let statement ...
I end up with the correct string, which I ran in another test esql - and it is fine. Just the variable does not work, when I include the variable instead of the string, I get the question mark
[quote] Your 'field.list.sub' does not seem correct.[/quote]
Yes, that is clear from testing - the hard coded values work - I took the hard coded values from the result of the variable in the data viewer ...
Can you reveal how you are building the string? and why do you need the variable if the string works?
There is something not quite right with the way that get's evaluated to be used with the eSQL.
For testing, I may set a $$variable with the query text string just so I can see what it is producing before being evaluated with eSQL() function.
Well, I use the script debugger and step through until each variable is set ...
The very strange things is - I have just retested it - and it now works ... ?
No idea - need to look at this again, thanks for all the suggestions...