Thread: OUT Parameter of type used defined object array obtained as null

It would be of great
help if somebody could just let me know whether or not fetch is possible
with the custom object Array types.
We have a sample code which yields no error but the OUT object array is
obtained as null. Any hint on what we are missing out or any other guidance
from your team will be highly appreciated.

Please find the code snippets below.

DB Objects:

CREATE TYPE pa_test_obj AS
   (p11 character varying(20),
    p12 character varying(20));

CREATE TYPE pa_test_tab
   (INPUT=udtabletype_in, OUTPUT=udtabletype_out, DEFAULT='',
       ELEMENT=pa_test_obj, DELIMITER=',',
       INTERNALLENGTH=-1, ALIGNMENT=double, STORAGE=PLAIN);

The below procedure ‘sp_test_fetch_convert’ calls the procedure
‘sp_test_fetch’ internally and converts its out put p_wic_dtl (custom table
data type) to  p_wic_dtl_obj (custom object array type) and returns it as
one of the output.

CREATE OR REPLACE PROCEDURE sp_test_fetch_convert(p_parnt_grop IN character
varying, p_loctn_cd IN character varying, /p_wic_dtl_obj OUT
pa_test_obj[],/p_err_cd OUT character varying, p_err_msg OUT character
varying) AS

p_wic_dtl_obj pa_test_obj[]:= '{}';
p_wic_dtl pa_test_tab;
p_wic_dtl_obj_temp pa_test_obj:=pa_test_obj(null,null);
 m   NUMBER;
 n   NUMBER;

begin
sp_test_fetch(p_parnt_grop, p_loctn_cd , p_wic_dtl  , p_err_cd , p_err_msg);
for i in 1..p_wic_dtl.count
loop
p_wic_dtl_obj_temp:=p_wic_dtl(i);
p_wic_dtl_obj[i]:=p_wic_dtl_obj_temp;
end loop;
m := array_lower(p_wic_dtl_obj, 1);
n := array_upper(p_wic_dtl_obj, 1);
FOR x IN m..n
loop
insert into bbbb(orderdate,name,product)
values(sysdate,p_wic_dtl_obj[x].p11,p_wic_dtl_obj[x].p12);
end loop;
end

In the above mentioned code by the end,the populated object array is
iterated and the values are inserted into a table through the insert
statement and that insert works fine.*But when called through  java, this
object is returned as null.*
Java code:

