Postgres Pro
Downloads
Home   >   mailing lists

R: Re: R: Re: Commitment control on updateable ResultSet - Mailing list pgsql-jdbc

From Flavio Palumbo
Subject R: Re: R: Re: Commitment control on updateable ResultSet
Date
Msg-id 19978789.2156021230904775497.JavaMail.root@pr004msr
Whole thread Raw
Responses Re: R: Re: R: Re: Commitment control on updateable ResultSet
List pgsql-jdbc
Tree view
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@fastcrypt.com
Data: 02/01/2009 14.16
A: "Flavio Palumbo"<flavio.palumbo@fastwebnet.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.palumbo@fastwebnet.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@fastcrypt.com
Data: 02/01/2009 13.19
A: "Flavio Palumbo"<flavio.palumbo@fastwebnet.it>
Cc: <pgsql-jdbc@postgresql.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.palumbo@fastwebnet.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@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc






pgsql-jdbc by date:

Previous
From: Flavio Palumbo
Date:
Subject: R: Re: Commitment control on updateable ResultSet
Next
From: "Dave Cramer"
Date:
Subject: Re: R: Re: R: Re: Commitment control on updateable ResultSet