PSQLException: ERROR: operator does not exist: integer = character varying
От | Anderson, Mark S. |
---|---|
Тема | PSQLException: ERROR: operator does not exist: integer = character varying |
Дата | |
Msg-id | D93B26E07F2DD147A3E17BA1602444B80980792662@IMCMBX2.MITRE.ORG обсуждение исходный текст |
Список | pgsql-jdbc |
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)
В списке pgsql-jdbc по дате отправления:
Предыдущее
От: "Kevin Grittner"Дата:
Сообщение: Re: Found a Bug in latest Driver (I THINK) and pg 8.4