Re: JDBC 7.3 dev (Java 2 SDK 1.4.0)

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