Skip site navigation (1) Skip section navigation (2)

OUT Parameter of type used defined object array obtained as null

From: njohny <neethu(dot)johny(at)wipro(dot)com>
To: pgsql-jdbc(at)postgresql(dot)org
Subject: OUT Parameter of type used defined object array obtained as null
Date: 2012-06-18 05:35:46
Message-ID: 1339997746172-5713033.post@n5.nabble.com (view raw or flat)
Thread:
Lists: pgsql-jdbc
                                                     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.

Responses

pgsql-jdbc by date

Next:From: dmpDate: 2012-06-18 18:55:11
Subject: Re: OUT Parameter of type used defined object array obtained as null
Previous:From: Craig RingerDate: 2012-06-13 02:17:27
Subject: Re: An I/O error occured while sending to the backend

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group