Updates and Bulk Load

Updating a Relational Database

When updating a relational database you must use the SQLUpdate activity object. You can pass in parameters or pull the the update data from another location (see this following page on Asynchronous Delivery).

1. Create an SQLUpdate object from an SQL update string. For example, you can insert a new row into table littleblackbook.

   String sql_insert = 
     "insert into littleblackbook values (10010,'Jimbo Goggins','Buckingham Palace','0123456789')";
   SQLUpdate update = new SQLUpdate( sql_insert );
   

2. Perform the update and print out the results.

   service.perform(update);
   

3. Similarly, you can update or delete rows in your table:

   String sql_update = 
   "update littleblackbook set address = '13 Cod Road' where id = 10010";
   String sql_delete = 
   "delete from littleblackbook where id = 10010";
   

4. As for SQL queries, you can set parameters with setParameter.

   String sql = "update littleblackbook set address = ? where id = ?";
   SQLUpdate update = new SQLUpdate( sql );
   update.setParameter(1, "35 Coronation Street, Manchester");
   update.setParameter(2, "10010");
   

5. Creating and dropping tables are also update operations. For example, the following SQL statement creates an empty table similar to the littleblackbook table.

   create table mytable (id INTEGER, name VARCHAR(64), address VARCHAR(128), phone VARCHAR(20))
   

   Use this SQL statement to create a new table. You can drop the table like this:

   drop table mytable
   

See OGSA-DAI/examples/src/uk/org/ogsadai/examples/clienttoolkit/SQLUpdateExample.java for an example solution.

Bulk loading Data into a Table

You can bulk load data from one table to another using the SQLBulkLoad activity object. It is important that the target table has the same schema as the source table.

1. Create a new table or make sure that there is an appropriate table available. We described table creation in SQL above.
2. Prepare an SQL query which selects the data you would like to bulk load into another table, for example

   SQLQuery query  = 
    new SQLQuery("select * from littleblackbook where name like '%Krause'");
   

   and connect its output to a WebRowSet activity:

   WebRowSet rowset = new WebRowSet( query.getOutput() );
   

3. Now you can construct an SQLBulkLoad activity object whose input connects to the WebRowSet output. You also have to specify the name of the target table.

   SQLBulkLoad bulkLoad = new SQLBulkLoad( rowset.getOutput(), tableName );
   

4. Bulk load activities can be performed in a transactional manner. This property can be switched on or off as follows:

   bulkLoad.setTransactionally(true);
   

   It is switched off by default.
5. Finally, add the two activities to an empty request and send it to the data service.

   ActivityRequest request = new ActivityRequest();
   request.add( query );
   request.add( rowset );
   request.add( bulkLoad );
   service.perform( request );
   

6. Check if the bulk load has been performed correctly by querying the new table.

See OGSA-DAI/examples/src/uk/org/ogsadai/examples/clienttoolkit/SQLBulkLoadExample.java for an example solution.

Updating an XML Database

Updates to an XML database are made using the XUpdate language. In this example, you will modify entry no. 99 and change the value within the name element.

1. Use the following XUpdate statement to modify entry number 99 in the database:

   <xu:modifications version="1.0" xmlns:xu="http://www.xmldb.org/xupdate">
      <xu:update select="/entry[@id=99]/name">
         Harold Bishop
      </xu:update>
   </xu:modifications>
   

2. Now construct an XUpdate object from the statement (as a string):

   XUpdate update = new XUpdate( xupdate_statement );
   

3. Finally, perform the update. This operation returns the number of modified nodes as a result.

See OGSA-DAI/examples/src/uk/org/ogsadai/examples/clienttoolkit/XUpdateExample.java for an example solution.