Don't forget that you can also inspect the literal SQL query string, i.e. the contents of $$req.
Quick way to spot an obvious syntax error.
It has the SQL debug custom function built in, so you can copy it from there and use. I've never had it fail on me.
Also: I see that you are using my custom functions for getting the TO and field names from a reference, but you are using it with the quoting set to "false" or off.
But in your sql statement you add an escaped quotes (\") at the beginning but nowhere else.
In general you can save yourself that hassle of quoting by passing True as the second parameter of the GetTO and GetField custom functions. That makes sure that even if you use SQL reserved keywords or other table or field names that SQL does not like then the query will still work.
Finally: in your screenshot you are setting _res twice, each time by executing an ExecuteSQL() call. The first one will not work because there is no substitute placeholder ("?") in the query. The second one might work.
But in the end your whole calculation will just return "" because that is what you ask it to do...
So a lot of things are not quite right....
The magic function shows only error message "which error", not "where", so you need look at the query string itself first.
erolst: Don't forget that you can also inspect the literal SQL query string, i.e. the contents of $$req.
I looked there, unfortunately I couldn't 'see' where the error was.
wimdecorte: But in the end your whole calculation will just return "" because that is what you ask it to do.
I was trying to get the error message from the SQL engine, but it didn't work that way. True in the screen shot, values for the ? parameters were missing. Thanks for the link to QueyBuilder, I added it to my tools !
With QueryBuilder unfortunately I was not able to find out what was wrong. But with SQLExplorer I was able to build a query that worked. Strangely I had to use ... >= Param1 AND <= Param2... instead of ... BETWEEN Param1 AND Param2... I prefer to use BETWEEN because I was told by an SQL Server guru it's more efficient than the other way. Why doesn't work with this query: I don't know. One day I will get back to it and maybe I will find.
Lets hope in the next version that will show up sometimes we will be able to access the error message more easily (it's cheap to dream ).
I prefer to use BETWEEN because I was told by an SQL Server guru it's more efficient than the other way.
Funny you should say that – IIRC, resident SQL guru (guress?) Beverly Voth stated the exact opposite.
This does seem to be a gray area. I typically test both ways and have generally found that using ≥ and ≤ to be faster than between. Now that I think about it some more, I believe that this has always been when comparing dates, so I can't vouch for this when comparing numbers, etc.
Aha, "added a watch on $$ListeDesEmployes in the Data Viewer" will do nothing, the magic is "calculate the function in data viewer".
Gilles, when debugging a LET in DataViewer, I normally hang the suspect var at the end of the result.
query = ......
result = ..... ]; ===> this becomes temporarily result = ..... & ¶ & query
And you believed me? IIRC, I also state all the time with eSQL: YMMV!
I prefer BETWEEN, but if I test and find that ≥ & ≤ is more efficient, for the particular query, then I use it in any flavor of SQL.
Yes, to get the MESSAGE of where the error occurs, the perform IN the dataviewer is a good way to go. You can, however, get the ERROR CODE returned and sometimes it will help, because it's the standard FM errors (FileMaker Pro error codes), except for two special FQL errors found by the plugin developers:
“8309” = Semantics error (logical)
& “8310” = Syntax Error (missing coding?)
if I get one of these errors, then I first look at my structure and test again. If I get on of these two after checking everything else, I chalk it up to "unsupported SQL function" with FMP ExecuteSQL().
The way to get these codes (not necessarily in the dataviewer)? Just put the function EvaluationError() around what you want to test. If it's "0" there is no error. More info on this function: EvaluationError
Beverly Voth wrote:
IIRC, I also state all the time with eSQL: YMMV!
That's why I wanted to bring to attention that even a guru's statement isn't the gospel … since there'll always be another guru with a different take on the matter – because, as always: it depends! (and, to quote Jack Bauer: “It's complicated!” )
Beverly Voth wrote:
The way to get these codes (not necessarily in the dataviewer)? Just put the function EvaluationError() around what you want to test.
If you want to catch all errors, including syntax errors, it's better to use EvaluationError(Evaluate(...))
EvaluationError("Case (1=1)") returns Error 0
EvaluationError(Evaluate("Case (1=1)")) returns 1201 Too few parameters in the function.
if you use FM.ExecuteFileSQL from MBS Plugin to execute, you get the error back.
Especially FM.ExecuteSQL.LastErrorMessage function gives you last error code.