| Web Development | |
Part of a Panther application is its database interface. Due to the stateless nature of the Web, the database processing in a Web application might not mirror the processing in other two-tier, client/server models. This chapter briefly describes the following topics that are necessary in building a database application:
Connecting to the Database |  |
Database connections are handled differently in two-tier and three-tier applications. In two-tier client/server applications, the Web application server handles database connections as part of its jserver startup. In three-tier applications, the Panther application server maintains the database connections. This section describes the procedures required by two-tier applications.
In two-tier Panther Web applications, connections to the database are usually declared in the web_startup procedure. Generally, the web_startup procedure is part of a JPL module specified in the application variable SMINITJPL. When the Web application server starts a jserver process, it initializes the JPL modules in that procedure before calling the web_startup event.
By declaring the database connection for the jserver (and not for each client request), you can reduce the overhead incurred by connecting to the database server for each URL. Database connections are made using the command DBMS DECLARE CONNECTION. For example, the following web_startup procedure makes a connection to JDB's videobiz database:
proc web_startup
DBMS ENGINE jdb
DBMS DECLARE jdbconn1 CONNECTION FOR DATABASE 'videobiz'
return
Database connections should be closed in the web_shutdown procedure. Before the Web application server closes a jserver process, it calls the web_shutdown event. Database connections are closed with the DBMS CLOSE CONNECTION command, as in this example:
proc web_shutdown
DBMS CLOSE CONNECTION jdbconn1
return
Each database engine has its own syntax for database connections. For the command syntax, refer to "Database Drivers."
Initializing the Panther Client | |
In three-tier applications, the Web application server's startup procedure web_startup must initialize the server as a Panther client. The web_startup procedure is usually in a JPL module that is specified by the application variable SMINITJPL. When the Web application server starts a jserver process, it initializes the JPL modules in that procedure before calling the web_startup event. For example:
proc web_startup
client_init
return
This connection to the request broker is closed in the web_shutdown procedure. Before the Web application server closes a jserver process, it calls the web_shutdown event.
proc web_shutdown
client_exit
return
Using Database Cursors | |
If you use the transaction manager, it automatically opens and closes the cursors it needs to perform database processing. However, if you are writing your own SQL statements, you also need to manage the database cursors.
For example, if you open a database cursor to execute a SQL statement or a stored procedure, you also need to close that database cursor. The commands to open and close the cursor should occur during a single URL request; otherwise, depending on your database engine, a shared lock can be in effect until the jserver shuts down or the cursor closes.
Database Transactions | |
If you are writing your own SQL statements, you should also complete the data base transaction during a single request. The DBMS BEGIN and DBMS COMMIT (or DBMS ROLLBACK) should both occur when the screen is posted back to the server.
By testing for K_WEBPOST–the event flag that is set for POST events—you can ensure that the database transaction only starts when the screen is submitted back to the server. For more information on K_WEBPOST, refer to "Screen Entry Context Flag."
Some type of optimistic locking for the database should be in place so that the data remains concurrent. Pessimistic locking schemes are not recommended for Web applications. The transaction manager has a version field that can be used to perform optimistic locking. You can also use engine-specific locking techniques, such as timestamp columns.
Fetching Multiple Rows | |
To fetch multiple rows, use Panther scrolling events instead of DBMS CONTINUE commands. With Panther scrolling, you set the number of occurrences in the screen to exceed the number of rows in the select set.
For example, the screen has a grid with 10 onscreen rows and a maximum number of 500 occurrences. You execute a SQL SELECT statement that returns 600 rows. The first 10 rows of the select set are displayed in the browser. The first 500 rows of the select set are cached on the Web application server. However, the remaining 100 occurrences institute a shared lock on the database until the rows are flushed, the cursor is closed, or the jserver is shut down.