need advice about out parameter settings while calling stored procedure in Java code

From: Alex Wang <alex(dot)wang(at)ebaotech(dot)com>
To: "pgsql-jdbc(at)postgresql(dot)org" <pgsql-jdbc(at)postgresql(dot)org>
Subject: need advice about out parameter settings while calling stored procedure in Java code
Date: 2015-07-30 13:18:35
Message-ID: 91b75bc23fbe41adba69c52ed4c2213f@shmbx01.ebaotech.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-jdbc

Hi ,

I am migrating from Oracle to Postgresql, and have encountered below issue while calling stored procedure via the latest PG JDBC (postgresql-9.4-1201.jdbc4.jar).

Here is the procedure snippet:

----------start------------

PROCEDURE p_myprocedure(a character varying, b character varying, c character varying,d character varying, OUT o1 text, OUT o2 text) IS
BEGIN

Blalabla;

END;

----------end-------------

In my Java code, I have to set the value of the out parameters like input as bellowing:

stmt = con.prepareCall("{call xxx. p_myprocedure(?,?,?,?,?,?)}");
stmt.setString(1, "x1");
stmt.setString(2, "x21");
stmt.setString(3, "x3");
stmt.setString(4, "x4");
stmt.setString (5,"");//need to set the output value
stmt.setString (6,""); //need to set the output value
before invoking the 'stmt.execute();';

Otherwise, it will throw error like:

ERROR: function p_myprocedure (character varying, character varying, character varying, character varying) does not exist
Hint: No function matches the given name and argument types. You might need to add explicit type casts.

But setting output is unnecessary if I use Oracle DB or access Postgresql DB via EDB jdbc driver.

Is there any possible way to avoid such out parameter settings ?

Thanks & regards,

Alex

This e-mail and any attachments thereto are intended for the sole use of the recipient(s) named above and may contain confidential and/or privileged material. Any use of the information contained herein (including, but not limited to, total or partial reproduction, communication, or dissemination in any form) by persons other than the intended recipient(s) is prohibited. If you received this in error, please contact the sender immediately and delete the material from any computer.

Responses

Browse pgsql-jdbc by date

  From Date Subject
Next Message Thomas Kellerer 2015-07-30 13:46:23 Re: need advice about out parameter settings while calling stored procedure in Java code
Previous Message Dave Cramer 2015-07-28 23:09:44 Re: Problem in PostgreSQL JDBC documentation file