EclipseLink/Development/DBWS/SQLBatchOperations

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>

Variable bindings

Need to figure out 2 types of variables:

1. parameters passed in (use JDBC '?' markers?)
2. temporary variables (@A)

Transaction Horizon

• 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.