Application Development


Chapter 32 . Writing Transaction Event Functions

A transaction manager event function replaces part of the functionality provided by a transaction model. You can write transaction event functions to:

In order to use a transaction manager event function, you need to:

This section explains how to write a simple event function, how to specify the appropriate return code, how to modify processing for SQL SELECT statements, and how to modify processing for SQL INSERT, UPDATE and DELETE operations.


The Nature of TM Event Functions

Transaction manager event functions can allow you to replace Panther's automatically generated SQL with your own, hand-coded SQL. The following example, a simple event function, executes a custom SQL statement:

proc simpleEventFunc (event)
if event == TM_VIEW || event == TM_SELECT
{
DBMS QUERY \
SELECT title_id, name, genre_code
call sm_tm_iset(TM_OCC_COUNT, @dmrowcount)
return TM_CHECK
}
return TM_PROCEED

To invoke the event function, the function property on the titles table view is set to simpleEventFunc. The function is called each time an event is processed for this table view. You can have an event function for each table view; however, for database queries, the event function must be specified in the first table view of a server view.

Since transaction event functions are passed one argument, specifically the transaction event, you need to identify which events you want to modify. In the simpleEventFunc function, the SQL statement replaces the processing for TM_VIEW or TM_SELECT events. For all other events, the return code TM_PROCEED tells the transaction manager to go ahead and call the transaction model, as if the event function had not been called, so that the transaction model's processing is performed.

However, the simpleEventFunc function specifies that if the event is TM_VIEW or TM_SELECT, the transaction manager uses the database driver's DBMS QUERY command to retrieve data from titles table. Calling sm_tm_iset sets the number of rows returned for this server view. The return code TM_CHECK tells the transaction manager to test for an error; the transaction manager can check for database errors from the SELECT statement.

Specifying a Return Code

Specifying the correct return code at the end of an event function tells the transaction manager what to do next. Table 32-1 summarizes possible return values.

Table 32-1 Return codes used by the transaction manager

Return value Means

TM_CHECK

Test to determine if an error occurred. (Used in data base-specific transaction models to check for SQL execution errors.)

TM_CHECK_ONE_ROW

Test to determine if an error occurred; event processing is considered successful only if exactly one row was affected.

TM_CHECK_SOME_ROWS

Test to determine if an error occurred; event processing is considered successful only if one or more rows were affected by the processing.

TM_FAILURE

Event processing failed.

TM_OK

Event processing succeeded. Further processing of the event is skipped.

TM_PROCEED

Proceed to the next step; after completing the event function, proceed as if the function was never called. In the case of a transaction manager event function, this means that the transaction model is called.

TM_UNSUPPORTED

Event was not recognized.

Specifying TM_PROCEED

TM_PROCEED tells the transaction manager to go ahead and call the transaction model as if the event function had not been called. In the following code example, the transaction model's processing is performed after the DBMS QUERY statement is executed.

if event == TM_VIEW || event == TM_SELECT
{
DBMS QUERY \
SELECT title_id, name, genre_code FROM titles
call sm_tm_iset(TM_OCC_COUNT, @dmrowcount)
return TM_PROCEED
}

The processing for these requests in the transaction models creates a select cursor, builds the structures to bring back data for all the members of the table view whose Use In Select (use_in_select) property is set to Yes, generates the SQL for the SELECT statement, and then executes the statement.

The processing for all the events takes place quickly, so you might not visually notice that both sets of processing are being performed unless you view the processing in the debugger and break on each transaction manager event.

If both an event function and a transaction model are called for a single event by using TM_PROCEED, the processing in the event function takes place before the processing in the model.

Specifying TM_OK

TM_OK is used when the processing is contained in itself; therefore, the transaction model is not required for processing and no checking for database errors is required. Since the following statement does not require any processing in the model, the TM_OK return code is a possible setting.

if event == TM_VIEW || event == TM_SELECT
{
DBMS QUERY \
SELECT title_id, name, genre_code FROM titles
call sm_tm_iset(TM_OCC_COUNT, @dmrowcount)
return TM_OK
}

Checking for Database Errors

Even though TM_OK does not check for database errors, the transaction models set TM_STATUS to -1 whenever the database error handler returns an error. If the statement causes the database error handler to be invoked, for example by specifying an invalid column name, the database error handler returns an error. Then, the transaction manager returns an error that the transaction model or event function had an error.

Three return codes are designed specifically for checking for errors from the database engine and from Panther's database drivers: TM_CHECK, TM_CHECK_ONE_ROW, and TM_CHECK_SOME_ROWS.

Specifying TM_FAILURE

TM_FAILURE pushes the TM_NOTE_FAILURE event onto the event stack. The type of error message displayed by the transaction manager depends on the value of various transaction manager variables, like TM_STATUS or TM_EMSG_USED. For more information, refer to "Processing Errors in the Transaction Manager."

Performing Error Checking

In the following event function, checkingEvent, the variant does its own checking for errors. It is only checking the @dmretcode, which would also be checked when TM_CHECK is returned. When checking for errors from database access, it is always important to check @dmretcode.

proc checkingEvent( event )
if event == TM_SEL_BUILD_PERFORM
{
DBMS DECLARE :@tm_sel_cursor CURSOR FOR \
SELECT actor_id, first_name, last_name \
FROM actors \
WHERE actor_id = ::actor_id
DBMS WITH CURSOR :@tm_sel_cursor EXECUTE USING actor_id
if @dmretcode != 0 return TM_FAILURE
call sm_tm_iset(TM_OCC_COUNT, @dmrowcount)
return TM_OK
}
return TM_PROCEED

