When SQLError

Use the SQL error-flow walkthrough to compare delegate handling, an early-return catch, and an explicit failure continuation.

SAL When SqlError is a scoped SQL error handler. It can access locals and parameters, but its return controls the SQL operation that raised the error rather than returning from the enclosing application function. That distinction matters when translating it.

Ice Porter supports two translation strategies: 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 there 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 SqlDisconnect(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 the SQL call throws, the catch records the error and returns from the enclosing method. The disconnect and failure continuation are skipped. The shown translation also moves DoSomethingElse() outside the failure branch, making it run after success; that indentation change is another behavior defect.

A correction must preserve both the local error context and the intended continuation. For the source example, cleanup and DoSomethingElse belong on the failure path:

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

Choose the continuation from the original business logic; moving code outside the catch unconditionally can make it run after a successful operation too. Transaction rollback and ownership of shared handles require separate review.

Function Delegate translation

This translation is designed to preserve the scoped handler's return behavior. It still requires testing of error paths and local-variable access. 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. The generated code uses the SQL overload that accepts an errorHandler delegate; check the overload of the specific operation when editing this code by hand.

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;

Verify the result returned by the SQL call, continuation after failure, cleanup, and transaction outcome against the original application.

Try/Catch Block translation​

Function Delegate translation​

Try/Catch Block translation

Function Delegate translation