Re: error calling function - jdbc driver limitation? - Mailing list pgsql-jdbc

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@postgresql.org
[mailto:pgsql-jdbc-owner@postgresql.org] On Behalf Of Sharon Cowling
Sent: Monday, January 21, 2002 9:46 PM
To: pgsql-jdbc@postgresql.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@postgresql.org)