Batch SQL operations
Initial contribution by customer Rainer Schild: A DBWSBuilder file that looks something like:
<batch-sql name="do_trace_analysis" lineDelimiter=";" > <text> <![CDATA[ START TRANSACTION; SELECT @A:=SUM(salary) FROM table1 WHERE type=1; UPDATE table2 SET summary=@A WHERE type=1; COMMIT; ]]> </text> </batch-sql>
Need to figure out 2 types of variables:
- parameters passed in (use JDBC '?' markers?)
- temporary variables (@A)
- explicit (see above)
- implicit - whole block is implicitly a start ... commit transaction
- nesting - can batch-sql operations be batched together: what then happens to the transaction horizon?
Current Support (EclipseLink 2.4)
Our support currently expects '\n' as the line delimiter; this is not configurable. The DBWSBuilder file should look like the following:
<batch-sql name="do_trace_analysis" > <batch-statement> <![CDATA[ START TRANSACTION\n SELECT @A:=SUM(salary) FROM table1 WHERE type=1\n UPDATE table2 SET summary=@A WHERE type=1\n COMMIT\n ]]> </batch-statement> </batch-sql>
Batch SQL statements are executed in order, without any processing or validation being performed. If execution of a statement results in an exception, no further statements are executed, and a value of -1 is returned. Passing of parameters is not supported. Temporary variables will be supported if the target database supports them and the SQL is configured correctly. Only explicit transactions are supported.