import java.sql.Array;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
 import com.edb.util.PGobject;

 public class testDb{
 public static void main(String[] args)
                {
                    try
                    {
                                  Class.forName("com.edb.Driver");
                                  Connection con =
DriverManager.getConnection("jdbc:edb://10.200.73.156:5444/edbdmsauto",
                                  "edbdmsauto","edbdmsauto");


((com.edb.PGConnection)con).addDataType("pa_test_obj", TestObj1.class);

                                  CallableStatement cstmt =
con.prepareCall("{call sp_test_fetch_obj(? ,? ,? ,?,?)}");
                                  cstmt.setString(1, "GT001");
                                  cstmt.setString(2, "GT04");
                                  cstmt.registerOutParameter(3,
Types.ARRAY);
                                  cstmt.registerOutParameter(4,
Types.VARCHAR);
                                  cstmt.registerOutParameter(5,
Types.VARCHAR);
                                  cstmt.execute();
                                  Array arr=  (Array) cstmt.getArray(3);
                                 System.out.println(arr);

                                  System.out.println("Executed
successfully");
                                  con.close();
                     }

                     catch(ClassNotFoundException e)
                     {
                                   System.out.println("Class Not Found : " +
e.getMessage());
                     }

*In the above java code,The printed array object ‘arr’ is obtained as null.*

Any guidance will be highly valued. Thanks in advance.


                     catch(SQLException exp) {
                                 exp.printStackTrace();
                     }
                }

}


--
View this message in context:
http://postgresql.1045698.n5.nabble.com/OUT-Parameter-of-type-used-defined-object-array-obtained-as-null-tp5713033.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.

>   It would be of great
> help if somebody could just let me know whether or not fetch is possible
> with the custom object Array types.
> We have a sample code which yields no error but the OUT object array is
> obtained as null. Any hint on what we are missing out or any other guidance
> from your team will be highly appreciated.
>
> Please find the code snippets below.

import java.sql.Array;
import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Types;
--> import com.edb.util.PGobject;

What is com.edb.util.PGobject? This does not appear to be the the PGobject
class provided from the PostgreSQL JDBC driver, org.posgresql.util.PGobject.
It does not appear you are using the Class.forName("org.postgresql.Driver");

danap.

Dave Cramer

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


On Mon, Jun 18, 2012 at 2:55 PM, dmp <danap@ttc-cmc.net> wrote:
>>  It would be of great
>> help if somebody could just let me know whether or not fetch is possible
>> with the custom object Array types.
>> We have a sample code which yields no error but the OUT object array is
>> obtained as null. Any hint on what we are missing out or any other
>> guidance
>> from your team will be highly appreciated.
>>
>> Please find the code snippets below.
>
>
> import java.sql.Array;
> import java.sql.CallableStatement;
> import java.sql.Connection;
> import java.sql.DriverManager;
> import java.sql.ResultSet;
> import java.sql.SQLException;
> import java.sql.Statement;
> import java.sql.Types;
> --> import com.edb.util.PGobject;
>
> What is com.edb.util.PGobject? This does not appear to be the the PGobject
> class provided from the PostgreSQL JDBC driver, org.posgresql.util.PGobject.
> It does not appear you are using the Class.forName("org.postgresql.Driver");
>
> danap.
>
>


This is the EDB jdbc driver.


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

Dave Cramer wrote:
> Dave Cramer
>
> dave.cramer(at)credativ(dot)ca
> http://www.credativ.ca
>
>
> On Mon, Jun 18, 2012 at 2:55 PM, dmp<danap@ttc-cmc.net>  wrote:
>>>   It would be of great
>>> help if somebody could just let me know whether or not fetch is possible
>>> with the custom object Array types.
>>> We have a sample code which yields no error but the OUT object array is
>>> obtained as null. Any hint on what we are missing out or any other
>>> guidance
>>> from your team will be highly appreciated.
>>>
>>> Please find the code snippets below.
>>
>>
>> import java.sql.Array;
>> import java.sql.CallableStatement;
>> import java.sql.Connection;
>> import java.sql.DriverManager;
>> import java.sql.ResultSet;
>> import java.sql.SQLException;
>> import java.sql.Statement;
>> import java.sql.Types;
>> -->  import com.edb.util.PGobject;
>>
>> What is com.edb.util.PGobject? This does not appear to be the the PGobject
>> class provided from the PostgreSQL JDBC driver, org.posgresql.util.PGobject.
>> It does not appear you are using the Class.forName("org.postgresql.Driver");
>>
>> danap.
>>
>>
>
>
> This is the EDB jdbc driver.
>
>
>> --
>> Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-jdbc
>

After I sent this I pretty much guessed this was the Enterprise DB version
of PostgreSQL. I guess the point was that don't they have there own compiled
version of PostgreSQL and maybe the JDBC? If so then this forum might not be
the appropriate place to pose the question, since EDB, features might be different.

danap.

Please do not print this email unless it is absolutely necessary.

The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments.

WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email.

www.wipro.com

On 19.06.2012 06:22, njohny wrote:
> Hi Dave,
> You are right.I am using Enterprise DB version
> of PostgreSQL .But As far as I know both have same features. It would be fine If you could suggest me some work outs
inpostgre sql. 

I'd suggest that you contact EnterpriseDB's support. I could give you
examples here on how to use PostgreSQL features, but this seems rather
EDB-specific, with the Oracle-style CREATE PROCEDURE syntax and all.
Besides, it's not really clear to me what you're trying to accomplish.

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

Thank you Dave for your response.Actually I am trying to call a procedure
with an OUT parameter of custom object array type.I have posted the similar
queries in enterprise db support forum.But couldn't get response.
With my code,the procedure is getting executed successfully and the OUT
object array is loaded with data.
But in java ,I am getting that array as null.Any suggestions would be of
great help.
Thanks in advance.
:)


--
View this message in context:
http://postgresql.1045698.n5.nabble.com/OUT-Parameter-of-type-USER-DEFINED-OBJECT-ARRAY-obtained-as-null-tp5713033p5713245.html
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.