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

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

From
cognizant
Date:
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.

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

From
Muhammad Altaf
Date:
UDTs are not yet supported in JDBC driver (using java.sql.Struct or SQLData)
 
-- Altaf Malik



From: cognizant <sudipbajoria@gmail.com>
To: pgsql-jdbc@postgresql.org
Sent: Friday, 6 April 2012 12:58 AM
Subject: [JDBC] 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.

--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc


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

From
cognizant
Date:
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.

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

From
Heikki Linnakangas
Date:
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

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

From
Muhammad Altaf
Date:
Can you change strSql="{? = call(SFBLPRDBILLDATE(?,?,?,?,?,?))}"; to strSql="{? = call SFBLPRDBILLDATE(?,?,?,?,?,?)}";
 
Kind Regards
Altaf Malik

From: jonykapil <jony.kapil@gmail.com>
To: pgsql-jdbc@postgresql.org
Sent: Tuesday, 15 May 2012 3:52 PM
Subject: [JDBC] Re: Issues with IN-OUT parameters for Array of Objects in EDB using Java

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.

--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc


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.

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

From
Muhammad Altaf
Date:
Use setTimestamp and no need to cast.

-- Altaf Malik

From: jonykapil <jony.kapil@gmail.com>
To: pgsql-jdbc@postgresql.org
Sent: Tuesday, 15 May 2012 10:46 PM
Subject: [JDBC] Re: Issues with IN-OUT parameters for Array of Objects in EDB using Java

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.

--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc