ExecuteSQL failing

[Justin Close]

How come this works:

ExecuteSQL ( "

SELECT "_ID"

FROM Courses

WHERE "_CourseName" = ?

" ; "|" ; "" ; "Git Training")

But I can't seem to get these forms to work?

[These two return: "Specified table cannot be found" and it highlights the pipe in quotes]

ExecuteSQL ( "

SELECT "__ID"

FROM Courses

WHERE " & Quote("_CourseName") & " = " & Quote("Git Training") &

" ; "|" ; "" )

ExecuteSQL ( "

SELECT "__ID"

FROM Courses

WHERE "_CourseName" = " & Quote("Git Training") &

" ; "|" ; "" )

[This returns: ? ]

ExecuteSQL ( "

SELECT "__ID"

FROM Courses

WHERE "_CourseName" = "Git Training"

" ; "|" ; "" )

Been staring at these too long.

- J

[Wim Decorte]

The quote function puts real quotes (") around the WHERE value. SQL expects single quotes ('). Which is why it is easier to pass it in as a parameter, when you do that, FM takes care of putting the single quotes there when it evaluates the sql syntax.

[David Jondreau]

As Wim mentioned, Quote() on a string with quotes is the problem for the first two.

In the last, double quotes are fine for qualifying field names, but for text strings (Git Training) you need single quotes.

[dansmith65]

Personally, I use three custom functions to assist with creating SQL statements. They would allow me to write the statement like this...

ExecuteSQL (

"SELECT " & SQLField( Courses::__ID )

& " FROM " & & SQLTable( Courses::__ID )

& " WHERE " & SQLField( Courses::_CourseName ) & "=" & SQLQuote( "Git Training" )

)

Here are the custom functions:

/* SQLField ( name )



PURPOSE:

Return only the name of the field, without the relationship portion. As opposed to GetFieldName() which also returns the relationship. This is to be used in SQL queries to protect the field references from breaking if the field is renamed.

The Quote() function allows us to reference "SQL reserved" field names without having to manually escape them.



PARAMETERS:

(field) field field to return name of



DEPENDENCIES:

FileMaker Pro 10+



REVISIONS:

2011-MAR-25 - Created by Kevin Frank http://www.filemakerhacks.com/?p=1065

2011-JUL-01 - Dan Smith [email protected]

- renamed from GFN to SQLField

- added to documentation section

####################################################################################################*/



Let (

[

a = GetFieldName ( field ) ;

b = Substitute ( a ; "::" ; ¶ )

] ;

Quote ( GetValue ( b ; 2 ) )

)

/* SQLTable ( name )



PURPOSE:

Return only the relationship portion of a field, quoted for use in a FileMaker SQL statement.



PARAMETERS:

(field) field field to get table occurence name from



DEPENDENCIES:

FileMaker Pro 10+



REVISIONS:

2011-JUL-01 - Created by Dan Smith [email protected]

####################################################################################################*/



Let (

[

a = GetFieldName ( field ) ;

b = Substitute ( a ; "::" ; ¶ )

] ;

Quote ( GetValue ( b ; 1 ) )

)

/* SQLQuote ( name )



PURPOSE:

Wrap text in single quotes, for use with SQL queries.

The purpose of the Substitute is to "escape" any internally embedded apostrophes, e.g., O'Malley becomes 'O''Malley', which SQL will convert into O'Malley



PARAMETERS:

(text) text text to quote



DEPENDENCIES:

none



REVISIONS:

2011-MAR-25 - Created by Kevin Frank http://www.filemakerhacks.com/?p=1065

2011-JUL-01 - Dan Smith [email protected]

- renamed from Q to SQLQuote

- added to documentation section

####################################################################################################*/



"'" & Substitute ( text ; "'"; "''" ) & "'"

[Joost Miltenburg]

These custom functions are a great help. I was really struggling with aliases and keyFields starting with an '_'. 

[dansmith65]

I'm glad they helped. I've updated these functions now, and added some more to help with FQL: http://www.fmfunctions.com/members_display_record.php?memberId=375

 

Also, here is a template I use when writing an SQL query: https://gist.github.com/dansmith65/4684647

[Darren Burgess]

Also, your _ID field must be quoted since in begins with an underscore.  While I understand you want to take advantage of field sorting to put your ID on top, lately I have been calling my keys simply ID.  Then the ID does not have to be quoted for the SELECT to work.  Same goes for CourseName

ExecuteSQL ( "
            SELECT ID
            FROM Courses
             WHERE CourseName = ?
            " ; "|" ; "" ; "Git Training")

 

I prefer to avoid having to escape quotes in the select statement to improve readability.

 

Darren Burgess

http://www.mightydata.com