Thread: Batch Update updatecounts when error happens
I'm having trouble with the error handling of the batch update feature of the JDBC 2.0 Postgresql driver. I do not see the standard behavior of returing a -3 for the statement that failed. In my example I insert mutliple rows in a single batch into a table. All the inserts have the same key value, which causes the second insert to fail with a duplicate key error. I get thrown back the BatchUpdateException which has the updateCounts array which I access via getUpdateCounts(). That array contains a single entry, updateCounts[0]=1. I am not seeing the -3 entry to indicate the stmt that failed. According to my assumptions about how JDBC 2.0 batch updates are supposed to work (and according to the JDBC--Postgresql compatability web page) I was hoping to see the updateCounts array contain: updateCounts[0]=1 updateCounts[1]=-3 I'm currently using Postgresql 7.2 and the JDBC driver installed with the Postgresql distributed rpm. Specifically I'm using jdbc7.2dev-1.2.jar Any help would be appreciated. Thanks Padraic
I think I might have figured it out. I think there might be a bug in the Postgresql driver. Specifically in the jdbc2/Statement.java file, the lines in executeBatch() that read: int[] resultSucceeded = new int[i]; System.arraycopy(result, 0, resultSucceeded, 0, i); should really be: int len = i+1; int[] resultSucceeded = new int[len]; System.arraycopy(result, 0, resultSucceeded, 0, len); I think that would do the trick. I'll now working on getting a modified version built so I can test it. Think I'm on the right track? wrong track? Thanks, Padraic Quoting Padraic Renaghan <list@renaghan.com>: > I'm having trouble with the error handling of the batch update feature > of the > JDBC 2.0 Postgresql driver. I do not see the standard behavior of > returing a -3 > for the statement that failed. > > In my example I insert mutliple rows in a single batch into a table. All > the > inserts have the same key value, which causes the second insert to fail > with a > duplicate key error. > > I get thrown back the BatchUpdateException which has the updateCounts > array > which I access via getUpdateCounts(). That array contains a single > entry, > updateCounts[0]=1. I am not seeing the -3 entry to indicate the stmt > that > failed. According to my assumptions about how JDBC 2.0 batch updates are > > supposed to work (and according to the JDBC--Postgresql compatability > web page) > I was hoping to see the updateCounts array contain: > updateCounts[0]=1 > updateCounts[1]=-3 > > I'm currently using Postgresql 7.2 and the JDBC driver installed with > the > Postgresql distributed rpm. Specifically I'm using jdbc7.2dev-1.2.jar > > Any help would be appreciated. > > Thanks > Padraic > -- Padraic Renaghan /pad-rik ren-a-han/ padraic@renaghan.com IM: abuhaina (AOL/Yahoo/MSN) 9437815 (ICQ) http://renaghan.com/pcr/ lure -> 390sig006@renaghan.com <- don't use
Sorry for talking with myself again... So I tried the code change below. Got slightly better results: updateCounts[0]=1 updateCounts[1]=0 I really think that second entry should be a -3. So I added one more line to that block so it now becomes: result[i]=-3; int len=i+1; int[] resultSucceeded = new int[len]; System.arraycopy(result, 0, resultSucceeded, 0, len); This worked best. I now get updateCounts[0]=1 updateCounts[1]=-3 So if folks think that is a valid change, I'll whip up a patch file and send it back to this list for further consideration. Thoughts? Thanks, Padraic * Padraic Renaghan <list@renaghan.com> [Aug 28 12:12pm]: > I think I might have figured it out. I think there might be a bug in the > Postgresql driver. Specifically in the jdbc2/Statement.java file, the lines in > executeBatch() that read: > > int[] resultSucceeded = new int[i]; > System.arraycopy(result, 0, resultSucceeded, 0, i); > > should really be: > > int len = i+1; > int[] resultSucceeded = new int[len]; > System.arraycopy(result, 0, resultSucceeded, 0, len); > > I think that would do the trick. I'll now working on getting a modified version > built so I can test it. > > Think I'm on the right track? wrong track? > > > Thanks, > Padraic > > Quoting Padraic Renaghan <list@renaghan.com>: > > I'm having trouble with the error handling of the batch update feature > > of the > > JDBC 2.0 Postgresql driver. I do not see the standard behavior of > > returing a -3 > > for the statement that failed. > > > > In my example I insert mutliple rows in a single batch into a table. All > > the > > inserts have the same key value, which causes the second insert to fail > > with a > > duplicate key error. > > > > I get thrown back the BatchUpdateException which has the updateCounts > > array > > which I access via getUpdateCounts(). That array contains a single > > entry, > > updateCounts[0]=1. I am not seeing the -3 entry to indicate the stmt > > that > > failed. According to my assumptions about how JDBC 2.0 batch updates are > > > > supposed to work (and according to the JDBC--Postgresql compatability > > web page) > > I was hoping to see the updateCounts array contain: > > updateCounts[0]=1 > > updateCounts[1]=-3 > > > > I'm currently using Postgresql 7.2 and the JDBC driver installed with > > the > > Postgresql distributed rpm. Specifically I'm using jdbc7.2dev-1.2.jar > > > > Any help would be appreciated. > > > > Thanks > > Padraic > > > > > > -- > Padraic Renaghan /pad-rik ren-a-han/ > padraic@renaghan.com > IM: abuhaina (AOL/Yahoo/MSN) 9437815 (ICQ) > http://renaghan.com/pcr/ > lure -> 390sig006@renaghan.com <- don't use > -- Padraic Renaghan /pad-rik ren-a-han/ padraic@renaghan.com IM: abuhaina (AOL/Yahoo/MSN) 9437815 (ICQ) http://renaghan.com/pcr/ lure --> 123sig987@renaghan.com <-- don't use
Padraic, The behavior in the driver that you are seeing is correct according to the jdbc spec. Your suggested changes actually violate the jdbc spec. From section 6.1.1 of the JDBC2.1 Core API: "JDBC drivers that do not continue processing after a failure never return -3 in an update count array. Drivers of this type simply return a status array containing an entry for each command that was processed successfully." thanks, --Barry Padraic Renaghan wrote: >Sorry for talking with myself again... > >So I tried the code change below. Got slightly better results: > >updateCounts[0]=1 >updateCounts[1]=0 > >I really think that second entry should be a -3. So I added one more >line to that block so it now becomes: > >result[i]=-3; >int len=i+1; >int[] resultSucceeded = new int[len]; >System.arraycopy(result, 0, resultSucceeded, 0, len); > >This worked best. I now get > >updateCounts[0]=1 >updateCounts[1]=-3 > >So if folks think that is a valid change, I'll whip up a patch file >and send it back to this list for further consideration. > >Thoughts? > >Thanks, >Padraic > >* Padraic Renaghan <list@renaghan.com> [Aug 28 12:12pm]: > > > >>I think I might have figured it out. I think there might be a bug in the >>Postgresql driver. Specifically in the jdbc2/Statement.java file, the lines in >>executeBatch() that read: >> >> int[] resultSucceeded = new int[i]; >> System.arraycopy(result, 0, resultSucceeded, 0, i); >> >>should really be: >> >> int len = i+1; >> int[] resultSucceeded = new int[len]; >> System.arraycopy(result, 0, resultSucceeded, 0, len); >> >>I think that would do the trick. I'll now working on getting a modified version >>built so I can test it. >> >>Think I'm on the right track? wrong track? >> >> >>Thanks, >>Padraic >> >>Quoting Padraic Renaghan <list@renaghan.com>: >> >> >>>I'm having trouble with the error handling of the batch update feature >>>of the >>>JDBC 2.0 Postgresql driver. I do not see the standard behavior of >>>returing a -3 >>>for the statement that failed. >>> >>>In my example I insert mutliple rows in a single batch into a table. All >>>the >>>inserts have the same key value, which causes the second insert to fail >>>with a >>>duplicate key error. >>> >>>I get thrown back the BatchUpdateException which has the updateCounts >>>array >>>which I access via getUpdateCounts(). That array contains a single >>>entry, >>>updateCounts[0]=1. I am not seeing the -3 entry to indicate the stmt >>>that >>>failed. According to my assumptions about how JDBC 2.0 batch updates are >>> >>>supposed to work (and according to the JDBC--Postgresql compatability >>>web page) >>>I was hoping to see the updateCounts array contain: >>>updateCounts[0]=1 >>>updateCounts[1]=-3 >>> >>>I'm currently using Postgresql 7.2 and the JDBC driver installed with >>>the >>>Postgresql distributed rpm. Specifically I'm using jdbc7.2dev-1.2.jar >>> >>>Any help would be appreciated. >>> >>>Thanks >>>Padraic >>> >>> >>> >>-- >>Padraic Renaghan /pad-rik ren-a-han/ >>padraic@renaghan.com >>IM: abuhaina (AOL/Yahoo/MSN) 9437815 (ICQ) >>http://renaghan.com/pcr/ >>lure -> 390sig006@renaghan.com <- don't use >> >> >> > > >
Makes sense. Sorry about the confusion. I was writing some code on my local machine (laptop running Linux & pgsql) that I was also testing on our production box (Solaris with IBM DB2). I assumed they'd behave the same. Guess I need some "if isDB2()" logic in my error handler code. Thanks for the reply, Padraic * Barry Lind <barry@xythos.com> [Sep 1 8:10pm] > The behavior in the driver that you are seeing is correct according to > the jdbc spec. Your suggested changes actually violate the jdbc spec. > From section 6.1.1 of the JDBC2.1 Core API: > > "JDBC drivers that do not continue processing after a failure never > return -3 in an update count array. Drivers of this type simply return a > status array containing an entry for each command that was processed > successfully." > > thanks, > --Barry > > Padraic Renaghan wrote: > > >Sorry for talking with myself again... > > > >So I tried the code change below. Got slightly better results: > > > >updateCounts[0]=1 > >updateCounts[1]=0 > > > >I really think that second entry should be a -3. So I added one more > >line to that block so it now becomes: > > > >result[i]=-3; > >int len=i+1; > >int[] resultSucceeded = new int[len]; > >System.arraycopy(result, 0, resultSucceeded, 0, len); > > > >This worked best. I now get > > > >updateCounts[0]=1 > >updateCounts[1]=-3 > > > >So if folks think that is a valid change, I'll whip up a patch file > >and send it back to this list for further consideration. > > > >Thoughts? > > > >Thanks, > >Padraic > > > >* Padraic Renaghan <list@renaghan.com> [Aug 28 12:12pm]: > > > > > > > >>I think I might have figured it out. I think there might be a bug in the > >>Postgresql driver. Specifically in the jdbc2/Statement.java file, the lines in > >>executeBatch() that read: > >> > >> int[] resultSucceeded = new int[i]; > >> System.arraycopy(result, 0, resultSucceeded, 0, i); > >> > >>should really be: > >> > >> int len = i+1; > >> int[] resultSucceeded = new int[len]; > >> System.arraycopy(result, 0, resultSucceeded, 0, len); > >> > >>I think that would do the trick. I'll now working on getting a modified version > >>built so I can test it. > >> > >>Think I'm on the right track? wrong track? > >> > >> > >>Thanks, > >>Padraic > >> > >>Quoting Padraic Renaghan <list@renaghan.com>: > >> > >> > >>>I'm having trouble with the error handling of the batch update feature > >>>of the > >>>JDBC 2.0 Postgresql driver. I do not see the standard behavior of > >>>returing a -3 > >>>for the statement that failed. > >>> > >>>In my example I insert mutliple rows in a single batch into a table. All > >>>the > >>>inserts have the same key value, which causes the second insert to fail > >>>with a > >>>duplicate key error. > >>> > >>>I get thrown back the BatchUpdateException which has the updateCounts > >>>array > >>>which I access via getUpdateCounts(). That array contains a single > >>>entry, > >>>updateCounts[0]=1. I am not seeing the -3 entry to indicate the stmt > >>>that > >>>failed. According to my assumptions about how JDBC 2.0 batch updates are > >>> > >>>supposed to work (and according to the JDBC--Postgresql compatability > >>>web page) > >>>I was hoping to see the updateCounts array contain: > >>>updateCounts[0]=1 > >>>updateCounts[1]=-3 > >>> > >>>I'm currently using Postgresql 7.2 and the JDBC driver installed with > >>>the > >>>Postgresql distributed rpm. Specifically I'm using jdbc7.2dev-1.2.jar > >>> > >>>Any help would be appreciated. > >>> > >>>Thanks > >>>Padraic > >>> > >>> > >>> > >>-- > >>Padraic Renaghan /pad-rik ren-a-han/ > >>padraic@renaghan.com > >>IM: abuhaina (AOL/Yahoo/MSN) 9437815 (ICQ) > >>http://renaghan.com/pcr/ > >>lure -> 390sig006@renaghan.com <- don't use > >> > >> > >> > > > > > > > > > -- Padraic Renaghan /pad-rik ren-a-han/ padraic@renaghan.com IM: abuhaina (AOL/Yahoo/MSN) 9437815 (ICQ) http://renaghan.com/pcr/ lure --> 123sig987@renaghan.com <-- don't use