Intland's free requirements, development and test management hosting.
This server hosts 100.000+ users on the cloud!
JDBC: Add support for queries in batch-mode#14268/v2
more
Tags:  v1.1

JDBC: Add support for queries in batch-mode[CRQ-14268]

Tracker: Change Requests Priority: NormalNormal Assigned to: Alexandr, ejboy Apr 27, 2011 06:13
Submitted by: ejboy Apr 27, 2011 06:13 Status: Resolved Category: --
Severity: -- Release: --
Relations
Loading...
Description
In order to support queries in batch mode the following changes have to be done:
  • Add statement.flushBeforeQuery parameter to configure flushing before query is being executed
  • Implement flushing when the parameter is enabled
Details
Comments & Attachments (5)
Associations (0)
Children (0)
References (0)
SCM Commits (1)
History (2)
Baselines (0)
All (3)

Filter
Submitted Comment
ejboy
Jan 24, 2012 07:32
I'll try to flush after executing a script if it is not executing inside queries. In your example, running an empty query would be no longer necessary.
srstclair
Jan 20, 2012 22:33
Patch works for me, although it was't immediately obvious how to use flushBeforeQuery. When batch transferring data from one connection to another, an empty query can be used on the destination connection to force cached statements to flush before continuing (see etl below). This should be explained in the documentation to avoid confusion.
<!DOCTYPE etl SYSTEM "http://scriptella.javaforge.com/dtd/etl.dtd">
<etl>
  <description>Test flushBeforeQuery</description>

  <connection id="source_db"
    url="jdbc:jtds:sqlserver://host/source_db"
    user="user"
    password="pass"
  />

  <connection id="dest_db"
    url="jdbc:jtds:sqlserver://host/dest_db"
    user="user"
    password="pass"
  >
    flushBeforeQuery = true	
    statement.batchSize = 1000
  </connection>
 
  <script connection-id="dest_db">
    DELETE FROM TABLE;
  </script>
	
  <!-- do an empty query on dest_db here to flush previous statement and prevent key conflicts -->
  <query connection-id="dest_db"/>
	
  <query connection-id="source_db">
    SELECT COL1, COL2
    FROM TABLE
    <script connection-id="dest_db">
      INSERT INTO TABLE( COL1, COL2 )
      VALUES( ?COL1, ?COL2 )
    </script>
  </query>     	
</etl>
ejboy
Jan 19, 2012 03:54
This feature was requested again in this thread http://groups.google.com/group/scriptella/browse_thread/thread/53da6a2d2d90fc1e
ejboy
Apr 28, 2011 17:17
flushBeforeQuery connection parameter
batch_processing_-_flush_support v2.patch 10 KB
ejboy
Apr 28, 2011 17:16
I tried a fix using flushBeforeQuery=true|false based on changes proposed by Alexander.

Pros: Less changes

Cons: Less flexible, but I still do not see an ideal solution without major changes.

I've added the explicit parameter to control flushing when a query is put into another query. This scenario is unlikely, but personally I'd prefer to have a chance of unflushed connection rather than performance degradation, but this is just my 2c:

<query>
   select * from src
   <query>
      select * from dest
      <script>
         insert into t2
      </script>
   </query>
</query>

Probably enabling flushing, when batching is enabled is a reasonable default...