Thread: JDBC 7.3 dev (Java 2 SDK 1.4.0)

JDBC 7.3 dev (Java 2 SDK 1.4.0)

From
snpe
Date:
Hello,
  I have simple table with column ID and values '4' in this.
I user 7.3 beta1 (from cvs 05.09.2002) and autocommit off in postgresql.conf.
Next program don't work .
I am tried with compiled postgresql.jar form CVS and with
pg73b1jdbc3.jar from 05.09.2002 on jdbc.postgresql.org

What is wrong ?

regards
Haris Peco
import java.io.*;
import java.sql.*;
import java.text.*;

public class PrepStatTest
{
    Connection db;
    String stat="DELETE FROM org_ban WHERE \"id\" = ?";
    String delid = "4";
    public PrepStatTest() throws ClassNotFoundException, FileNotFoundException,
IOException, SQLException
    {
        Class.forName("org.postgresql.Driver");
        db = DriverManager.getConnection("jdbc:postgresql://spnew/snpe", "snpe",
"snpe");
        PreparedStatement st = db.prepareStatement(stat);
            st.setString(1, delid);
            int rowsDeleted = st.executeUpdate();
        System.out.println("Rows deleted " + rowsDeleted);
        db.commit();
        st.close();
        db.close();
    }

    public static void main(String args[])
    {
        try
        {
            PrepStatTest test = new PrepStatTest();
        }
        catch (Exception ex)
        {
            System.err.println("Exception caught.\n" + ex);
            ex.printStackTrace();
        }
    }
}


Re: JDBC 7.3 dev (Java 2 SDK 1.4.0)

From
Dave Cramer
Date:
Harris,

What error do you get?

Also you don't need  the quotes around id

Dave
On Fri, 2002-09-06 at 10:06, snpe wrote:
> Hello,
>   I have simple table with column ID and values '4' in this.
> I user 7.3 beta1 (from cvs 05.09.2002) and autocommit off in postgresql.conf.
> Next program don't work .
> I am tried with compiled postgresql.jar form CVS and with
> pg73b1jdbc3.jar from 05.09.2002 on jdbc.postgresql.org
>
> What is wrong ?
>
> regards
> Haris Peco
> import java.io.*;
> import java.sql.*;
> import java.text.*;
>
> public class PrepStatTest
> {
>     Connection db;
>     String stat="DELETE FROM org_ban WHERE \"id\" = ?";
>     String delid = "4";
>     public PrepStatTest() throws ClassNotFoundException, FileNotFoundException,
> IOException, SQLException
>     {
>         Class.forName("org.postgresql.Driver");
>         db = DriverManager.getConnection("jdbc:postgresql://spnew/snpe", "snpe",
> "snpe");
>         PreparedStatement st = db.prepareStatement(stat);
>             st.setString(1, delid);
>             int rowsDeleted = st.executeUpdate();
>         System.out.println("Rows deleted " + rowsDeleted);
>         db.commit();
>         st.close();
>         db.close();
>     }
>
>     public static void main(String args[])
>     {
>         try
>         {
>             PrepStatTest test = new PrepStatTest();
>         }
>         catch (Exception ex)
>         {
>             System.err.println("Exception caught.\n" + ex);
>             ex.printStackTrace();
>         }
>     }
> }
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>
>




Re: JDBC 7.3 dev (Java 2 SDK 1.4.0)

From
snpe
Date:
Hello Dave,
  There isn't any error.Program write 'Rows deleted 1', but row hasn't been
deleted

Thanks
Haris Peco
On Friday 06 September 2002 04:05 pm, Dave Cramer wrote:
> Harris,
>
> What error do you get?
>
> Also you don't need  the quotes around id
>
> Dave
>
> On Fri, 2002-09-06 at 10:06, snpe wrote:
> > Hello,
> >   I have simple table with column ID and values '4' in this.
> > I user 7.3 beta1 (from cvs 05.09.2002) and autocommit off in
> > postgresql.conf. Next program don't work .
> > I am tried with compiled postgresql.jar form CVS and with
> > pg73b1jdbc3.jar from 05.09.2002 on jdbc.postgresql.org
> >
> > What is wrong ?
> >
> > regards
> > Haris Peco
> > import java.io.*;
> > import java.sql.*;
> > import java.text.*;
> >
> > public class PrepStatTest
> > {
> >     Connection db;
> >     String stat="DELETE FROM org_ban WHERE \"id\" = ?";
> >     String delid = "4";
> >     public PrepStatTest() throws ClassNotFoundException,
> > FileNotFoundException, IOException, SQLException
> >     {
> >         Class.forName("org.postgresql.Driver");
> >         db = DriverManager.getConnection("jdbc:postgresql://spnew/snpe",
> > "snpe", "snpe");
> >         PreparedStatement st = db.prepareStatement(stat);
> >             st.setString(1, delid);
> >             int rowsDeleted = st.executeUpdate();
> >         System.out.println("Rows deleted " + rowsDeleted);
> >         db.commit();
> >         st.close();
> >         db.close();
> >     }
> >
> >     public static void main(String args[])
> >     {
> >         try
> >         {
> >             PrepStatTest test = new PrepStatTest();
> >         }
> >         catch (Exception ex)
> >         {
> >             System.err.println("Exception caught.\n" + ex);
> >             ex.printStackTrace();
> >         }
> >     }
> > }
> >
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly


Re: JDBC 7.3 dev (Java 2 SDK 1.4.0)

From
Dave Cramer
Date:
Remove the quotes around id, and let me know what happens

Dave
On Fri, 2002-09-06 at 10:52, snpe wrote:
> Hello Dave,
>   There isn't any error.Program write 'Rows deleted 1', but row hasn't been
> deleted
>
> Thanks
> Haris Peco
> On Friday 06 September 2002 04:05 pm, Dave Cramer wrote:
> > Harris,
> >
> > What error do you get?
> >
> > Also you don't need  the quotes around id
> >
> > Dave
> >
> > On Fri, 2002-09-06 at 10:06, snpe wrote:
> > > Hello,
> > >   I have simple table with column ID and values '4' in this.
> > > I user 7.3 beta1 (from cvs 05.09.2002) and autocommit off in
> > > postgresql.conf. Next program don't work .
> > > I am tried with compiled postgresql.jar form CVS and with
> > > pg73b1jdbc3.jar from 05.09.2002 on jdbc.postgresql.org
> > >
> > > What is wrong ?
> > >
> > > regards
> > > Haris Peco
> > > import java.io.*;
> > > import java.sql.*;
> > > import java.text.*;
> > >
> > > public class PrepStatTest
> > > {
> > >     Connection db;
> > >     String stat="DELETE FROM org_ban WHERE \"id\" = ?";
> > >     String delid = "4";
> > >     public PrepStatTest() throws ClassNotFoundException,
> > > FileNotFoundException, IOException, SQLException
> > >     {
> > >         Class.forName("org.postgresql.Driver");
> > >         db = DriverManager.getConnection("jdbc:postgresql://spnew/snpe",
> > > "snpe", "snpe");
> > >         PreparedStatement st = db.prepareStatement(stat);
> > >             st.setString(1, delid);
> > >             int rowsDeleted = st.executeUpdate();
> > >         System.out.println("Rows deleted " + rowsDeleted);
> > >         db.commit();
> > >         st.close();
> > >         db.close();
> > >     }
> > >
> > >     public static void main(String args[])
> > >     {
> > >         try
> > >         {
> > >             PrepStatTest test = new PrepStatTest();
> > >         }
> > >         catch (Exception ex)
> > >         {
> > >             System.err.println("Exception caught.\n" + ex);
> > >             ex.printStackTrace();
> > >         }
> > >     }
> > > }
> > >
> > >
> > > ---------------------------(end of broadcast)---------------------------
> > > TIP 3: if posting/reading through Usenet, please send an appropriate
> > > subscribe-nomail command to majordomo@postgresql.org so that your
> > > message can get through to the mailing list cleanly
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
>




Re: JDBC 7.3 dev (Java 2 SDK 1.4.0)

From
snpe
Date:
Hi Dave,
That is same.Program work with and without quote but row don't deleted.
Postgresql is 7.3 beta (from cvs) and parameter autocommit in postgresql.conf
is off (no auto commit).
I am tried with db.autocommit(true) after getConnection, but no success

I thin that is bug in JDBC
PGSql 7.3 beta have new features autocommit on/off and JDBC driver don't work
with autocommit off

Thanks

P.S
I am play ith Oracle JDeveloper 9i and Postgresql and I get error in prepared
statement like this error :
(oracle.jbo.SQLStmtException) JBO-27123: SQL error during call statement
preparation.  Statement:  DELETE FROM org_ban WHERE "id"=?

and pgsqlerror is :
(org.postgresql.util.PSQLException) Malformed stmt [DELETE FROM org_ban WHERE
"id"=?] usage : {[? =] call <some_function> ([? [,?]*]) }

I think that JDeveloper call CallableStatement for insert or delete (select
and update work fine), but I don't know how.

