# SQL error checking improved

**URL:** https://the.fmsoup.org/t/sql-error-checking-improved/5406
**Category:** MBS Plugins
**Tags:** sql, executesql
**Created:** [May 2, 2026, 5:50am UTC](https://the.fmsoup.org/t/sql-error-checking-improved/5406 "2026-05-02T05:50:28Z")
**Posts on this page:** 1
**Page:** 1

<div class="post-metadata">

### Author: ![MonkeybreadSoftware](https://yyz2.discourse-cdn.com/flex030/user_avatar/the.fmsoup.org/monkeybreadsoftware/32/361_2.png) [@MonkeybreadSoftware](https://the.fmsoup.org/u/MonkeybreadSoftware)
#### Post date: [May 2, 2026, 5:50am UTC](https://the.fmsoup.org/t/sql-error-checking-improved/5406/1 "2026-05-02T05:50:28Z")

</div>

## [SQL error checking improved](https://www.mbsplugins.de/archive/2026-04-30/SQL_error_checking_improved)

We have various [FMSQL](https://www.mbsplugins.eu/component_FMSQL.shtml) functions in [MBS FileMaker Plugin](https://www.monkeybreadsoftware.com/filemaker/) for years. You can execute some SQL command and pass parameters with [FM.ExecuteFileSQL](https://www.mbsplugins.eu/FMExecuteFileSQL.shtml) function. The function does the job and either returns OK or an error. But in case of the error, we like to log as much as possible.

After each call to our of our FileMaker [SQL](https://www.mbsplugins.eu/component_SQL.shtml) functions, you can query details:

### Last error code

The [FM.ExecuteSQL.LastError](https://www.mbsplugins.eu/FMExecuteSQLLastError.shtml) function provides the last error code. This is zero in case there is no error.

Like this little example where we log the error code:

Let([  
sql = "SELECT \* FROM Test";  
r = **MBS** ("[FM.ExecuteFileSQL](https://www.mbsplugins.de/archive/2026-04-30/FMExecuteFileSQL.shtml)"; ""; sql);  
errorCode = **MBS** ( "[FM.ExecuteSQL.LastError](https://www.mbsplugins.eu/FMExecuteSQLLastError.shtml)" )  
]; errorCode)  
  
 Example result: 8309

### Last error message

The [FM.ExecuteSQL.LastErrorMessage](https://www.mbsplugins.eu/FMExecuteSQLLastErrorMessage.shtml) function provides the last error message from the SQL engine. In our documentation we have a list of possible errors, e.g. FQL0001 and "There is an error in the syntax of the query". These errors come from the SQL engine and are always in english. Some contain placeholders for the incorrect value.

Let([  
sql = "SELECT \* FROM Test";  
r = **MBS** ("[FM.ExecuteFileSQL](https://www.mbsplugins.de/archive/2026-04-30/FMExecuteFileSQL.shtml)"; ""; sql);  
errorCode = **MBS** ( "[FM.ExecuteSQL.LastError](https://www.mbsplugins.de/archive/2026-04-30/FMExecuteSQLLastError.shtml)" );  
errorMessage = **MBS** ("[FM.ExecuteSQL.LastErrorMessage](https://www.mbsplugins.eu/FMExecuteSQLLastErrorMessage.shtml)")  
]; errorCode & ": " & errorMessage)  
  
 Example result: 8309: ERROR: FQL0002/(1:14): The table named "Test" does not exist.

### Last SQL statement

The [FM.ExecuteSQL.LastSQL](https://www.mbsplugins.eu/FMExecuteSQLLastSQL.shtml) function provides the last SQL statement. This may be a statement created by our plugin for you, e.g. when using [FM.InsertRecord](https://www.mbsplugins.eu/FMInsertRecord.shtml) function.

Let([  
sql = "SELECT \* FROM Test";  
r = **MBS** ("[FM.ExecuteFileSQL](https://www.mbsplugins.de/archive/2026-04-30/FMExecuteFileSQL.shtml)"; ""; sql);  
errorCode = **MBS** ( "[FM.ExecuteSQL.LastError](https://www.mbsplugins.de/archive/2026-04-30/FMExecuteSQLLastError.shtml)" );  
lastSQL = **MBS** ("[FM.ExecuteSQL.LastSQL](https://www.mbsplugins.eu/FMExecuteSQLLastSQL.shtml)")  
]; errorCode & ": " & lastSQL)  
  
 Example result: 8309: SELECT \* FROM Tes t

### Last SQL parameters

The [FM.ExecuteSQL.LastParameters](https://www.mbsplugins.eu/FMExecuteSQLLastParameters.shtml) function, new for v16.2, provides the parameters of the last call. This allows you to know what was the problem when you do multiple inserts or updates in one call.

We can try this with the following example:

Let([  
sql = "INSERT INTO Test (FirstName, Age) VALUES (?,?)";  
r = **MBS** ("[FM.ExecuteFileSQL](https://www.mbsplugins.de/archive/2026-04-30/FMExecuteFileSQL.shtml)"; ""; sql; 9; 13; "Joe"; 23);  
params = **MBS** ("[FM.ExecuteSQL.LastParameters](https://www.mbsplugins.eu/FMExecuteSQLLastParameters.shtml)")  
]; params)  
  
 Example result:  
["Joe", 2 3]

### Thread safe

The error status is saved within the plugin per thread.

In FileMaker Pro you always have one thread, but in FileMaker Server you have multiple threads running different scripts at the same time.

Running two scripts in parallel is a common thing and we don't want to confuse the error from one script with the one from another script.

Be aware, that a sub script has access to the error state of the calling script. Except for calling another script with Perform Script on Server of course.

### Error as JSON

Combine all the above and we can create one big JSON object with the SQL error status:

Let([  
sql = "INSERT INTO Test (FirstName, Age) VALUES (?,?)";  
r = **MBS** ("[FM.ExecuteFileSQL](https://www.mbsplugins.de/archive/2026-04-30/FMExecuteFileSQL.shtml)"; ""; sql; 9; 13; "Joe"; 23);

```
errorCode = **MBS** ( "[FM.ExecuteSQL.LastError](https://www.mbsplugins.de/archive/2026-04-30/FMExecuteSQLLastError.shtml)" );
errorMessage = **MBS** ("[FM.ExecuteSQL.LastErrorMessage](https://www.mbsplugins.de/archive/2026-04-30/FMExecuteSQLLastErrorMessage.shtml)");
lastSQL = **MBS** ("[FM.ExecuteSQL.LastSQL](https://www.mbsplugins.de/archive/2026-04-30/FMExecuteSQLLastSQL.shtml)");
params = **MBS** ("[FM.ExecuteSQL.LastParameters](https://www.mbsplugins.eu/FMExecuteSQLLastParameters.shtml)");

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 (?,?) "  
}

Please try this with your solution. Logging all errors is important, so the developer has a chance to improve the solution. The worst is always if an error goes unnoticed for a long time and you have inconsistencies in your database. Like you create two related records and one fails as you renamed a field.