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

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