ExecuteSQL issue

[JOrdanMcC]

Hello 

 

I'm in the process of redeveloping an FM11 solution in FM13.

I have a dream of only having one table occurrence per actual table in my FM13 update.

I have been happily doing all my lookups utilizing executeSQL until this evening when I attempted to building a simple select statement with a WHERE clause that included references to fields of type "TEXT"

I have tried inclosing the reference fields in " as suggested in many forum postings I have read but I still get the dreaded ? as the result.

 

Example 

 

$query = " SELECT code FROM Courses WHERE FM11PK = " & Sections::FK_course

the above example works as expected

 

$query = " SELECT code FROM Courses WHERE PK = "  & Sections::FK_courseFM13 

the above example returns the ?  

 

the only difference I can decipher is "PK" and "FK_courseFM13" are type TEXT where in the first example the two comparison fields are of type NUMBER

 

I have tried a couple other example in other tables using only reference fields of type TEXT and have no been able to get it to work - it must be a simple SYNTAX issue.

 

I'm heading to bed because I've been trying to figure this out all day - any help would be very appreciated.

Thanks

 

 

 

[JOrdanMcC]

Argh I think I got it - needed single quotes "'" around the Where Value. 

 

" SELECT code FROM Courses WHERE PK = '"  & Sections::FK_courseFM13 & "'"

 

works as expected!

[bruceR]

Note that this problem could have been solved a different way, using the tools and recommended methods built into ExecuteSQL.

These provide for using a ? to represent placeholders for values to be used in the query. One advantage is the elimination of the need to manage single quotes.

 

http://www.filemaker.com/help/13/fmp/en/html/func_ref3.33.6.html

 

In your case it would be:

 

ExecuteSQL( "SELECT code FROM Courses WHERE PK = ?" ; ""; "" ; Sections::FK_courseFM13 )