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

Well I'm more curious what this breaks for the rest of us

Dave Cramer

dave.cramer(at)credativ(dot)ca
http://www.credativ.ca

On 31 July 2015 at 06:49, Alex Wang <alex(dot)wang(at)ebaotech(dot)com> wrote:

> Hi all,
>
>
>
> I have fixed my issue with some modifications:
>
>
>
> Modify the org.postgresql.core.v3.SimpleParameterList java file, the two
> revised methods are as below:
>
>
>
> *public* *void* registerOutParameter( *int* index, *int* sqlType )
> *throws* SQLException
>
> {
>
> *if* (index < 1 || index > paramValues.length)
>
> *throw* *new* PSQLException(GT.*tr*("The column index is out
> of range: {0}, number of columns: {1}.", *new* Object[]{index, paramValues
> .length}), PSQLState.*INVALID_PARAMETER_VALUE*);
>
>
>
> flags[index-1] |= *OUT*;
>
> paramValues[index-1] = sqlType; //added by Alex: there is no such
> line in the original source code
>
> }
>
>
>
> *public* *void* convertFunctionOutParameters()
>
> {
>
> /* for (int i=0; i<paramTypes.length; ++i)
>
> {
>
> if (direction(i) == OUT)
>
> {
>
> paramTypes[i] = Oid.VOID;
>
> paramValues[i] = "null";
>
> }
>
>
>
> }*/
>
> } // revised by Alex
>
>
>
> Thanks & regards,
>
>
>
> Alex
>
>
>
> *From:* pgsql-jdbc-owner(at)postgresql(dot)org [mailto:
> pgsql-jdbc-owner(at)postgresql(dot)org] *On Behalf Of *Alex Wang
> *Sent:* 2015年7月30日 21:19
> *To:* pgsql-jdbc(at)postgresql(dot)org
> *Subject:* [JDBC] need advice about out parameter settings while calling
> stored procedure in Java code
>
>
>
> 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.
>