When SQLError

The WhenSqlError construct cannot be fully translated to any object oriented construct. It is a strange SAL feature, it is similar to a try/catch block but it behaves like an inner function. Code inside the When Sql Error block is executed when a Sql function causes a Sql error, it has access to all the automatic variables (locals and parameters) but the return statement returns from the Sql function that triggered the error.

We translate this peculiar feature in two ways: 1) to a try/catch block; 2) to a function delegate. Both methods are described below:

Try/Catch Block translation

This type of translation requires a developer's manual intervention to ensure that the code logic is not broken. It results however in "cleaner" code.

The code at the same level as the WhenSqlError statement is placed in the try block. Code inside the WhenSqlError statement is placed in the catch block. However, this changes the code execution path since the return statement in the catch block returns from the function and not the Sql statement. Therefore we also log a warning and a TODO comment (in the code as well) to let the developer check and re-arrange the code if necessary.

Example:

 When SqlError
         Call MySqlErrorHandler(hSql)
         Return FALSE
 If Not SqlPrepareAndExecute(hSql, sSql)
         Return FALSE

Is Translated to:

using (new WhenSqlError())
{
  try
  {
    if (!Sql.PrepareAndExecute(hSql, sSql))
      return false;
  }
  catch (SalSqlError)
  {
    MySqlErrorHandler(hSql);
      return false;
  }
}

There is a problem when the original code continues execution after a Sql function has failed either because the code does not check the return value or because the When SqlError block returns true, or when their is code executed when the Sql functions returns false. In this case, the code that follows the Sql function should be moved after the try/catch block.

Basically each and every WhenSqlError statement should be carefully evaluated and adapted by hand. It is advisable to place all the error processing in the catch block to make it more readable and compliant with the standard structured exceptions technique.

Additionally, since there is no way at runtime to know that a piece of code is within a try/catch block, the code is also enclosed in a context block using the "using (new WhenSqlError())" constructs. When a sql error occurs within a WhenSqlError block the PPJ Framework "knows" not to dispatch it to the global SalApplication.OnSqlError() event handler, which is the replacement for the SAM_SqlError message.

Example of wrong code:

 When SqlError
         Call MySqlErrorHandler(hSql)
         Return FALSE
 If Not SqlPrepareAndExecute(hSql, sSql)
         Call Sql.Disconnect(hSql)
         Call DoSomethingElse()

Is Translated to:

using (new WhenSqlError())
{
  try
  {
    if (!Sql.PrepareAndExecute(hSql, sSql))
    {
      Sql.Disconnect(ref hSql);
    }
    DoSomethingElse();
  }
  catch (SalSqlError)
  {
    MySqlErrorHandler(hSql);
    return false;
  }
}

If an error occurs, the code in the catch block is executed but the return statement exits the function, therefore the code at lines 5 and 7 is never executed.

The code above should be changed manually to:

try
{
  Sql.PrepareAndExecute(hSql, sSql);
}
catch (SalSqlError)
{
  MySqlErrorHandler(hSql);
  Sql.Disconnect(ref hSql);
}
DoSomethingElse();

Function Delegate translation

This type of translation doesn't require any manual intervention and the original code logic is left intact. This option should be used in conjunction with the local bind variables generation to allow the WhenSqlError function delegate to access a function's local variables.

The code inside a WhenSqlError block is generated inside a new function named WhenSqlError#, where # is a unique sequential number starting from 1. A function delegate is created at the same level the original WhenSqlError block was and it is passed to all the Sql functions at the same level. All Sql functions have an overloaded version with an additional last parameter called errorHandler.

Example:

When SqlError
  Call MySqlErrorHandler(hSql)
  Return FALSE
If Not SqlPrepareAndExecute(hSql, sSql)
  Return FALSE

Is Translated to:

private SalBoolean WhenSqlError1(SalSqlHandle hSql)
{
  MySqlErrorHandler(hSql);
  return false;
}
...
WhenSqlErrorHandler sqlErrorHandler1 = new WhenSqlErrorHandler(this.WhenSqlError1);
if (!Sql.PrepareAndExecute(hSql, sSql, sqlErrorHandler1))
  return false;

The code above works exactly as expected in the original application and doesn't require any manual intervention.