I've implemented withTransaction() now, and I'm running continually waiting for another hang.  If there were an error, wouldn't it crash the script--or does hang == crash?  I still can't get a return from withBatch() with an actual input list, which consists of many different datatypes, only with 'sanitized' input lists of Strings and ints...


On Tue, Jul 14, 2015 at 11:02 AM, Owen Rubel <orubel@gmail.com> wrote:
The hangs are probably a result of:

For integrity and performance reasons, you may wish to consider executing your batch command(s) within a transaction:
 sql.withTransaction {
     def result1 = sql.withBatch { ... }

On Tue, Jul 14, 2015 at 7:28 AM, Dinko Srkoč <dinko.srkoc@gmail.com> wrote:
On 14 July 2015 at 15:20, Daniel Price <danprice303@gmail.com> wrote:
> [...]
> I'm wondering why I'm not seeing a non-empty return from my batch code:
> myList = [['a','b'],['c','d']]
> def result = sql.withBatch(someInt, "insert into myDB.dbo.myTable (column1,
> column2) values (?,?)"){ ps ->
>    myList.each{
>       ps.addBatch(it)
>    }
>    ps.executeBatch()
> }
> println "result: " + result
> prints:
> result: []
> Shouldn't result == [1] * myList.size()?

The reason for `[]` result might be the explicit call to
`ps.executeBatch()`. That method is actually called implicitly by
`sql.withBatch`, after the Closure parameter is invoked, and it resets
the internal results storage.

So, your explicit call to `executeBatch` executes whatever is left to
execute, then resets the internal results storage and returns the
collected result. Unfortunately, that result is lost, as
`sql.withBatch` calls `executeBatch` again, but this time there is
nothing left to execute, so the final result is empty array of ints
that is added to the emptied internal results storage.

Remove the `ps.executeBatch()` from your code.

I can't say anything about the hangs, sorry.


> Hangs always happen upon completion of the batch...
> Regards,
> D
> On Mon, Jul 13, 2015 at 4:06 PM, Daniel Price <danprice303@gmail.com> wrote:
>> Good Afternoon,
>>      I'm experiencing infrequent (1/20) hangs with a Groovy script that
>> uses withBatch() to insert and update a SQL Server DB using stored
>> procedures.  In each case (insert or update), the batch completes as
>> indicated by database content, but the script hangs and does not continue
>> until I ctrl-c it.  This script is the only user of the table, so DB locking
>> isn't involved.  The batches are only about 200k rows, but I set the
>> withBatch() parameter to 25k to be nice to the DB.  Has anybody seen this
>> before?  Any suggestions?  Thanks!
>> D