Thread: Issues with IN-OUT parameters for Array of Objects in EDB using Java

Hi

I am having issues with passing IN-OUT Parameters from a function which
returns a array of custom defined objects. I know that the new version(8.X+)
of EDB support nested tables and so was wondering this issue must be faced
by other java developers when trying to use the nested tables. Note I tried
types.Other, Array and STRUCT to register the output nested table object but
didnot work.

*Below is my SQL code*

create or replace
TYPE OT_Ref_Country AS OBJECT (
    countryId               Integer,
    isoFullName         Varchar2(256),
    doNotUseIndicator        Integer
);

create or replace
TYPE NT_Ref_Countries IS TABLE OF OT_Ref_Country;

FUNCTION getCountryList ( ip_countryId IN
Ref_Country.countryid%TYPE,iop_NT_Ref_Countries in out NT_Ref_Countries
) RETURN Number ;


*Java code:*

CallableStatement  st = con.prepareCall("{call
pkg_ref_geography_mgr.getCountryList(4,?)}");
st.registerOutParameter(1,Types.OTHER);
st.execute();


Please let me know if any additional details are needed. The version used is
Postgresql 8.4 with Java6

Tks for the help
-Mike

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Issues-with-IN-OUT-parameters-for-Array-of-Objects-in-EDB-using-Java-tp5620617p5620617.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

1. Can I get a list of features not supported/planned
2. Any estimates when will be support for nested table be available?

-Mike

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Issues-with-IN-OUT-parameters-for-Array-of-Objects-in-EDB-using-Java-tp5620617p5622702.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

Hi, This is my first post on the forum. Hope, I'll get a resolution to my
problem.

We are in process of migration from Oracle 10g to Postgres9.1 Advance
Server. The stored procedures/functions from Oracle have some parameters
registered as Out parameters. Now, when we call it from Java, an exception
thrown -:

*org.postgresql.util.PSQLException: Malformed function or procedure escape
syntax at offset 9.*

The signature of Postgres Function is as ::

*sfblprdbilldate(IN p_billdate numeric, IN p_cutoffday numeric, IN
p_billfreq character varying, IN p_givendate timestamp without time zone,
OUT po_startdate timestamp without time zone, OUT po_end_date timestamp
without time zone)*


Java code for calling this function is as follows ::

  *strSql="{? = call(SFBLPRDBILLDATE(?,?,?,?,?,?))}";

                   objCstmt = objConn.prepareCall(strSql);
                   objCstmt.registerOutParameter(1,Types.VARCHAR);
                   objCstmt.setInt(2,iBillDay);
                   objCstmt.setInt(3,iBillDay);
                   objCstmt.setString(4,strBillFrequency);
                   objCstmt.setDate(5,dtSysDate);
                   objCstmt.registerOutParameter(6,Types.DATE);
                   objCstmt.registerOutParameter(7,Types.DATE);
                   objCstmt.execute();
                   strBillPeriod = objCstmt.getString(1);
                   dtStartDate   = objCstmt.getDate(6);*Anybody, who has
resolved this sort of issue, please help me as this is in numbers in our
code and we are looking for a solutuin to this problem.

Thanks
Jony.

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Issues-with-IN-OUT-parameters-for-Array-of-Objects-in-EDB-using-Java-tp5620617p5708726.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

On 15.05.2012 08:52, jonykapil wrote:
> Hi, This is my first post on the forum. Hope, I'll get a resolution to my
> problem.
>
> We are in process of migration from Oracle 10g to Postgres9.1 Advance
> Server.

Advanced Server is EnterpriseDB's proprietary product, while this
mailing list is for community PostgreSQL. Please contact EnterpriseDB's
support at support@enterprisedb.com.

--
   Heikki Linnakangas
   EnterpriseDB   http://www.enterprisedb.com

Hello Heikki,
     Thanks for your prompt reply but this function call is simply running
in the Oracle DB and it has nothing specific to the Postgres9.1 Advance
Server. I think this is a generic problem while migration to Postgres9.1 DB.
I hope you will understand the point.
Please help me to sort out this issue.

Thanks
Jony





--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Issues-with-IN-OUT-parameters-for-Array-of-Objects-in-EDB-using-Java-tp5620617p5708738.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

On Tue, May 15, 2012 at 3:32 AM, jonykapil <jony.kapil@gmail.com> wrote:
> Hello Heikki,
>     Thanks for your prompt reply but this function call is simply running
> in the Oracle DB and it has nothing specific to the Postgres9.1 Advance
> Server. I think this is a generic problem while migration to Postgres9.1 DB.
> I hope you will understand the point.
> Please help me to sort out this issue.
>
> Thanks
> Jony


A test case would help.


Dave Cramer

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

Hello Altaf,
      Thanks for your reply.
I changed my calling code as following.

*
objCstmt = objConn.prepareCall("{? = call SFBLPRDBILLDATA(?,?,?, cast(? as
timestamp without time zone),cast(? as timestamp without time zone),cast(?
as timestamp without time zone) )}");

objCstmt.registerOutParameter(1,Types.VARCHAR);
           objCstmt.setInt(2,iBillDay);
           objCstmt.setInt(3,iBillDay);
           objCstmt.setString(4,strBillFrequency);
           objCstmt.setDate(5,dtSysDate);
           objCstmt.registerOutParameter(6,Types.DATE);
           objCstmt.registerOutParameter(7,Types.DATE);
           objCstmt.execute();*


Exception thrown by Postgres is ::

*org.postgresql.util.PSQLException: ERROR: cannot cast type void to
timestamp without time zone  Position:84*

I am not able to understand why now it is taking registerOutParameter as
void.
Please help me out of this...

Thanks & regards
jony

--
View this message in context:
http://postgresql.1045698.n5.nabble.com/Issues-with-IN-OUT-parameters-for-Array-of-Objects-in-EDB-using-Java-tp5620617p5708766.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.