Application Development

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

• Modify generated SQL.

• Supply hand-coded SQL or stored procedure calls to replace generated SQL.

• Modify or query properties using the property API.

• Change error handling.

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

• Write a function which includes:

  • The event whose functionality you want to modify or replace.

  • Processing to be added to the event.

  • Return codes which tells the transaction manager how to proceed.

• Install the event function by:

  • Setting the table view's Function property to the name of the JPL module containing the function.

  • Modifying the transaction model.

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.

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
}

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.

• TM_CHECK—Used to check for any error from Panther's database drivers. If one is found, the transaction manager displays a message to that effect. The event TM_TEST_ERRORS is pushed onto the event stack for this return code. This is the best return code for the sample statement.

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

• TM_CHECK_ONE_ROW—Pushes the TM_TEST_ONE_ROW event onto the event stack. This event checks the value of @dmrowcount (global variable) to make sure its value is equal to 1. This return code is used following SQL statements that modify the database to make sure that only one row was affected.

• TM_CHECK_SOME_ROWS—Pushes the TM_TEST_SOME_ROW event onto the event stack. This event checks the value of @dmrowcount to make sure its value is greater than or equal to 1. This can be used to make sure that SQL SELECT statements return rows from the database.

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

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:

• TM_GET_SEL_CURSOR—Allocates a Panther cursor for use by the SELECT statement. Depending on the database, a Panther cursor may or may not correspond to a database cursor.

• TM_PREPARE_CONTINUE—Checks the value of the Fetch Directions property for the table view.

• TM_SEL_GEN—Generates data structures that are used to build the SQL statements needed to view the data. This slice and the next slice are separated to enable "tweaking" of the SQL that is about to be built.

• TM_SEL_BUILD_PERFORM—Builds and executes the SQL statements needed to view the data. Uses the Panther cursor allocated in the TM_GET_SEL_CURSOR slice event.

• TM_SEL_CHECK—Check to determine whether to give up the Panther cursor allocated in the TM_GET_SEL_CURSOR step. This cursor is given up here only if the select set is exhausted.

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

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:

• dm_gen_change_execute_using—Modifies the bind parameters in the EXECUTE USING statement.

• dm_gen_change_select_from—Modifies the table list in a SQL SELECT statement.

• dm_gen_change_select_group_by—Modifies the GROUP BY clause in a SQL SELECT statement.

• dm_gen_change_select_having—Modifies the HAVING clause in a SQL SELECT statement.

• dm_gen_change_select_list—Modifies the select list in a SQL SELECT statement.

• dm_gen_change_select_order_by—Modifies the ORDER BY clause in a SQL SELECT statement.

• dm_gen_change_select_suffix—Adds the specified text to the end of a SQL SELECT statement.

• dm_gen_change_select_where—Modifies the WHERE clause in a SQL SELECT statement.

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

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_PRE_SAVE—Indicates that save processing has started.

• TM_SAVE—The transaction models do nothing.

• TM_DELETE—Transaction models generate SQL DELETE statements for records to be deleted; one per modified record.

• TM_UPDATE—Transaction models generate SQL UPDATE statements for records to be updated; one per modified record. Note that changing the primary key is implemented by deleting the record with the old value and inserting a record with the new value.

• TM_INSERT—Transaction models generate SQL INSERT statements for records to be updated; one per entered record.

• TM_POST_SAVE—Transaction models do commit and rollback processing here. Rollback processing occurs if there was an error in the saving process. Commit processing occurs only for full implementations of the SAVE command, not partials.

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:

• TM_GET_SAVE_CURSOR—Generated only if this is the first TM_INSERT, TM_UPDATE, or TM_DELETE event for the SAVE command. Allocate a cursor for use as the save cursor, and, if needed by the database, begin a database transaction.

• TM_request_DECLARE—Generates the SQL statement and uses the generated statement in the declaration of the cursor. The processing of this slice avoids cursor re-declaration if the proper SQL statement is already declared.

• TM_request_EXEC—Executes the declared cursor.

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:

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