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(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