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
