Technique #4: eliminating recursive embedded SQLs
It is actually quite common to find embedded SQL in a loop,
especially Select and Insert statements. As explained previously,
server calls that are recursive in nature are quite dangerous,
potentially generating tremendous number of server calls. If your
application requires loops or recursive functions, it would be best to
replace any code resulting in server calls with code that does
not.
For this technique, we will assume we have Select and Insert SQL
statements in a loop. The general idea is to first create a
DataWindow/DataStore using the SQL. Then replace the SQL statements
contained in the loop with PowerScript modifying the
DataWindow/DataStore, which does not result in server calls. If the
SQL statement contained in the loop is an Insert statement, we would
want to replace that with PowerScript that would insert data into the
DataWindow/DataStore. Once all the data has been inserted, then in one
shot we would update the DataWindow/DataStore to the database (outside
the loop), resulting in only one server call. If the SQL statement
contained in the loop is a Select statement, we would retrieve data
into a DataWindow/DataStore before executing the loop, and then write
PowerScript in the loop to select the desired data from the
DataWindow/DataStore.
The following is a code example that increases the price of a
specific order by 20%, where embedded SQL is used to update the change
row-by-row (hence the loop), and then save those changes to the
database:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
long ll_id declare order_detail cursor for select id from order_detail where orderid = :arg_orderid; open order_detail; fetch order_detail into :ll_id; do while sqlca.sqlcode = 0 update order_detail set price = price*1.2 where orderid = :arg_orderid and id = :ll_id; if sqlca.sqlcode < 0 then rollback; return end if fetch order_detail into :ll_id; loop close order_detail; commit; |
Now we will replace the embedded SQL with a DataWindow.
Specifically, we will cache the data in a DataWindow and update the
database with a single DataWindow Update, resulting in just one server
call:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
long ll_rows, i ll_rows = dw_1.retrieve(arg_orderid) for i = 1 to ll_rows dw_1.SetItem(i, "price", dw_1.GetItemDecimal(i, "price")*1.2) next if dw_1.update() = 1 then commit; else rollback; end if |
With this technique we have just eliminated server calls from
inside the loop, reduced the number of server calls to just one, and
created a data caching mechanism at the client-side that can be used
to feed data to other controls of the PowerBuilder client.