Обсуждение: 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.
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 in postgre sql.
From: dmp-2 [via PostgreSQL] [mailto:ml-node+[hidden email]]
Sent: Tuesday, June 19, 2012 7:15 AM
To: Neethu Johny (WI01 - Solutions)
Subject: Re: OUT Parameter of type used defined object array obtained as null
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<[hidden email]> 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 ([hidden email])
>> 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.
--
Sent via pgsql-jdbc mailing list ([hidden email])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc
If you reply to this email, your message will be added to the discussion below:
To unsubscribe from OUT Parameter of type-(USER DEFINED OBJECT ARRAY) obtained as null, click here.
NAML
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
View this message in context: RE: OUT Parameter of type used defined object array obtained as null
Sent from the PostgreSQL - jdbc mailing list archive at Nabble.com.
Re: Re: OUT Parameter of type used defined object array obtained as null
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.