Unsupported Events

The database-specific transaction models use TM_UNSUPPORTED to indicate that the event is not supported in the transaction model. This is important to note in case you add transaction events to the model. This return code pushes the TM_NOTE_UNSUPPORTED event onto the event stack.

Modifying SELECT Statement Processing

The following slices are generated for TM_VIEW and TM_SELECT events:

Replacing a SQL SELECT Statement

The following event function, myEvent, uses the transaction model's cursor management and error reporting capabilities. The variable @tm_sel_cursor contains the name of the cursor to be used to execute the SQL SELECT statement.

proc myEvent (event)
if event == TM_SEL_BUILD_PERFORM
{
DBMS DECLARE :@tm_sel_cursor CURSOR FOR \
SELECT title_id, name, genre_code \
FROM titles \
WHERE title_id = ::title_id
DBMS WITH CURSOR :@tm_sel_cursor EXECUTE USING title_id
call sm_tm_iset(TM_OCC_COUNT, @dmrowcount)
return TM_CHECK
}
return TM_PROCEED

The event function is called for the TM_SEL_BUILD_PERFORM event. This event builds and executes the SQL statements. Since this slice is generated for both TM_SELECT and TM_VIEW requests, the event function is called for either request.

In some cases, you might want to check for errors by writing error checking code within the event function. In that case, you should return TM_FAILURE if an error is encountered, and TM_OK if there is no error.

The myEvent function permits the TM_SEL_GEN slice to be handled by the transaction model, which (in the case of the database-specific transaction models) has Panther build unneeded data structures. For slightly better performance, that slice could be skipped as follows:

proc fasterEvent (event)
if event == TM_SEL_BUILD_PERFORM
{
DBMS DECLARE :@tm_sel_cursor CURSOR FOR \
SELECT title_id, name, genre_code \
FROM titles \
WHERE title_id = ::title_id
DBMS WITH CURSOR :@tm_sel_cursor EXECUTE USING title_id
call sm_tm_iset(TM_OCC_COUNT, @dmrowcount)
return TM_CHECK
}
if event == TM_SEL_GEN
return TM_OK
return TM_PROCEED

Modifying SQL Generation

In addition to writing event functions which replace the SQL SELECT statement, you can also write event functions to modify the automatic SQL generation. Use one or more of the following C functions that are prototyped in tmusubs.h:

A sample event function which adds a column and its corresponding table to the SELECT statement is shown below:

proc titlesEvent(event)
vars retval(5)

if (event == TM_SEL_BUILD_PERFORM)
{
retval = dm_gen_change_select_list("", "name", "name", \
DM_GEN_APPEND)

retval = dm_gen_change_select_from \
("", "titles", "titles", DM_GEN_APPEND)

if (retval != 0)
return TM_FAILURE
}
return TM_PROCEED

Replacing Other SQL Statements

SQL INSERT, UPDATE, and DELETE statements are generated as part of the processing of the transaction manager SAVE command, but only if data is modified or new data is entered. In total, the transaction manager can generate up to six types of requests when processing a SAVE command. They are generated in the following order:

TM_DELETE is generated before TM_UPDATE and TM_INSERT in order to prevent duplicate record errors. This is also why TM_UPDATE is generated before TM_INSERT. Also, a single cursor is used for all SAVE operations. This permits all SAVE operations to be part of the same database transaction.

The transaction models further slice the TM_DELETE, TM_UPDATE, and TM_INSERT requests. However, the slicing is performed only if slices are needed. For example, when a row is inserted, no slices are generated for the TM_DELETE request. The three slices for each of these requests are:

Supplying custom INSERT, UPDATE, and DELETE statements should normally be done in the TM_request_DECLARE events, since they occur only when a new cursor must be declared (which can be a somewhat expensive operation, depending on the database).

The following event function provides custom SQL INSERT, UPDATE, and DELETE statements:

proc saveEvent( event )
if ( event == TM_DELETE_DECLARE )
{
DBMS DECLARE :@tm_save_cursor CURSOR FOR \
DELETE FROM actors WHERE actor_id=::w_actor_id
return TM_CHECK
}
if ( event == TM_UPDATE_DECLARE )
{
DBMS DECLARE :@tm_save_cursor CURSOR FOR \
UPDATE actors SET first_name=::s_first_name, \
last_name=::s_last_name \
WHERE actor_id=::w_actor_id
return TM_CHECK
}
if ( event == TM_INSERT_DECLARE )
{
DBMS DECLARE :@tm_save_cursor CURSOR FOR \
INSERT INTO actors (actor_id, first_name, last_name)\
VALUES(::v_actor_id, ::v_first_name, ::v_last_name)\
WHERE actor_id=::w_actor_id
return TM_CHECK
}
return TM_PROCEED

The variable @tm_save_cursor contains the name of the cursor to be used to perform the save operations. During the handling of these events, the transaction models execute the cursor whose name is stored in @tm_save_cursor.

In the execution of the cursor, the bind variables (for example,::v_first_name) are matched to actual data by assuming that the bind variable name is the column name preceded by a prefix, as follows:

Use Prefix Example

WHERE clause

w_

w_actor_id

SET clause

s_

s_actor_id

VALUES clause

v_

v_actor_id

For information about how the bind variables are used in SQL generation, refer to "Viewing the SQL Statements."