FM.ExecuteSQL.LastErrorMessage ------------------------------ Queries the last error message from an SQL call | Component | Version | macOS | Windows | Linux | Server | iOS SDK | |---|---|---|---|---|---|---| | [FM](component_FM.md) [FMSQL](component_FMSQL.md) | [3.0](newinversion30.md) | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes | ✅ Yes | MBS( "FM.ExecuteSQL.LastErrorMessage" ) ### Parameters none ### Result Returns error message. ### Description Queries the last error message from an SQL call Set by [FM.InsertRecord](FMInsertRecord.md), [FM.SQL.Execute](FMSQLExecute.md), [FM.ExecuteSQL](FMExecuteSQL.md), [FM.ExecuteFileSQL](FMExecuteFileSQL.md), [FM.ExecuteSQLOnIdle](FMExecuteSQLOnIdle.md) and other SQL using functions. If last error code was 0 (no error), you get an empty string. Error may be something like this: ERROR: FQL0007/(1:7): The column named "fieldname" does not exist in any table in the column reference's scope. | Error | Message | |---|---| | FQL0001 | There is an error in the syntax of the query. | | FQL0002 | The table named "?0" does not exist. | | FQL0003 | The table named "?0" already exists in this query. | | FQL0004 | The query is too complex. The maximum number of tables has been exceeded. | | FQL0005 | Expressions involving aggregations are not supported. | | FQL0006 | "The column named "?0" appears in more than one table in the column reference's scope." | | FQL0007 | The column named "?0" does not exist in any table in the column reference's scope. | | FQL0008 | The table named "?0" does not exist in the column reference's scope. | | FQL0009 | The column named "?1" does not exist in table "?0". | | FQL0010 | The literal value "?0" is not a valid DATE, TIME or TIMESTAMP. | | FQL0011 | Predicate must contain a logical operation (=, <, OR, AND, IS NULL, ...). | | FQL0012 | The ordinal reference "?0" in the ORDER BY clause is not valid. | | FQL0013 | Incompatible types in assignment. | | FQL0014 | The number of values in a VALUES row value constructor does not match the number of values in the target. | | FQL0015 | The number of values in an INSERT...SELECT statement does not match the number of values in the target. | | FQL0016 | A subquery contains an illegal outer reference to a column in the INSERT's target table. | | FQL0017 | An expression contains data types that cannot be compared. | | FQL0018 | An expression contains incompatible data types. | | FQL0019 | The result data type of a CASE expression cannot be inferred; they are all NULL. | | FQL0020 | An invalid number of parameters was supplied to the function "?0" | | FQL0021 | Parameter number ?0 to the function "?1" is not of the correct type. | | FQL0022 | A subquery expression must have exactly one value in the SELECT list. | | FQL0023 | A CAST expression requested an invalid data type conversion. | | FQL0024 | A reference to ROWID must be qualified if more than one table is present in the query. | | FQL0025 | All non-aggregated column references in the SELECT list and HAVING clause must be in the GROUP BY clause. | | FQL0026 | The number of columns in both inputs to a UNION operation must be the same. | | FQL0027 | The data types of corresponding columns in the inputs to a UNION operation must be the same. | | FQL0028 | Field repetitions must be numeric and between 1 and ?0. | | FQL0029 | A field repetition in the SET clause of an UPDATE statement must be a constant. | | FQL0030 | "?0" is an invalid function. | | FQL0031 | The the parameter's type cannot be inferred in this context. At least one query parameter must be an expression, a column or a constant. | | FQL0032 | A query may contain either named parameters or dynamic parameters, but not both. | | FQL0033 | Column names in FROM clause subqueries must be unique. | | FQL0034 | The number of output columns in a FROM clause subquery must match the number of columns in the table's name list. | | FQL0035 | Cursor support is not enabled for this query. | | FQL0036 | A cursor with the name "?0" already exists. | | FQL0037 | There is no cursor with the name "?0". | | FQL0038 | The cursor "?0" is already open. | | FQL0039 | The cursor "?0" is not open. | | FQL0040 | The target cursor "?0" does not reference a query that is valid for WHERE CURRENT OF . | | FQL0041 | The target cursor "?0" does not reference the same table as the current statement. | | FQL0042 | The default value for column "?0" does not match the column's data type. | | FQL0043 | The string "?0" is not a valid stream name. | | FQL0044 | The column "?0" is not valid in this context. The targets of GETAS and PUTAS must be Container fields. | See also [FM.ExecuteSQL.LastError](FMExecuteSQLLastError.md). Use [FM.ExecuteSQL.LastSQL](FMExecuteSQLLastSQL.md) to get the SQL command. If you get error "FQL0013 Incompatible types in assignment" when using [FM.InsertRecord](FMInsertRecord.md) or similar functions, the data type doesn't match. For a numeric field, you can use GetAsNumber() in the call to make sure the value is numeric. Same for date/time/timestamp fields with GetAsDate/GetAsTime/GetAsTimestamp functions and of course text fields, which may need a GetAsText(). ### Examples Custom function to log the last SQL error to a log file: Let( \[ e = MBS ( "IsError" ) ; r = If( e ; Let( \[ errorCode = MBS ( "[FM.ExecuteSQL.LastError](FMExecuteSQLLastError.md)" ) ; errorText = MBS ( "FM.ExecuteSQL.LastErrorMessage" ) ; sql = MBS ( "[FM.ExecuteSQL.LastSQL](FMExecuteSQLLastSQL.md)" ) ; path = "/Users/cs/Desktop/sql.log" ; r = MBS ( "[Text.AppendTextFile](TextAppendTextFile.md)" ; "Error " & errorCode & ": " & errorText & " while running " & sql & ¶ & ¶ ; path ) \] ; "" ) ) \] ; "" ) // to be used after a MBS () call with SQL to log the error. Try the error message: Let(\[ sql = "SELECT \* FROM Test"; r = MBS ("[FM.ExecuteFileSQL](FMExecuteFileSQL.md)"; ""; sql); errorCode = MBS ( "[FM.ExecuteSQL.LastError](FMExecuteSQLLastError.md)" ); errorMessage = MBS ("FM.ExecuteSQL.LastErrorMessage") \]; errorCode & ": " & errorMessage) Example result: 8309: ERROR: FQL0002/(1:14): The table named "Test" does not exist.Build a JSON after causing an error: Let(\[ sql = "INSERT INTO Test (FirstName, Age) VALUES (?,?)"; r = MBS ("[FM.ExecuteFileSQL](FMExecuteFileSQL.md)"; ""; sql; 9; 13; "Joe"; 23); errorCode = MBS ( "[FM.ExecuteSQL.LastError](FMExecuteSQLLastError.md)" ); errorMessage = MBS ("FM.ExecuteSQL.LastErrorMessage"); lastSQL = MBS ("[FM.ExecuteSQL.LastSQL](FMExecuteSQLLastSQL.md)"); params = MBS ("[FM.ExecuteSQL.LastParameters](FMExecuteSQLLastParameters.md)"); json = JSONSetElement ( "{}" ; \["Parameters"; params; JSONArray\]; \["ErrorCode"; errorCode; JSONNumber\]; \["SQL"; lastSQL; JSONString\]; \["ErrorMessage"; errorMessage; JSONString\]) \]; JSONFormatElements ( json )) Example result: { "ErrorCode" : 8309 , "ErrorMessage" : "ERROR: FQL0002/(1:12): The table named \\"Test\\" does not exist." , "Parameters" : \[ "Joe" , 23 \], "SQL" : "INSERT INTO Test (FirstName, Age) VALUES (?,?)" } ### See also - [FM.SQL.Execute](FMSQLExecute.md) - [FM.SQL.InsertRecords](FMSQLInsertRecords.md) - [IsError](IsError.md) - [JSON.Import](JSONImport.md) - [Matrix.InsertOrUpdateRecords](MatrixInsertOrUpdateRecords.md) - [SQL.Execute](SQLExecute.md) - [SQL.InsertRecords](SQLInsertRecords.md) - [Text.AppendTextFile](TextAppendTextFile.md) - [XML.Import](XMLImport.md) - [XML.Import.Work](XMLImportWork.md) ### Example Databases - [SQL in FileMaker/Create Fields in Filemaker Table](https://www.mbsplugins.eu/MBS-FileMaker-Plugin-Examples/SQL%20in%20FileMaker/Create%20Fields%20in%20Filemaker%20Table.shtml#1ScriptAnchor_) ### Blog Entries - [SQL error checking improved](https://www.mbsplugins.de/archive/2026-04-30/SQL_error_checking_improved/monkeybreadsoftware_blog_filemaker) - [MBS FileMaker Advent calendar - Door 13 - Last SQL](https://www.mbsplugins.de/archive/2025-12-13/MBS_FileMaker_Advent_calendar_/monkeybreadsoftware_blog_filemaker) - [MBS FileMaker Advent calendar - Door 8 - Error handling](https://www.mbsplugins.de/archive/2025-12-08/MBS_FileMaker_Advent_calendar_/monkeybreadsoftware_blog_filemaker) - [Storing data in the FileMaker schema](https://www.mbsplugins.de/archive/2024-05-09/Storing_data_in_the_FileMaker_/monkeybreadsoftware_blog_filemaker) - [Comparing Base Elements Plugin to MBS FileMaker Plugin](https://www.mbsplugins.de/archive/2017-10-24/Comparing_Base_Elements_Plugin/monkeybreadsoftware_blog_filemaker) - [ExecuteSQL and getting error details](https://www.mbsplugins.de/archive/2014-10-25/ExecuteSQL_and_getting_error_d/monkeybreadsoftware_blog_filemaker) - [MBS Filemaker Plugin, version 3.2pr2](https://www.mbsplugins.de/archive/2013-02-25/MBS_Filemaker_Plugin_version_3/monkeybreadsoftware_blog_filemaker) - [Create fields in Filemaker database from a Script](https://www.mbsplugins.de/archive/2013-02-24/Create_fields_in_Filemaker_dat/monkeybreadsoftware_blog_filemaker) - [MBS Filemaker Plugin, version 3.0pr2](https://www.mbsplugins.de/archive/2012-10-14/MBS_Filemaker_Plugin_version_3/monkeybreadsoftware_blog_filemaker) ### FileMaker Magazin - [Ausgabe 3/2018, Seite 34](https://filemaker-magazin.de/neuigkeit/3953-Appetithappen-FMM_201803) - [Ausgabe 2/2015, Seite 16](https://filemaker-magazin.de/neuigkeit/3798-Appetithappen-FMM_201502) This function is free to use. Created 18th August 2014 , last changed 27th April 2026 [FM.ExecuteSQL.LastError](FMExecuteSQLLastError.md) - [FM.ExecuteSQL.LastParameters](FMExecuteSQLLastParameters.md) [HTML Version](FMExecuteSQLLastErrorMessage.shtml)