Thread: JDBC parse error with preparedStatement!

JDBC parse error with preparedStatement!

From
Ramin Rad
Date:
I am getting a very annoying parse error message on a simple delete statement:

  String sqlStmt = "DELETE FROM ft_member WHERE username = ?";
  PreparedStatement stmt = connection.prepareStatement( sqlStmt );
  stmt.setString( 1, "test");
  stmt.executeUpdate( sqlStmt );

Here is the error message:

Exception in thread "main" java.sql.SQLException: ERROR:  parser: parse error
at end of input

        at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:131)
        at
org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection.java:505)
        at
org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:320)
        at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:48)
        at
org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc1Statement.java:197)
        at
org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc1Statement.java:183)

I am using PostgreSql 7.3.3 and downloaded the JDBC driver in
postgresql-jdbc-7.3.3-1PGDG.i386.rpm file.

Please help, I don't understand why this is happenning, I seem to be doing
everything correctly.  I doubt my table name or column names are reserved
words. They work in SELECT queries.

Thanks,
-ramin


Re: JDBC parse error with preparedStatement!

From
Kris Jurka
Date:

On Sun, 11 Jan 2004, Ramin Rad wrote:

>
> I am getting a very annoying parse error message on a simple delete statement:
>
>   String sqlStmt = "DELETE FROM ft_member WHERE username = ?";
>   PreparedStatement stmt = connection.prepareStatement( sqlStmt );
>   stmt.setString( 1, "test");
>   stmt.executeUpdate( sqlStmt );
>
> Here is the error message:
>
> Exception in thread "main" java.sql.SQLException: ERROR:  parser: parse error
> at end of input
>

I can't see anything that's going wrong here either.  The best way to
debug this problem is to enable statement loggin on the server so we can
see the exact query the server tries to run.  To do this enable
log_statement in postgresql.conf and restart the server.

Kris Jurka


Re: JDBC parse error with preparedStatement!

From
Kris Jurka
Date:

On Sun, 11 Jan 2004, Ramin Rad wrote:
> > > I am getting a very annoying parse error message on a simple delete
> > statement:
> > >
> > >   String sqlStmt = "DELETE FROM ft_member WHERE username = ?";
> > >   PreparedStatement stmt = connection.prepareStatement( sqlStmt );
> > >   stmt.setString( 1, "test");
> > >   stmt.executeUpdate( sqlStmt );
> > >
> > > Here is the error message:
> > >
> > > Exception in thread "main" java.sql.SQLException: ERROR:  parser: parse
> > error
> > > at end of input
> > >

Re-reading your original message made the problem apparent. You should
just do stmt.executeUpdate(), not pass in the sqlStat which is overriding
the prepared query.

Kris Jurka


Re: JDBC parse error with preparedStatement!

From
Ramin Rad
Date:
For what it's worth, I get a different error message if I try to change the
query slightly, from:

  String sqlStmt = "DELETE FROM ft_member WHERE username = ?";

to:

  String sqlStmt = "DELETE FROM ft_member WHERE username=?";

(notice, removed the spaces around '='.)

Here is the error message:

Exception in thread "main" java.sql.SQLException: ERROR:  Unable to identify a
postfix operator '=?' for type 'character varying'
        You may need to add parentheses or an explicit cast

        at org.postgresql.core.QueryExecutor.execute(QueryExecutor.java:131)
        at
org.postgresql.jdbc1.AbstractJdbc1Connection.ExecSQL(AbstractJdbc1Connection.java:505)
        at
org.postgresql.jdbc1.AbstractJdbc1Statement.execute(AbstractJdbc1Statement.java:320)
        at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:48)
        at
org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc1Statement.java:197)
        at
org.postgresql.jdbc1.AbstractJdbc1Statement.executeUpdate(AbstractJdbc1Statement.java:183)


And here is what I see in the server log file:

LOG:  query: set datestyle to 'ISO'; select version(), case when
pg_encoding_to_char(1) = 'SQL_ASCII' then 'UNKNOWN' else getdatabaseencoding()
end;
LOG:  query: set client_encoding = 'UNICODE'; show autocommit
LOG:  query: DELETE FROM ft_member WHERE username=?
ERROR:  Unable to identify a postfix operator '=?' for type 'character varying'
        You may need to add parentheses or an explicit cast
