Escaped quotes vs. single quotes in ExecuteSQL

[Justin Close]

Can someone enlighten me why this fails:

ExecuteSQL( "SELECT COUNT ( IDs ) FROM TableA WHERE aaName = "Bob Bob" " ; "" ; ""  )

But this one works?

ExecuteSQL( "SELECT COUNT ( IDs ) FROM TableA WHERE aaName = 'Bob Bob' " ; "" ; ""  )

To mine eyes the only difference is the single quotes, and I thought that the escaped double quotes should be an acceptable form.  I know I have used escaped double quotes before, I swear.  (Oh wait, that's right, I am swearing too much recently.  ) 

 

I guess I have used escaped quotes around field/table names but never as part of the WHERE clause's string-to-match.  I typically have used the replaceable parameter in these cases; but this time I was trying to put it directly in the statement.  I.e. this version also works:

ExecuteSQL("SELECT COUNT ( IDs ) FROM TableA WHERE aaName = ?" ; "" ; "" ; "Bob Bob" )

Thanks,

C

 

[Wim Decorte]

text fields require the value to be in single quotes, no quotes required for numbers.

 

To avoid the confusion, always use the 3rd approach.  FM then figures it out for itself.

[Justin Close]

Wim,

   Thanks for the reply.  It makes some sense and certainly fits with what I found.  But it is annoying and obviously bit me in the butt for a while. 

 

--  C

[Priyabrata]

It would be more faster and efficient to use the Filemaker value count function instead of using the Exceute SQL count () funtion.

 

ValueCount(

                   ExecuteSQL(....)

                   )

[Fitch]

I'd be curious to see test results on that. I have found some ExSQL function to be slow (e.g. BETWEEN), but Count seems to perform very well. Interestingly, Count(*) is quite fast, it may be using internal record IDs or something.

[Priyabrata]

In Comparison to the Filemaker value count function in compared to the Count, it is slightly slow.

In case of using the ExecuteSQL Sum() function, it is really slow in compared to that using any custom function to sum down the values in a list.

[Justin Close]

I had recently done some testing of these methods and here is what I came up with:

 

This calculation is a CF on the Notes icon, showing if there are notes that exist or not.

Returning 258 records today.  The 'No CF' timing is the baseline for loading the layout in general.  This is a rather large list-view layout (i.e. lots of columns).  This formatting equation was only one calculation, and was the only thing changed for these tests.  You could subtract the average time there from each of the following tests to get how much time the calculation itself added to the loading.

 

The file is hosted on FMSA 12.03.  File was closed each time (i.e. no caching involved).

More calculation timing (CF on the Notes icon):

Returning 258 records today:

*******************
No CF calculation:
--------------------
5.75
5.84
5.83

*******************
Calculation references only local static data (a field in the same table that is set by script triggers)
--------------------
7.54
7.4
6.57
6.8
7.8
6.1
8.3
5.9

*******************
ValueCount( ExecuteSQL ( "SELECT Author FROM Comment WHERE C_ID = ? " ; Char(9) ; Char(13) ; Parent:_ID )  ) < 1
-----------------
9.77
8.1
8.23
8.36

******************
ExecuteSQL ( "SELECT COUNT (Author ) FROM Comment WHERE C_ID = ? " ; Char(9) ; Char(13) ; Parent:_ID )   < 1
-----------------
9.0
7.9
7.8
8.1

******************
Robustified ESQL:   
ExecuteSQL ( "SELECT COUNT (" & GFN ( Comment::Name ) & ") FROM " & GTN ( Comment::Name ) & " WHERE " & GFN ( Comment::C_ID ) & " = ? "  ; Char(9) ; Char(13) ; Parent:_ID  )  < 1
 ------------------
 8.82
 9.3
 8.96

*****************
ExecuteSQL ("SELECT COUNT (*) FROM " & GTN ( Comment::Name ) & " WHERE " & GFN ( Comment::C_ID ) & " = ? "  ; Char(9) ; Char(13) ; Parent:_ID )   < 1
-----------------
8.97
8.66
9.47
9.11

*****************
ExecuteSQL ("SELECT COUNT (*) FROM Comment WHERE C_ID = ? "  ; Char(9) ; Char(13) ; Parent:_ID )   < 1
-----------------
7.01
6.96
7.10
7.11

Added a test for "= 0" instead of "< 1":

*****************
ExecuteSQL ("SELECT COUNT (*) FROM Comment WHERE C_ID = ? "  ; Char(9) ; Char(13) ; Parent:_ID ) = 0
-----------------
6.99
7.03
6.98
6.97

So...calculations are bad for scrolling.  ESQL (Count(*) ) is pretty quick, but not as good as local data (generally - variances due to server/network).  Impact of Count(*) on scrolling was also less, but it was still noticable that it would hitch as you scrolled.

 

--  J