Application Development

You can write the SQL statements needed to access the database for the entire application or for a single screen in the application.

Database Development Process

For database applications where you write the SQL statements, the following steps outline a possible application development process:

Database Interface Commands

Two database interface commands can be used to construct your own SQL statements: DBMS QUERY and DBMS RUN. DBMS QUERY is used for SQL SELECT statements and for stored procedures that return result set data to the application. For more information on fetching database information, refer to Chapter 29, "Reading Information from the Database."

DBMS RUN is used for data modification statements, such as SQL UPDATE, that do not return data to the application. For more information on writing information to a database, refer to Chapter 30, "Writing Information to the Database."

Using Database Cursors

A cursor is a SQL object associated with a specific query or operation. Panther stores information on each cursor, including:

• The cursor's name.

• The cursor's connection.

• Any cursor attributes assigned with the following DBMS commands: ALIAS, CATQUERY, COLUMN_NAMES, FORMAT, OCCUR, START, STORE, and UNIQUE.

• Other operation-specific information (for example, the number of rows to fetch, information on target variables or binding parameters, etc.).

Every connection has one or two default cursors which Panther automatically creates. Your application can also declare named cursors on a connection; Panther can use either or both types.

DBMS commands are provided for changing the default behavior for a cursor associated with a SELECT statement. The commands are ALIAS, CATQUERY, COLUMN_NAMES, FORMAT, OCCUR, START, and UNIQUE. For descriptions of these commands, refer to Chapter 11, "DBMS Statements and Commands," in Programming Guide.

This section describes the use of cursors, default and named cursors, in an application. For information on how data are passed between an application and a database, refer to Chapter 29, "Reading Information from the Database," and Chapter 30, "Writing Information to the Database."

Using a Default Cursor

Default cursors are convenient for SQL statements that are executed once, and for applications using only one select set at a time. Database commands executed with the JPL commands DBMS QUERY, DBMS RUN, and DBMS SQL use default cursors unless a different cursor is specified.

For most engines, Panther automatically declares two default cursors—one for SQL SELECT statements and one for non-SELECT statements (such as UPDATE). In a few cases, where the engine's standard is a single default cursor, Panther adheres to that standard and declares one default cursor. On such engines, an additional option, CURSORS, is supported in the engine's DECLARE CONNECTION statement. It permits you to choose between one or two default cursors for the connection. For more information on how cursors are handled for each engine, refer to Database Drivers.

A default SELECT cursor is associated with a particular connection, namely the connection in effect when a SELECT statement is executed. For example:

DBMS CONNECTION c2

The first statement sets c2 as the default connection. The second statement uses WITH CONNECTION to set c1 as the current connection for the SELECT statement. In the UPDATE statement, no connection is specified. Therefore, Panther uses the default connection c2.

An application can also close the default cursor if it is not needed. For more information, refer to "Closing a Cursor."

Using a Named Cursor

Named cursors are convenient for SQL statements that are executed several times. A cursor is declared for a statement; executing the cursor, executes the statement. Named cursors often improve an application's efficiency because the same statement does not need parsing each time it is executed. Named cursors are also necessary for applications using more than one select set at a time.

When a cursor is declared, Panther creates a structure for it and adds its name to a list of open cursors. The cursor is available throughout the application until the application closes the cursor or closes the cursor's connection. Panther frees the structure when the cursor is closed.

You can create one or more named cursors to access and manipulate data. The sequence is as follows:

• Declare one or more named cursors.

• Execute cursor.

• Close cursor.

Declaring a Cursor

Named cursors are created with a declaration statement. The statement names the cursor and associates it with a connection and a SQL statement. If a connection is not named in the declaration, Panther uses the default connection.

DBMS [ WITH CONNECTION connectionName ] \
	DECLARE cursorName CURSOR FOR SQL_statement

An application can declare a named cursor for any valid SQL statement. For example:

DBMS DECLARE c1 CURSOR FOR SELECT * FROM rentals

The SQL statement is not executed until the cursor is executed:

