Data Changes

   

The correct way to do an ordinary data change is still the sameUse SQL's INSERT/UPDATE/DELETE statements, or use stored procedures. What are you doing with a cursor for updating in the first place? Oh, well. We will merely touch on the extra features that JDBC provides for executing data changes while traversing result sets.

In the first place, you can try to ensure that result sets are updatable. Your best updatable cursor has the form:

 SELECT <primary key column>, <other columns>   FROM <Table>   WHERE <search condition> 

That is, the query includes no DISTINCT, GROUP BY, or ORDER BY clauses and contains only one table in the FROM clauseno joining. Note that if you're going to INSERT into the query, the "other columns" should include all columns in the table that are either non-nullable or have no default value.

If your cursor is updatable and you specified that the result set is updatable when you prepped the query, you should be able to use the updateXXX methods . Is there any advantage in doing so? Yesif you can thereby avoid creating another stmt and parsing it. But do not expect that you are saving a complete parse. In the end, the driver has to construct an UPDATE statement based on the changes that the updateXXX methods caused. One good thing: UpdateRow() will not cause an automatic COMMIT, whereas UPDATE ... WHERE CURRENT OF <cursor> might.

Updating "in batches" is a good idea becauserecall our discussion of the subject in Chapter 12, "ODBC"it reduces network messaging.

Finally, here's a trick that affects data changes with stored procedures. The idea is this: A stored procedure can be called directly with RPC, thus bypassing the DBMS parsing layer. This is especially a feature of Microsoft. However, this call can't use RPC:

 CALL Sp_proc(12345)      /* where Sp_proc expects an INTEGER parameter */ 

The problem is that the DBMS would have to translate 12345 from ASCII to binary before calling Sp_proc . Solution: This call can use RPC:

 CALL Sp_proc(?) 

The Java application would be responsible for setting the parameter (with the setInt method). The trick is to use parameters so that you allow RPC to happen if the DBMS can handle it.

The Bottom Line: Data Changes

The correct way to do an ordinary data change is still the sameUse SQL's INSERT, UPDATE, or DELETE statements, or use stored procedures. Don't bother with cursors .

Updating in batches is a good idea because it reduces network messaging.

A stored procedure can be called directly with RPC, thus bypassing the DBMS parsing layer. The trick is to use parameters so that you allow RPC to happen if the DBMS can handle it.

   


SQL Performance Tuning
SQL Performance Tuning
ISBN: 0201791692
EAN: 2147483647
Year: 2005
Pages: 125

Similar book on Amazon

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net