BUG #6292: java.sql.PreparedStatement.setNull() throws PSQLException

Started by David Pinheiroover 14 years ago2 messagesbugs
Jump to latest
#1David Pinheiro
davidsantospinheiro@gmail.com

The following bug has been logged online:

Bug reference: 6292
Logged by: David Pinheiro
Email address: davidsantospinheiro@gmail.com
PostgreSQL version: 8.4
Operating system: Linux
Description: java.sql.PreparedStatement.setNull() throws
PSQLException
Details:

I'm trying to make:
java.sql.PreparedStatement.setNull(1,java.sql.Types.NULL);

But i get:
org.postgresql.util.PSQLException: ERROR: column "number_column" is of type
integer but expression is of type character varying
Hint: You will need to rewrite or cast the expression.

My code is something like:
if (String.valueOf(input_field) == "null"){
statement.setNull(1, java.sql.Types.NULL);
} else {
statement.setObject(1, valor);
}
statement.executeUpdate();

Java says:
org.postgresql.util.PSQLException: ERROR: column "number_column" is of type
integer but expression is of type character varying
Hint: You will need to rewrite or cast the expression.
Position: 159
at
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorI
mpl.java:2096)
at
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.ja
va:1829)
at
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)

at
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.j
ava:510)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2St
atement.java:386)
at
org.postgresql.jdbc2.AbstractJdbc2Statement.executeUpdate(AbstractJdbc2State
ment.java:332)
at
pt.cm_olhao.tecidoempresarial.database.estabelecimentos.EstabelecimentosFree
formStatementDelegate.storeRow(EstabelecimentosFreeformStatementDelegate.jav
a:160)

I have Postgresql 8.4 and postgresql-jdbc-8.4-703.

Thanks a lot!
David Pinheiro

#2Kris Jurka
books@ejurka.com
In reply to: David Pinheiro (#1)
Re: BUG #6292: java.sql.PreparedStatement.setNull() throws PSQLException

On Mon, 14 Nov 2011, David Pinheiro wrote:

Bug reference: 6292
PostgreSQL version: 8.4
Description: java.sql.PreparedStatement.setNull() throws
PSQLException
Details:

I'm trying to make:
java.sql.PreparedStatement.setNull(1,java.sql.Types.NULL);

org.postgresql.util.PSQLException: ERROR: column "number_column" is of type
integer but expression is of type character varying
Hint: You will need to rewrite or cast the expression.

My code is something like:
if (String.valueOf(input_field) == "null"){
statement.setNull(1, java.sql.Types.NULL);
} else {
statement.setObject(1, valor);
}
statement.executeUpdate();

I don't think your problem is with setNull, I think your problem is
actually with the setObject branch of your if statement. If the
valor variable is a String, the JDBC driver is assigning it a string
type. You may want to do setObject(1, valor, Types.INTEGER) or
convert it prior to doing the setObject call to an appropriate
numeric type.

Kris Jurka