Re: error calling function - jdbc driver limitation?

That's correct callable functions are not supported.

To do what you want to do just put it in a select statement

PreparedStatement pstmt = conn.prepareStatement("select returnkey(?)");
pstmt.setString(1,string);
ResultSet rs = pstmt.executeQuery();
If (rs.next()){
String result = rs.getInt("returnkey");
}

Dave

-----Original Message-----
From: pgsql-jdbc-owner(at)postgresql(dot)org
[mailto:pgsql-jdbc-owner(at)postgresql(dot)org] On Behalf Of Sharon Cowling
Sent: Monday, January 21, 2002 9:46 PM
To: pgsql-jdbc(at)postgresql(dot)org
Subject: [JDBC] error calling function - jdbc driver limitation?

Hi,

I'm trying to call a function from a JSP page and I'm getting an
exception saying: Callable Statements are not supported at this time.

I've included information I thought would be helpful, if you need more,
let me know.

I'm running the following version:

version
---------------------------------------------------------------
PostgreSQL 7.1.3 on i686-pc-linux-gnu, compiled by GCC 2.95.3

JDBC Driver: jdbc7.1-1.2.jar

Function:
CREATE FUNCTION returnkey(varchar) returns int as '
BEGIN
UPDATE archive_faps_key
SET date_returned = now()
WHERE key_code = $1
AND permit_id =
(SELECT permit_id FROM faps_key WHERE key_code = $1);
UPDATE faps_key
SET
date_key_issued = null,
date_key_due = null,
key_issued_by = null,
permit_id = null
WHERE key_code = $1;
RETURN 1;
END;'
LANGUAGE 'plpgsql';

Java Code:

Driver driverReturnKey =
(Driver)Class.forName(postgresql_DRIVER).newInstance();
Connection conn =
DriverManager.getConnection(postgresql_STRING,postgresql_USERNAME,postgr
esql_PASSWORD);
String returnKeyFunction = ("{call returnkey(?)}"); CallableStatement cs
= conn.prepareCall("{call null_key('" + rsReturnKey__varKeyNum + "')}");
cs.execute();

Stack Trace:

Error: 500
Location: /return_key2.jsp
Internal Servlet Error:

javax.servlet.ServletException: Callable Statements are not supported at
this time.
at
org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContex
tImpl.java:459)
at
_0002freturn_0005fkey_00032_0002ejspreturn_0005fkey2_jsp_5._jspService(_
0002freturn_0005fkey_00032_0002ejspreturn_0005fkey2_jsp_5.java:139)
at
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:119)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at
org.apache.jasper.servlet.JspServlet$JspCountedServlet.service(JspServle
t.java:130)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at
org.apache.jasper.servlet.JspServlet$JspServletWrapper.service(JspServle
t.java:282)
at
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:429)
at
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:500)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at
org.apache.tomcat.core.ServletWrapper.doService(ServletWrapper.java:405)
at org.apache.tomcat.core.Handler.service(Handler.java:287)
at
org.apache.tomcat.core.ServletWrapper.service(ServletWrapper.java:372)
at
org.apache.tomcat.core.ContextManager.internalService(ContextManager.jav
a:812)
at
org.apache.tomcat.core.ContextManager.service(ContextManager.java:758)
at
org.apache.tomcat.service.http.HttpConnectionHandler.processConnection(H
ttpConnectionHandler.java:213)
at
org.apache.tomcat.service.TcpWorkerThread.runIt(PoolTcpEndpoint.java:416
)
at
org.apache.tomcat.util.ThreadPool$ControlRunnable.run(ThreadPool.java:50
1)
at java.lang.Thread.run(Thread.java:484)

Root cause:
Callable Statements are not supported at this time.
at
org.postgresql.jdbc2.Connection.prepareCall(Connection.java:143)
at
org.postgresql.jdbc2.Connection.prepareCall(Connection.java:138)
at
_0002freturn_0005fkey_00032_0002ejspreturn_0005fkey2_jsp_5._jspService(_
0002freturn_0005fkey_00032_0002ejspreturn_0005fkey2_jsp_5.java:102)
at
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:119)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at
org.apache.jasper.servlet.JspServlet$JspCountedServlet.service(JspServle
t.java:130)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at
org.apache.jasper.servlet.JspServlet$JspServletWrapper.service(JspServle
t.java:282)
at
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:429)
at
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:500)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:853)
at
org.apache.tomcat.core.ServletWrapper.doService(ServletWrapper.java:405)
at org.apache.tomcat.core.Handler.service(Handler.java:287)
at
org.apache.tomcat.core.ServletWrapper.service(ServletWrapper.java:372)
at
org.apache.tomcat.core.ContextManager.internalService(ContextManager.jav
a:812)
at
org.apache.tomcat.core.ContextManager.service(ContextManager.java:758)
at
org.apache.tomcat.service.http.HttpConnectionHandler.processConnection(H
ttpConnectionHandler.java:213)
at
org.apache.tomcat.service.TcpWorkerThread.runIt(PoolTcpEndpoint.java:416
)
at
org.apache.tomcat.util.ThreadPool$ControlRunnable.run(ThreadPool.java:50
1)
at java.lang.Thread.run(Thread.java:484)

Is this a limitation of the jdbc driver that I am using that I cannot
call functions? Or could it be something I'm doing?

Regards,

Sharon Cowling

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to majordomo(at)postgresql(dot)org)