Summary

This article describes ways to handle Connect SQL errors, and the creation of several flows and activities that "wrap" the four RDB methods. Use these techniques to make your connectors resilient.

Suggested Approach

Relational databases hold much of the world's structured data, and accessing and manipulating external databases is a key component of many Process Commander applications. Your application must handle successfully accessing data and detecting errors. Ideally the application can seamlessly recover from some of these errors, but error-handling logic can often consume a good chunk of your application development time.

This article describes interesting aspects of handling Rule-Connect-SQLerrors, and walks you through the creation of flows and activities that "wrap" the four RDB methods. The questions addressed in this article include:

• How do you know whether there was an error?
• How can you obtain and record detailed information about the error?
• How can your application gracefully recover from these errors?

How to Detect Errors

Process Commander provides two complementary mechanisms for determining if there has been an error:

• Transitions in activity steps, and
• The standard connector error handler flow (Work-.ConnectionProblem)

Transitions in Activity Steps
Each RDB method provides a mechanism to let you know if there were possible errors. The table below shows several methods:

A second way to determine if there has been an error is to leverage the Rule-Connect-SQLerror handler flow. Each tab of the Connect SQL rule form includes a field where you can enter the name of a flow that will be started when there is an error. This flow starts if:

• The Rdb method throws a database ConnectionException (Process Commander catches all database ConnectionException objects, then wraps them in a ResourceUnavailableExceptionbefore being re-thrown), and
• Your activity does not catch exceptions (including the ResourceUnavailableException) in a transition.

No other errors are caught by this mechanism.

Developers often like to have a single location for handling all types of errors. To follow this approach, you can catch all exceptions in a transition in the step that invokes your Rdb method and leave the Error Handler Flow field blank.

Gathering additional information

To obtain - and record - detailed information about each error, you can develop a MySqlErrorsPage to include return status information from the database software. Enter a line at the top of your SQL code in the format:

{SQLPage:MySqlErrorsPage}

As each SQL statement is executed, Process Commander creates the page that you specify (whether or not there are errors). For reasons that will become clear later, it's good practice to use one page name for all of your SQL statements - for example, MySqlErrorsPage.

In this page, the properties holding the error information are:

• pxRDBError- an error message from Process Commander.
• pxRDBSQLCode- a result code form the database access driver.
• pxRDBSQLVendorError1- an error code from the database vendor package.
• pxRDBSQLVendorError2- an error code from the database vendor package.
• pxRDBSQLVendorMessage1- an error message from the database vendor package.
• pxRDBSQVendorMessage2- an error message from the database vendor package.
• pxSQLStatementPre- the SQL statement as you entered it in the Connect SQL form.
• pxSQLStatementPost - the SQL you entered in the Connect SQL form tab with the references resolved to show their values, so you can verify the references.

A sample MySqlErrorsPage is shown in the table below.

It's a good idea to record any error information so that you can refer to it later. As a good practice, record this information in two places:

• Within your work object, and
• By writing entries to the Pega log file. By writing it to the log file, you're guaranteed to have access to the error information even if your work object becomes invalid - and thus won't save.