On Friday 06 September 2002 04:35 pm, Dave Cramer wrote:
> Remove the quotes around id, and let me know what happens
>
> Dave
>
> On Fri, 2002-09-06 at 10:52, snpe wrote:
> > Hello Dave,
> >   There isn't any error.Program write 'Rows deleted 1', but row hasn't
> > been deleted
> >
> > Thanks
> > Haris Peco
> >
> > On Friday 06 September 2002 04:05 pm, Dave Cramer wrote:
> > > Harris,
> > >
> > > What error do you get?
> > >
> > > Also you don't need  the quotes around id
> > >
> > > Dave
> > >
> > > On Fri, 2002-09-06 at 10:06, snpe wrote:
> > > > Hello,
> > > >   I have simple table with column ID and values '4' in this.
> > > > I user 7.3 beta1 (from cvs 05.09.2002) and autocommit off in
> > > > postgresql.conf. Next program don't work .
> > > > I am tried with compiled postgresql.jar form CVS and with
> > > > pg73b1jdbc3.jar from 05.09.2002 on jdbc.postgresql.org
> > > >
> > > > What is wrong ?
> > > >
> > > > regards
> > > > Haris Peco
> > > > import java.io.*;
> > > > import java.sql.*;
> > > > import java.text.*;
> > > >
> > > > public class PrepStatTest
> > > > {
> > > >     Connection db;
> > > >     String stat="DELETE FROM org_ban WHERE \"id\" = ?";
> > > >     String delid = "4";
> > > >     public PrepStatTest() throws ClassNotFoundException,
> > > > FileNotFoundException, IOException, SQLException
> > > >     {
> > > >         Class.forName("org.postgresql.Driver");
> > > >         db = DriverManager.getConnection("jdbc:postgresql://spnew/snpe",
> > > > "snpe", "snpe");
> > > >         PreparedStatement st = db.prepareStatement(stat);
> > > >             st.setString(1, delid);
> > > >             int rowsDeleted = st.executeUpdate();
> > > >         System.out.println("Rows deleted " + rowsDeleted);
> > > >         db.commit();
> > > >         st.close();
> > > >         db.close();
> > > >     }
> > > >
> > > >     public static void main(String args[])
> > > >     {
> > > >         try
> > > >         {
> > > >             PrepStatTest test = new PrepStatTest();
> > > >         }
> > > >         catch (Exception ex)
> > > >         {
> > > >             System.err.println("Exception caught.\n" + ex);
> > > >             ex.printStackTrace();
> > > >         }
> > > >     }
> > > > }
> > > >
> > > >
> > > > ---------------------------(end of
> > > > broadcast)--------------------------- TIP 3: if posting/reading
> > > > through Usenet, please send an appropriate subscribe-nomail command
> > > > to majordomo@postgresql.org so that your message can get through to
> > > > the mailing list cleanly
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster


Re: JDBC 7.3 dev (Java 2 SDK 1.4.0)

From
snpe
Date:
I set autocommit true in postgresql.conf and program work fine

regards
Haris Peco
On Friday 06 September 2002 04:35 pm, Dave Cramer wrote:
> Remove the quotes around id, and let me know what happens
>
> Dave
>
> On Fri, 2002-09-06 at 10:52, snpe wrote:
> > Hello Dave,
> >   There isn't any error.Program write 'Rows deleted 1', but row hasn't
> > been deleted
> >
> > Thanks
> > Haris Peco
> >
> > On Friday 06 September 2002 04:05 pm, Dave Cramer wrote:
> > > Harris,
> > >
> > > What error do you get?
> > >
> > > Also you don't need  the quotes around id
> > >
> > > Dave
> > >
> > > On Fri, 2002-09-06 at 10:06, snpe wrote:
> > > > Hello,
> > > >   I have simple table with column ID and values '4' in this.
> > > > I user 7.3 beta1 (from cvs 05.09.2002) and autocommit off in
> > > > postgresql.conf. Next program don't work .
> > > > I am tried with compiled postgresql.jar form CVS and with
> > > > pg73b1jdbc3.jar from 05.09.2002 on jdbc.postgresql.org
> > > >
> > > > What is wrong ?
> > > >
> > > > regards
> > > > Haris Peco
> > > > import java.io.*;
> > > > import java.sql.*;
> > > > import java.text.*;
> > > >
> > > > public class PrepStatTest
> > > > {
> > > >     Connection db;
> > > >     String stat="DELETE FROM org_ban WHERE \"id\" = ?";
> > > >     String delid = "4";
> > > >     public PrepStatTest() throws ClassNotFoundException,
> > > > FileNotFoundException, IOException, SQLException
> > > >     {
> > > >         Class.forName("org.postgresql.Driver");
> > > >         db = DriverManager.getConnection("jdbc:postgresql://spnew/snpe",
> > > > "snpe", "snpe");
> > > >         PreparedStatement st = db.prepareStatement(stat);
> > > >             st.setString(1, delid);
> > > >             int rowsDeleted = st.executeUpdate();
> > > >         System.out.println("Rows deleted " + rowsDeleted);
> > > >         db.commit();
> > > >         st.close();
> > > >         db.close();
> > > >     }
> > > >
> > > >     public static void main(String args[])
> > > >     {
> > > >         try
> > > >         {
> > > >             PrepStatTest test = new PrepStatTest();
> > > >         }
> > > >         catch (Exception ex)
> > > >         {
> > > >             System.err.println("Exception caught.\n" + ex);
> > > >             ex.printStackTrace();
> > > >         }
> > > >     }
> > > > }
> > > >
> > > >
> > > > ---------------------------(end of
> > > > broadcast)--------------------------- TIP 3: if posting/reading
> > > > through Usenet, please send an appropriate subscribe-nomail command
> > > > to majordomo@postgresql.org so that your message can get through to
> > > > the mailing list cleanly
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 2: you can get off all lists at once with the unregister command
> >     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster


Re: JDBC 7.3 dev (Java 2 SDK 1.4.0)

From
snpe
Date:
Hello,

If I set db.setAutoCommit(false) after getConnection row is deleted