DBMS WITH CURSOR c1 EXECUTE

The cursor can be executed any number of times. The name of the cursor must be a valid Panther identifier. The cursor name is case-sensitive, so CUR1 and cur1 are two distinct names.

For more information on writing data to the database and using parameters in a cursor declaration, refer to "Using Parameters in a Cursor Declaration."

Supplying Values Using Colon Expansion

A cursor can use colon-variables in the DECLARE CURSOR statement. For example:

DBMS DECLARE c1 CURSOR FOR \
	SELECT * FROM rentals WHERE rental_date = :+today

The variable today is dereferenced when the cursor is declared. It is not dereferenced when the cursor is executed. An application can use colon variables or colon-plus variables anywhere in the statement.

Supplying Values Using Binding

To dereference variables each time the cursor is executed, use bind tags in the DECLARE CURSOR statement. For example:

DBMS DECLARE c1 CURSOR FOR \
	SELECT * FROM rentals WHERE rental_date = ::rental_date

The bind tag is two colons followed by any valid identifier; the bind tag is not an actual variable. When the cursor is executed, the application must provide a literal value, a valid variable name, or a Panther expression for each bind tag. When this particular example is executed, the application fetches all rentals where rental_date is the value of today. To execute the select again where rental_date is another value, change the contents of today and reexecute the cursor:

today = @date(today) - 1
DBMS WITH CURSOR c1 EXECUTE USING rental_date = today

You can supply a new variable for the bind tag:

DBMS WITH CURSOR c1 EXECUTE USING rental_date = yesterday

Literals and expressions are valid values for a bind tag. For example:

DBMS DECLARE c1 CURSOR FOR \
	SELECT * FROM titles WHERE title LIKE ::title_qbe

or

DBMS WITH CURSOR c1 EXECUTE USING \
	title_qbe = title_val ## "%"

