Door 13 The last SQL

In Door 8, we learned about error handling and were able to use the FM.ExecuteSQL.LastError and FM.ExecuteSQL.LastErrorMessage functions to read the error code and error description provided by the last SQL query with FileMaker. We can display such an error in a dialog box, for example. Often, you may not even know which SQL query was involved. But how can we find out? The FM.ExecuteSQL.LastSQL function helps us with this. It provides you with the last SQL command as run by the plugin.

We can now write this script to query the error in the SQL query and at the same time output the SQL query itself.

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 ]
	Set Variable [ $errorcode ; Value: MBS("FM.ExecuteSQL.LastError") ]
	Set Variable [ $errortext ; Value: MBS("FM.ExecuteSQL.LastErrorMessage") ]
	Set Variable [ $SQLQuery ; Value: MBS("FM.ExecuteSQL.LastSQL") ]
	Show Custom Dialog [ "Error" ; "Last SQL Query: " &$SQLQuery & 
	   "¶¶Errorcode: " &  $errorcode & "¶¶Error text: " &  $errortext ]
End If

Have fun figuring out your SQL queries.