Driver don't see parameter autocommit in postgresql.conf
regards
On Friday 06 September 2002 05:21 pm, snpe wrote:
> I set autocommit true in postgresql.conf and program work fine
>
> regards
> Haris Peco
>
> On Friday 06 September 2002 04:35 pm, Dave Cramer wrote:
> > Remove the quotes around id, and let me know what happens
> >
> > Dave
> >
> > On Fri, 2002-09-06 at 10:52, snpe wrote:
> > > Hello Dave,
> > >   There isn't any error.Program write 'Rows deleted 1', but row hasn't
> > > been deleted
> > >
> > > Thanks
> > > Haris Peco
> > >
> > > On Friday 06 September 2002 04:05 pm, Dave Cramer wrote:
> > > > Harris,
> > > >
> > > > What error do you get?
> > > >
> > > > Also you don't need  the quotes around id
> > > >
> > > > Dave
> > > >
> > > > On Fri, 2002-09-06 at 10:06, snpe wrote:
> > > > > Hello,
> > > > >   I have simple table with column ID and values '4' in this.
> > > > > I user 7.3 beta1 (from cvs 05.09.2002) and autocommit off in
> > > > > postgresql.conf. Next program don't work .
> > > > > I am tried with compiled postgresql.jar form CVS and with
> > > > > pg73b1jdbc3.jar from 05.09.2002 on jdbc.postgresql.org
> > > > >
> > > > > What is wrong ?
> > > > >
> > > > > regards
> > > > > Haris Peco
> > > > > import java.io.*;
> > > > > import java.sql.*;
> > > > > import java.text.*;
> > > > >
> > > > > public class PrepStatTest
> > > > > {
> > > > >     Connection db;
> > > > >     String stat="DELETE FROM org_ban WHERE \"id\" = ?";
> > > > >     String delid = "4";
> > > > >     public PrepStatTest() throws ClassNotFoundException,
> > > > > FileNotFoundException, IOException, SQLException
> > > > >     {
> > > > >         Class.forName("org.postgresql.Driver");
> > > > >         db = DriverManager.getConnection("jdbc:postgresql://spnew/snpe",
> > > > > "snpe", "snpe");
> > > > >         PreparedStatement st = db.prepareStatement(stat);
> > > > >             st.setString(1, delid);
> > > > >             int rowsDeleted = st.executeUpdate();
> > > > >         System.out.println("Rows deleted " + rowsDeleted);
> > > > >         db.commit();
> > > > >         st.close();
> > > > >         db.close();
> > > > >     }
> > > > >
> > > > >     public static void main(String args[])
> > > > >     {
> > > > >         try
> > > > >         {
> > > > >             PrepStatTest test = new PrepStatTest();
> > > > >         }
> > > > >         catch (Exception ex)
> > > > >         {
> > > > >             System.err.println("Exception caught.\n" + ex);
> > > > >             ex.printStackTrace();
> > > > >         }
> > > > >     }
> > > > > }
> > > > >
> > > > >
> > > > > ---------------------------(end of
> > > > > broadcast)--------------------------- TIP 3: if posting/reading
> > > > > through Usenet, please send an appropriate subscribe-nomail command
> > > > > to majordomo@postgresql.org so that your message can get through to
> > > > > the mailing list cleanly
> > >
> > > ---------------------------(end of
> > > broadcast)--------------------------- TIP 2: you can get off all lists
> > > at once with the unregister command (send "unregister
> > > YourEmailAddressHere" to majordomo@postgresql.org)
> >
> > ---------------------------(end of broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org


Re: JDBC 7.3 dev (Java 2 SDK 1.4.0)

From
snpe
Date:
Hello Dave,
  I am find bug with CallableStatement. in Pgsql JDBC (I think that is bug)
CallableStatement interface extends PreparedStatement (jdbc 3.0 specification)
and
command DELETE or UPDATE must work like with PreparedStatement
Pgsql JDBC work only with  {[? =] call <some_function> ([? [,?]*]) } form

Next code work in Oracle :

import java.io.*;
import java.sql.*;
import java.text.*;

public class PrepStatTestOra
{
    Connection db;
    String stat="DELETE FROM org_ban WHERE id = ?";
    String delid = "4";
    public PrepStatTestOra() throws ClassNotFoundException,
FileNotFoundException, IOException, SQLException
    {
        Class.forName("oracle.jdbc.OracleDriver");
        db = DriverManager.getConnection("jdbc:oracle:thin:@spnew:1521:V9i",
"snpe2001", "snpe2001");
        //db.setAutoCommit(false);
        //PrepareStatement st = db.prepareStatement(stat);
        CallableStatement st = db.prepareCall(stat);
            st.setString(1, delid);
            int rowsDeleted = st.executeUpdate();
        System.out.println("Rows deleted " + rowsDeleted);
        db.commit();
        st.close();
        db.close();
    }

    public static void main(String args[])
    {
        try
        {
            PrepStatTestOra test = new PrepStatTestOra();
        }
        catch (Exception ex)
        {
            System.err.println("Exception caught.\n" + ex);
            ex.printStackTrace();
        }
    }
}

This is for pgsql :

import java.io.*;
import java.sql.*;
import java.text.*;

public class PrepStatTest
{
    Connection db;
    String stat="DELETE FROM org_ban WHERE id = ?";
    String delid = "4";
    public PrepStatTest() throws ClassNotFoundException, FileNotFoundException,
IOException, SQLException
    {
        Class.forName("org.postgresql.Driver");
        db = DriverManager.getConnection("jdbc:postgresql://spnew/snpe", "snpe",
"snpe");
        db.setAutoCommit(false); // hack for 'autocommit true' in postgresql.conf
        //PrepareStatement st = db.prepareStatement(stat); // PreparedStatement work
fine
        CallableStatement st = db.prepareCall(stat); // this must work like previous
line with PreparedStatement
            st.setString(1, delid);
            int rowsDeleted = st.executeUpdate();
        System.out.println("Rows deleted " + rowsDeleted);
        db.commit();
        st.close();
        db.close();
    }

    public static void main(String args[])
    {
        try
        {
            PrepStatTest test = new PrepStatTest();
        }
        catch (Exception ex)
        {
            System.err.println("Exception caught.\n" + ex);
            ex.printStackTrace();
        }
    }
}

Example for Oracle work fine and Pgsql get error (same in JDeveloper) :

Exception caught.
Malformed stmt [DELETE FROM org_ban WHERE "id" = ?] usage : {[? =] call
<some_function> ([? [,?]*]) }
Malformed stmt [DELETE FROM org_ban WHERE "id" = ?] usage : {[? =] call
<some_function> ([? [,?]*]) }
    at
org.postgresql.jdbc1.AbstractJdbc1Statement.modifyJdbcCall(AbstractJdbc1Statement.java:1720)
    at
org.postgresql.jdbc1.AbstractJdbc1Statement.parseSqlStmt(AbstractJdbc1Statement.java:88)
    at
org.postgresql.jdbc1.AbstractJdbc1Statement.<init>(AbstractJdbc1Statement.java:79)
    at
org.postgresql.jdbc2.AbstractJdbc2Statement.<init>(AbstractJdbc2Statement.java:32)
    at
org.postgresql.jdbc3.AbstractJdbc3Statement.<init>(AbstractJdbc3Statement.java:23)
    at
org.postgresql.jdbc3.Jdbc3CallableStatement.<init>(Jdbc3CallableStatement.java:11)
    at org.postgresql.jdbc3.Jdbc3Connection.prepareCall(Jdbc3Connection.java:36)
    at
org.postgresql.jdbc2.AbstractJdbc2Connection.prepareCall(AbstractJdbc2Connection.java:39)
    at PrepStatTest.<init>(PrepStatTest.java:16)
    at PrepStatTest.main(PrepStatTest.java:29)

On Friday 06 September 2002 05:38 pm, you wrote:
> Possibly, callable statements are a bit of a hack in postgres, since
> they don't really exist. If you can send me something that causes the
> errors I can try to fix it.
>
> Dave
>
> On Fri, 2002-09-06 at 11:59, snpe wrote:
> > Hello,
> > This is postgresql error on DELETE command
> > 'Malformed stmt' is in CallableStatement in JDBC source only
> > I think that CallableStatement in Pgsql JDBC driver have any
> > incompatibilty because meratnt drivers (for DB2, MS SQL) work fine
> >
> > Thanks
> > Haris Peco
> > (org.postgresql.util.PSQLException) Malformed stmt [DELETE FROM org_ban
> > WHERE "id"=?] usage : {[? =] call <some_function> ([? [,?]*]) }
> >
> > On Friday 06 September 2002 05:21 pm, you wrote:
> > > No, not off hand, I've never used JDeveloper, nor have I seen that
> > > error message
> > >
> > > Sorry,
> > >
> > > Dave
> > >
> > > On Fri, 2002-09-06 at 11:36, snpe wrote:
> > > > Hi Dave
> > > > Have You any cooment on my P.S. (problem with JDeveloper) ?
> > > >
> > > > On Friday 06 September 2002 05:07 pm, you wrote:
> > > > > Hmmm.... interesting, I guess we have to fix that in the driver
> > > > >
> > > > > Dave
> > > > >
> > > > > On Fri, 2002-09-06 at 11:21, snpe wrote:
> > > > > > I set autocommit true in postgresql.conf and program work fine
> > > > > >
> > > > > > regards
> > > > > > Haris Peco
> > > > > >
> > > > > > On Friday 06 September 2002 04:35 pm, Dave Cramer wrote:
> > > > > > > Remove the quotes around id, and let me know what happens
> > > > > > >
> > > > > > > Dave
> > > > > > >
> > > > > > > On Fri, 2002-09-06 at 10:52, snpe wrote:
> > > > > > > > Hello Dave,
> > > > > > > >   There isn't any error.Program write 'Rows deleted 1', but
> > > > > > > > row hasn't been deleted
> > > > > > > >
> > > > > > > > Thanks
> > > > > > > > Haris Peco
> > > > > > > >
> > > > > > > > On Friday 06 September 2002 04:05 pm, Dave Cramer wrote:
> > > > > > > > > Harris,
> > > > > > > > >
> > > > > > > > > What error do you get?
> > > > > > > > >
> > > > > > > > > Also you don't need  the quotes around id
> > > > > > > > >
> > > > > > > > > Dave
> > > > > > > > >
> > > > > > > > > On Fri, 2002-09-06 at 10:06, snpe wrote:
> > > > > > > > > > Hello,
> > > > > > > > > >   I have simple table with column ID and values '4' in
> > > > > > > > > > this. I user 7.3 beta1 (from cvs 05.09.2002) and
> > > > > > > > > > autocommit off in postgresql.conf. Next program don't
> > > > > > > > > > work .
> > > > > > > > > > I am tried with compiled postgresql.jar form CVS and with
> > > > > > > > > > pg73b1jdbc3.jar from 05.09.2002 on jdbc.postgresql.org
> > > > > > > > > >
> > > > > > > > > > What is wrong ?
> > > > > > > > > >
> > > > > > > > > > regards
> > > > > > > > > > Haris Peco
> > > > > > > > > > import java.io.*;
> > > > > > > > > > import java.sql.*;
> > > > > > > > > > import java.text.*;
> > > > > > > > > >
> > > > > > > > > > public class PrepStatTest
> > > > > > > > > > {
> > > > > > > > > >     Connection db;
> > > > > > > > > >     String stat="DELETE FROM org_ban WHERE \"id\" = ?";
> > > > > > > > > >     String delid = "4";
> > > > > > > > > >     public PrepStatTest() throws ClassNotFoundException,
> > > > > > > > > > FileNotFoundException, IOException, SQLException
> > > > > > > > > >     {
> > > > > > > > > >         Class.forName("org.postgresql.Driver");
> > > > > > > > > >         db =
> > > > > > > > > > DriverManager.getConnection("jdbc:postgresql://spnew/snpe
> > > > > > > > > >", "snpe", "snpe");
> > > > > > > > > >         PreparedStatement st = db.prepareStatement(stat);
> > > > > > > > > >             st.setString(1, delid);
> > > > > > > > > >             int rowsDeleted = st.executeUpdate();
> > > > > > > > > >         System.out.println("Rows deleted " + rowsDeleted);
> > > > > > > > > >         db.commit();
> > > > > > > > > >         st.close();
> > > > > > > > > >         db.close();
> > > > > > > > > >     }
> > > > > > > > > >
> > > > > > > > > >     public static void main(String args[])
> > > > > > > > > >     {
> > > > > > > > > >         try
> > > > > > > > > >         {
> > > > > > > > > >             PrepStatTest test = new PrepStatTest();
> > > > > > > > > >         }
> > > > > > > > > >         catch (Exception ex)
> > > > > > > > > >         {
> > > > > > > > > >             System.err.println("Exception caught.\n" + ex);
> > > > > > > > > >             ex.printStackTrace();
> > > > > > > > > >         }
> > > > > > > > > >     }
> > > > > > > > > > }
> > > > > > > > > >
> > > > > > > > > >
> > > > > > > > > > ---------------------------(end of
> > > > > > > > > > broadcast)--------------------------- TIP 3: if
> > > > > > > > > > posting/reading through Usenet, please send an
> > > > > > > > > > appropriate subscribe-nomail command to
> > > > > > > > > > majordomo@postgresql.org so that your message can get
> > > > > > > > > > through to the mailing list cleanly
> > > > > > > >
> > > > > > > > ---------------------------(end of
> > > > > > > > broadcast)--------------------------- TIP 2: you can get off
> > > > > > > > all lists at once with the unregister command (send
> > > > > > > > "unregister YourEmailAddressHere" to
> > > > > > > > majordomo@postgresql.org)
> > > > > > >
> > > > > > > ---------------------------(end of
> > > > > > > broadcast)--------------------------- TIP 4: Don't 'kill -9'
> > > > > > > the postmaster
> > > > > >
> > > > > > ---------------------------(end of
> > > > > > broadcast)--------------------------- TIP 6: Have you searched
> > > > > > our list archives?
> > > > > >
> > > > > > http://archives.postgresql.org


Re: problem with new autocommit config parameter and jdbc

From
snpe
Date:
Hello Barry,
  JDBC driver must find autocommit (off or on) and set autoCommit field
when open connection.
regards
On Friday 06 September 2002 06:52 pm, Barry Lind wrote:
> Haris,
>
> You can't use jdbc (and probably most other postgres clients) with
> autocommit in postgresql.conf turned off.
>
> Hackers,
>
> How should client interfaces handle this new autocommit feature?  Is it
> best to just issue a set at the beginning of the connection to ensure
> that it is always on?
>
> thanks,
> --Barry
>
> snpe wrote:
>  >Hi Dave,
>  >That is same.Program work with and without quote but row don't deleted.
>  >Postgresql is 7.3 beta (from cvs) and parameter autocommit in
>
> postgresql.conf
>
>  >is off (no auto commit).
>  >I am tried with db.autocommit(true) after getConnection, but no success
>  >
>  >I thin that is bug in JDBC
>  >PGSql 7.3 beta have new features autocommit on/off and JDBC driver
>
> don't work
>
>  >with autocommit off
>  >
>  >Thanks
>  >
>  >P.S
>  >I am play ith Oracle JDeveloper 9i and Postgresql and I get error in
>
> prepared
>
>  >statement like this error :
>  >(oracle.jbo.SQLStmtException) JBO-27123: SQL error during call statement
>  >preparation.  Statement:  DELETE FROM org_ban WHERE "id"=?
>  >
>  >and pgsqlerror is :
>  >(org.postgresql.util.PSQLException) Malformed stmt [DELETE FROM
>
> org_ban WHERE
>
>  >"id"=?] usage : {[? =] call <some_function> ([? [,?]*]) }
>  >
>  >I think that JDeveloper call CallableStatement for insert or delete
>
> (select
>
>  >and update work fine), but I don't know how.
>  >
>  >On Friday 06 September 2002 04:35 pm, Dave Cramer wrote:
>  >>Remove the quotes around id, and let me know what happens
>  >>
>  >>Dave
>  >>
>  >>On Fri, 2002-09-06 at 10:52, snpe wrote:
>  >>>Hello Dave,
>  >>>  There isn't any error.Program write 'Rows deleted 1', but row hasn't
>  >>>been deleted
>  >>>
>  >>>Thanks
>  >>>Haris Peco
>  >>>
>  >>>On Friday 06 September 2002 04:05 pm, Dave Cramer wrote:
>  >>>>Harris,
>  >>>>
>  >>>>What error do you get?
>  >>>>
>  >>>>Also you don't need  the quotes around id
>  >>>>
>  >>>>Dave
>  >>>>
>  >>>>On Fri, 2002-09-06 at 10:06, snpe wrote:
>  >>>>>Hello,
>  >>>>>  I have simple table with column ID and values '4' in this.
>  >>>>>I user 7.3 beta1 (from cvs 05.09.2002) and autocommit off in
>  >>>>>postgresql.conf. Next program don't work .
>  >>>>>I am tried with compiled postgresql.jar form CVS and with
>  >>>>>pg73b1jdbc3.jar from 05.09.2002 on jdbc.postgresql.org
>  >>>>>
>  >>>>>What is wrong ?
>  >>>>>
>  >>>>>regards
>  >>>>>Haris Peco
>  >>>>>import java.io.*;
>  >>>>>import java.sql.*;
>  >>>>>import java.text.*;
>  >>>>>
>  >>>>>public class PrepStatTest
>  >>>>>{
>  >>>>>    Connection db;
>  >>>>>    String stat="DELETE FROM org_ban WHERE \"id\" = ?";
>  >>>>>    String delid = "4";
>  >>>>>    public PrepStatTest() throws ClassNotFoundException,
>  >>>>>FileNotFoundException, IOException, SQLException
>  >>>>>    {
>
>     Class.forName("org.postgresql.Driver");
>
>     db = DriverManager.getConnection("jdbc:postgresql://spnew/snpe",
>
>  >>>>>"snpe", "snpe");
>
>     PreparedStatement st = db.prepareStatement(stat);
>
>  >>>>>            st.setString(1, delid);
>  >>>>>            int rowsDeleted = st.executeUpdate();
>
>     System.out.println("Rows deleted " + rowsDeleted);
>
>     db.commit();
>
>     st.close();
>
>     db.close();
>
>  >>>>>    }
>  >>>>>
>  >>>>>    public static void main(String args[])
>  >>>>>    {
>
>     try
>
>     {
>
>         PrepStatTest test = new PrepStatTest();
>
>     }
>
>     catch (Exception ex)
>
>     {
>
>         System.err.println("Exception caught.\n" + ex);
>
>         ex.printStackTrace();
>
>     }
>
>  >>>>>    }
>  >>>>>}
>  >>>>>
>  >>>>>
>  >>>>>---------------------------(end of
>  >>>>>broadcast)--------------------------- TIP 3: if posting/reading
>  >>>>>through Usenet, please send an appropriate subscribe-nomail command
>  >>>>>to majordomo@postgresql.org so that your message can get through to
>  >>>>>the mailing list cleanly
>  >>>
>  >>>---------------------------(end of
>  >>> broadcast)--------------------------- TIP 2: you can get off all lists
>  >>> at once with the unregister command (send "unregister
>  >>> YourEmailAddressHere" to majordomo@postgresql.org)
>  >>
>  >>---------------------------(end of broadcast)---------------------------
>  >>TIP 4: Don't 'kill -9' the postmaster
>  >
>  >---------------------------(end of broadcast)---------------------------
>  >TIP 3: if posting/reading through Usenet, please send an appropriate
>  >subscribe-nomail command to majordomo@postgresql.org so that your
>  >message can get through to the mailing list cleanly


problem with new autocommit config parameter and jdbc

From
Barry Lind
Date:
Haris,

You can't use jdbc (and probably most other postgres clients) with
autocommit in postgresql.conf turned off.

Hackers,

How should client interfaces handle this new autocommit feature?  Is it
best to just issue a set at the beginning of the connection to ensure
that it is always on?

thanks,
--Barry



snpe wrote:

   >Hi Dave,
   >That is same.Program work with and without quote but row don't deleted.
   >Postgresql is 7.3 beta (from cvs) and parameter autocommit in
postgresql.conf
   >is off (no auto commit).
   >I am tried with db.autocommit(true) after getConnection, but no success
   >
   >I thin that is bug in JDBC
   >PGSql 7.3 beta have new features autocommit on/off and JDBC driver
don't work
   >with autocommit off
   >
   >Thanks
   >
   >P.S
   >I am play ith Oracle JDeveloper 9i and Postgresql and I get error in
prepared
   >statement like this error :
   >(oracle.jbo.SQLStmtException) JBO-27123: SQL error during call statement
   >preparation.  Statement:  DELETE FROM org_ban WHERE "id"=?
   >
   >and pgsqlerror is :
   >(org.postgresql.util.PSQLException) Malformed stmt [DELETE FROM
org_ban WHERE
   >"id"=?] usage : {[? =] call <some_function> ([? [,?]*]) }
   >
   >I think that JDeveloper call CallableStatement for insert or delete
(select
   >and update work fine), but I don't know how.
   >
   >On Friday 06 September 2002 04:35 pm, Dave Cramer wrote:
   >
   >
   >>Remove the quotes around id, and let me know what happens
   >>
   >>Dave
   >>
   >>On Fri, 2002-09-06 at 10:52, snpe wrote:
   >>
   >>
   >>>Hello Dave,
   >>>  There isn't any error.Program write 'Rows deleted 1', but row hasn't
   >>>been deleted
   >>>
   >>>Thanks
   >>>Haris Peco
   >>>
   >>>On Friday 06 September 2002 04:05 pm, Dave Cramer wrote:
   >>>
   >>>
   >>>>Harris,
   >>>>
   >>>>What error do you get?
   >>>>
   >>>>Also you don't need  the quotes around id
   >>>>
   >>>>Dave
   >>>>
   >>>>On Fri, 2002-09-06 at 10:06, snpe wrote:
   >>>>
   >>>>
   >>>>>Hello,
   >>>>>  I have simple table with column ID and values '4' in this.
   >>>>>I user 7.3 beta1 (from cvs 05.09.2002) and autocommit off in
   >>>>>postgresql.conf. Next program don't work .
   >>>>>I am tried with compiled postgresql.jar form CVS and with
   >>>>>pg73b1jdbc3.jar from 05.09.2002 on jdbc.postgresql.org
   >>>>>
   >>>>>What is wrong ?
   >>>>>
   >>>>>regards
   >>>>>Haris Peco
   >>>>>import java.io.*;
   >>>>>import java.sql.*;
   >>>>>import java.text.*;
   >>>>>
   >>>>>public class PrepStatTest
   >>>>>{
   >>>>>    Connection db;
   >>>>>    String stat="DELETE FROM org_ban WHERE \"id\" = ?";
   >>>>>    String delid = "4";
   >>>>>    public PrepStatTest() throws ClassNotFoundException,
   >>>>>FileNotFoundException, IOException, SQLException
   >>>>>    {
   >>>>>
    Class.forName("org.postgresql.Driver");
   >>>>>
    db = DriverManager.getConnection("jdbc:postgresql://spnew/snpe",
   >>>>>"snpe", "snpe");
   >>>>>
    PreparedStatement st = db.prepareStatement(stat);
   >>>>>            st.setString(1, delid);
   >>>>>            int rowsDeleted = st.executeUpdate();
   >>>>>
    System.out.println("Rows deleted " + rowsDeleted);
   >>>>>
    db.commit();
   >>>>>
    st.close();
   >>>>>
    db.close();
   >>>>>    }
   >>>>>
   >>>>>    public static void main(String args[])
   >>>>>    {
   >>>>>
    try
   >>>>>
    {
   >>>>>
        PrepStatTest test = new PrepStatTest();
   >>>>>
    }
   >>>>>
    catch (Exception ex)
   >>>>>
    {
   >>>>>
        System.err.println("Exception caught.\n" + ex);
   >>>>>
        ex.printStackTrace();
   >>>>>
    }
   >>>>>    }
   >>>>>}
   >>>>>
   >>>>>
   >>>>>---------------------------(end of
   >>>>>broadcast)--------------------------- TIP 3: if posting/reading
   >>>>>through Usenet, please send an appropriate subscribe-nomail command
   >>>>>to majordomo@postgresql.org so that your message can get through to
   >>>>>the mailing list cleanly
   >>>>>
   >>>>>
   >>>---------------------------(end of
broadcast)---------------------------
   >>>TIP 2: you can get off all lists at once with the unregister command
   >>>    (send "unregister YourEmailAddressHere" to
majordomo@postgresql.org)
   >>>
   >>>
   >>---------------------------(end of
broadcast)---------------------------
   >>TIP 4: Don't 'kill -9' the postmaster
   >>
   >>
   >
   >
   >---------------------------(end of broadcast)---------------------------
   >TIP 3: if posting/reading through Usenet, please send an appropriate
   >subscribe-nomail command to majordomo@postgresql.org so that your
   >message can get through to the mailing list cleanly
   >
   >
   >






Re: [HACKERS] problem with new autocommit config parameter and jdbc

From
Bruce Momjian
Date:
Barry Lind wrote:
> Haris,
>
> You can't use jdbc (and probably most other postgres clients) with
> autocommit in postgresql.conf turned off.
>
> Hackers,
>
> How should client interfaces handle this new autocommit feature?  Is it
> best to just issue a set at the beginning of the connection to ensure
> that it is always on?

Yes, I thought that was the best fix for apps that can't deal with
autocommit being off.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: [HACKERS] problem with new autocommit config parameter and jdbc

From
snpe
Date:
On Saturday 07 September 2002 02:55 am, Bruce Momjian wrote:
> Barry Lind wrote:
> > Haris,
> >
> > You can't use jdbc (and probably most other postgres clients) with
> > autocommit in postgresql.conf turned off.
> >
> > Hackers,
> >
> > How should client interfaces handle this new autocommit feature?  Is it
> > best to just issue a set at the beginning of the connection to ensure
> > that it is always on?
>
> Yes, I thought that was the best fix for apps that can't deal with
> autocommit being off.
Can client get information from backend for autocommit (on or off) and that
work like psql ?



Re: [HACKERS] problem with new autocommit config parameter and

From
Bruce Momjian
Date:
snpe wrote:
> On Saturday 07 September 2002 02:55 am, Bruce Momjian wrote:
> > Barry Lind wrote:
> > > Haris,
> > >
> > > You can't use jdbc (and probably most other postgres clients) with
> > > autocommit in postgresql.conf turned off.
> > >
> > > Hackers,
> > >
> > > How should client interfaces handle this new autocommit feature?  Is it
> > > best to just issue a set at the beginning of the connection to ensure
> > > that it is always on?
> >
> > Yes, I thought that was the best fix for apps that can't deal with
> > autocommit being off.
> Can client get information from backend for autocommit (on or off) and that
> work like psql ?

Sure, you can do SHOW autocommit.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: [HACKERS] problem with new autocommit config parameter

From
Barry Lind
Date:
Yes it is possible, but according to the jdbc spec, a new connection in
jdbc is always initialized to autocommit=true.  So jdbc needs to ignore
whatever the current server setting is and reset to autocommit=true.

--Barry

snpe wrote:
> On Saturday 07 September 2002 02:55 am, Bruce Momjian wrote:
>
>>Barry Lind wrote:
>>
>>>Haris,
>>>
>>>You can't use jdbc (and probably most other postgres clients) with
>>>autocommit in postgresql.conf turned off.
>>>
>>>Hackers,
>>>
>>>How should client interfaces handle this new autocommit feature?  Is it
>>>best to just issue a set at the beginning of the connection to ensure
>>>that it is always on?
>>
>>Yes, I thought that was the best fix for apps that can't deal with
>>autocommit being off.
>
> Can client get information from backend for autocommit (on or off) and that
> work like psql ?
>
>
>


Re: [HACKERS] problem with new autocommit config parameter and jdbc

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Barry Lind wrote:
>> How should client interfaces handle this new autocommit feature?  Is it
>> best to just issue a set at the beginning of the connection to ensure
>> that it is always on?

> Yes, I thought that was the best fix for apps that can't deal with
> autocommit being off.

If autocommit=off really seriously breaks JDBC then I don't think a
simple SET command at the start of a session is going to do that much
to improve robustness.  What if the user issues another SET to turn it
on?

I'd suggest just documenting that it is broken and you can't use it,
until such time as you can get it fixed.  Band-aids that only partially
cover the problem don't seem worth the effort to me.

In general I think that autocommit=off is probably going to be very
poorly supported in the 7.3 release.  We can document it as being
"work in progress, use at your own risk".

            regards, tom lane

Re: [HACKERS] problem with new autocommit config parameter and jdbc

From
snpe
Date:
On Monday 09 September 2002 08:53 pm, Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Barry Lind wrote:
> >> How should client interfaces handle this new autocommit feature?  Is it
> >> best to just issue a set at the beginning of the connection to ensure
> >> that it is always on?
> >
> > Yes, I thought that was the best fix for apps that can't deal with
> > autocommit being off.
>
> If autocommit=off really seriously breaks JDBC then I don't think a
> simple SET command at the start of a session is going to do that much
> to improve robustness.  What if the user issues another SET to turn it
> on?
>
> I'd suggest just documenting that it is broken and you can't use it,
> until such time as you can get it fixed.  Band-aids that only partially
> cover the problem don't seem worth the effort to me.
>
> In general I think that autocommit=off is probably going to be very
> poorly supported in the 7.3 release.  We can document it as being
> "work in progress, use at your own risk".
>

I'm use 'autocommit=false' and have problem with psql
When any commnad is lost, then next commnad get error for transactions
(simple select command).BTW

snpe> select * from org_ba;
ERROR: relation org_ba does not exists
snpe> select * from org_ban;
ERROR: current transactions is aborted, queries ignored until end of
transaction block
snpe> rollback;
ROLLBACK
snpe> select * from org_ban;

this command is ok.
regards
Haris Peco

Re: [HACKERS] problem with new autocommit config parameter and jdbc

From
Tom Lane
Date:
snpe <snpe@snpe.co.yu> writes:
> I'm use 'autocommit=false' and have problem with psql
> When any commnad is lost, then next commnad get error for transactions
> (simple select command).BTW

> snpe> select * from org_ba;
> ERROR: relation org_ba does not exists
> snpe> select * from org_ban;
> ERROR: current transactions is aborted, queries ignored until end of
> transaction block

Um, what's wrong with that?

It seems to me that an application that is using autocommit=off will
expect the first SELECT to start a transaction block.  If the first
SELECT fails, then subsequent commands *should* fail until you commit
or rollback.  Certainly if you did an explicit BEGIN before the first
SELECT, the above is what you'd get --- why should implicit BEGIN
work differently?

            regards, tom lane

Re: [HACKERS] problem with new autocommit config parameter and jdbc

From
Curt Sampson
Date:
On Mon, 9 Sep 2002, Tom Lane wrote:

> snpe <snpe@snpe.co.yu> writes:
>
> > snpe> select * from org_ba;
> > ERROR: relation org_ba does not exists
> > snpe> select * from org_ban;
> > ERROR: current transactions is aborted, queries ignored until end of
> > transaction block
>
> Um, what's wrong with that?
>
> It seems to me that an application that is using autocommit=off will
> expect the first SELECT to start a transaction block.

Yup. In fact, the standard (at least, insofar as I have information
relating to it), specifies that the first SELECT statement above
*must* start a transaction.

From Date's _A Guide to the SQL Standard_ (Fourth Edition):

    An SQL-transaction is initiated when the relevant SQL-agent executes
    a "transaction-initiating" SQL Statement (see below) and the
    SQL-agent does not already have an SQL-transaction in progress.
    ...
    The following SQL statements are _not_ transaction-initiating:

    CONNECT
    SET CONNECTION
    DISCONNECT
    SET SESSION AUTHORIZATION
    SET CATALOG
    SET SCHEMA
    SET NAMES
    SET TIME ZONE
    SET TRANSACTION
    SET CONSTRAINTS
    COMMIT
    ROLLBACK
    GET DIAGNOSTICS

    Nor, of course, are the nonexecutable statements DECLARE CURSOR,
    DECLAR LOCAL TEMPORARY TABLE, BEGIN DECLARE SECTION, SEND DECLARE
    SECTIONS, and WHENEVER.

So SELECT ought always to initiate a transaction, if one is not already
in progress. If auto-commit is enabled, of course, that statement may
be committed immediately after execution, if it doesn't fail.

As far as the JDBC driver goes, I'm not too sure of the issues here, but
it should certainly be ensuring that autocommit is enabled, as per the
JDBC specification, when a new connection is created. I see no reason
this couldn't be done with a "SET AUTOCOMMIT TO OFF" or whatever, if
that's necessary to override a possible configuration file setting.

cjs
--
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC


Re: [HACKERS] problem with new autocommit config parameter

From
Curt Sampson
Date:
On Mon, 9 Sep 2002, Tom Lane wrote:

> If autocommit=off really seriously breaks JDBC then I don't think a
> simple SET command at the start of a session is going to do that much
> to improve robustness.  What if the user issues another SET to turn it
> on?

You mean, to turn it off again? The driver should catch this, in theory.

In practice we could probably live with saying, "Don't use SET
AUTOCOMMIT; use the methods on the Connection class instead."

Probably the driver should be changed for 7.3 just to use the server's
SET AUTOCOMMIT functionality....

cjs
--
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC


Re: [HACKERS] problem with new autocommit config parameter and jdbc

From
Tom Lane
Date:
Curt Sampson <cjs@cynic.net> writes:
> Probably the driver should be changed for 7.3 just to use the server's
> SET AUTOCOMMIT functionality....

That should happen eventually, IMHO, but I am not going to tell the JDBC
developers that they must make it happen for 7.3.  They've already got a
pile of much-higher-priority things to fix for 7.3, like schema
compatibility and dropped-column handling.

My feeling about the original complaint is very simple: setting server
autocommit to off is not supported with JDBC (nor is it fully supported
with any other of our frontend clients, right at this instant, though
that may improve somewhat before 7.3 release).  If you don't like it,
tough; contribute the required fixes or stop complaining.  Someone else
will fix it when they get around to it, but there are bigger problems to
deal with first.  Autocommit is only a work-in-progress today, not
something that we promise will do anything useful for anybody.

            regards, tom lane

Re: [HACKERS] problem with new autocommit config parameter and jdbc

From
Tom Lane
Date:
Curt Sampson <cjs@cynic.net> writes:
> From Date's _A Guide to the SQL Standard_ (Fourth Edition):
>     ...
>     The following SQL statements are _not_ transaction-initiating:

>     CONNECT
>     SET CONNECTION
>     DISCONNECT
>     SET SESSION AUTHORIZATION
>     SET CATALOG
>     SET SCHEMA
>     SET NAMES
>     SET TIME ZONE
>     SET TRANSACTION
>     SET CONSTRAINTS
>     COMMIT
>     ROLLBACK
>     GET DIAGNOSTICS

Hm.  This brings up a thought I've been turning over for the past
couple days.  As of CVS tip, SET commands *do* initiate transactions
if you have autocommit off.  By your reading of Date, this is not
spec compliant for certain SET variables: a SET not already within
a transaction should not start a transaction block, at least for the
variables mentioned above.  It occurs to me that it'd be reasonable
to make it act that way for all SET variables.

An example of how this would simplify life: consider the problem of
a client that wants to ensure autocommit is on.  A simple
    SET autocommit TO on;
doesn't work at the moment: if autocommit is off, then you'll need
to issue a COMMIT as well to get out of the implicitly started
transaction.  But you don't want to just issue a COMMIT, because
you'll get a nasty ugly WARNING message on stderr if indeed autocommit
was on already.  The only warning-free way to issue a SET right now
if you are uncertain about autocommit status is
    BEGIN; SET .... ; COMMIT;
Blech.  But if SET doesn't start a transaction then you can still
just do SET.  This avoids some changes we'll otherwise have to make
in libpq startup, among other places.

Does anyone see any cases where it's important for SET to start
a transaction?  (Of course, if you are already *in* a transaction,
the SET will be part of that transaction.  The question is whether
we want SET to trigger an implicit BEGIN or not.)

>     Nor, of course, are the nonexecutable statements DECLARE CURSOR,
>     DECLAR LOCAL TEMPORARY TABLE, BEGIN DECLARE SECTION, SEND DECLARE
>     SECTIONS, and WHENEVER.

Hmm.  I think the spec's notion of DECLARE must be different from ours.
Our implementation of DECLARE CURSOR both declares and opens the cursor,
and as such it *must* be transaction-initiating; else it's useless.

            regards, tom lane

Re: [HACKERS] problem with new autocommit config parameter

From
Barry Lind
Date:
I am waiting for this thread to conclude before deciding exactly what to
do for the jdbc driver for 7.3.  While using the 'set autocommit true'
syntax is nice when talking to a 7.3 server, the jdbc driver also needs
to be backwardly compatible with 7.2 and 7.1 servers.  So it may just be
easier to continue with the current way of doing things, even in the 7.3
case.

thanks,
--Barry

Curt Sampson wrote:
 > On Mon, 9 Sep 2002, Tom Lane wrote:
 >
 >
 >>If autocommit=off really seriously breaks JDBC then I don't think a
 >>simple SET command at the start of a session is going to do that much
 >>to improve robustness.  What if the user issues another SET to turn it
 >>on?
 >
 >
 > You mean, to turn it off again? The driver should catch this, in theory.
 >
 > In practice we could probably live with saying, "Don't use SET
 > AUTOCOMMIT; use the methods on the Connection class instead."
 >
 > Probably the driver should be changed for 7.3 just to use the server's
 > SET AUTOCOMMIT functionality....
 >
 > cjs



Re: [HACKERS] problem with new autocommit config parameter and

From
Bruce Momjian
Date:
Tom Lane wrote:
> An example of how this would simplify life: consider the problem of
> a client that wants to ensure autocommit is on.  A simple
>     SET autocommit TO on;
> doesn't work at the moment: if autocommit is off, then you'll need
> to issue a COMMIT as well to get out of the implicitly started
> transaction.  But you don't want to just issue a COMMIT, because
> you'll get a nasty ugly WARNING message on stderr if indeed autocommit
> was on already.  The only warning-free way to issue a SET right now
> if you are uncertain about autocommit status is
>     BEGIN; SET .... ; COMMIT;
> Blech.  But if SET doesn't start a transaction then you can still
> just do SET.  This avoids some changes we'll otherwise have to make
> in libpq startup, among other places.
>
> Does anyone see any cases where it's important for SET to start
> a transaction?  (Of course, if you are already *in* a transaction,
> the SET will be part of that transaction.  The question is whether
> we want SET to trigger an implicit BEGIN or not.)

Uh, well, because we now have SET's rollback in an aborted transaction,
there is an issue of whether the SET is part of the transaction or not.
Seems it has to be for consistency with our rollback behavior.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: [HACKERS] problem with new autocommit config parameter and jdbc

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> Tom Lane wrote:
>> Does anyone see any cases where it's important for SET to start
>> a transaction?  (Of course, if you are already *in* a transaction,
>> the SET will be part of that transaction.  The question is whether
>> we want SET to trigger an implicit BEGIN or not.)

> Uh, well, because we now have SET's rollback in an aborted transaction,
> there is an issue of whether the SET is part of the transaction or not.
> Seems it has to be for consistency with our rollback behavior.

Yeah, it must be part of the transaction unless we want to reopen the
SET-rollback can of worms (which I surely don't want to).

However, a SET issued outside any pre-existing transaction block could
form a self-contained transaction without any logical difficulty, even
in autocommit-off mode.  The question is whether that's more or less
convenient, or standards-conforming, than what we have.

An alternative that I'd really rather not consider is making SET's
behavior dependent on exactly which variable is being set ...

            regards, tom lane

Re: [HACKERS] problem with new autocommit config parameter and

From
Bruce Momjian
Date:
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > Tom Lane wrote:
> >> Does anyone see any cases where it's important for SET to start
> >> a transaction?  (Of course, if you are already *in* a transaction,
> >> the SET will be part of that transaction.  The question is whether
> >> we want SET to trigger an implicit BEGIN or not.)
>
> > Uh, well, because we now have SET's rollback in an aborted transaction,
> > there is an issue of whether the SET is part of the transaction or not.
> > Seems it has to be for consistency with our rollback behavior.
>
> Yeah, it must be part of the transaction unless we want to reopen the
> SET-rollback can of worms (which I surely don't want to).
>
> However, a SET issued outside any pre-existing transaction block could
> form a self-contained transaction without any logical difficulty, even
> in autocommit-off mode.  The question is whether that's more or less
> convenient, or standards-conforming, than what we have.

That seems messy.  What you are saying is that if autocommit is off,
then in:

    SET x=1;
    UPDATE ...
    SET y=2;
    ROLLBACK;

that the x=1 doesn't get rolled back bu the y=2 does?  I can't see any
good logic for that.

> An alternative that I'd really rather not consider is making SET's
> behavior dependent on exactly which variable is being set ...

Agreed.  We discussed that in the SET rollback case and found it was
more trouble that it was worth.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: [HACKERS] problem with new autocommit config parameter and jdbc

From
Tom Lane
Date:
Bruce Momjian <pgman@candle.pha.pa.us> writes:
> That seems messy.  What you are saying is that if autocommit is off,
> then in:

>     SET x=1;
>     UPDATE ...
>     SET y=2;
>     ROLLBACK;

> that the x=1 doesn't get rolled back bu the y=2 does?

Yes, if you weren't in a transaction at the start.

> I can't see any good logic for that.

How about "the SQL spec requires it"?  Date seems to think it does,
at least for some variables (of course we have lots of variables
that are not in the spec).

I can't find anything very clear in the SQL92 or SQL99 documents,
and I'm not at home at the moment to look at my copy of Date, but
if Curt's reading is correct then we have spec precedent for acting
this way.

            regards, tom lane

Re: [HACKERS] problem with new autocommit config parameter and

From
Bruce Momjian
Date:
Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > That seems messy.  What you are saying is that if autocommit is off,
> > then in:
>
> >     SET x=1;
> >     UPDATE ...
> >     SET y=2;
> >     ROLLBACK;
>
> > that the x=1 doesn't get rolled back bu the y=2 does?
>
> Yes, if you weren't in a transaction at the start.
>
> > I can't see any good logic for that.
>
> How about "the SQL spec requires it"?  Date seems to think it does,
> at least for some variables (of course we have lots of variables
> that are not in the spec).
>
> I can't find anything very clear in the SQL92 or SQL99 documents,
> and I'm not at home at the moment to look at my copy of Date, but
> if Curt's reading is correct then we have spec precedent for acting
> this way.

Spec or not, it looks pretty weird so I would question following the
spec on this one.

Do we want to say "With autocommit off, SET will be in it's own
transaction if it appears before any non-SET command", and "SETs are
rolled back except if autocommit off and they appear before any
non-SET"?

I sure don't.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: [HACKERS] problem with new autocommit config parameter and jdbc

From
snpe
Date:
On Tuesday 10 September 2002 09:55 pm, Tom Lane wrote:
> Bruce Momjian <pgman@candle.pha.pa.us> writes:
> > That seems messy.  What you are saying is that if autocommit is off,
> > then in:
> >
> >     SET x=1;
> >     UPDATE ...
> >     SET y=2;
> >     ROLLBACK;
> >
> > that the x=1 doesn't get rolled back bu the y=2 does?
>
> Yes, if you weren't in a transaction at the start.
>
> > I can't see any good logic for that.
>
> How about "the SQL spec requires it"?  Date seems to think it does,
> at least for some variables (of course we have lots of variables
> that are not in the spec).
>
> I can't find anything very clear in the SQL92 or SQL99 documents,
> and I'm not at home at the moment to look at my copy of Date, but
> if Curt's reading is correct then we have spec precedent for acting
> this way.

I know what Oracle do (default mode autocommit off except JDBC) :
only DML and DDL command start transaction and DDL command end transaction.
There is another problem: if select start transaction why  error - I will
continue transaction.
Why invalid command start transaction ?

regards
haris peco

Re: [HACKERS] problem with new autocommit config parameter and jdbc

From
Curt Sampson
Date:
On Tue, 10 Sep 2002, Tom Lane wrote:

> As of CVS tip, SET commands *do* initiate transactions
> if you have autocommit off.  By your reading of Date, this is not
> spec compliant for certain SET variables: a SET not already within
> a transaction should not start a transaction block, at least for the
> variables mentioned above.  It occurs to me that it'd be reasonable
> to make it act that way for all SET variables.

I agree. SET variables are normally related to the behaviour of a
session, not information stored in the database. And your autocommit
example shows why having them start a transaction is a problem.

But there were some issues with rolling back and SET commands,
weren't there? I remember a long discussion about this that I'm
not sure I want to go back to. :-)

> >     Nor, of course, are the nonexecutable statements DECLARE CURSOR,
> >     DECLAR LOCAL TEMPORARY TABLE, BEGIN DECLARE SECTION, SEND DECLARE
> >     SECTIONS, and WHENEVER.
>
> Hmm.  I think the spec's notion of DECLARE must be different from ours.
> Our implementation of DECLARE CURSOR both declares and opens the cursor,
> and as such it *must* be transaction-initiating; else it's useless.

Well, I'm not going to go chase it down right now, but ISTR that
DECLAREing a cursor just allocates a variable name or the storage for it
or something like that; it doesn't actually create an active cursor.

cjs
--
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC


Re: [HACKERS] problem with new autocommit config parameter

From
Curt Sampson
Date:
On Tue, 10 Sep 2002, Barry Lind wrote:

> I am waiting for this thread to conclude before deciding exactly what to
> do for the jdbc driver for 7.3.  While using the 'set autocommit true'
> syntax is nice when talking to a 7.3 server, the jdbc driver also needs
> to be backwardly compatible with 7.2 and 7.1 servers.

Can you not check the server's version on connect?

It would be ideal if the JDBC driver, without modification, ran
all tests properly against 7.3, 7.2 and 7.1.

cjs
--
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC


Re: [HACKERS] problem with new autocommit config parameter

From
Curt Sampson
Date:
On Tue, 10 Sep 2002, Bruce Momjian wrote:

> Do we want to say "With autocommit off, SET will be in it's own
> transaction if it appears before any non-SET command", and "SETs are
> rolled back except if autocommit off and they appear before any
> non-SET"?

Not really, I don't think.

But I'm starting to wonder if we should re-think all SET commands being
rolled back if a transaction fails. Some don't seem to make sense, such
as having SET AUTOCOMMIT or SET SESSION AUTHORIZATION roll back.

cjs
--
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC


Re: [HACKERS] problem with new autocommit config parameter

From
Bruce Momjian
Date:
Curt Sampson wrote:
> On Tue, 10 Sep 2002, Bruce Momjian wrote:
>
> > Do we want to say "With autocommit off, SET will be in it's own
> > transaction if it appears before any non-SET command", and "SETs are
> > rolled back except if autocommit off and they appear before any
> > non-SET"?
>
> Not really, I don't think.
>
> But I'm starting to wonder if we should re-think all SET commands being
> rolled back if a transaction fails. Some don't seem to make sense, such
> as having SET AUTOCOMMIT or SET SESSION AUTHORIZATION roll back.

Yes, but the question is whether it is better to be consistent and roll
them all back, or to pick and choose which ones to roll back.
Consistency is nice.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

Re: [HACKERS] problem with new autocommit config parameter

From
Barry Lind
Date:
Curt,

Yes I can check the server version on connect.  In fact that is what the
  driver already does.  However I can't check the version and then based
on the version call set autocommit true in one round trip to the server.
  Since many people don't use connection pools, I am reluctant to add
the overhead of an extra roundtrip to the database to set a variable
that for most people will already be set to true.  It would be ideal if
I could in one hit to the database determine the server version and
conditionally call set autocommit based on the version at the same time.

thanks,
--Barry



Curt Sampson wrote:
> On Tue, 10 Sep 2002, Barry Lind wrote:
>
>
>>I am waiting for this thread to conclude before deciding exactly what to
>>do for the jdbc driver for 7.3.  While using the 'set autocommit true'
>>syntax is nice when talking to a 7.3 server, the jdbc driver also needs
>>to be backwardly compatible with 7.2 and 7.1 servers.
>
>
> Can you not check the server's version on connect?
>
> It would be ideal if the JDBC driver, without modification, ran
> all tests properly against 7.3, 7.2 and 7.1.
>
> cjs


Re: [HACKERS] problem with new autocommit config parameter

From
Curt Sampson
Date:
On Tue, 10 Sep 2002, Barry Lind wrote:

> Yes I can check the server version on connect.  In fact that is what the
>   driver already does.  However I can't check the version and then based
> on the version call set autocommit true in one round trip to the server.
>   Since many people don't use connection pools, I am reluctant to add
> the overhead of an extra roundtrip to the database to set a variable
> that for most people will already be set to true.  It would be ideal if
> I could in one hit to the database determine the server version and
> conditionally call set autocommit based on the version at the same time.

Hmm. I don't think that there's any real way to avoid a second round
trip now, but one thing we might do with 7.3 would be to add a standard
stored procedure that will deal with setting appropriate variables and
suchlike, and returning the version number and any other information
that the JDBC driver needs. (Maybe it can return a key/value table.)
That way, once we desupport 7.2 in the far future, we can reduce this to
one round trip.

Or perhaps we we could try to execute that stored procedure and, if it
fails, create it. (Or, if creating it fails, do things the hard way.) That
way the first connection you make where the SP is not there you have the
overhead of adding it, but all connections after that can use it. (I assume
you'd grant all rights to it to the general public.) And it could return
its own version so that newer drivers could upgrade it if necessary. Or
maybe just have a differently-named one for each version of the driver.
This is a bit kludgy, but also sort of elegant, if you think about it....

On the other hand, perhaps we should just live with two round trips. So
long as we've got command batching at some point, we can get the version,
and then send all the setup commands we need as a single batch after that.

cjs
--
Curt Sampson  <cjs@cynic.net>   +81 90 7737 2974   http://www.netbsd.org
    Don't you know, in this new Dark Age, we're all light.  --XTC


Patch for getBestRowIdentifier (for testing with Oracle JDeveloper)

From
snpe
Date:
Please appl
--- AbstractJdbc1DatabaseMetaData.java    Wed Sep 11 22:21:25 2002
+++ AbstractJdbc1DatabaseMetaData.java.orig    Wed Sep 11 22:20:36 2002
@@ -2381,44 +2381,21 @@
     // Implementation note: This is required for Borland's JBuilder to work
     public java.sql.ResultSet getBestRowIdentifier(String catalog, String
schema, String table, int scope, boolean nullable) throws SQLException
     {
-        if (connection.haveMinimumServerVersion("7.3")) {
-            StringBuffer sql = new StringBuffer(512);
-            sql.append("SELECT " +
-                scope + " as SCOPE," +
-                "a.attname as COLUMN_NAME," +
-                "a.atttypid as DATA_TYPE," +
-                "t.typname as TYPE_NAME," +
-                "t.typlen as COLUMN_SIZE," +
-                "0::int4 as BUFFER_LENGTH," +
-                "0::int4 as DECIMAL_DIGITS," +
-                "0::int4 as PSEUDO_COLUMN " +
-             "FROM pg_catalog.pg_type t,pg_catalog.pg_class bc," +
-                "pg_catalog.pg_class ic, pg_catalog.pg_index i, pg_catalog.pg_attribute a
" +
-             "WHERE bc.relkind = 'r' " +
-              "AND t.oid=a.atttypid " +
-              "AND upper(bc.relname) = upper('" + table + "') " +
-              "AND i.indrelid = bc.oid " +
-              "AND i.indexrelid = ic.oid " +
-              "AND ic.oid = a.attrelid " +
-              "AND i.indisprimary='t' ");
-            return connection.createStatement().executeQuery(sql.toString());
-        } else {
-            // for now, this returns an empty result set.
-            Field f[] = new Field[8];
-            ResultSet r;    // ResultSet for the SQL query that we need to do
-            Vector v = new Vector();        // The new ResultSet tuple stuff
+        // for now, this returns an empty result set.
+        Field f[] = new Field[8];
+        ResultSet r;    // ResultSet for the SQL query that we need to do
+        Vector v = new Vector();        // The new ResultSet tuple stuff

-            f[0] = new Field(connection, "SCOPE", iInt2Oid, 2);
-            f[1] = new Field(connection, "COLUMN_NAME", iVarcharOid, NAME_SIZE);
-            f[2] = new Field(connection, "DATA_TYPE", iInt2Oid, 2);
-            f[3] = new Field(connection, "TYPE_NAME", iVarcharOid, NAME_SIZE);
-            f[4] = new Field(connection, "COLUMN_SIZE", iInt4Oid, 4);
-            f[5] = new Field(connection, "BUFFER_LENGTH", iInt4Oid, 4);
-            f[6] = new Field(connection, "DECIMAL_DIGITS", iInt2Oid, 2);
-            f[7] = new Field(connection, "PSEUDO_COLUMN", iInt2Oid, 2);
+        f[0] = new Field(connection, "SCOPE", iInt2Oid, 2);
+        f[1] = new Field(connection, "COLUMN_NAME", iVarcharOid, NAME_SIZE);
+        f[2] = new Field(connection, "DATA_TYPE", iInt2Oid, 2);
+        f[3] = new Field(connection, "TYPE_NAME", iVarcharOid, NAME_SIZE);
+        f[4] = new Field(connection, "COLUMN_SIZE", iInt4Oid, 4);
+        f[5] = new Field(connection, "BUFFER_LENGTH", iInt4Oid, 4);
+        f[6] = new Field(connection, "DECIMAL_DIGITS", iInt2Oid, 2);
+        f[7] = new Field(connection, "PSEUDO_COLUMN", iInt2Oid, 2);

-            return connection.getResultSet(null, f, v, "OK", 1);
-        }
+        return connection.getResultSet(null, f, v, "OK", 1);
     }

     /*



I'am sorry (reverse *java and *orig)

correct patch
--- AbstractJdbc1DatabaseMetaData.java.orig    Wed Sep 11 22:20:36 2002
+++ AbstractJdbc1DatabaseMetaData.java    Wed Sep 11 22:50:37 2002
@@ -2381,21 +2381,44 @@
     // Implementation note: This is required for Borland's JBuilder to work
     public java.sql.ResultSet getBestRowIdentifier(String catalog, String
schema, String table, int scope, boolean nullable) throws SQLException
     {
-        // for now, this returns an empty result set.
-        Field f[] = new Field[8];
-        ResultSet r;    // ResultSet for the SQL query that we need to do
-        Vector v = new Vector();        // The new ResultSet tuple stuff
+        if (connection.haveMinimumServerVersion("7.3")) {
+            StringBuffer sql = new StringBuffer(512);
+            sql.append("SELECT " +
+                scope + " as SCOPE," +
+                "a.attname as COLUMN_NAME," +
+                "a.atttypid as DATA_TYPE," +
+                "t.typname as TYPE_NAME," +
+                "t.typlen as COLUMN_SIZE," +
+                "0::int4 as BUFFER_LENGTH," +
+                "0::int4 as DECIMAL_DIGITS," +
+                "0::int4 as PSEUDO_COLUMN " +
+             "FROM pg_catalog.pg_type t,pg_catalog.pg_class bc," +
+                "pg_catalog.pg_class ic, pg_catalog.pg_index i, pg_catalog.pg_attribute a
" +
+             "WHERE bc.relkind = 'r' " +
+              "AND t.oid=a.atttypid " +
+              "AND upper(bc.relname) = upper('" + table + "') " +
+              "AND i.indrelid = bc.oid " +
+              "AND i.indexrelid = ic.oid " +
+              "AND ic.oid = a.attrelid " +
+              "AND i.indisprimary='t' ");
+            return connection.createStatement().executeQuery(sql.toString());
+        } else {
+            // for now, this returns an empty result set.
+            Field f[] = new Field[8];
+            ResultSet r;    // ResultSet for the SQL query that we need to do
+            Vector v = new Vector();        // The new ResultSet tuple stuff

-        f[0] = new Field(connection, "SCOPE", iInt2Oid, 2);
-        f[1] = new Field(connection, "COLUMN_NAME", iVarcharOid, NAME_SIZE);
-        f[2] = new Field(connection, "DATA_TYPE", iInt2Oid, 2);
-        f[3] = new Field(connection, "TYPE_NAME", iVarcharOid, NAME_SIZE);
-        f[4] = new Field(connection, "COLUMN_SIZE", iInt4Oid, 4);
-        f[5] = new Field(connection, "BUFFER_LENGTH", iInt4Oid, 4);
-        f[6] = new Field(connection, "DECIMAL_DIGITS", iInt2Oid, 2);
-        f[7] = new Field(connection, "PSEUDO_COLUMN", iInt2Oid, 2);
+            f[0] = new Field(connection, "SCOPE", iInt2Oid, 2);
+            f[1] = new Field(connection, "COLUMN_NAME", iVarcharOid, NAME_SIZE);
+            f[2] = new Field(connection, "DATA_TYPE", iInt2Oid, 2);
+            f[3] = new Field(connection, "TYPE_NAME", iVarcharOid, NAME_SIZE);
+            f[4] = new Field(connection, "COLUMN_SIZE", iInt4Oid, 4);
+            f[5] = new Field(connection, "BUFFER_LENGTH", iInt4Oid, 4);
+            f[6] = new Field(connection, "DECIMAL_DIGITS", iInt2Oid, 2);
+            f[7] = new Field(connection, "PSEUDO_COLUMN", iInt2Oid, 2);

-        return connection.getResultSet(null, f, v, "OK", 1);
+            return connection.getResultSet(null, f, v, "OK", 1);
+        }
     }

     /*


Re: [HACKERS] problem with new autocommit config parameter and jdbc

From
Rod Taylor
Date:
On Tue, 2002-09-10 at 21:44, Curt Sampson wrote:
> But there were some issues with rolling back and SET commands,
> weren't there? I remember a long discussion about this that I'm
> not sure I want to go back to. :-)

So..  Unless explicitly requested, a SET command should have immediate
effect?

The other constrictive value I can think of is search_path.

-- Must be transaction safe
BEGIN;
CREATE SCHEMA <newschema>;
SET search_path = <newschema>;
ROLLBACK;
CREATE TABLE...


-- This should be ok
BEGIN;
SET autocommit = on;
INSERT ...
COMMIT;
-- SET takes place on commit, as it was an explicit transaction


-- This is requested behavior
SET autocommit = off;
SET autocommit = on;
INSERT...  -- immediate effect, since autocommit is on


-- This gets interesting be ok as the schema must exist
SET autocommit = off;
CREATE SCHEMA <newschema>;
SET search_path = <newschema>;  -- implicit commit here?
ROLLBACK;
CREATE TABLE ...
-- search_path must roll back or schema must have been created


-- Similar to the above
SET autocommit = off;
CREATE TABLE ...
SET autocommit = on; -- implicit commit here?
ROLLBACK;
-- Does this rollback anything?
-- Was CREATE TABLE committed with the second SET statement?



> Well, I'm not going to go chase it down right now, but ISTR that
> DECLAREing a cursor just allocates a variable name or the storage for it
> or something like that; it doesn't actually create an active cursor.

Indeed, this is how the cursor is able to cross transactions. It is
closed at transaction commit, and re-created in next use.

4.29:

For every <declare cursor> in an SQL-client module, a cursor is
effectively created when an SQLtransaction (see Subclause 4.32,
SQL-transactions  ) referencing the SQL-client module is initiated.

--
  Rod Taylor


Re: Patch for getBestRowIdentifier (for testing with Oracle

From
Kris Jurka
Date:
I will add a version of this patch to my work on making DatabaseMetaData
schema aware.

Kris Jurka


On Wed, 11 Sep 2002, snpe wrote:

> I'am sorry (reverse *java and *orig)
>
> correct patch
> --- AbstractJdbc1DatabaseMetaData.java.orig    Wed Sep 11 22:20:36 2002
> +++ AbstractJdbc1DatabaseMetaData.java    Wed Sep 11 22:50:37 2002
> @@ -2381,21 +2381,44 @@
>      // Implementation note: This is required for Borland's JBuilder to work
>      public java.sql.ResultSet getBestRowIdentifier(String catalog, String
> schema, String table, int scope, boolean nullable) throws SQLException
>      {
> -        // for now, this returns an empty result set.
> -        Field f[] = new Field[8];
> -        ResultSet r;    // ResultSet for the SQL query that we need to do
> -        Vector v = new Vector();        // The new ResultSet tuple stuff
> +        if (connection.haveMinimumServerVersion("7.3")) {
> +            StringBuffer sql = new StringBuffer(512);
> +            sql.append("SELECT " +
> +                scope + " as SCOPE," +
> +                "a.attname as COLUMN_NAME," +
> +                "a.atttypid as DATA_TYPE," +
> +                "t.typname as TYPE_NAME," +
> +                "t.typlen as COLUMN_SIZE," +
> +                "0::int4 as BUFFER_LENGTH," +
> +                "0::int4 as DECIMAL_DIGITS," +
> +                "0::int4 as PSEUDO_COLUMN " +
> +             "FROM pg_catalog.pg_type t,pg_catalog.pg_class bc," +
> +                "pg_catalog.pg_class ic, pg_catalog.pg_index i, pg_catalog.pg_attribute a
> " +
> +             "WHERE bc.relkind = 'r' " +
> +              "AND t.oid=a.atttypid " +
> +              "AND upper(bc.relname) = upper('" + table + "') " +
> +              "AND i.indrelid = bc.oid " +
> +              "AND i.indexrelid = ic.oid " +
> +              "AND ic.oid = a.attrelid " +
> +              "AND i.indisprimary='t' ");
> +            return connection.createStatement().executeQuery(sql.toString());
> +        } else {
> +            // for now, this returns an empty result set.
> +            Field f[] = new Field[8];
> +            ResultSet r;    // ResultSet for the SQL query that we need to do
> +            Vector v = new Vector();        // The new ResultSet tuple stuff
>
> -        f[0] = new Field(connection, "SCOPE", iInt2Oid, 2);
> -        f[1] = new Field(connection, "COLUMN_NAME", iVarcharOid, NAME_SIZE);
> -        f[2] = new Field(connection, "DATA_TYPE", iInt2Oid, 2);
> -        f[3] = new Field(connection, "TYPE_NAME", iVarcharOid, NAME_SIZE);
> -        f[4] = new Field(connection, "COLUMN_SIZE", iInt4Oid, 4);
> -        f[5] = new Field(connection, "BUFFER_LENGTH", iInt4Oid, 4);
> -        f[6] = new Field(connection, "DECIMAL_DIGITS", iInt2Oid, 2);
> -        f[7] = new Field(connection, "PSEUDO_COLUMN", iInt2Oid, 2);
> +            f[0] = new Field(connection, "SCOPE", iInt2Oid, 2);
> +            f[1] = new Field(connection, "COLUMN_NAME", iVarcharOid, NAME_SIZE);
> +            f[2] = new Field(connection, "DATA_TYPE", iInt2Oid, 2);
> +            f[3] = new Field(connection, "TYPE_NAME", iVarcharOid, NAME_SIZE);
> +            f[4] = new Field(connection, "COLUMN_SIZE", iInt4Oid, 4);
> +            f[5] = new Field(connection, "BUFFER_LENGTH", iInt4Oid, 4);
> +            f[6] = new Field(connection, "DECIMAL_DIGITS", iInt2Oid, 2);
> +            f[7] = new Field(connection, "PSEUDO_COLUMN", iInt2Oid, 2);
>
> -        return connection.getResultSet(null, f, v, "OK", 1);
> +            return connection.getResultSet(null, f, v, "OK", 1);
> +        }
>      }
>
>      /*
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>