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)