This section describes the use of the JDBCStoreSchemaGenerator class.
Connecting to the database
JDBCStoreSchemaGenerator uses its own connection to the database. So, before an application can use this class after creating a new instance, a connect() method (providing the database URL as an argument, and eventually the userId and the password) must be run to connect to the specific database where the table has to be created. The following is an example of requesting a connection to the database:
This method sets the databaseConnection static attribute of the Schema Generator. The Schema Generator does not use the connection pooling facilities.
Creating the STORE table
After the connection to the database has been established, the application then generates the table in the database, using one of the following methods:
The first argument is the table name, and the second argument is a string with the SQL definition of the STORE table. You can also give an additional argument, the name of the schema where the table is to be created. If the schema does not have to be explicitly created in the database, the application must set the createSchema attribute to false before calling the generateTable() method.
Note The STORE table SQL definition does not need to include the record identification DSERECID primary key column and the record mark DSERECMK column. The table generator automatically adds these columns to the table definition.
The following sample code shows the use of the generateTable method:
When using DB2 UDB for OS/390, the database name where the STORE table is created must be specified, as the database URL refers to a location where different databases can be defined. You can do this using the setDatabaseName method. If the databaseName attribute is not null and not blank, the JDBCStoreSchemaGenerator creates the table in the database specified by the databaseName attribute value within the location set in the database URL specification.
JDBCStoreSchemaGenerator arguments
schemaName
Contains the name of the store schema. If you do not set this attribute, either explicitly or as an argument of the generateSchema() method, the method uses the default value of null. When the schema name is null, the DBMS uses only the table name to create the table in the default schema assigned to the user executing the CREATE TABLE statement. If you set the name of the schema, the schema must already exist as result of the creation of a user in the database system. In this case, the schema name always matches the name of a defined user.
When the DBMS is Oracle, the application may need to create the STORE table in the user's default schema. If this is the case, use the default value of null.
Default value: null
createSchema
Indicates whether the schema identified by schemaName must be explicitly created before the tables are created in it. If this attribute is set to false, the schema identified by schemaName must already be available in the database.
When the DBMS is Oracle and a schemaName has been set, set this attribute to false as the schemas are automatically created when a new database user is defined and no other schemas can be created.
When the DBMS is DB2 UDB for OS/390, set this attribute to false.
Default value: true
createIndex
Indicates whether the STORE table index must be explicitly created after creating the table. DB2 for NT /AIX and Oracle automatically create indexes on the table primary keys when the table is created. DB2 for OS/390 requires an explicit creation of the indexes after it creates the tables, so you must set this attribute to true for this environment.
Default value: false
databaseName
Keeps the database name when working with DB2 UDB in OS/390. In this environment the database URL as set in the database connection has the following format: jdbc:db2os390:<location>. The <location> is the name of a shared database subsystem with different databases and storage groups defined in it. The databaseName attribute indicates which database contains the table. If you do not specify a value, the DBMS creates the STORE table in the default database within this location.
As a subclass of the Service class, the JDBCStore class has an externalizer that allows it to define any store object attributes in an external definition file (these attribute values are set automatically when the store object is instantiated). The JDBCStore class inherits all Service attributes; for instance, the name attribute, which will allow the application to request a specific Store service instance in a hierarchy of toolkit contexts.
A store object will usually be instantiated by providing the name of the service to be created to the ServiceExternalizer class. The ServiceExternalizer class then creates an instance of the class (in this case, a store), and sets its attributes to the values read from the services definition file. Alternatively, a store object may be instantiated by requesting this service from the operation context, which will internally run the usual instantiation process if the service has not yet been instantiated in the hierarchy.
After a store service has been instantiated, a name, a tableName, a schema (the database schema where the table is created, if it applies to the DBMS being used) and database connection properties are assigned to the store. After the application has requested the database connection, it sends the store service an open() message to activate the current store object. From this point on, the store service is active and ready to be worked with.
When the application has finished making changes to the STORE table, it can send the close() message to the store service. The name of the table assigned to a store service, the name of the store service, and the database connection properties cannot be changed while the store is active; a close() message needs to be sent before changing these attributes. This may be required when the same store instance should be used to work with different database tables. An application can send the isActive() message at any time to find out the current state of a store object.
While a store object is active, a client can add, retrieve, retrieve for forwarding, update, and delete records. It can commit and roll back either explicitly or by setting the autoCommit attribute to true, in which case every add, update, or delete is followed automatically by a commit.
Adding records
To add a record, send the addRecord() message to an active store object. The format of the record is defined at customization time and is implementation-dependent. The formatting facilities of the toolkit can be used, so the record can be built from different data fields located in different levels of the operation context. Each concrete store implementation provides an external HashtableFormat definition, which describes the data fields to be stored and how they must be formatted so that the store record will be constructed. A record is obtained by sending the format(Context aContext) message to the specific HashtableFormat object.
The arguments of the addRecord() method provide the Store service with the data already formatted, a context and a format name, or a context and a format. In the cases where the third argument is either a format or a format name, the service itself will call the format() method of the format object to build the store record.
Retrieving records
The Store service provides the following methods for retrieving records:
▪ retrieveFirstRecord. This method returns the first record, or null if the table is empty.
▪ retrieveNextRecord. This method returns the next record from the current cursor in the STORE table.
▪ retrieveFirstRecordForForwarding. This method returns the first record in the STORE table and marks it as retrieved for forwarding (it sets the value in the record mark column to retrievedForForwarding).
▪ retrieveNextRecordForForwarding. This method returns the next record from the current cursor, and marks it as retrievedForForwarding.
▪ retrieveRecord. This method returns the first record that matches a search criterion or the record identified by the record id passed as argument.
▪ retrieveRecordForForwarding. This method returns the first record that matches the search criterion or the record identification passed as argument, and marks it as retrievedForForwarding.
▪ retrieveRecordsForForwarding. This method returns the records that match a specific search condition and marks them as retrievedForForwarding.
Deleting records
The forwarding application is responsible for deleting the store records after receiving confirmation that the host application has successfully received the transaction. The deletion can be accomplished either by using the deleteRecords() method, passing a search criterion as an argument; by using the deleteRecord() method with a record identification as argument; or by using the deleteAllRetrievedForForwarding() method, which deletes all records in the table that have the retrievedForForwarding record mark.
Updating records
Several methods are available for updating records. All of them accept a formatted record or a context instance and a format name, and work the same way as the addRecord() method (see Adding records). The JDBCStore service allows the application to use the updateRecord() method, which updates the specified record (a record identifier is passed as the first argument) with the data provided as the second argument. An SQL exception is raised if there are no records to update in the STORE table.
A forwarding application can decide to update a record with a new record mark if the record has already been retrieved for forwarding and there is no positive response from the host confirming its execution. The record mark can then be set to potentiallyDuplicated, so that the next time the forwarding application retrieves this record, it will send it to execution informing the host that the operation could have been previously executed.
See the JDBCStore code example.
Creating a forwarder application
The Store service provides methods that help obtain the information that a transaction could not be executed, that facilitate the development of a forwarder application that will get this information from the database, and that later process the pending transactions. The forwarder uses the record marks in the store database entries to determine the current status of a transaction and decide if its entry should be deleted from the database if it has already been executed, or sent on to the host for execution. The following is a description of a common forwarder application flow, based on the marks set by the Store service:
1 The forwarder requests from the JDBCStore service the first record to be forwarded, using the retrieveFirstRecordForForwarding() method. This method returns the first store database entry with the record mark column (DSERECMK) set to "added" or "updated", and sets that column to a new value, retrievedForForwarding.
2 The forwarder tries to execute the operation in the host.
▪ Normally, the forwarder application works in synchronous mode: that is, it waits for the host response, and if the operation has been successfully executed, the forwarder deletes the operation entry in the store database by calling the deleteRecords(aSearchCriteria) method. As the forwarder knows which record is processing, the search criterion can easily be set to the primary key of the record to be deleted. If the problems with the host continue, it is the forwarder's responsibility to keep this operation information and retry later. One way of doing that is to work with the autoCommit service option set to false; then, the forwarding application can decide to roll back the changes done to the record when it was retrieved for forwarding, and reset the record mark to its original value. If the forwarder deletes the record, it will also have to commit these changes to the database to make them persistent. The next step is to retrieve the next record to be forwarded by calling the retrieveNextRecordForForwarding() method.
▪ Another option for the forwarder application is to go through all the operations in the STORE table without initially deleting the records. The forwarder will get a STORE table record and mark it as retrievedForForwarding. If that operation is successfully executed, the forwarder commits the database changes. If the host does not respond or there is a host error, the forwarder rolls back these changes, and the record stays with an added or updated record mark. In this scenario, a unique delete will be needed after the forwarding application has completed all pending operations in the database; the forwarder will use the JDBCStore deleteAllRetrievedForForwarding() method.
The two figures below show the described flows for the application used for forwarding. The first case is as follows:
The following flow of events corresponds to the numbers in the diagram above:
3 If the host responds and the received answer is what the forwarder application is expecting, the forwarder deletes the record from the store database and commits the changes. If the response is not what the forwarder is expecting, it can take the appropriate action before deleting, committing, or rolling back the database changes.
4 If the host does not respond, the forwarder application can roll back the database changes.
5 In either case, the forwarder continues with the next pending operation stored in the database.
The second case is as follows:
The following flow of events corresponds to the numbers in the diagram above:
6 If the host responds and the received answer is what the forwarder application is expecting, the forwarder commits the previous database operation. If the response is not what the forwarder is expecting, it can take the appropriate action before committing or rolling back the database changes.
7 If the host does not respond, the forwarder application can roll back the database changes.
8 In either case, the forwarder continues with the next pending operation stored in the database.
9 At the end, all records marked as retrieved for forwarding are deleted from the database.
See Manual Forwarding and Manual Forwarding implementation for the sample implementations provided. Because these realizations assume that the host is online, an actual implementation will need to include a check for host availability.