(The rest of this section assumes that the activities that you're using to access the external database are in your Work- class. If this is not the case, adjust the Applies To class of these rules accordingly). To do this, follow the steps and examples below:

STEP-1: SqlErrorList property
Create the SqlErrorList Page List property, of class Code-Pega-List.

STEP-2: RecordSqlErrorInformation activity
Create the RecordSqlErrorInformation activity to record information about any SQL errors using the example below as a guide.

(RecordSqlErrorInformation activity)

STEP-3: DoRdbList, DoRdbOpen, DoRdbSave, DoRdbDelete activities
Since you want to follow each invocation of an RDB method with a Transition and a possible call to the RecordSqlErrorInformation activity, it's helpful to create four activities that "wrap" the corresponding RDB methods.

Each activity has parameters that match those required by the corresponding RDB method. As with any called activity, if these activities encounter an error they will return the "worst error" to the caller, who can test for errors using StepStatusFail.

Here's an example of DoRdbList and the parameters:

Note: This technique does not support RunInParallel, and the steps that invoke the Rdb-List method catch all unhandled exceptions.
(DoRdbList Steps)

Recovering From Errors

There are two basic types of database errors:

• Those that will get better by themselves over time (transient errors), and
• More permanent errors that are typically due to an error in the SQL or the application logic.

FIrst, consider stale connections, or transient errors. If the database that you're trying to access has been restarted, most application servers (such as IBM WebSphere) report a "stale connection" to your application. This error will be passed to your application - and in the process this stale connection will be discarded from the database connection pool.

The next time that your application accesses the database, Process Commander obtains a new connection from the pool, and your application can now access this database.

You can enhance each of the wrapper activities so that they automatically try to recover from transient errors. Simply add a loop to the steps of DoRdbList that invoke the Rdb-List method.

(Adding a Loop )

Regarding longer-lasting errors, remember that error information is available during execution, so you can continue to refine your code to automatically recover from additional types of errors. Often, however, human intervention is required to repair the condition that caused the error. Either the database is offline, or there's an error in the SQL or the application logic.

In these cases, your application needs to:

• Suspend processing until the problem has been resolved, and
• Return control to the user (or send a response if your activity was invoked as part of a Process Commander service).

This is where you can leverage Process Commander's flow processing capabilities. Specifically,

• You can suspend processing by placing an assignment into an RdbErrors workbasket.
• If the database was offline, but later becomes available, an administrator can perform a single bulk operation on this workbasket to retry all of the errant database operations!
• If there's a programmatic error, once the error has been fixed an administrator can do a bulk retry.

To follow this through, you need the following:

• Some final modifications to the DoRdbList activity,
• A DoRdbList flow that wraps the DoRdbList activity and adds flow capabilities for error handling (this flow should be used every time that the application needs to do an Rdb-List),
• A PerformSqlError harness with a ShowSqlErrors section that will display the error information that has been stored in the work object, and
• A sample DoRdbListTester flow for testing (this simulates your real application).

Modifications to the DoRdbList activity

This activity needs to return a status that can be checked by a flow. Thus, add TaskStatus-Set methods to the Error and OK paths. The images below illustrate the process.
(DoRdbList activity)

The DoRdbList flow

(DoRdbList flow )

A PerformSqlError harness to show errors

(PerformSqlError harness)

The ShowSqlErrors section

(ShowSqlErrors section)

Adding an SLA to the RdbErrors assignment
At this point, you may find it tempting to include a service level rule that will perform a ResumeFlow along the RdbErrorRetry flow action. While this could be done, it is not recommended.

The types of errors that end up in this workbasket require human intervention to be corrected - either bringing the database online, or correcting an error in the SQL. Only when these corrections have been made will the retry be successful. Thus, the recommended approach for retrying is to perform bulk processing after the problem has been fixed - retrying all of the assignments in this workbasket.

A Sample DoRdbListTester flow
You should replace the OK and Rejected branches with processing that is appropriate to your application. Referring to the image below,

(Flow Example)

if the List produces an error when you run this flow, the user will see a Confirm form (in this example, the link is clickable for testing purposes only).

(Confirm form)

When an administrator looks at this assignment, they will see the SQL errors displayed something like this (depending upon the actual error)

(Errors Displayed)

Summary
The creation of flows and associated rules that wrap each RDB operation provide an effective, centralized mechanism for catching, documenting, and recovering from Connect SQL errors.

These subflows return error indicators to the calling flow. Thus, your application flow retains full control over how to respond to an error condition.

This technique can be extended to applications that are implemented as services. Whenever a wrapper flow detects a permanent error, a step can be added to create a Service Response that indicates that an RDB error has occurred. This extends the error reporting capabilities to all applications that invoke this service.