jstl sql integer string type coercion postgres serial operator does not exist: bigint = character varying
Problem:
After upgrading from PostgreSQL 8.0 to 8.4 the following JSTL that queries a table by a passed in "ID":
<sql:query var="examples" dataSource="${exampleDataSource}">
select ExampleName as "name"
from ExampleTable
where ExampleId = ?
order by ExampleName ASC
<sql:param value="${param['ID']}"/>
</sql:query>
Fails with the exception:
javax.servlet.jsp.JspException:
select ExampleName as "name"
from ExampleTable
where ExampleId = ?
order by ExampleName ASC
: ERROR: operator does not exist: bigint = character varying
at org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.doEndTag(QueryTagSupport.java:220)
....
Caused by: java.sql.SQLException: ERROR: operator does not exist: bigint = character varying
at org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:1471)
at org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1256)
at org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:175)
at org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:389)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:330)
at org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:240)
at org.apache.tomcat.dbcp.dbcp.DelegatingPreparedStatement.executeQuery(DelegatingPreparedStatement.java:93)
at org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.doEndTag(QueryTagSupport.java:215)
Why are there data type errors all of a sudden? ... and how do you fix it?
Solution:
There is normally Type Coercion for EL expressions but it's a bit vague for
sql:param. Ideally it should coerce the param into the type required but this would require knowing the schema & the SQL being executed. So looking at the source for QueryTagSupport it will just call setObject using the default type that was supplied in the param. Contrary to the
sql:param documentation the value does not have to be a String.To get the right type into
sql:param use type coercion in EL before the param gets the value. To coerce a String to Integer, you could multiply by 1. For example:
<sql:query var="examples" dataSource="${exampleDataSource}">
select ExampleName as "name"
from ExampleTable
where ExampleId = ?
order by ExampleName ASC
<sql:param value="${param['ID']*1}"/>
</sql:query>
Or in two steps:
<c:set var="exampleId" value="${param['ID']*1}"/>
<sql:query var="examples" dataSource="${exampleDataSource}">
select ExampleName as "name"
from ExampleTable
where ExampleId = ?
order by ExampleName ASC
<sql:param value="${exampleId}"/>
</sql:query>
Why did this come up after a PostgreSQL upgrade? It seems something to do with different handling of the
SERIAL data type which is now compiled to it's actual representation of integer with sequence rather than leaving it as it's "notational convenience" name. Perhaps the PostgreSQL JDBC will coerce a String to a serial but not an integer? It's hard to say the above work around is best practice but it will be harmless for databases that handle coercion at the JDBC level and necessary for those that don't.
