Your SQL syntax has an error and that's what the very unfriendly and uniformative ? is supposed to be telling you.
Try this SQL expression:
"SELECT NAME FROM PRODUCTS WHERE NAME = \"PEAR\""
Otherwise the SQL engine is trying to find a field named PEAR and can't find it. (\" is how you insert quotation marks inside a quoted string in FileMaker expressions.)
I recommend reading the SELECT query examples towards the end of the ODBC JDBC Guide you can access via FileMaker Help and also SeedCode's free SQLExplorer.
Thanks for the quick response Phil but that doesn't seem to work in the context of formating the whole sql statement into a variable first. I can seem to get it to work if I put the condition text in the parameter section:
Set Variable [$$SQL2;Value:"SELECT NAME FROM PRODUCTS WHERE NAME = \"PEAR\""]
Set Variable [$$RESULT2;Value:ExecuteSQL ($$SQL2;",";",")]
DOES NOT WORK BUT
Set Variable [$$RESULT3;Value:ExecuteSQL ("SELECT NAME FROM PRODUCTS WHERE NAME = ?";",";",";"PEAR")]
WORKS. I thought it better to format the whole SQL statement first so I could see exactly what I was executing but its not clear if this allows me the same conditional formating. I am re-reading the Guide as you indicated...
Just double checked the examples in the JDBC ODBC guide, use single quotes ' in place of \" and it should work.
Tried that too... all kinds of funky stuff to get the string to look right. I'm also not seeing any examples in the guide where the select statment is formated first (with conditional operators) and the ExecuteSQL is executed without anything in the parameters section.
Also-- to clarify, I am executing this against a normal Filemaker 12 Table.
Use a single quote
Set Variable [$$SQL2;Value:"SELECT NAME FROM PRODUCTS WHERE NAME = 'PEAR' "]
I'm also not seeing any examples in the guide where the select statment is formated first (with conditional operators) and the ExecuteSQL is executed without anything in the parameters section.
Correct. The guide documents the SQL syntax. It was never intended to document the ExecuteSQL funciton call syntax. It just so happens that the JDBC and ODBC queries interact with the same SQL engine as does Execute SQL so these SQL examples (select only) also work with ExecuteSQL.
I played around with this in the following Demo File: https://dl.dropboxusercontent.com/u/78737945/SQLQuoteDemo.fmp12
I got this to work with one small syntax change. The demo file tests this expression via unstored calculation fields in the same table, a different table and in a script.
Here's the script:
Set Variable [$SQL ; value: "SELECT Products.Name FROM PRODUCTS WHERE NAME = 'PEAR' " ]
Show Custom Dialog [ ExecuteSQL ( $SQL ; "" ; "" ) ]
Note also that text comparisons in SQL are case sensitive. In FileMaker calculations, "PEAR" = "pear" is true. IN SQL, it is not.
Good Show Phil! One of those answers tho that makes one a little less happy because it is so arcane and undocumented.Clearly the parser works differently on constants in the ExecuteSQL function than when parsing variables WITH EXACTLY THE SAME SYNTAX AS THE CONSTANT!
Using ? and specifying "Pear" as a parameter did not work for me, got the dreaded ?, until I also modified it to use Products.Name.
But I totally agree on the "arcane and poorly documented" aspects of this. A wizard for building queries built right in as a standard feature of FileMaker would be a huge improvement--one where you can graphically build the joins from your table occurrences and then be able to see and edit the SQL produced and be able to view the data produced by the query.