PSQLException: ERROR: operator does not exist: integer = character varying

Hi Folks,

PSQLException: ERROR: operator does not exist: integer = character varying [see full error messages below.]

This error occurs when I attempt to add a parameter to a prepared statement. Everything I've read indicates that the likely cause is that I do not use the right datatype when adding the parameter to my prepared statement. For example,

PreparedStatement ps = conn.prepareStatement("select article_title from articles where article_id = ?");
ps.setString(urlParameters.get("article_id"));

Here article_id is an integer in the database, but the parameter I set is a string.

However, I am not using Java directly, so I don't have a ps.setString() statement. I am using the JSTL, specifically, the SQL tag library.

This is my code:
<%@ taglib prefix="c" uri="http://java.sun.com/jsp/jstl/core" %>
<%@ taglib prefix="sql" uri="http://java.sun.com/jsp/jstl/sql" %>

<c:set var="query" value="
select a.article_id, a.title, a.pub_year, a.article_type, a.citation, au.lname, au.fname
from (articles a inner join authors_articles aa on a.article_id = aa.article_id)
inner join authors au on aa.author_id = au.author_id
where a.article_id = ?"
/>

<sql:query var="articleInfo" sql="${query}">
<sql:param value="${param.article_id}" />
</sql:query>

The error occurs on the <sql:param ...> line. "param.article_id" is indeed a string (it's a URL parameter). The problem that I have is that I can't specify the datatype of ${param.article_id} or convert it to an integer. I've tried to cast it to an int using to_number() in the query, but I get "function does not exist" from Postgres.

How can I convert the string to an int in my JSP file?

Mark

root cause

javax.servlet.ServletException: javax.servlet.jsp.JspException:
select a.article_id, a.title, a.pub_year, a.article_type, a.citation, au.lname, au.fname
from (article a inner join authors_articles aa on a.article_id = aa.article_id)
inner join authors au on aa.author_id = au.author_id
where a.article_id = ?: ERROR: operator does not exist: integer = character varying
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Position: 276
org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:858)
org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:791)
org.apache.jsp.showReference_jsp._jspService(showReference_jsp.java:174)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:374)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:342)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:267)
javax.servlet.http.HttpServlet.service(HttpServlet.java:717)

root cause

org.postgresql.util.PSQLException: ERROR: operator does not exist: integer = character varying
Hint: No operator matches the given name and argument type(s). You might need to add explicit type casts.
Position: 276
org.postgresql.core.v3.QueryExecutorImpl.receiveErrorResponse(QueryExecutorImpl.java:2062)
org.postgresql.core.v3.QueryExecutorImpl.processResults(QueryExecutorImpl.java:1795)
org.postgresql.core.v3.QueryExecutorImpl.execute(QueryExecutorImpl.java:257)
org.postgresql.jdbc2.AbstractJdbc2Statement.execute(AbstractJdbc2Statement.java:479)
org.postgresql.jdbc2.AbstractJdbc2Statement.executeWithFlags(AbstractJdbc2Statement.java:367)
org.postgresql.jdbc2.AbstractJdbc2Statement.executeQuery(AbstractJdbc2Statement.java:271)
org.apache.taglibs.standard.tag.common.sql.QueryTagSupport.doEndTag(Unknown Source)
org.apache.jsp.showReference_jsp._jspx_meth_sql_005fquery_005f0(showReference_jsp.java:241)
org.apache.jsp.showReference_jsp._jspService(showReference_jsp.java:96)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
javax.servlet.http.HttpServlet.service(HttpServlet.java:717)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:374)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:342)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:267)
javax.servlet.http.HttpServlet.service(HttpServlet.java:717)