SQL error checking improved

SQL error checking improved

We have various FMSQL functions in MBS FileMaker Plugin for years. You can execute some SQL command and pass parameters with FM.ExecuteFileSQL 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 functions, you can query details:

Last error code

The FM.ExecuteSQL.LastError 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"; ""; sql);
    errorCode = MBS( "FM.ExecuteSQL.LastError" )
]; errorCode)

Example result: 8309

Last error message

The FM.ExecuteSQL.LastErrorMessage 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"; ""; sql);
    errorCode = MBS( "FM.ExecuteSQL.LastError" );
    errorMessage = MBS("FM.ExecuteSQL.LastErrorMessage")
]; errorCode & ": " & errorMessage)

Example result: 8309: ERROR: FQL0002/(1:14): The table named "Test" does not exist.

Last SQL statement

The FM.ExecuteSQL.LastSQL function provides the last SQL statement. This may be a statement created by our plugin for you, e.g. when using FM.InsertRecord function.

Let([
    sql = "SELECT FROM Test";
    r = MBS("FM.ExecuteFileSQL"; ""; sql);
    errorCode = MBS( "FM.ExecuteSQL.LastError" );
    lastSQL = MBS("FM.ExecuteSQL.LastSQL")
]; errorCode & ": " & lastSQL)

Example result: 8309: SELECT FROM Test

Last SQL parameters

The FM.ExecuteSQL.LastParameters 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"; ""; sql; 9; 13; "Joe"; 23);
    params = MBS("FM.ExecuteSQL.LastParameters")
]; params)

Example result:
["Joe", 23]

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"; ""; sql; 9; 13; "Joe"; 23);

    errorCode = MBS( "FM.ExecuteSQL.LastError" );
    errorMessage = MBS("FM.ExecuteSQL.LastErrorMessage");
    lastSQL = MBS("FM.ExecuteSQL.LastSQL");
    params = MBS("FM.ExecuteSQL.LastParameters");

    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.