Skip to main content

Notice: this Wiki will be going read only early in 2024 and edits will no longer be possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.

Jump to: navigation, search

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.

Back to the top