Building Queries and Data Views
|
 |
 |
|
|
 |
 |
If you have stored procedures defined in your databases, you can expose them to Liquid Data as a data source and use them in your Liquid Data queries.
You can also expose any query from the database as a data source. You expose these "SQL Calls" to Liquid Data through a SQL Call Description File. This chapter describes how to define stored procedures and SQL queries in a SQL Call Description File, and contains the following sections:
For an example and a demo of defining a stored procedure and using it in a query, see Example: Defining and Using a Customer Orders Stored Procedure. For information on samples installed with Liquid Data, including a stored procedure sample and a SQL Call example, see Samples Installed with Liquid Data in Liquid Data by Example.
To use stored procedures in Liquid Data, you must create a SQL Call Description File. The SQL Call Description File is an XML schema file that defines the types and the functions for a set of stored procedures. For details on defining a SQL Call Description File, see SQL Call Description File and Rules for Specifying SQL Call Description Files. For database-specific information, see Stored Procedure Support by Database.
Perform the following steps to define a stored procedure for use with Liquid Data.
sql_calls directory of the Liquid Data repository. For details, see SQL Call Description File and Rules for Specifying SQL Call Description Files.You can now access your stored procedures in the Data View Builder. If you are already connected to the server in the Data View Builder, you must re-connect by selecting File —> Connect from Data View Builder menu. The Stored Procedures tab appears in the Design view under the sources tab of the Data View Builder. You can now use your stored procedures as you do other building blocks (for example, data sources, XQuery functions, and so on) to build queries.
The SQL Call Description File is an XML file that defines stored procedures to Liquid Data. This section describes the schema of the SQL Call Description File, and contains the following sections:
For sample SQL Call Description Files, see Sample SQL Call Description Files.
The SQL Call Description File has the following main sections:
The type definitions section of the SQL Call Description File is defined in the <types> element. This element defines namespaces and complex types for the stored procedures defined in the SQL Call Description File.
The function definitions section of the SQL Call Description File is defined in the <functions> element. Within the <functions> element are <function> elements, each of which defines the signature of a stored procedure. You can define one or more stored procedures in a single SQL Call Description File.
The following is the schema definition file for the SQL Call Description File:
<?xml version="1.0" encoding="UTF-8"?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" elementFormDefault="qualified">
<xsd:element name="definitions">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="types"/>
<xsd:element ref="functions"/>
</xsd:sequence>
<xsd:attribute name="targetNamespace" use="optional"
type="xsd:string"/>
</xsd:complexType>
</xsd:element>
<xsd:element name="types">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="schema"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="schema" type="xsd:anyType"/>
<xsd:element name="functions">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="function" maxOccurs="unbounded"/>
</xsd:sequence>
</xsd:complexType>
</xsd:element>
<xsd:element name="function">
<xsd:complexType>
<xsd:sequence>
<xsd:element ref="argument" minOccurs="0"
maxOccurs="unbounded"/>
<xsd:element ref="presentation" minOccurs="0"/>
<xsd:element ref="description" minOccurs="0"/>
</xsd:sequence>
<xsd:attribute name="name" use="required"
type="xsd:string"/>
<xsd:attribute name="return_type" use="required"
type="xsd:string"/>
</xsd:complexType>
</xsd:element>
<xsd:element name="argument">
<xsd:complexType>
<xsd:attribute name="type" use="required"
type="xsd:string"/>
<xsd:attribute name="label" use="required"
type="xsd:string"/>
<xsd:attribute name="mode" use="required" type="modeType"/>
</xsd:complexType>
</xsd:element>
<xsd:simpleType name="modeType">
<xsd:restriction base="xsd:string">
<xsd:enumeration value="input_only"/>
<xsd:enumeration value="output_only"/>
<xsd:enumeration value="input_output"/>
</xsd:restriction>
</xsd:simpleType>
<xsd:element name="presentation">
<xsd:complexType>
<xsd:attribute name="group" use="required"
type="xsd:string"/>
</xsd:complexType>
</xsd:element>
<xsd:element name="description" type="xsd:string"/>
</xsd:schema>
Table 10-1 lists and describes the elements and attributes of the SQL Call Description File.
|
|
|
|
|
Declares any primitive or complex data types used in the stored procedure. |
|
|
Contains the function definitions for all of the stored procedures represented in this file. |
|
|
|
|
Name of the stored procedure as defined in the database. If the stored procedure is part of a package, the name is the fully qualified name of the stored procedure (for example, If you are using procedure groups in Sybase or Microsoft SQL Server, see Rules for Procedure Names Containing a Semi-Colon. |
|
|
Return type of the stored procedure. The type is defined in the |
|
|
|
Contains the SQL statement text for a query against the data source. The query can then be used as a data source. |
|
|
Contains the argument declarations for the inputs and/or outputs of the stored procedure. |
|
The name of the argument input or output. This name is used in queries and is displayed in clients such as the Data View Builder. |
|
|
Type of the argument. The type can be one of the types listed in Supported Datatypes, or it can be a complex type declared in the SQL Call Description File. |
|
|
Lists whether the argument is part of the input, output, or both. Possible values are: |
|
|
|
|
|
|
Comment text describing the stored procedures used in the SQL Call Description File. |
Note: An element within the types definition with a name specified with name="return_value" is reserved to specify the return value from a function or a procedure. For an example, see Example 2: Type Definition with Simple Return Value.
The stored procedures you define in the SQL Call Description Files must use the XML data types shown in Table 10-2. You must map the database data types to one of the types in this table. For data type support by database, see Stored Procedure Support by Database.
Except for user-defined complex data types, the types are all primitive data types.
For JDBC and database-specific type mapping, see Datatypes in the XQuery Reference Guide.
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:
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 SQL Call 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 SQL Call 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 SQL Call 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 definition 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.
Sybase and Microsoft SQL Server databases provide the ability to group stored procedures by using a semicolon 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 SQL Call Description File, use the database name (the name with the semicolon character). When you use these procedure names in an XQuery, however, you must substitute an underscore character (_) for the semicolon character. The Data View Builder automatically substitutes the underscore character for the semicolon character in the XQuery it generates.
For example, consider the following definition for a stored procedure in a SQL Call 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
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 SQL Call 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.
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.
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.
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).
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.
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>
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 SQL Call 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>
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 SQL Call 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 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 SQL Call 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.
This section shows several sample SQL Call Description Files. For simplicity and readability, each of the examples shown defines a single stored procedure and its supporting type; your SQL Call Description Files can define multiple stored procedures and multiple types. For information on samples installed with Liquid Data, including a stored procedure sample and a SQL Call example, see Samples Installed with Liquid Data in Liquid Data by Example.
This section includes the following examples:
The following SQL Call Description File describes a DB2 stored procedure that returns simple data types with input_only, output_only, and input_output parameters.
<?xml version="1.0" encoding="UTF-8"?>
<definitions>
<types>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="EmptyOutput">
<xs:complexType>
<xs:sequence>
</xs:sequence>
</xs:complexType>
</xs:schema>
</types>
<functions>
<!-- given a customer id if valid, returns as output
parameters all the customer details -->
<function name="CALLINCALLOUT" return_type="EmptyOutput">
<argument label="custid" mode="input_only" type="xs:string"/>
<argument label="fname" mode="output_only" type="xs:string"/>
<argument label="lname" mode="output_only" type="xs:string"/>
<argument label="telephoneNumber" mode="output_only"
type="xs:long"/>
<argument label="customerSinceAsData" mode="output_only"
type="xs:date"/>
<argument label="customerSinceAsTimeStamp"
mode="output_only" type="xs:dateTime"/>
<presentation group="DB2 stored procedures"/>
</function>
</functions>
</definitions>
The following is the stored procedure signature for this SQL Call Description File. This signature creates a procedure that has one simple input and returns five simple outputs.
CREATE PROCEDURE DB2ADMIN.CALLINCALLOUT (
IN CUSTID varchar(64), OUT FNAME varchar(4000),
OUT LNAME varchar(4000), OUT TELEPHONENUMBER bigint,
OUT CUSTOMERSINCE date, OUT CUSTOMERSINCE1 timestamp )
EXTERNAL NAME
'"DB2ADMIN".SQL30205005750980:db2test.CallInCallOut.callInCallOut'
SPECIFIC DB2ADMIN.CALLINCALLOUT
RESULT SETS 0
LANGUAGE JAVA
PARAMETER STYLE JAVA
NOT DETERMINISTIC
FENCED NO
DBINFO NULL
CALL MODIFIES SQL DATA
The following SQL Call Description File describes an Oracle stored procedure that returns an output parameter as a cursor.
<?xml version="1.0" encoding="UTF-8"?>
<definitions>
<types>
<xs:element name="OutCursor">
<xs:complexType>
<xs:sequence>
<xs:element name="CUSTOMER" minOccurs="0"
maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="C_CUSTKEY" type="xs:integer"/>
<xs:element name="C_FNAME" type="xs:string"/>
<xs:element name="C_LNAME" type="xs:string"/>
<xs:element name="C_STATE" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="Output">
<xs:complexType>
<xs:sequence/>
</xs:complexType>
</xs:element>
</xs:schema>
</types>
<functions>
<function name="TEST_PACKAGE.GETCUSTOMER" return_type="Output">
<argument label="CUSTID" mode="input_only" type="xs:string"/>
<argument label="customer_OUT" mode="output_only"
type="OutCursor"/>
<presentation group="OR-TEST stored procedures"/>
</function>
</functions>
</definitions>
The following is the stored procedure signature for this SQL Call Description File. This signature creates a procedure that returns a cursor as an output parameter.
create or replace package test_package as
-- Stored procedure that returns a cursor as an output parameter
procedure getCustomer
( CUSTOMERID IN VARCHAR, cust_cursor1 OUT ref_cursor );
end test_package ;
The following SQL Call Description File describes a DB2 stored procedure that returns multiple result sets.
<?xml version="1.0" encoding="UTF-8"?>
<definitions>
<types>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="CustomerAndOrders">
<xs:complexType>
<xs:sequence>
<xs:element ref="resultSetCustomer"/>
<xs:element ref="resultSetCustomerOrders"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="resultSetCustomer">
<xs:complexType>
<xs:sequence>
<xs:element name="customerRow" minOccurs="1"
maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="C_CUSTKEY" type="xs:string"/>
<xs:element name="C_FNAME" type="xs:string"/>
<xs:element name="C_LNAME" type="xs:string"/>
<xs:element name="C_STATE" type="xs:string"/>
<xs:element name="C_SINCE" type="xs:date"/>
<xs:element name="C_TELEPHONENO" type="xs:long"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="resultSetCustomerOrders">
<xs:complexType>
<xs:sequence>
<xs:element name="orderRow" minOccurs="1"
maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="C_CUSTKEY" type="xs:string"/>
<xs:element name="CO_ORDERKEY" type="xs:string"/>
<xs:element name="CO_ORDERDATE" type="xs:date"/>
<xs:element name="CO_SHIPMETHOD" type="xs:date"/>
<xs:element name="CO_TOTALORDERAMT" type="xs:decimal"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
</types>
<functions>
<!-- result one returns a customer, result 2 has the
orders for that customer -->
<function name="CALLMULTIPLERESULTSET"
return_type="CustomerAndOrders">
<argument label="custid" mode="input_only" type="xs:string"/>
<presentation group="DB2 stored procedures"/>
</function>
</functions>
</definitions>
The following is the stored procedure signature for this SQL Call Description File. This signature creates a procedure that returns multiple result sets.
CREATE PROCEDURE DB2ADMIN.CALLMULTIPLERESULTSET (
IN CUSTID varchar(64) )
EXTERNAL NAME '"DB2ADMIN".SQL30206110348560:db2test.CallMultipleResultSet.callMultipleResultSet'
SPECIFIC DB2ADMIN.CALLMULTIPLERS
RESULT SETS 2
LANGUAGE JAVA
PARAMETER STYLE JAVA
NOT DETERMINISTIC
FENCED NO
DBINFO NULL
CALL MODIFIES SQL DATA
The following SQL Call Description File describes an Oracle stored procedure that returns a cursor as a return_value.
<?xml version="1.0" encoding="UTF-8"?>
<definitions>
<types>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Output_TEST_PACKAGE.GETCUSTOMERBYID">
<xs:complexType>
<xs:sequence>
<xs:element name="return_value">
<xs:complexType>
<xs:sequence>
<xs:element maxOccurs="unbounded" minOccurs="0"
name="customer">
<xs:complexType>
<xs:sequence>
<xs:element name="FIRST_NAME" type="xs:string"/>
<xs:element name="LAST_NAME" type="xs:string"/>
<xs:element name="CUSTOMER_ID" type="xs:string"/>
<xs:element name="STATE" type="xs:string"/>
<xs:element name="ZIPCODE" type="xs:string"/>
<xs:element name="CITY" type="xs:string"/>
<xs:element name="STREET_ADDR2"
type="xs:string"/>
<xs:element name="STREET_ADDR1"
type="xs:string"/>
<xs:element name="CUSTOMER_SINCE"
type="xs:dateTime"/>
<xs:element name="EMAIL_ADDRESS"
type="xs:string"/>
<xs:element name="TELEPHONE_NUMBER"
type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
</types>
<functions>
<function name="TEST_PACKAGE.GETCUSTOMERBYID"
return_type="Output_TEST_PACKAGE.GETCUSTOMERBYID">
<argument label="CUSTID" mode="input_only" type="xs:string"/>
</function>
</functions>
</definitions>
The following is the stored procedure signature for this SQL Call Description File. This signature creates a procedure that returns a cursor.
create or replace package body test_package as
-- SP that returns a cursor
FUNCTION getCustomerByID (custID varchar)
RETURN CUST_CURSOR IS cur CUST_CURSOR;
BEGIN
open cur for
select first_name, last_name, customer_id, state,
zipCode,city, street_address2, street_address1,
customer_since, email_address, telephone_number
from wireless.customer
where customer_id = custID;
return cur;
END;
end test_package ;
The following SQL Call Description File defines a SQL statement with a subquery and syntax specific to Oracle.
<?xml version="1.0" encoding="UTF-8"?>
<definitions>
<types>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Customers">
<xs:complexType>
<xs:sequence>
<xs:element ref="resultSetCustomer"/>
</xs:sequence>
</xs:complexType>
</xs:element>
<xs:element name="resultSetCustomer">
<xs:complexType>
<xs:sequence>
<xs:element name="customerRow" minOccurs="0"maxOccurs="unbounded">
<xs:complexType>
<xs:sequence>
<xs:element name="CUSTOMER_ID" type="xs:string"/>
<xs:element name="ORDER_ID" type="xs:string"/>
<xs:element name="LINE_ID" type="xs:string"/>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
</types>
<functions>
<function name="GetOrderInfoSQL" return_type="Customers" >
<sql_statement>
SELECT t1.customer_id, t2.order_id, t3.line_id from
customer t1,
(SELECT * from customer_order where customer_id != 'CUSTOMER_1' ) t2,
customer_order_line_item t3
where t1.customer_id = ? and t2.customer_id (+) = t1.customer_id and t3.order_id(+)=t2.order_id
</sql_statement>
<argument label="customer_id" mode="input_only" type="xs:string"/>
<presentation group="Oracle SQL Call"/>
</function>
</functions>
</definitions>
This section lists stored procedure support by database vendor. Each vendor supports the data types supported in their respective databases. The following databases are supported:
Table 10-3 describes the stored procedure support for Oracle databases. Table 10-4 describes Oracle stored procedures return values.
|
Only database data types that you can map to one of the Liquid Data primitive types defined in Supported Datatypes. |
|
|
|
||
|
Only database data types that you can map to one of the Liquid Data primitive types defined in Supported Datatypes. |
|
See Table 10-3. |
Table 10-5 describes the stored procedure support for Microsoft SQL Server databases. Table 10-6 describes Microsoft SQL Server stored procedures return values.
|
Only database data types that you can map to one of the Liquid Data primitive types defined in Supported Datatypes. |
||
|
Only database data types that you can map to one of the Liquid Data primitive types defined in Supported Datatypes. |
If you are using procedure groups, see Rules for Procedure Names Containing a Semi-Colon for information on mapping the procedure names to the SQL Call Description File and using the names in an XQuery.
Microsoft SQL Server parameter names begin with the @ character, but the name must appear in the SQL Call Description File without the @ character. For example, a parameter named @myInputParameter must be mapped as myInputParameter.
Table 10-7 describes the stored procedure support for Sybase databases. Table 10-8 describes Sybase stored procedures return values.
|
Only database data types that you can map to one of the Liquid Data primitive types defined in Supported Datatypes. |
||
|
Only database data types that you can map to one of the Liquid Data primitive types defined in Supported Datatypes. |
If you are using procedure groups, see Rules for Procedure Names Containing a Semi-Colon for information on mapping the procedure names to the SQL Call Description File and using the names in an XQuery.
Sybase parameter names begin with the @ character, but the name must appear in the SQL Call Description File without the @ character. For example, a parameter named @myInputParameter must be mapped as myInputParameter.
Table 10-9 describes the stored procedure support for IBM DB2 databases. Table 10-10 describes IBM DB2 stored procedures return values.
|
Only database data types that you can map to one of the Liquid Data primitive types defined in Supported Datatypes. |
|
|
Only database data types that you can map to one of the Liquid Data primitive types defined in Supported Datatypes. |
|
|
Only database data types that you can map to one of the Liquid Data primitive types defined in Supported Datatypes. |
Table 10-11 describes the stored procedure support for Informix databases. Table 10-12 describes Informix stored procedures return values.
|
Only database data types that you can map to one of the Liquid Data primitive types defined in Supported Datatypes. |
You can use stored procedures to build queries in the Data View Builder just like you use other data sources. Drag and drop input elements into the inputs of the procedure and drag and drop output elements to combine with other sources or to map onto a target XML schema.
Figure 10-13 Drag and Drop Input Elements into the Elements of the Stored Procedure
This section shows an example of defining a stored procedure and then using it in a query, and is divided into the following sections:
You must define the stored procedures to Liquid Data before you can use them in queries. For details, see To Define Stored Procedures to Liquid Data. To use a stored procedure in the Data View Builder, select Stored Procedures from the Sources tab, navigate to your stored procedure, then drag and drop it into the design workspace. You can then connect data by dragging and dropping inputs and outputs.
This example details the steps to define a stored procedure to Liquid Data and then use it in a query. This example is similar to the example installed in the following directory:
BEA_HOME/liquiddata/samples/buildQuery/stored-procedure
The demo in this directory includes the SQL Call Description File and a Data View Builder project file.
The stored procedure in this example answers the following business question: For all orders greater than or equal to $500.00, find the number of orders and the total value of all of those orders for a given customer.
Stored Procedure Demo... If you are looking at this documentation online, you can click the "Demo" button to see a viewlet demo showing how to define a stored procedure and use it in a query. This demo previews the steps described in detail in the following sections.
You must have stored procedures defined in your database before you can access them through Liquid Data.
Every database has its own way of creating stored procedures. This sample uses a Pointbase database, and Pointbase uses Java stored procedures. The source code for the sample stored procedure is installed with Liquid Data in the following file:
BEA_HOME/liquiddata/samples/buildQuery/stored-procedure/pbsp.java
The signature for this stored procedure is created with the following SQL statements:
create procedure
GetOrderInfo( IN P1 VARCHAR(20), IN P2 INTEGER,
OUT P3 INTEGER, OUT P4 INTEGER)
LANGUAGE JAVA
SPECIFIC GetOrderInfo
EXTERNAL NAME "com.bea.ldi.sample.pbsp::GetOrderInfo"
PARAMETER STYLE SQL;
For details on the structure of the SQL Call Description File, see SQL Call Description File and Rules for Specifying SQL Call Description Files.
The SQL Call Description File for this example defines an empty complex type in the <types> section and defines a function that returns that complex type in the <functions> section. The function definition contains <argument> elements for each input and output argument. The <argument> elements specify the name (label attribute), parameter type (mode attribute), and data type (type attribute) for each input and output of the stored procedure.
The following is a code listing of the SQL Call Description File for this example.
<?xml version="1.0" encoding="UTF-8"?>
<definitions>
<types>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
<xs:element name="Results">
<xs:complexType>
<xs:sequence>
</xs:sequence>
</xs:complexType>
</xs:element>
</xs:schema>
</types>
<functions>
<function name="GetOrderInfo" return_type="Results">
<argument label="customer_id" mode="input_only"
type="xs:string"/>
<argument label="order_amount" mode="input_only"
type="xs:integer"/>
<argument label="totalsum" mode="output_only"
type="xs:integer"/>
<argument label="totalorder" mode="output_only"
type="xs:integer"/>
<presentation group="Pointbase stored procedures"/>
</function>
</functions>
</definitions>
Perform the following steps to specify the SQL Call Description File in the data source description:
If you are creating a new data source, you must also configure a JDBC Connection Pool to access your database and a JDBC Data Source for the connection pool.
Start the Data View Builder and connect to the Liquid Data server. If you are already connected, run the File > Connect command to reconnect. If you configured the Stored procedure correctly, it appears in the Sources tab as one of the stored procedures.
Perform the following steps in the Data View Builder to create a query that uses the stored procedure.
Perform the following to run this query:
<Results>
<totalsum>7000</totalsum>
<totalorder>3</totalorder>
</Results>
|
|
|
