WebSphere Adapter for JDBC

Stored Procedure definition

Stored procedures are defined at the verb level. Each stored procedure definition consists of the following elements: StoredProcedureType, StoredProcedureName, ResultSet, Parameters, and ReturnValue.

Stored procedure definition elements are described in the following table.

Table 1. Stored procedure definition
Element Type Description Value Bidirectional transformation supported
StoredProcedure
Type
String Defines the type of stored procedure to be used, and this determines when the stored procedure is called, for example, before processing a business object.
Note: Stored procedure types associated with RetrieveAll apply to top-level business objects only.
Can be
  • BeforeVerbSP,
  • AfterVerbSP, or
  • VerbSP
where the verb is either Create, Update, Delete, Retrieve, or RetrieveAll.
No
StoredProcedure
Name
String The name of the stored procedure that is associated with the appropriate StoredProcedureType.   Yes
ResultSet Boolean This value determines whether the stored procedure returns a result or not. If the result set is returned, an N-cardinality child for the current business object is created using the values returned in the result set rows. true|false No
Parameters String Defines the list of parameters for stored procedures.
Note: In the case of Oracle stored procedures, a result set can be returned only as an output parameter. In that case, one of the values in the list of parameters is result set (RS).
A combination of
  • IP for input only,
  • OP for output only, and
  • IO for input and output.
No
ReturnValue String A value that indicates it is a function call, not a procedure call, because the value is returned by the function. If the returned value is RS, the value is a result set and is used to create the N-cardinality container corresponding to this business object. If the returned value is an attribute, the value is assigned to that particular attribute in the business object. If the attribute is another child business object, the adapter returns an error. Can be RS or a business object attribute. No
Here is a sample of a stored procedure definition:
<jdbcasi:JDBCBusinessObjectTypeMetadata
xmlns:jdbcasi="http://www.ibm.com/xmlns/prod/websphere/j2ca/jdbc/metadata">
            <jdbcasi:TableName>customer</jdbcasi:TableName><jdbcasi:Operation>
               <jdbcasi:Name>Retrieve</jdbcasi:Name>
               <jdbcasi:StoredProcedures>
<jdbcasi:StoredProcedureType>RetrieveSP</jdbcasi:StoredProcedureType>
<jdbcasi:StoredProcedureName>retrieve_cust</jdbcasi:StoredProcedureName>
<jdbcasi:ResultSet>false</jdbcasi:ResultSet>
                     <jdbcasi:Parameters>                                                           
                          <jdbcasi:Type>IP</jdbcasi:Type>
                          <jdbcasi:PropertyName>primaryKey</jdbcasi:PropertyName>    
                    </jdbcasi:Parameters>
                    <jdbcasi:Parameters>
                          <jdbcasi:Type>OP</jdbcasi:Type>
                           <jdbcasi:PropertyName>custCode</jdbcasi:PropertyName>                  
                    </jdbcasi:Parameters>                                     
                    <jdbcasi:Parameters>                       
                          <jdbcasi:Type>OP</jdbcasi:Type>             
                           <jdbcasi:PropertyName>firstName</jdbcasi:PropertyName>                 
                    </jdbcasi:Parameters>                  
                    <jdbcasi:Parameters>                        
                          <jdbcasi:Type>OP</jdbcasi:Type>                        
                              <jdbcasi:PropertyName>lastName</jdbcasi:PropertyName>                    
                    </jdbcasi:Parameters>                  
                    </jdbcasi:StoredProcedures>                  
                    <jdbcasi:StoredProcedures>
<jdbcasi:StoredProcedureType>AfterRetrieveSP</jdbcasi:StoredProcedureType>
<jdbcasi:StoredProcedureName>retrieve_cust</jdbcasi:StoredProcedureName>                    
                    <jdbcasi:ResultSet>false</jdbcasi:ResultSet>                  
                    <jdbcasi:Parameters>                        
                         <jdbcasi:Type>IP</jdbcasi:Type>                        
                           <jdbcasi:PropertyName>primaryKey</jdbcasi:PropertyName>                  
                    </jdbcasi:Parameters>                  
                    <jdbcasi:Parameters>                        
                         <jdbcasi:Type>OP</jdbcasi:Type>                        
                          <jdbcasi:PropertyName>custCode</jdbcasi:PropertyName>                   
                    </jdbcasi:Parameters>                  
                    <jdbcasi:Parameters>                        
                         <jdbcasi:Type>OP</jdbcasi:Type>                           
                          <jdbcasi:PropertyName>firstName</jdbcasi:PropertyName>                  
                    </jdbcasi:Parameters>                  
                    <jdbcasi:Parameters>                        
                         <jdbcasi:Type>OP</jdbcasi:Type>                         
                         <jdbcasi:PropertyName>lastName</jdbcasi:PropertyName>                  
                    </jdbcasi:Parameters>               
                 </jdbcasi:StoredProcedures>            
             </jdbcasi:Operation>
</jdbcasi:JDBCBusinessObjectTypeMetadata>

The Resource adapter property ReturnDummyBOForSP returns output parameters even when the result set is true but empty. In the case of RetrieveSP, a result set is returned. If the result set is empty, no business objects are created and there is no way to retrieve the output parameters returned by the procedure call. If ReturnDummyBOForSP is true, a dummy business object with values from the output and input/output parameters populated in the corresponding attributes is returned. The default value for this property is false.


Terms of use |

Last updated: Tue 12 Dec 2006 03:32:39

(c) Copyright IBM Corporation 2005, 2006.
This information center is powered by Eclipse technology (http://www.eclipse.org)