| Download Docs | Site Map | Glossary | |
|
|
|||
| bea.com | products | dev2dev | support | askBEA |
|
|
|
|||||||
 |
| e-docs > Liquid Data for WebLogic > Building Queries and Data Views > Rules for Specifying Stored Procedure Description Files |
|
Building Queries and Data Views
|
Rules for Specifying Stored Procedure Description Files
This section describes rules for the return_type attribute of the <function> element and the mode attribute of the <argument> element. This section includes the following rules:
Rules for Element and Attribute Names
XML requires that element and attribute names begin with a non-numeric character. Therefore, when you specify the name attribute of the <xs:element> element in a Stored Procedure Description file, you must specify a name that does not start with a numeric character. For example, if you have a stored procedure that returns a cursor, and the cursor returns columns that start with a numeric character, you must map those column names to valid XML element names in your Stored Procedure Description file.
For the W3C definition of a valid name for an attribute or element, see:
http://www.w3.org/TR/2000/WD-xml-2e-20000814#dt-name
For example, consider a cursor named MY_CURSOR is declared with the following SQL statement:
open MY_CURSOR for
select 1_column, 2_column, 3_column
from MY_TABLE
return MY_CURSOR
When you define the cursor type in the <types> section of your Stored Procedure Description file, you must map the column names from the cursor output to start with a non-numeric character so the resulting XML generated is valid. You could use the following type definiton for this cursor, which starts each numeric column name with an underscore character (_):
<types>
<xs:element name="MY_CURSOR" minOccurs="0"
maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="_1_column" type="xs:integer"/>
<xs:element name="_2_column" type="xs:string"/>
<xs:element name="_3_column="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</types>
For some notes on relational database object names and how to specify them so they can be used in an XQuery, see Relational Databases.
Rules for Procedure Names Containing a Semi-Colon
Sybase and Microsoft SQL Server databases provide the ability to group stored procedures by using a semi-colon character (;) to separate a procedure name with a number. For example, you can have two stored procedures with the following names:
MY_SP;1
MY_SP;2
When you specify these procedures in the Stored Procedure Description file, use the database name (the name with the semi-colon character). When you use these procedure names in an XQuery, however, you must substitute an underscore character (_) for the semi-colon character. The Data View Builder automatically substitutes the underscore character for the semi-colon character in the XQuery it generates.
For example, consider the following definition for a stored procedure in a Stored Procedure Description file:
<function name="MY_SP;2" return_type="Results">
<argument label="COLUMN_123" mode="input_only"
type="xs:string"/>
<argument label="ANOTHER_COLUMN" mode="output_only"
type="xs:int"/>
</function>
When you reference this function in an XQuery, it is referred to as follows:
MY_SP_2
Rules and Examples of <type> Declarations to Use in the <function> return_type Attribute
The return_type attribute of the <function> element specifies the complex type for the stored procedure. The complex type must be declared in the <types> section of the Stored Procedure Description file. For example, the following element opening tag shows a function named myFunction with a return_type of myReturnType:
<function name="myFunction" return_type="myReturnType" >
The return type myReturnType must be declared in the <types> section. The type must contain the actual return value of the stored procedure (if it has a return value) and the row set definitions (if applicable). The row set definitions must appear in the order in which the stored procedure returns them.
When a stored procedure returns a primitive type, you must declare the primitive type using the return_value keyword for the name attribute. For an example of this, see Example 2: Type Definition with Simple Return Value.
Example 1: Type Definition with No Return Value
The following is a type definition for a stored procedure that has no return value and returns no row sets.
<types>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="EmptyOutput">
<xs:complexType>
<xs:sequence>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
</types>
Use a similar type definition in a stored procedure that does not have any return value. This EmptyOutput type definition is required for all stored procedures and functions that do not return anything.
Example 2: Type Definition with Simple Return Value
The following is a type definition for a stored procedure that has a simple return value (xs:integer) and returns no row sets.
<types>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="SimpleOutput">
<xs:complexType>
<xs:sequence>
<xs:element name="return_value"
type="xs:integer" />
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
</types>
Use a similar type definition with a stored procedure that returns a status code or a single value.
Example 3: Type Definition for Complex Row Set Type
The following is a type definition for a stored procedure that returns a row set, which is a complex type.
<types>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="customerTable">
<xs:complexType>
<xs:sequence>
<xs:element name="CUSTOMER" minOccurs="0"
maxOccurs="unbounded" >
<xs:complexType>
<xs:sequence>
<xs:element name="C_NAME"
type="xs:string"/>
<xs:element name="C_ACCTBAL"
type="xs:decimal"/>
</xs:sequence>
</xs:complexType>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
</types>
Use a similar type definition with a stored procedure that returns a result set (for example, in Sybase, Microsoft SQL Server, or DB2).
Example 4: Type Definition with Complex Return Value
The following is a type definition for a stored procedure that returns a complex type. Assume that the customerTable complex type (shown in the previous example) is defined in the same Stored Procedure Definition file.
<types>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="return_value" type="customerTable">
</xs:element>
</xs:schema>
</types>
Use a similar type definition with an Oracle stored procedure that returns a cursor with a complex type.
Example 5: Type Definition with Simple Return Value and Two Row Sets
The following is a type definition for a stored procedure that has a simple return value (xs:integer) and returns two row sets.
<types>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
......
......{-- Definitions for complex types customerTable and
ordersTable go here --}
......
<xs:element name="OutputName">
<xs:complexType>
<xs:sequence>
<xs:element name="return_value"
type="xs:integer" />
<xs:element ref="customerTable" />
{-- customerTable defined above--}
<xs:element ref="ordersTable" />
{-- ordersTable defined above --}
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
</types>
Rules for the mode Attribute output_only <argument> Element
If you define a function that has an <argument> element that has the mode output_only, then you need only reference the type definition in the function definition. The following example references the customerTable type (defined in the <types> section of the Stored Procedure Description file, as described in Example 3: Type Definition for Complex Row Set Type). Assume that the customerTable type maps to a cursor returned from an Oracle stored procedure.
<function name="GetAllCustomersByState" return_type="EmptyOutput">
<argument label="state" mode="input_only" type="xs:string"/>
<argument label="CustomersOutput" mode="output_only"
type="customerTable"/>
</function>
Rules for Transforming the Function Signature When Hand Writing an XQuery
There are two issues to remember when hand crafting an XQuery that accesses a stored procedure:
All queries that access stored procedures must have a unique namespace with a URI of the of the following form:
urn:<Liquid_Data_Relational_data_source_name>
Declare the namespace in the query prolog. The namespace declaration has the following syntax:
namespace <alias>="<URI>"
namespace SY_WL_NS="urn:SY-WL"
You can then access the stored procedure using the namespace alias and the name of the stored procedure object. For example:
SY_WL_NS:wireless.dbo.RetAndOpParamTransformation("CUSTOMER_11")For stored procedures that return both a return value (for example, an integer return value) and have output or input_output parameters, the function signature in the Stored Procedure Description file is different from the signature that is used to write queries that access the stored procedure. If you look at the schema that displays in the the Stored Procedures palette of the Data View Builder, you will see the transformed signature.
The transformed signature combines the return value and any output or input_output parameters.
For example, consider an example using a Sybase stored procedure with the following signature:
create proc RetAndOpParamTransformation (@custidPattern varchar(64), @custCount numeric(10) output )
as
select @custCount = count(*) from CUSTOMER
where
CUSTOMER.CUSTOMER_ID Like '%' + @custidPattern + '%'
RETURN 1
The following is the Stored Procedure Description file for this stored procedure:
<?xml version="1.0" encoding="UTF-8"?>
<definitions>
<types>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<!-- The stored procedure returns an integer, which is mapped as the return_value, a reserved element name for stored procedure with a return.
-->
<xs:element name="RetAndOpParamTransformation">
<xs:complexType>
<xs:sequence>
<xs:element name="return_value"
type="xs:integer"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
</types>
<!-- The stored procedure signature mapping. The element RetAndOpParamTransformation wraps the return_value of the stored procedure. This stored procedure has custidPattern as an input parameter. custCount is defined as an output parameter of type integer (because it returns an integer count).
-->
<functions>
<function name="wireless.dbo.RetAndOpParamTransformation"
return_type="RetAndOpParamTransformation">
<argument label="custidPattern" mode="input_only"
type="xs:string"/>
<argument label="custCount" mode="output_only"
type="xs:integer"/>
<presentation group="Sample to show transformation of return_value and output prameter in a stored procedure"/>
</function>
</functions>
</definitions>
Because this stored procedure has a return value and an output parameter, the output of the function is transformed to the following schema:
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema">
<xsd:element name="RetAndOpParamTransformation">
<xsd:complexType>
<xsd:sequence>
<xsd:element name="return_value"
type="xsd:integer"/>
<xsd:element name="custCount" type="xsd:integer"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
</xsd:schema>
where the return_value is the return from the stored procedure and custCount is the output parameter. If you view this stored procedure in the Data View Builder, you will see the transformed schema.
The following is a query against this stored procedure:
namespace SY-WL-NS = "urn:SY-WL"
let $SY_WL_SP_Return := SY-WL-NS:wireless.dbo.RetAndOpParamTransformation("CUSTOMER_11")
return
<RetAndOpParamTransformation>
<return_value>{ xf:data($SY_WL_SP_Return/RetAndOpParamTransformation/return_value) }
</return_value>
<custCount>{ xf:data($SY_WL_SP_Return/RetAndOpParamTransformation/custCount) }
</custCount>
</RetAndOpParamTransformation>
In this query, the XPath expressions for return_value and for custCount have the same parent element.
|
|
|
|
||
 |
|
|
 |
 |
 |
|
||