Announcement Announcement Module
Collapse
No announcement yet.
Table of Oracle Object Type as a stored procedure out parameter Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Table of Oracle Object Type as a stored procedure out parameter

    Hello

    I have been following the thread with Bob (http://forum.springframework.org/showthread.php?t=33053) whereby he has been having difficulty with passing an array as an in parameter using Spring.

    My issue is that I have a - no in parameter stored procedure call - which returns a table of object types as the out parameter.

    I've been able to retrieve data when the procedure was changed to just return a single record of the object type rather than the table to ensure I was on the right lines with using SqlReturnType().

    When the procedure was returned to being a table of the object type (as there are multiples) then I am struggling. I've changed the return type in the SqlOutParameter and I had to change the type to ARRAY as it recognised I was returning a collection.

    The difficulty I have is that, as it returns a table, it is only one instance in the map - how can I make Spring recognise that the return type is a table which contains multiple instances of data?

  • #2
    if there os any further information required, please respond and I will provide it

    Comment


    • #3
      If you've already written this in JDBC, it should be pretty easy to re-write in Spring. The StoredProceedure classes are pretty simple I would take a look at the code and see what's going on.

      Comment


      • #4
        Unfortunately I haven't already written this in JDBC.

        There are a nested table called rule_operators_typ which contains rows of rule_operator_typ.

        I can return the data......

        Code:
        public class OperatorsProc extends StoredProcedure
        {
        public static final String sql = "get_rule_operators";
        	
        public OperatorsProc(JdbcTemplate ds)
        {
        setJdbcTemplate(ds);
        setSql(sql);
        setFunction(false);
        
        declareParameter(new SqlOutParameter(
        		"p_Operators",
        		OracleTypes.ARRAY,
        		"RULE_OPERATORS_TYP",
        		new SqlReturnType()
        		{
        			public Object getTypeValue(CallableStatement cs,
        							int paramIndex,
        							int sqlType,
        							String typeName) throws SQLException
        			{
        				Connection con = cs.getConnection();
        				Map<String, Class<?>> typeMap = con.getTypeMap();
        				typeMap.put(typeName, Operators.class);
        				Object o = cs.getObject(paramIndex);
        				return o;
        			}
        		}
        		));
        compile();
        }
        }
        which is called by

        Code:
        JdbcTemplate jt = new JdbcTemplate((DataSource)Utils.getContext().getBean("dataSource"));
        SimpleNativeJdbcExtractor nje = new SimpleNativeJdbcExtractor();
        nje.setNativeConnectionNecessaryForNativeCallableStatements(true);
        nje.setNativeConnectionNecessaryForNativePreparedStatements(true);
        nje.setNativeConnectionNecessaryForNativeStatements(true);
        jt.setNativeJdbcExtractor(nje);
        Connection con = jt.getDataSource().getConnection();
        OperatorsProc oProc = new OperatorsProc(jt);
        
        Map res = oProc.execute(new HashMap());
        Array retArray = (Array)res.get("p_Operators");
        Object[] retObject = (Object[])retArray.getArray();
        this tells me I have the requisite number of objects in the array

        I have the following methods in the business object to return the object type....

        Code:
        public String getSQLTypeName() throws SQLException {
        	return "RULE_OPERATORS_TYP";
        }
        
        public void readSQL(SQLInput stream, String typeName) throws SQLException {
        	operatorId = stream.readInt();
        	operator = stream.readString();
        	multiValuesAllowedIn = stream.readString();
        }
        
        public void writeSQL(SQLOutput stream) throws SQLException {
        	stream.writeInt(operatorId);
        	stream.writeString(operator);
        	stream.writeString(multiValuesAllowedIn);
        }
        It is probably me just being stupid, but I'm struggling to return the object types into an array of the object, or whatever the best method would be.

        I hope this is enough info for you...
        Last edited by rowens; Feb 27th, 2007, 09:12 AM.

        Comment


        • #5
          Originally posted by karldmoore View Post
          If you've already written this in JDBC, it should be pretty easy to re-write in Spring. The StoredProceedure classes are pretty simple I would take a look at the code and see what's going on.
          With the above in mind, please could you review the above code.


          Thanks

          Comment


          • #6
            For someting that is relatively simple, I am still yet to find examples of it working. Just to reiterate I am calling a stored procedure which returns a table of Oracle object types. I can handle the table (providing it is of native types), I can handle a single instance of the object type. The difficulty I have is in getting the data from the table of object types. Please could you point me towards documentation/help for this.

            I have not written this previously in JDBC.

            Thanks

            Comment


            • #7
              I no longer require help with this particular issue as I have managed to return the data. Thanks anyway

              Comment


              • #8
                Any chance you could post the solution for other people with the same problem?

                Comment


                • #9
                  OK - basically I create a WSDL for the interface and using wsdl2java create the objects for my services.

                  There is an oracle object type, in the database, that contains 3 elements - multiple instances of the object type are contained in a "table of" the object type.

                  The difficulty I had was in returning this - I was able to do a single object type and a table of, for example, varchar. However the object type escaped me for a while.

                  This is what I ended up doing...

                  I create a new object that extends the object I created using the wsdl to hold the methods required for doing the Oracle Object type mapping.

                  Code:
                  public class OperatorsDatabaseType extends Operators implements SQLData 
                  {
                  	public String getSQLTypeName() throws SQLException {
                  		return "RULE_OPERATOR_TYP";
                  	}
                  
                  	public void readSQL(SQLInput stream, String typeName) throws SQLException {
                  		setOperatorId(stream.readInt());
                  		setOperator(stream.readString());
                  		setMultiValuesAllowedIn(stream.readString());
                  	}
                  
                  	public void writeSQL(SQLOutput stream) throws SQLException {
                  		stream.writeInt(getOperatorId());
                  		stream.writeString(getOperator());
                  		stream.writeString(getMultiValuesAllowedIn());
                  	}
                  
                  }
                  I created a class that extends stored procedure that declares the out parameter (there is only one and no in parameters).

                  Code:
                  public class OperatorsProc extends StoredProcedure
                  {
                  public static final String sql = "database_schema.get_procedure";
                  	
                  public OperatorsProc(JdbcTemplate ds)
                  {
                  	setJdbcTemplate(ds);
                  	setSql(sql);
                  	setFunction(false);
                  	
                  	declareParameter(new SqlOutParameter(
                  				"p_Operators",
                  				OracleTypes.ARRAY,
                  				"RULE_OPERATORS_TYP",
                  				new SqlReturnType()
                  				{
                  				public Object getTypeValue(CallableStatement cs,
                  								int paramIndex,
                                                                                  int sqlType,
                  								String typeName) throws SQLException
                  				{
                  			Connection con = cs.getConnection();
                  			Map typeMap = con.getTypeMap();
                  		        typeMap.put(typeName, OperatorsDatabaseType.class);
                  			Object o = cs.getObject(paramIndex);
                  			return o;
                  				}
                  			}
                  			));
                  			
                  			
                  compile();
                  }
                  		
                  public Map execute()
                  {
                       // has no input parameters so new map.
                       return super.execute(new HashMap());
                  }
                  }
                  I have used the extended class as the cast class in the sqlreturntype, gettypevalue call. The return type is an array and be careful that whatever you put in the return name (in my case "p_Operators") exactly matches the return name in the calling method.

                  I also have a single method with a return type of an array of the base business object which is as follows.

                  Code:
                  public Operators[] getOperators() throws SQLException
                  {
                  	JdbcTemplate jt = new JdbcTemplate((DataSource)Utils.getContext().getBean("dataSource"));
                  	SimpleNativeJdbcExtractor nje = new SimpleNativeJdbcExtractor();
                  	nje.setNativeConnectionNecessaryForNativeCallableStatements(true);
                  	nje.setNativeConnectionNecessaryForNativePreparedStatements(true);
                  	nje.setNativeConnectionNecessaryForNativeStatements(true);
                  	jt.setNativeJdbcExtractor(nje);
                  
                  	Connection con = jt.getDataSource().getConnection();
                  
                  	OperatorsProc oProc = new OperatorsProc(jt);
                  		
                  	// no input parameters so create a new map...
                  	Map res = oProc.execute();
                  		
                  	// Create an array of the returned data...
                  	Array retArray = (Array)res.get("p_Operators");
                  		
                  	// Cast the array to an object..
                  	Object[] retObject = (Object[])retArray.getArray();
                  		
                  	// use struct to ascertain database types..
                  	Operators[] operators = new Operators[retObject.length];
                  	Struct[] struct = new Struct[retObject.length];
                  		
                  	for (int i=0; i<retObject.length; i++)
                  	{
                  struct[i] = (Struct) retObject[i];
                  operators[i] = new Operators();
                  operators[i].setOperatorId(((BigDecimal)(struct[i].getAttributes()[0])).intValue());
                  operators[i].setOperator((String)struct[i].getAttributes()[1]);
                  operators[i].setMultiValuesAllowedIn((String)struct[i].getAttributes()[2]);
                  	}
                  	return operators;
                  }
                  As you can see from the above, once you have returned the data and cast it to an object, it becomes relatively simple to then loop through, casting to a Strut, and populating the setter methods of the base business object.

                  I am sure there are a number of improvements that could be made to what I have done. Any suggestions for improvements would be appreciated as I've learnt my Java 'on the job' so am prone to showing other peoples bad habits that I have taken as my own.

                  Thanks

                  Comment

                  Working...
                  X