Thread: Calling PGSQL Stored function thru 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

On Thu, 29 Jul 2004, Nick Selva wrote:

> [my CallableStatement doesn't work.]

You have misnumbered your parameters.  You want to do cs.setString(2, "P")
because that matches with the second "?", and cs.registerOutParameter(1,
Types.VARCHAR) because that is the out parameter.  You also may need to
say "{ ? = call get_state(?) }", note the equal sign.

Kris Jurka

Actually I changed the cs.setString(2, "P") from 1st
to 2nd parameter becoz i was thinking that could hv
been the problem. but looks like i've introduced one
more bug into my program. I've changed it back and
added the equal sign to "{ ? = call get_state(?) }".

Now the program works FINE. It's really great to have
JDBC leverage stored function in pgsql!!!! Thanks alot
Kris.

Since I'm new here, I would love to get to know what
are the type of applications that have been developed
so far that are using postgresql. This is because I
want to convince my friends and customers to adopt
pgsql for their businesses rather than wasting their
money on databases like mssql, oracle, informix. PGSQL
is greater my all means.

So Kris may I know what sort of application that you
are using pgsql, jdbc for? If its too personal a
question, i apologize and you can reserve your answer.
I hope others too will share their information on the
type of software application that pgsql is being used.

Hope I'm not troubling anyone. Just a wana quench my
immense thirst for pgsql knowledge..:)

Will wait for reply.

Regards,

Selvam

--- Kris Jurka <books@ejurka.com> wrote:

>
>
> On Thu, 29 Jul 2004, Nick Selva wrote:
>
> > [my CallableStatement doesn't work.]
>
> You have misnumbered your parameters.  You want to
> do cs.setString(2, "P")
> because that matches with the second "?", and
> cs.registerOutParameter(1,
> Types.VARCHAR) because that is the out parameter.
> You also may need to
> say "{ ? = call get_state(?) }", note the equal
> sign.
>
> Kris Jurka
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 7: don't forget to increase your free space map
> settings
>




__________________________________
Do you Yahoo!?
Yahoo! Mail Address AutoComplete - You start. We finish.
http://promotions.yahoo.com/new_mail

On July 30, 2004 12:10 pm, Nick Selva wrote:
> Since I'm new here, I would love to get to know what
> are the type of applications that have been developed
> so far that are using postgresql. This is because I
> want to convince my friends and customers to adopt
> pgsql for their businesses rather than wasting their
> money on databases like mssql, oracle, informix. PGSQL
> is greater my all means.

The most famous example is of course the .org root domain.

All DNS queries for a .org go through postgresql.

JdV!!

--
--------------------------------------------------------------
Jan de Visser                     jdevisser@digitalfairway.com

                Baruk Khazad! Khazad ai-menu!
--------------------------------------------------------------