Using SQLData to pass nested types from DB

[Joe May]

Hi,

I have the following structure..

import java.sql.SQLData;
import java.sql.SQLException;
import java.sql.SQLInput;
import java.sql.SQLOutput;
import java.util.ArrayList;
import java.util.List;

public class FundVO implements SQLData {

	private int fundId;

	private FundGeneralInfoVO generalInfo;

	private FundApprovalVO approvalInfo;

	private List<FundRelationshipVO> relationshipInfo;

	private List<TradingSystemAndCodesVO> tradingSystemAndCodes;

	public FundApprovalVO getApprovalInfo() {
		return approvalInfo;
	}

	public void setApprovalInfo(FundApprovalVO approvalInfo) {
		this.approvalInfo = approvalInfo;
	}

	public int getFundId() {
		return fundId;
	}

	public void setFundId(int fundId) {
		this.fundId = fundId;
	}

	public FundGeneralInfoVO getGeneralInfo() {
		return generalInfo;
	}

	public void setGeneralInfo(FundGeneralInfoVO generalInfo) {
		this.generalInfo = generalInfo;
	}

	public List<TradingSystemAndCodesVO> getTradingSystemAndCodes() {
		return tradingSystemAndCodes;
	}

	public void setTradingSystemAndCodes(
			List<TradingSystemAndCodesVO> tradingSystemAndCodes) {
		this.tradingSystemAndCodes = tradingSystemAndCodes;
	}

	public FundVO() {
		this.generalInfo = new FundGeneralInfoVO();
		this.approvalInfo = new FundApprovalVO();
		this.relationshipInfo = new ArrayList<FundRelationshipVO>();
		this.tradingSystemAndCodes = new ArrayList<TradingSystemAndCodesVO>();
	}

	public List<FundRelationshipVO> getRelationshipInfo() {
		return relationshipInfo;
	}

	public void setRelationshipInfo(List<FundRelationshipVO> relationshipInfo) {
		this.relationshipInfo = relationshipInfo;
	}

	public String getSQLTypeName() throws SQLException {
		System.out.println("getting sql type name");
		return "mcd.T_TEST_FUND";
	}

	@SuppressWarnings("unchecked")
	public void readSQL(SQLInput stream, String typeName) throws SQLException {
		System.out.println("reading FundVO");
		System.out.println(stream.readInt());
		System.out.println(stream.readObject());
		System.out.println(stream.readObject());
		System.out.println(stream.readObject());
		System.out.println(stream.readObject());
	}

	public void writeSQL(SQLOutput stream) throws SQLException {

	}
}

/////////////////////////////////////////////////////////////////////////////////////////////

CREATE OR REPLACE
TYPE     MCD.T_TEST_FUND AS OBJECT (
   fund_id              NUMBER,
   general_info         mcd.t_test_fund_gen_info,
   fund_relationships   mcd.tab_test_fund_relations,
   approval_info        mcd.t_test_fund_approval,
   trading_info         mcd.tab_test_fund_trades
);
/

////////////////////////////////////////////////////////////////////////////////////////////
			CallableStatement cs = connection
					.prepareCall("call mcd.p_test_fund(?,?)");
			Map map = connection.getTypeMap();
			map.put("MCD.T_TEST_FUND",FundVO.class);
			map.put("MCD.T_TEST_FUND_GEN_INFO",FundGeneralInfoVO.class);
			map.put("MCD.T_TEST_FUND_APPROVAL",FundApprovalVO.class);
			map.put("MCD.T_TEST_FND_RELATIONS",FundRelationshipVO.class);
			map.put("MCD.T_TEST_TRADING_SYSTEM",TradingSystemAndCodesVO.class);
			ArrayDescriptor ad1 = ArrayDescriptor.createDescriptor("MCD.TAB_TEST_FUND_RELATIONS", connection);
			ARRAY arr1 = new ARRAY(ad1, connection, (new ArrayList()).toArray());
			ArrayDescriptor ad2 = ArrayDescriptor.createDescriptor("MCD.TAB_TEST_FUND_TRADES", connection);
			ARRAY arr2 = new ARRAY(ad2, connection, (new ArrayList()).toArray());		
			cs.setInt(1, 8247);
			[color=red][b]cs.setArray(2,arr1);[/b][/color]
			cs.registerOutParameter(2, java.sql.Types.STRUCT,"MCD.T_TEST_FUND");
			System.out.println("before execute update");
			cs.executeUpdate();
			System.out.println("proc executed");
			Object myOut = cs.getObject(2);
			System.out.println("done..."+myOut);

I have a problem here.... I have to pass my FundVO object to the stored procedure and retrieve it as my DB object MCD.T_TEST_FUND... Now, i know that i have to use the array descriptor to declare the table types. But the table types are inside the object. How should I do this> When i see my readData method getting executed.. the lists are coming as null. Any pointers would be greatly appreciated. Thanks!

[Joe May]

sorry, I'm trying to retrieve the Db object into a Java class. I have said that i am doing the reverse in my previous post. Sorry

[Joe May]

Okay.. caught the issue.... the problem was.. i was trying to read the list as an object... instead it shud be array

[Jeanne Boyarsky]

Thanks for posting the solution.