The first example supplies the literal "Citizen Kane" as the value for the bind tag. The second example uses the concatenation operator (##) to append the contents of the title_val variable with the literal percent sign (%) as the value for the bind tag.

It is not required to supply the bind tag names in the EXECUTE USING statement. If the tag names are not supplied, Panther associates the first variable with the first tag, the second variable with the second tag, etc. For example:

DBMS DECLARE c1 CURSOR FOR \
	SELECT * FROM customers \
	WHERE first_name LIKE ::first_qbe \
	AND last_name LIKE ::last_qbe

Panther uses the contents of f1 as the value for bind tag ::first_qbe and uses the contents of f2 as the value for bind tag ::last_qbe.

A bind tag is valid for any column value in a DECLARE CURSOR statement. A bind tag is not permitted for SQL keywords, table names, or columns names. Therefore, bind tags are valid for column values in any of the following:

• WHERE clause of SELECT, UPDATE, and DELETE statements.

• SET clause of UPDATE statements.

• VALUES clause of INSERT statements.

For example:

DBMS DECLARE c1 CURSOR FOR \
	UPDATE pricecats SET price = ::newprice \
	WHERE pricecat = ::pricecat

Bind tags are also valid for stored procedure parameter values.

Executing a Cursor with Multiple Connections

The command DBMS EXECUTE does not permit the WITH CONNECTION clause. The cursor remains associated with the connection specified by name or by default in the DECLARE statement. For example:

DBMS CONNECTION sybcon

When cursor cur1 is declared, Panther associates it with the default connection sybcon. Although the default connection is changed to oracon before the cursor is executed, the connection associated with cur1 does not change. When the cursor is executed, Panther performs the SELECT on connection sybcon. The default connection oracon performs the subsequent UPDATE.

Modifying a Cursor

A cursor can be redeclared on the same connection for another SQL statement. For example:

DBMS DECLARE abc CURSOR FOR \
	SELECT cust_id, title_id FROM rentals \
	WHERE return_date IS NULL
DBMS WITH CURSOR abc EXECUTE

DBMS DECLARE abc CURSOR FOR \
	SELECT * FROM titles WHERE title_id = ::title_num
DBMS WITH CURSOR abc EXECUTE USING title_num

If the cursor is associated with a SQL SELECT statement, you can modify its behavior by using additional DBMS commands These commands include ALIAS, CATQUERY which can be used with FORMAT, COLUMN_NAMES, OCCUR, START, and UNIQUE. Refer to Chapter 11, "DBMS Statements and Commands," in the Programming Guide for more information about each command. These settings are not lost when a cursor is redeclared, but only when the cursor is closed. A cursor cannot be redeclared for a different connection.

Using Cursors in the Transaction Manager

Generally, the transaction manager declares and closes cursors as needed. Once the transaction manager creates the select cursor during a TM_GET_SEL_CURSOR event, the variable @tm_sel_cursor contains the name of the select cursor. Using this variable, you can write a transaction event function to declare the cursor and to execute any additional processing. Then, subsequent transaction events attach the SQL statement by redeclaring the cursor.

In the following example, the make_cursor event function declares the cursor and sets a variable to hold select results with the DBMS CATQUERY command. Then, if you choose the VIEW or SELECT command in the transaction manager, this event function is called and is followed by the transaction events that redeclare and execute the cursor with the applicable SQL statement, writing the select results to the title_all variable.

In addition, the RELEASE command gives up cursors in the transaction manager.

proc make_cursor (event)
if event == TM_SEL_BUILD_PERFORM
{
	DBMS DECLARE :@tm_sel_cursor CURSOR
	DBMS WITH CURSOR :@tm_sel_cursor CATQUERY title_all 
	return TM_PROCEED
}
return TM_PROCEED

Cursors are closed when the application closes the connection. However, if you want to reuse a named cursor, redeclare a cursor on a different connection or free the resources needed by the cursor, you must close the cursor.

To close a cursor and free its data structure, execute:

The default cursor remains closed unless the application executes a DBMS QUERY, DBMS RUN or DBMS SQL statement without specifying a cursor using the WITH CURSOR clause. Panther automatically reopens the default cursor if it is needed.

Database Transaction

A database transaction is a logical unit of work on a database. The unit of work is usually a set of statements that update a database in a consistent way. Either all of the statements in the unit must be completed or none of the statements should be completed at all.

In the VideoBiz sample application, there are a least two transactions:

• A video rental transaction—The first statement in the transaction inserts a row into the rentals table supplying a customer identification code, title code, copy number, rental date, and due date. The second statement of the transaction, updates the customers table for the rental amount and the number of rentals. The third statement updates the tapes table by increasing the number of times the tape (increment of 1) has been rented and changing the status of the rental.

• A new video transaction—Inserts data into four tables: titles, tapes, title_dscr, and roles. The insert into the titles table supplies the name, title code, director information, film length, price category, and film type code. Multiple inserts into the tapes table enters information about each copy of the video. Multiple inserts into the title_dscr table store the film description. Multiple inserts are made into the roles table, each one supplying an actor code and a role for the new video.

Transaction processing is engine dependent and requires an understanding of the engine's behavior. For some errors, the application must explicitly tell the engine to undo the transaction. The application must test for these errors.

Engine-Specific Behavior

Transaction processing is not implemented consistently among SQL databases. Review the documentation on transaction processing supplied by your database vendor.

Database transaction processing, in general, occurs when changes are being made to a database. The transaction begins (with a DBMS BEGIN statement), and the changes are not permanently effective until the transaction is committed (with a DBMS COMMIT statement). The alternative to committing a transaction is rolling it back (with a DBMS ROLLBACK statement), which essentially throws away the changes. Usually, database engines support either explicit transactions, as described here, or auto transactions, which generally start with the first recoverable statement after a logon, COMMIT, or ROLLBACK.

On engines supporting explicit transactions, each COMMIT or ROLLBACK must have a matching BEGIN. On engines supporting autocommit modes, the application can use any number of COMMIT or ROLLBACK statements; if there is no recoverable statement, the COMMIT or ROLLBACK is ignored.

Engines have different ways of handling transactions that are not terminated by an explicit commit or rollback. Some automatically commit or rollback the transaction; others can leave the database in an inconsistent state. Under no circumstances should your application use the engine's default behavior to terminate a transaction.

Use explicit rollbacks and commits to:

• Protect the integrity of the database.

• Make new and updated data available to the rest of the application and other users at the logical end of the transaction.

• Release locks set on tables by the transaction which would otherwise be held until the connection closes, permitting the rest of the application and other users to begin new transactions on the tables.

• Reduce the chances for unrelated operations to interfere with one another.

• Produce applications which are more database-independent.

Finally, although vendors supply commands for transaction processing in their SQL language, use DBMS COMMIT, DBMS ROLLBACK, and other transaction commands provided with Panther database drivers. Using DBMS RUN to specify engine-specific commit and rollback processing is not recommended. Using the DBMS versions permits Panther to establish necessary structures and it provides better error handling if a transaction fails.

Error Processing for a Transaction

There are various kinds of errors that can occur during an application. The engine is responsible for recovery from system failures such as power loss. Also, if a single statement fails for some reason in the middle of execution, the engine is responsible for rolling back the effects of that statement. If that statement was executed in a transaction, however, the application must execute an explicit rollback to undo any work done between the start of the transaction and the failed statement.

At the very least, a Panther application must execute a rollback when the engine returns an error to the application. An example of this would be when the engine rejects an insert because the row's primary key is not unique. If the insert were part of a transaction, the application should stop executing the transaction and execute a rollback to undo any work done by previous statements in the transaction.

As an additional precaution, it is recommended that you execute a rollback for any error that occurs during the transaction, including an error detected by Panther before a statement is passed to the engine. An error detected by Panther rather than the engine is usually the result of a development or maintenance error rather than bad user input (for example, a statement's colon-plus or binding variable cannot be found because a widget was renamed). While these errors are rare, the application should provide handling for them.

If the transaction processing is done with the C library functions provided by Panther's database drivers, error codes from Panther are returned to the calling function, either directly or via an installed error handler. If a transaction requires very sophisticated error handling, it might be easier to use these Panther library functions rather than JPL.

One method for transaction processing in JPL uses a generic JPL procedure as a transaction handler. This JPL procedure could perform the following:

• Define and declare a JPL variable, jpl_retcode.

• Call a JPL subroutine that contains the actual transaction statements.

• On return from the subroutine, examine the JPL variable, jpl_retcode. If it is 0, the subroutine, and therefore the transaction, executed successfully. If it is not zero, the subroutine was aborted by a Panther or by the error handler. For either type of error, it executes a rollback.

A sample of such a procedure is shown in the JPL code below. The actual transaction statements are executed in the subroutine whose name is passed to this procedure. This transaction handler can be used with the default error handler or with an installed error handler that returns the abort code (1) for all errors.

proc tran_handle (subroutine)
{
	vars jpl_retcode

# Call the subroutine.
	jpl_retcode = :subroutine

# Check the value of jpl_retcode. If it is 0, all
# statements in the subroutine executed successfully
# and the transaction was committed. If it is 1,
# the error handler aborted the subroutine. If it
# is -1, Panther aborted the subroutine. Execute a
# ROLLBACK for all non-zero return codes.

	if jpl_retcode
	{
		msg emsg "Aborting transaction."
		DBMS ROLLBACK
	}
	else
	{
		msg emsg "Transaction succeeded."
	}
	return 0
}

In this application, there are JPL procedures containing transactions which update the database. The new_cust procedure adds a new customer to the database:

proc new_cust()
{
	DBMS RUN INSERT INTO customers ....
	DBMS COMMIT
	return 0
}

To execute this new customer transaction, the application should execute the following JPL statements:

vars newCust = "new_cust()"
call tran_handle (newCust)

Once tran_handle has set up the variable, it calls the procedure new_cust. Whether new_cust is successful or unsuccessful, control is always returned to tran_handle.

Refer to the Database Drivers for a list and description of the supported transaction commands for each engine.