Mac OS X
New in version: 4.0 4.1 4.2 4.3 4.4 5.0 5.1 5.2 5.3 5.4 6.0 6.1 6.2 6.3 6.4 6.5 Statistic
|SQL Statement||SQL Statement as a Text string|
Columns Separator, only used with SELECT statement. Default is TAB
Record Separator, only used with SELECT statement, Default is Return
Returns result of SQL expression or error.
DescriptionExecute the SQL Statement against the current FileMaker File.
There must be an occurrence of the Table on the current tables' Graph. SELECT returns the the records in as a tabbed delimited text string. All other SQL commands return "".
Use FM.ExecuteSQL.LastError to check for errors.
Supported Commands: SELECT UPDATE (see limitation below) DELETE INSERT CREATE TABLE DROP TABLE CREATE INDEX DROP INDEX WHERE
String comparisons are Case Senstive! WHERE First_Name="todd" will not find records that have "Todd" in the first Name Field. You can wrap the field you are searching for with "lower" like this WHERE lower(First_Name)="todd" KNOWN LIMITATIONS Schema editing SQL functions are not supported in FileMaker 8 and 8.5.
Record Locking Errors are Not Reported! This means you must exercise caution when using UPDATE. CAUTION! This is a very powerful function! It is relying on very new and relatively untested resources made available by the release of FileMaker 8.
Seems like on FileMaker 11 you get LastError 8310 for any syntax error, like bad braces or missing quotes. You can put field names in double quotes and string values in single quotes.
Use FM.ExecuteSQLOnIdle for special commands which change database like ALTER TABLE.
Use FM.ExecuteSQL.LastErrorMessage and FM.ExecuteSQL.LastError to see error message.
Error 8309 means you did pass wrong data type, like string for a number.
If you need to pass parameters, please use FM.ExecuteFileSQL.
Select records using delimiters
SELECT records with a Where clause
Get list of fields in all tables:
Create Record with values via SQL
Set value for all records of a table:
Set timestamp to today:
Update 5 fields:
Select with passing variable as part of expression:
Update date field:
Delete all data in a table:
Create an index:
Drop an index:
Value in List of values:
Delete all records from Table People:
Query value of a repeating field with :
Set value of repeating field:
Query only first row:
Truncates table in FileMaker 15 or newer:
Feedback: Report problem or ask question.