Door 8 Error handling

In Door 2, we saw that FileMaker itself has a function called ExecuteSQLe that can output error messages when errors occur in the process. If we have an error in the SQL query with the MBS functions, then instead of a result or a reference number, we receive an error message from the function that went wrong.

Set Variable [ $r ; Value: MBS( "FM.ExecuteFileSQL"; Get(FileName);
   "SELECT Nme, Launch FROM Movie WHERE Director=? OR Launch=? AND NOT Director=?";
   "-" ;  "¶"; "Chris Columbus"; 2000; "Greg Beeman" ) ]

In some cases, it would be nice to have a function that would allow you to simply query the error without having to look at the first characters of the output. The plugin provides two functions that can help you with this: FM.ExecuteSQL.LastError and FM.ExecuteSQL.LastErrorMessage. The FM.ExecuteSQL.LastError function gives us an error code for the last error in the SQL. The error code always refers to the last SQL query called. If everything went well with the SQL query, we get the error code 0 back. Otherwise, we receive the corresponding error code, and there are several of these. This error code can also be very useful if you need to make a case distinction, because if it is 0, you know that there was no SQL error in the function and can continue working with the result, or if an error occurred, you can terminate the script, for example.

Set Variable [ $r ; Value: MBS( "FM.ExecuteFileSQL"; Get(FileName);
   "SELECT Nme, Launch FROM Movie WHERE Director=? OR Launch=? AND NOT Director=?";   
   "-" ;  "¶"; "Chris Columbus"; 2000; "Greg Beeman" ) ]
If [ MBS("FM.ExecuteSQL.LastError")=0 ]
	# Do something
Else
	Exit Script [ Text Result:    ]
End If

The error code does not provide much assistance in resolving the error, or we need to know what this code is stand for. For this reason, there is the FM.ExecuteSQL.LastErrorMessage function, which displays the corresponding error message for the last SQL query. In this example, the text clearly indicates what is incorrect:

Set Variable [ $r ; Value: MBS( "FM.ExecuteFileSQL"; Get(FileName); 
   "SELECT Nme, Launch FROM Movie WHERE Director=? OR Launch=? AND NOT Director=?";
   "-" ;  "¶"; "Chris Columbus"; 2000; "Greg Beeman" ) ]
Set Variable [ $errorcode ; Value: MBS("FM.ExecuteSQL.LastError") ]
Set Variable [ $errortext ; Value: MBS("FM.ExecuteSQL.LastErrorMessage") ]
Show Custom Dialog [ "Error" ;  $errorcode & "-" & $errortext ]

We spelled the name of the field incorrectly. If we correct this, our SQL will be correct again and we will not get an error message.

That's it for today on error handling. I hope you enjoyed it and we'll see each other again tomorrow.