Re: R: Re: R: Re: Commitment control on updateable ResultSet

Yes, once there is an error in a transaction no other queries will succeed.

Dave

On Fri, Jan 2, 2009 at 8:59 AM, Flavio Palumbo <flavio(dot)palumbo(at)fastwebnet(dot)it
> wrote:

> Hi Dave,
>
> here is a testcase :
>
> code
> ----------------------------------------------------------------------------------------
>
> import java.sql.Connection;
> import java.sql.Statement;
> import java.sql.Savepoint;
>
> public class TestCommitPostgresRS {
> public TestCommitPostgresRS() {
> Connection con = null;
> try {
> Class.forName("org.postgresql.Driver").newInstance();
> con =
> java.sql.DriverManager.getConnection("jdbc:postgresql://localhost:5432/postgres",
> "user", "pass");
> Statement ps =
> con.createStatement(java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE,
> java.sql.ResultSet.CONCUR_UPDATABLE);
> con.setAutoCommit(false);
> String s = "SP" +
> java.util.Calendar.getInstance().getTimeInMillis();
> con.setSavepoint(s);
> try {
> java.sql.ResultSet rsC = ps.executeQuery("select count(*)
> from (select T1.* from public.teststrike T1 for update) as query");
> // this obviuosly wrong for the sintax and raises the exception
> // org.postgresql.util.PSQLException: ERROR: SELECT FOR UPDATE/SHARE is not
> allowed with aggregate functions
> // but it should be ignored
> } catch (Throwable tx) {
> System.out.println("Errore1 - " + tx);
> }
> java.sql.ResultSet rs = ps.executeQuery("select T1.* from
> public.teststrike T1 for update");
> // this raises the exception
> // org.postgresql.util.PSQLException: ERROR: current transaction is
> aborted, commands ignored until end of transaction block
> // this is the true point of interest for me
> } catch (Throwable t) {
> System.out.println("errore - " + t);
> }
> try {
> if (con != null) {
> con.commit();
> con.setAutoCommit(true);
> con.close();
> }
> } catch (Throwable t) {
> }
> }
> public static void main(String[] args) {
> new TestCommitPostgresRS();
> System.exit(0);
> }
> }
> code
> ----------------------------------------------------------------------------------------
> Now I realise that I execute 2 queries :
> - first to obtain the number of records included in the second query,
> ignored if raises exception
> - second to obtain real data
>
> In the test I can see that if I comment the first query the second works
> fine ... it seems that the 2 queries are tied for some reason.
>
> Any hint would be appreciated.
>
> Thanks
> Flavio
>
>
> ----Messaggio originale----
> Da: pg(at)fastcrypt(dot)com
> Data: 02/01/2009 14.16
> A: "Flavio Palumbo"<flavio(dot)palumbo(at)fastwebnet(dot)it>
> Ogg: Re: R: Re: [JDBC] Commitment control on updateable ResultSet
>
> You should be able to,
>
> Show me the exact error .
>
> Dave
>
> On Fri, Jan 2, 2009 at 8:05 AM, Flavio Palumbo <
> flavio(dot)palumbo(at)fastwebnet(dot)it> wrote:
>
>> Hi Dave,
>>
>> thanks for your replay, I followed in debug mode my program and I've seen
>> that the executeQuery method raised the exception.
>>
>> It appears as if commitment control cannot be applied to an updateable
>> ResultSet ; I cannot understand, what's wrong ?
>>
>>
>> Thanks
>> Flavio
>>
>> ----Messaggio originale----
>> Da: pg(at)fastcrypt(dot)com
>> Data: 02/01/2009 13.19
>> A: "Flavio Palumbo"<flavio(dot)palumbo(at)fastwebnet(dot)it>
>> Cc: <pgsql-jdbc(at)postgresql(dot)org>
>> Ogg: Re: [JDBC] Commitment control on updateable ResultSet
>>
>> One of the statements between setAutoCommit(false) and execute query is
>> generating an error.
>>
>> Dave
>>
>> On Fri, Jan 2, 2009 at 6:47 AM, Flavio Palumbo <
>> flavio(dot)palumbo(at)fastwebnet(dot)it> wrote:
>>
>>> Hi all,
>>>
>>> I try to control the commitment on an updatable ResultSet but I get a
>>> strange ex
>>> ception :
>>> "ERROR: current transaction is aborted, commands ignored until end of
>>> transactio
>>> n block"
>>>
>>> I do this :
>>> - Connection.setAutoCommit(false);
>>> - Connection.setSavepoint(point);
>>> - Connection.createStatement(java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE,
>>> java.sq
>>> l.ResultSet.CONCUR_UPDATABLE);
>>> - Statetement.executeQuery("SELECT T1.* FROM public.teststrike T1 for
>>> update")
>>>
>>> at this point a I get the above exception.
>>>
>>> Can somebody help me to figure out why this happens ?
>>>
>>> Thanks
>>> Flavio
>>>
>>>
>>> --
>>> Sent via pgsql-jdbc mailing list (pgsql-jdbc(at)postgresql(dot)org)
>>> To make changes to your subscription:
>>> http://www.postgresql.org/mailpref/pgsql-jdbc
>>>
>>
>>
>>
>>
>
>
>