LOG:  pq_recvbuf: unexpected EOF on client connection


I also tried falling back to JDK1.3.1, instead of 1.4.2, I recompiled the JDBC
driver and my code with the older version of java. Same error.

Thanks,
-ramin

--- Kris Jurka <books@ejurka.com> wrote:
>
>
> On Sun, 11 Jan 2004, Ramin Rad wrote:
>
> >
> > I am getting a very annoying parse error message on a simple delete
> statement:
> >
> >   String sqlStmt = "DELETE FROM ft_member WHERE username = ?";
> >   PreparedStatement stmt = connection.prepareStatement( sqlStmt );
> >   stmt.setString( 1, "test");
> >   stmt.executeUpdate( sqlStmt );
> >
> > Here is the error message:
> >
> > Exception in thread "main" java.sql.SQLException: ERROR:  parser: parse
> error
> > at end of input
> >
>
> I can't see anything that's going wrong here either.  The best way to
> debug this problem is to enable statement loggin on the server so we can
> see the exact query the server tries to run.  To do this enable
> log_statement in postgresql.conf and restart the server.
>
> Kris Jurka
>


Re: JDBC parse error with preparedStatement!

From
Ramin Rad
Date:
You are right! I just discovered that a few minutes ago myself by looking at
the Driver code and seeing this:

public int executeUpdate(String p_sql) throws SQLException
{
      String l_sql = replaceProcessing(p_sql);
      m_sqlFragments = new String[] {l_sql};
--->  m_binds = new Object[0];

After staring at it a few minutes and scratching my head thinking why this
method is doing "m_binds = new", I realized that I was calling the wrong
method!

Thank you SO SO SO much for your help.

-ramin


--- Kris Jurka <books@ejurka.com> wrote:
>
>
> On Sun, 11 Jan 2004, Ramin Rad wrote:
> > > > I am getting a very annoying parse error message on a simple delete
> > > statement:
> > > >
> > > >   String sqlStmt = "DELETE FROM ft_member WHERE username = ?";
> > > >   PreparedStatement stmt = connection.prepareStatement( sqlStmt );
> > > >   stmt.setString( 1, "test");
> > > >   stmt.executeUpdate( sqlStmt );
> > > >
> > > > Here is the error message:
> > > >
> > > > Exception in thread "main" java.sql.SQLException: ERROR:  parser: parse
> > > error
> > > > at end of input
> > > >
>
> Re-reading your original message made the problem apparent. You should
> just do stmt.executeUpdate(), not pass in the sqlStat which is overriding
> the prepared query.
>
> Kris Jurka
>


Re: JDBC parse error with preparedStatement!

From
Ramin Rad
Date:
Thanks for the suggestion. Here is the log:

LOG:  query: set datestyle to 'ISO'; select version(), case when
pg_encoding_to_char(1) = 'SQL_ASCII' then 'UNKNOWN' else getdatabaseencoding()
end;
LOG:  query: set client_encoding = 'UNICODE'; show autocommit
LOG:  query: DELETE FROM ft_member WHERE username = ?
ERROR:  parser: parse error at end of input
LOG:  pq_recvbuf: unexpected EOF on client connection

I don't quite know how the driver works, but shouldn't the '?' be replaced with
the variable by stmt.setString( 1, "test") before it is sent to the database?
Looks like a problem with JDBC driver.

Your help is extremely appreciated.

-ramin


--- Kris Jurka <books@ejurka.com> wrote:
>
>
> On Sun, 11 Jan 2004, Ramin Rad wrote:
>
> >
> > I am getting a very annoying parse error message on a simple delete
> statement:
> >
> >   String sqlStmt = "DELETE FROM ft_member WHERE username = ?";
> >   PreparedStatement stmt = connection.prepareStatement( sqlStmt );
> >   stmt.setString( 1, "test");
> >   stmt.executeUpdate( sqlStmt );
> >
> > Here is the error message:
> >
> > Exception in thread "main" java.sql.SQLException: ERROR:  parser: parse
> error
> > at end of input
> >
>
> I can't see anything that's going wrong here either.  The best way to
> debug this problem is to enable statement loggin on the server so we can
> see the exact query the server tries to run.  To do this enable
> log_statement in postgresql.conf and restart the server.
>
> Kris Jurka
>