Skip site navigation (1) Skip section navigation (2)

Calling PGSQL Stored function thru JDBC

From: Nick Selva <mavles_pgsql(at)yahoo(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: Calling PGSQL Stored function thru JDBC
Date: 2004-07-30 04:18:23
Message-ID: 20040730041823.98636.qmail@web90007.mail.scd.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
Hi everyone. I'm new here. Need the help of Guru(s)
here.

I am trying to write some java code to leverage the
power of stored-functions is pgsql. I have created the
function in the database and tested it with a 'SELECT'
function and it seems to return the result requested.
But when I try to embed some code into my Java
program, it gives errors. I will list out everything
below.

The stored function that i created in the database
looks like this:
-------------------------------------------------------
CREATE FUNCTION get_state(char(1)) RETURNS varchar(20)
AS
' DECLARE
   my_state VARCHAR(20);
BEGIN
   SELECT INTO my_state name FROM statename WHERE   
   code=$1;
RETURN my_state;
END;
' LANGUAGE plpgsql;
-------------------------------------------------------

I tested it in the database using the SELECT function
as below:

ttms=#SELECT get_state('D');
 get_state
-----------
 Dallas
(1 row)

So this proves that as far as postgresql database is
concerned its working perfectly fine.

Then I wrote the some java code to read a value from
the table using JDBC ResultSet to test if my
connection thru JDBC works. And it does. So at the end
of the same program I included Callable statemenet
code to call the function get_state() from the program
to receive the return value. I have attached the
program with this email. The file is called as
sample.java. Please do review my novice code. I can
compile and even run the bytecode. But it only runs
without any errors until it reaches the
CallableStatement portion. Then the program ends
abrubtly catching exception. The following is the
error message.
---------------------------------------------------------------------------------------------------
Exception caught.
org.postgresql.util.PSQLException: PostgreSQL only
supports function return value [@ 1] (no OUT or INOUT
arguments)
org.postgresql.util.PSQLException: PostgreSQL only
supports function return value [@ 1] (no OUT or INOUT
arguments)
        at
org.postgresql.jdbc1.AbstractJdbc1Statement.registerOutParameter(AbstractJdbc1Statement.java:1745)
        at sample.<init>(sample.java:41)
        at sample.main(sample.java:55)
---------------------------------------------------------------------------------------------------

I don't think so it's the problam with the JDBC
driver, coz if it was, it wouldn't succeed in getting
the values for the ResultSet in the earlier portion of
the code. I suppose I am missing out something in the
syntax or in the code.

Anyone have come across this? Please don't tell me
jdbc cant work with pgsql stored function coz im
planning to write half the application logic using
stored function.
Somebody please shed some light in this matter.

I thank in advance for the help rendered. Will wait
for the reply.

Best Regards,

Selvam


	
		
__________________________________
Do you Yahoo!?
New and Improved Yahoo! Mail - 100MB free storage!
http://promotions.yahoo.com/new_mail 

Attachment: sample.java
Description: application/octet-stream (2.4 KB)

Responses

pgsql-jdbc by date

Next:From: Kris JurkaDate: 2004-07-30 06:31:23
Subject: Re: Calling PGSQL Stored function thru JDBC
Previous:From: Oliver JowettDate: 2004-07-30 00:03:25
Subject: Re: Bug in 7.4_213 driver: returns VARCHAR instead of

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group