ExecuteSQL chokes on Smart Quotes

[fmbiz.net]

Hello,

I thought I was almost an expert at FileMaker until I tried to use the ExecuteSQL function. I thought it would be simple to type in a basic SQL statement and come back with some usable data. But I was surprised to find that everytime I tried to use the WHERE clause with comparison of text as the data type I would always recieve ERROR 8310.

I spent the better part of the day trying to figure out this one. It seems that the ExecuteSQL function provided in ScriptMaster cannot understand the Smart Quote character, example: ‘your text here’.

By default FileMaker turns off the 'Use Smart Quotes' option, but somewhere along the way you may have forgotten that you did turn this option On. You will not see this if the SQL statement is generated by concatenating fields in a calculation, but if you are manually typing in the SQL statement the smart quotes appear.

So a simple statement like: SELECT CategoryID, Category, Item FROM SQLdata WHERE Category=‘Oranges’ comes back with an ERROR 8310 from the function.

The statement that works is this: SELECT CategoryID, Category, Item FROM SQLdata WHERE Category='Oranges'

Which returned 2 rows of data

Subtle difference but the ticks around the word Oranges make a world of difference.

Here is how this can be resolved (if it is not fixed internally by 360works at the next release)

===========================================

If you use Smart Quotes in your default Text Handling settings you will have to revert these characters back to the basic tick-styled quote ( ' ). Check File Options… and then select the Text tab to view your setting in Text Handling.

To automatically convert text fields with smart quotes use the following Auto-Enter calculation:

Substitute( Self ; [Char(8217) ; Char(39)] )

===========================================

I hope this saves some of you many hours of searching and questioning your understanding of FileMaker and SQL, my take away from this is a nice little utility designed to generate SQL statements that I built while trying to figure out why typing a statement manually always failed.

Best Regards,

Charlie M.

FMBiz.net

[john renfrew]

Kevin Frank has some great custom functions which help out with this

Look in the FQL forum here and also at filemakerhacks.com