![]() |
Updates and Work Units
|
|
Using Work Units
SQL-J Extensions for Work UnitsWork units are registered using CREATE WORK ALIAS at the source. They are unregistered using DROP WORK ALIAS at the source. You must publish a work unit to any target that will use it. Work units are invoked using one of the following statements: Work units for a target must have been included (using the ADD WORK ALIAS clause of CREATE PUBLICATION or ALTER PUBLICATION) in the publication that created that target. As with other explicitly published dictionary objects, you cannot drop a work unit unless you drop all publications that contain it. See Chapter 10, "Cloudsync Commands Reference", for details on these statements. Creating a Work UnitWork units are registered at the source using CREATE WORK ALIAS, then are put in the publication that creates the target. The syntax for CREATE WORK ALIAS is:
CREATE WORK ALIAS WorkAliasName The SourceMethodName and TargetMethodName are of the form classname.methodname (class aliases may be used). If you do not specify the target method name, it is assumed to be the same as the source method name. Even if the source and target methods have the same name, the code in the methods may be different. Because the work unit is an alias, publishing the work unit does not automatically copy any classes to the target. (To automatically update target classes when source classes are updated, put the classes into jar files in the source database. See Distributing Application Code Within the Database for details.) If the source and target methods do not have the same number of parameters, the work unit will fail when applied at the source during the refresh. Work units, method aliases, and user-defined aggregates (which are a type of method alias) occupy the same name space. You cannot create a work unit using an existing method alias name. The following example registers a work unit:
CREATE WORK ALIAS recordSalesOrder See Work Unit Examples for examples of work units. Invoking a Work UnitTo invoke a work unit, use the CALL statement or a VALUES statement at either the target or the source. Calling a work unit is syntactically similar to calling other methods in Cloudscape. Calling a work unit at the source is just like making source changes directly. Use CALL if the work unit does not return a value or if you don't use the return value. Use VALUES to see the work unit's return value. The syntax of CALL with a work unit is:
CALL WorkUnitName The syntax of VALUES with a work unit is:
VALUES ( WorkUnitName To see a work unit's return value, fetch the first value of the statement's result set. For example: Connection conn; You can specify any number of parameters in a CALL or VALUES statement. The same parameter values are passed to both the target-side and source-side methods of the work unit. The rules for parameters are the same as for other SQL-J expressions. See the Cloudscape Reference Manual for details about parameter syntax. If CALL or VALUES cannot find the work unit name or the Java method for the work unit, an error is raised. If the failure occurs at the target, a statement exception is raised. If the failure occurs at the source, an environmental error is raised and the refresh halts. You can call one of the work unit's methods directly, without using the work unit alias. If you do, it will behave like a non-synchronized method. Specifying the Connection in a Work UnitA work unit typically executes a query. To do that, it must know which connection to execute the query on. To obtain a connection in a work unit, put a line like: Connection c = DriverManager.getConnection( "jdbc:cloudscape:;current=true"); It is also permissible to pass getCurrentConnection() to CALL or VALUES as a parameter. SQL-J can correctly interpret getCurrentConnection() at refresh time. When invoking work units, avoid using "?" parameters to represent connections. If the parameter does not represent the current connection, a runtime error is raised at the target. Transactions and Work UnitsTransaction control inside work units is the same as in methods issued inside queries. You cannot commit a transaction inside a method or a work unit invoked in an SQL-J statement. By default, target work unit methods run at the target database's isolation level, and source work unit methods run at the source database's isolation level. You can override these defaults for individual SELECT statements within a work unit. See the Cloudscape Developer's Guide for more information about setting isolation levels in a SELECT statement. LOCK TABLE is not automatically propagated in either direction. You can, however, specify target-to-source table locking by putting LOCK TABLE into a work unit. Error Handling in Work UnitsIf an exception in a work unit is raised and not caught, the CALL or VALUES statement and all its transactional work are rolled back. If the exception is caught, the work unit determines what action to take at the source. Cloudsync provides two exceptions in COM.cloudscape.synchronization specifically for work units. These exceptions let you decide how you want error situations to affect your transaction. The exceptions, which are valid only at the source, are:
Consistency errors and environmental errors are described in Applying Provisional Transactions at the Source. Work units let you determine when you want these errors raised. See Work Unit Examples for examples of these exceptions. NOTE: If the target-side method fails with an error, the source-side method does not run. It is important not to catch deadlock and other transaction-severity errors in a work unit. It is safer to let the entire transaction roll back when a severe error is encountered. To find out whether an SQLException is severe enough to terminate the current transaction, use the COM.cloudscape.database.checkJBMSException method. The following code fragment shows how to use this method: public static void workUnit1( ... ){ try { PreparedStatement ps = ...; ps.executeQuery(); } catch ( SQLException se ) { checkJBMSException( se ); // if we get here, the exception is mild // enough to continue ... } // end tryCatch ... } Altering Work Unit MethodsYou can invoke CALL or VALUES even if the source-side method has been altered. This gives you the flexibility to alter update procedures without changing target-side code. The source reloads the class containing the source work unit method only when the server is restarted or the class is garbage collected (which is a characteristic of Java, not only of Cloudscape.) Therefore, if you change a source-side work unit method stored in the file system, the changes may not take effect immediately. A way of circumventing this problem is to put classes into the database using jar files, and replace the jar files as needed. See Distributing Application Code Within the Database for details. Any changes you make to a work unit method, even changes to its signature, are distributed to targets, as long as you use the following guidelines:
Getting Information About the Target at the SourceThe source can get information about a target when a target refreshes, because a target is is always associated with an instance of the class COM.cloudscape.synchronization.TargetDescription at the source. Application code such as source-side work units and a refresh authentication callback class have access to this object. Source-side work units have access to the TargetDescription for the target currently refreshing from the class COM.cloudscape.synchronization.SyncFactory, like so: TargetDescription td = SyncFactory.getRefresher(); Refresh authentication callback classes have access to the TargetDescription for the target currently refreshing because the TargetDescription is a parameter to the methods preConnect and postConnect (For more information on refresh authentication callback and refresh properties, see Chapter 5, "Refresh Authentication"). Information available from TargetDescription:
NEW: TargetDescriptions are new in Version 3.6. Saving Information Such as PreparedStatements Across a RefreshWork units are static Java methods, which typically do not have any access to non-static object fields. This may present a problem if you are trying to use a PreparedStatement within a work unit. (As you know, using PreparedStatements is better for performance than Statements.) If the method does not have any access to non-static object fields, the only way it could execute a PreparedStatement is to create a new one each time it is called. Cloudscape's statement cache eliminates some of the performance hit of doing this, but a better solution would be to be able to prepare a statement once for a single refresh and then to be able to execute it only within the work unit. NOTE: For Cloudscape databases, an even better solution would be to use stored prepared statements, since they are pre-compiled and shared across connections. For more information, see Tuning Cloudscape. However, Cloudscape does provide a way for you to save information at the source across the duration across a refresh using the two methods getObject and saveObject in the class COM.cloudscape.synchronization.TargetDescription (see Getting Information About the Target at the Source). The following class is an example of how to do this within source-side work unit code: import java.sql.PreparedStatement; import java.sql.ResultSet; import java.sql.ResultSetMetaData; import java.sql.Types; import java.sql.DriverManager; import java.sql.Connection; import java.sql.SQLException; import COM.cloudscape.synchronization.TargetDescription; import COM.cloudscape.synchronization.SyncFactory; import COM.cloudscape.synchronization.StopRefreshSQLException; import COM.cloudscape.synchronization.SkipTransactionSQLException; public class SourceLogic { private PreparedStatement getSalesRepId; private String getSalesRepIdText = "SELECT salesRepId FROM salesReps WHERE salesRepId = ?"; private PreparedStatement getToysQuantity; private PreparedStatement updateToysQuantity; private PreparedStatement insertOrder; private PreparedStatement insertLead; private int salesRepIdPubParameter; public static void makeSaleAtSource(byte[] orderID, int salesRepID, String customer, String toy, int quantityOrdered) throws SQLException { Connection conn = DriverManager.getConnection( "jdbc:default:connection"); //get the TargetDescription so we can //save the PreparedStatement //across connections TargetDescription td = SyncFactory.getRefresher(); SourceLogic state = (SourceLogic) td.getSavedObject(); if (state == null) { //this must be the first work unit // execution in the refresh. //create the object. state = new SourceLogic(); //get the value of the publication parameter salesRepId state.salesRepIdPubParameter=getTargetParameter("salesRepId", td); state.getSalesRepId = conn.prepareStatement( state.getSalesRepIdText); state.getToysQuantity=conn.prepareStatement( "SELECT quantity FROM toys WHERE toy = ?"); state.updateToysQuantity=conn.prepareStatement( "UPDATE toys SET quantity = quantity-? " + "WHERE toy = ?"); state.insertOrder=conn.prepareStatement( "INSERT INTO orders VALUES(?,?,?,?,?,?)"); td.saveObject(state); } if (state.getToysQuantity == null) { //state was instantiated only by sendLeadAtSource state.getToysQuantity=conn.prepareStatement( "SELECT quantity FROM toys WHERE toy = ?"); state.updateToysQuantity=conn.prepareStatement( "UPDATE toys SET quantity = quantity-? WHERE toy = ?"); state.insertOrder=conn.prepareStatement( "INSERT INTO orders VALUES(?,?,?,?,?,?)"); td.saveObject(state); } //get some other information from the TargetDescription //for extra security, make sure salesRepId that is passed //as a parameter to the work unit is // also the official publication //parameter if (salesRepID != state.salesRepIdPubParameter) { throw StopRefreshSQLException.stopRefreshSQLException( "Your sales repId is not the official "+ "salesRepId for your target." + "Please contact your system administrator."); } PreparedStatement getSalesRepId = state.getSalesRepId; getSalesRepId.setInt(1, salesRepID); // Abort refresh if salesRepID is invalid ResultSet repsRS = getSalesRepId.executeQuery(); if (!repsRS.next()) throw StopRefreshSQLException.stopRefreshSQLException( "Your sales rep ID "+ salesRepID + " is invalid. Please contact your system administrator."); repsRS.close(); String status = ""; int quantityAvailable; // Find out how many toys are available PreparedStatement getToysQuantity = state.getToysQuantity; getToysQuantity.setString(1, toy); ResultSet rs = getToysQuantity.executeQuery(); if (!rs.next()) { quantityAvailable = 0; status = "TOY UNAVAILABLE"; } else { quantityAvailable = rs.getInt(1); } rs.close(); // set status durably // to ORDERED or BACKORDERED if (quantityAvailable >= quantityOrdered) { PreparedStatement updateToysQuantity = state.updateToysQuantity; updateToysQuantity.setInt(1, quantityOrdered); updateToysQuantity.setString(2, toy); updateToysQuantity.executeUpdate(); status = "ORDERED"; } else if (status != "TOY UNAVAILABLE") { status = "BACKORDERED"; } PreparedStatement insertOrder = state.insertOrder; insertOrder.setBytes(1, orderID); insertOrder.setInt(2, salesRepID); insertOrder.setString(3, customer); insertOrder.setString(4, toy); insertOrder.setInt(5, quantityOrdered); insertOrder.setString(6, status); insertOrder.executeUpdate(); //don't close the prepared statements or the connection } NEW: The ability to save information across a refresh is new in Version 3.6. |
|
![]() Cloudscape Version 3.6 For information about Cloudscape technical support, go to: www.cloudscape.com/support/.Copyright © 1998, 1999, 2000 Informix Software, Inc. All rights reserved. |