Notice: This Wiki is now read only and edits are no longer possible. Please see: https://gitlab.eclipse.org/eclipsefdn/helpdesk/-/wikis/Wiki-shutdown-plan for the plan.
Difference between revisions of "EclipseLink/Development/DBWS/SQLBatchOperations"
(New page: == batch SQL operations ==) |
(→Current Support (EclipseLink 2.4)) |
||
(13 intermediate revisions by the same user not shown) | |||
Line 1: | Line 1: | ||
− | == | + | <css> |
+ | .source-sql {padding:1em;border:1px solid black; background-color: white;} | ||
+ | .source-java5 {padding:1em;border:1px solid black; background-color: white;} | ||
+ | .source-xml {padding:1em;border:1px solid black; background-color: white;} | ||
+ | .source-text {padding:1em;border:1px solid black; background-color: white;} | ||
+ | </css> | ||
+ | __NOTOC__ | ||
+ | == Batch SQL operations == | ||
+ | Initial contribution by customer Rainer Schild: A DBWSBuilder file that looks something like: | ||
+ | <source lang="xml"> | ||
+ | <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> | ||
+ | </source> | ||
+ | === Variable bindings === | ||
+ | Need to figure out 2 types of variables: | ||
+ | # parameters passed in (use JDBC '?' markers?) | ||
+ | # temporary variables (@A) | ||
+ | |||
+ | === Transaction Horizon === | ||
+ | * explicit (see above) | ||
+ | * implicit - whole block is implicitly a start ... commit transaction | ||
+ | * nesting - can <tt>batch-sql</tt> 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: | ||
+ | <source lang="xml"> | ||
+ | <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> | ||
+ | </source> | ||
+ | |||
+ | 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. |
Latest revision as of 15:54, 13 June 2011
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:
- parameters passed in (use JDBC '?' markers?)
- 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.