How to Debug FQL

[no1tmorrow]

So trying to use ExecuteSQL but needing a way to debug the query. I tried using the function sql.debug but I obviously am doing that wrong since I don't get any response. Looked for additional documentation on that function but no luck. How do you all debug sql statement?

 

Thanks

Thom

[Lee Smith]

Hi Thom,

 

Did you do a google search for your keywords?

 

There is a file in our Download (see the button above) called SQL Builder. Seedcode, one of our sponsors also offers a free SQL file 

SQL Exploreer here

 

HTH

 

Lee

[Wim Decorte]

www.modularfilemaker.org has a SQL Builder tool available that has the debug function built in.

[Lee Smith]

Wim, isn’t that the same one that pointed to in my reply “Download” above?

 

file in our Download (see the button above) called SQL Builder

 

Or is this a new tool?

Edited July 2, 2014 by Lee Smith
added quote

[Wim Decorte]

it's a different one, actually called Query Builder:

 

http://www.modularfilemaker.org/2013/12/query-builder/

[Lee Smith]

Hey Wim,

 

I forgot about that one and it is probably the one he will get the most out of.

 

Lee

 

p.s.

 

Here is an article by Kevin Frank and Beverly Voth Missing FM 12 Execute SQL Ref

Edited July 2, 2014 by Lee Smith
p.s.

[wintergreen]

 Sigh. What a lot of waffle just to get to this:

 

If(statement = "?"; ""; statement)

put that in a custom function and wrap it around your executeSQL call.

[Wim Decorte]

Your point is?

 

The tool referenced allows you to do this without having to build it into your dev environment IF you don't want to and to be able to tackle it from a standalone perspective.

You're referencing this "as a lot of waffle" does not do justice to the folks and their brainpower that are trying to bring us tools to do this and allowing us to check it from a a place that does not require us to code it into one place.

[Fitch]

If the OP is talking about this custom function, it only works in the data viewer. It will display the actual error.

@wintergreen, I would argue that displaying nothing is not the same thing as debugging.

[Mark Scott]

Just to add to the options . . . there are also some good ideas, and well-articulated debate!, over at FM Standards.org re ideas for formatting ExecuteSQL ( ) calcs so as to facilitate debugging.

[beverly]

 Sigh. What a lot of waffle just to get to this:

 

If(statement = "?"; ""; statement)

put that in a custom function and wrap it around your executeSQL call.

Actually, if you go to the filemaker hacks article (get the PDF), you 'll see that I used EvalutionError() to help. Also, looking at the query in Data Viewer can point out many errors.

Just to add to the options . . . there are also some good ideas, and well-articulated debate!, over at FM Standards.org re ideas for formatting ExecuteSQL ( ) calcs so as to facilitate debugging.

That tells you how to get fields in the query that might otherwise break the query if 'hard-coded' and any of the names change.

There are many ways to "format" the eSQL to make it easier to debug (mostly to help you read it). I tend to use $vars, as these can be helpful while using Data Viewer. Then when the query works, just use any named var within the Let().

There have been links to several "builders" that can take some of the "pain" out of writing queries if you don't understand how or are getting many errors. Keep in mind that ExecuteSQL() is not going to let you do queries as you might in one of the big-gun SQL dbs. Perhaps if OP posted the query we can help point out errors?