Thread: PreparedStatement batch statement impossible
Hi, when using addBatch() and executeBatch() it is impossible to get the generated keys. addBatch() fails with an BatchUpdateExceptionbecause it isn't expecting results. I am using postgres 9.0 and the newest 9.0_801 JDBC-Thingy. The statement is generated with cn.prepareStatement(sqlStufff,Statement.RETURN_GENERATED_KEYS); Code works fine with Mysql, so I guess it's a driver problem. Until this bug is fixed, what is the workaround too use batchupdates on more than one table? Tables beeing linked by generatedkeys. Any ideas? Thanks so much. -- Schon gehört? GMX hat einen genialen Phishing-Filter in die Toolbar eingebaut! http://www.gmx.net/de/go/toolbar
Hans, The API says that executeBatch is to return an array of update counts. How would one return generated keys ? Dave On Wed, Mar 2, 2011 at 9:38 AM, hans wulf <lotu1@gmx.net> wrote: > Hi, > > when using addBatch() and executeBatch() it is impossible to get the generated keys. addBatch() fails with an BatchUpdateExceptionbecause it isn't expecting results. I am using postgres 9.0 and the newest 9.0_801 JDBC-Thingy. > > The statement is generated with cn.prepareStatement(sqlStufff,Statement.RETURN_GENERATED_KEYS); > > Code works fine with Mysql, so I guess it's a driver problem. > > Until this bug is fixed, what is the workaround too use batchupdates on more than one table? Tables beeing linked by generatedkeys. > > Any ideas? > > Thanks so much. > > > -- > Schon gehört? GMX hat einen genialen Phishing-Filter in die > Toolbar eingebaut! http://www.gmx.net/de/go/toolbar > > -- > Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-jdbc >
Hans, It's certainly not clear that this is a requirement of the API. Even executeBatch is an optional requirement. Does anyone have any clear direction on how to handle this ? It would appear that even oracle's jdbc throws an error. Dave On Tue, Mar 8, 2011 at 9:17 AM, hans wulf <lotu1@gmx.net> wrote: > hi, > > the API says you can use getGeneratedKeys() after executing the statements. But executeBatch() fails... > > The driver has got a problem here: > > The normal way would be: > > 1. Create a PreparedStatement with the GET_GENERATED_KEYS flag > 2. executeBatch without Exception > 3. call getGeneratedKeys() > > > -- > GMX DSL Doppel-Flat ab 19,99 Euro/mtl.! Jetzt mit > gratis Handy-Flat! http://portal.gmx.net/de/go/dsl >
Hi Dave,
thanks for the quick reply.
without executeBatch the whole JDBC driver is worthless, we don't need a discussion on that :-)
Without being able to get the generatedKeys from an Batch, it is impossible to insert into multiple tables
vendor-independently.
Mysql manages this without any problems.
This should be the way to go for the future.
I don't thing I have to tell the story of my ugly code:
if (MYSQL) doThePropperAPIStuff()
else if (POSTGRES) {
1. generate a bulk of future keys
2. pass out these future keys to all entries
}else if (ORACLE){
1. do more a less the same as in POSTGRES but with different Syntax
}
I don't think that sort of code was the intension of JDBC...
-------- Original-Nachricht --------
> Datum: Tue, 8 Mar 2011 09:27:44 -0500
> Von: Dave Cramer <pg@fastcrypt.com>
> An: hans wulf <lotu1@gmx.net>
> CC: pgsql-jdbc@postgresql.org
> Betreff: Re: PreparedStatement batch statement impossible
> Hans,
>
> It's certainly not clear that this is a requirement of the API. Even
> executeBatch is an optional requirement.
>
> Does anyone have any clear direction on how to handle this ? It would
> appear that even oracle's jdbc throws an error.
>
> Dave
>
> On Tue, Mar 8, 2011 at 9:17 AM, hans wulf <lotu1@gmx.net> wrote:
> > hi,
> >
> > the API says you can use getGeneratedKeys() after executing the
> statements. But executeBatch() fails...
> >
> > The driver has got a problem here:
> >
> > The normal way would be:
> >
> > 1. Create a PreparedStatement with the GET_GENERATED_KEYS flag
> > 2. executeBatch without Exception
> > 3. call getGeneratedKeys()
> >
> >
> > --
> > GMX DSL Doppel-Flat ab 19,99 Euro/mtl.! Jetzt mit
> > gratis Handy-Flat! http://portal.gmx.net/de/go/dsl
> >
--
GMX DSL Doppel-Flat ab 19,99 Euro/mtl.! Jetzt mit
gratis Handy-Flat! http://portal.gmx.net/de/go/dsl
hi, the API says you can use getGeneratedKeys() after executing the statements. But executeBatch() fails... The driver has got a problem here: The normal way would be: 1. Create a PreparedStatement with the GET_GENERATED_KEYS flag 2. executeBatch without Exception 3. call getGeneratedKeys() -- GMX DSL Doppel-Flat ab 19,99 Euro/mtl.! Jetzt mit gratis Handy-Flat! http://portal.gmx.net/de/go/dsl
On Tue, Mar 8, 2011 at 9:55 AM, hans wulf <lotu1@gmx.net> wrote:
> Hi Dave,
>
> thanks for the quick reply.
>
> without executeBatch the whole JDBC driver is worthless, we don't need a discussion on that :-)
>
> Without being able to get the generatedKeys from an Batch, it is impossible to insert into multiple tables
vendor-independently.
>
> Mysql manages this without any problems.
>
> This should be the way to go for the future.
>
> I don't thing I have to tell the story of my ugly code:
>
> if (MYSQL) doThePropperAPIStuff()
> else if (POSTGRES) {
> 1. generate a bulk of future keys
> 2. pass out these future keys to all entries
> }else if (ORACLE){
> 1. do more a less the same as in POSTGRES but with different Syntax
> }
>
> I don't think that sort of code was the intension of JDBC...
>
Well we accept patches. But obviously you aren't going to get away
from if (ORACLE) unless of course you are petitioning them to change
their driver ?
As far as the intention of JDBC, it has lots of ambiguity so it's not
clear that it had any real intention.
Dave
> -------- Original-Nachricht --------
>> Datum: Tue, 8 Mar 2011 09:27:44 -0500
>> Von: Dave Cramer <pg@fastcrypt.com>
>> An: hans wulf <lotu1@gmx.net>
>> CC: pgsql-jdbc@postgresql.org
>> Betreff: Re: PreparedStatement batch statement impossible
>
>> Hans,
>>
>> It's certainly not clear that this is a requirement of the API. Even
>> executeBatch is an optional requirement.
>>
>> Does anyone have any clear direction on how to handle this ? It would
>> appear that even oracle's jdbc throws an error.
>>
>> Dave
>>
>> On Tue, Mar 8, 2011 at 9:17 AM, hans wulf <lotu1@gmx.net> wrote:
>> > hi,
>> >
>> > the API says you can use getGeneratedKeys() after executing the
>> statements. But executeBatch() fails...
>> >
>> > The driver has got a problem here:
>> >
>> > The normal way would be:
>> >
>> > 1. Create a PreparedStatement with the GET_GENERATED_KEYS flag
>> > 2. executeBatch without Exception
>> > 3. call getGeneratedKeys()
>> >
>> >
>> > --
>> > GMX DSL Doppel-Flat ab 19,99 Euro/mtl.! Jetzt mit
>> > gratis Handy-Flat! http://portal.gmx.net/de/go/dsl
>> >
>
> --
> GMX DSL Doppel-Flat ab 19,99 Euro/mtl.! Jetzt mit
> gratis Handy-Flat! http://portal.gmx.net/de/go/dsl
>
On Wed, 2 Mar 2011, hans wulf wrote: > when using addBatch() and executeBatch() it is impossible to get the > generated keys. addBatch() fails with an BatchUpdateException because it > isn't expecting results. I am using postgres 9.0 and the newest 9.0_801 > JDBC-Thingy. > I have implemented returning generated keys for batch execution. Unfortunately, it does have to make a performance compromise to be able to support returning values. Since the returned values may be potentially large, we can't batch up multiple statements for sending to the server because of the deadlock risk. Test jar available here: http://www.ejurka.com/pgsql/jars/batchgenkey/ Kris Jurka