For additional information in XA transaction adaptor settings see "Developing Adaptors" in BEA WebLogic Integration documentation: http://e-docs.bea.com/wli/docs81/devadapt/dbmssamp.html

Importing Relational Table and View Metadata

To create metadata on relational tables and views follow these steps:

Select the project in which you want to create your metadata. For example, if you have a project called myLDProject right-click on the project name and select Import Source Metadata... from the pop-up menu. Click Next.
From the available data sources in the Import Wizard select Relational (see Figure 3-3).

Figure 3-3 Selecting a Relational Source from the Import Metadata Wizard




 
Either select a data source from available sources or make a new data source available to the WLS.

Figure 3-4 Import Data Source Metadata Selection Dialog Box




 

Data Object Selection Options

For information on creating a new data source see Creating a New Data Source.

If you choose to select from an existing data source, several options are available (Figure 3-4).

Select All Database Objects

If you choose to select all, a table will appear containing all the tables, views, and stored procedures in your data source organized by catalog and schema.

Filter Data Source Objects

Sometimes you know exactly the objects in your data source that you want to turn into data services. Or your data source may be so large that a filter is needed. Or you may be looking for objects with specific naming characteristics (such as %audit2003%, a string which would retrieve all objects containing the enclosed string).

In such cases you can identify the exact parts of your relational source that you want to become data service candidates using standard JDBC wildcards. An underscore (_) creates a wildcard for an individual character. A percentage sign (%) indicates a wildcard for a string. Entries are case-sensitive.

For example, you could search for all tables starting with CUST with the entry: CUST%. Or, if you had a relational schema called ELECTRONICS, you could enter that term in the Schema field and retrieve all the tables, views, and stored procedure that are a part of that schema.

Another example:

 CUST%, PAY% 

entered in the Tables/Views field retrieves all tables and views starting with either CUST or PAY.

Note: If no items are entered for a particular field, all matching items are retrieved. For example, if no filtering entry is made for the Procedure field, all stored procedures in the data object will be retrieved.

For relational tables and views you should choose either the Select all option or Selected data source objects. For details on stored procedures see Importing Stored Procedure-Based Metadata.

SQL statement

Allows you to enter an SQL statement that is used as the basis for creating a data service. See Using SQL to Import Metadata for details.

Creating a New Data Source

Most often you will work with existing data sources. However, if you choose New... the WLS DataSource Viewer appears (Figure 3-5). Using the DataSource Viewer you can create new data pools and sources.

Figure 3-5 BEA WebLogic Data Source Viewer

For details on using the DataSource Viewer see Configuring a Data Source in WebLogic Workshop documentation.

Selecting an Existing Data Source

Only data sources that have set up through the BEA WebLogic Administration Console are available to a Data Services Platform application or project. In order for the BEA WebLogic Server used by DSP to access a particular relational data source you need to set up a JDBC connection pool and a JDBC data source.

• For details on setting up a JDBC connection pool see:

http://e-docs.bea.com/wls/docs81/ConsoleHelp/domain_jdbcconnectionpool_config_general.html

• For details on setting up a JDBC data source see:

http://e-docs.bea.com/wls/docs81/ConsoleHelp/domain_jdbcdatasource_config.html

Figure 3-6 Selecting a Data Source

Once you have selected a data source, you need to choose how you want to develop your metadata — by selecting all objects in the database, by filtering database objects, or by entering a SQL statement. (see Figure 3-4).

Creating Table- and View-Based Metadata

Once you have selected a data source and any optional filters, a list of available database objects appears.

Figure 3-7 Identifying Database Objects to be Used as Data Services

Using standard dialog commands you can add one or several tables to the list of selected data objects. To deselect a table, select that table in the right-hand column and click Remove.

A Search field is also available. This is useful for data sources which have many objects. Enter a search string, then click Search repeatedly to move through your list.

 

Once you have selected one or several data sources, click Next to verify the location of the to-be-created data services and the names of your new data services.

The imported data summary screen:

• Lists selected objects by name. You can mouse over the XML type to see the complete path (Figure 3-8).
• Lists the location of the generated data service in the current application.
• Identifies any name conflicts. Name conflicts occur when there is an data service of the same name present in the target directory. Any name conflicts are highlighted in red.

You can edit the file name to clarify the name or to avoid conflicts. Simply click on the name of the file and make any editing changes.

Alternatively, choose Remove All to return to the initial, nothing-is-selected state.

There are several situations where you will need to change the name of your data service:
• There already is a data service of the same name in your application.
• You are trying to create multiple data services with the same name.

In such cases the name(s) of the data service(s) having name conflicts appear in red. Simply change to a unique name using the built-in line editor.

Figure 3-8 Relational Source Import Data Summary Screen




 

Click Finish. A data service will be created for each object selected. The file extension of the created data services will always be .ds.

Database-specific Considerations

Database vendors variously support database catalogs and schemas. Table 3-9 describes this support for several major vendors.

Table 3-9 Vendor Support for Catalog and Schema Objects

XML Name Conversion Considerations

When a source name is encountered that does not fit within XML naming conventions, default generated names are converted according to rules described by the SQLX standard. Generally speaking, an invalid XML name character is replaced by its hexadecimal escape sequence (having the form _xUUUU_).

For additional details see section 9.1 of the W3C draft version of this standard:

http://www.sqlx.org/SQL-XML-documents/5WD-14-XML-2003-12.pdf 

Once you have created your data services you are ready to start constructing logical views on your physical data. See Using Data Services Design View. and Modeling Data Services.

Importing Stored Procedure-Based Metadata

Enterprise databases utilize stored procedures to improve query performance, manage and schedule data operations, enhance security, and so forth. You can import metadata based on stored procedures. Each stored procedure becomes a data service.

Note: Refer to your database documentation for details on managing stored procedures.

Stored procedures are essentially database objects that logically group a set of SQL and native database programming language statements together to perform a specific task.

Table 3-10 defines some commonly used terms as they apply to this discussion of stored procedures.

Table 3-10 Terms Commonly Used When Discussing Stored Procedures

Importing Stored Procedures Using the Metadata Import Wizard

The initial three steps for importing stored procedures are the same as importing any relational metadata (described under Importing Relational Table and View Metadata).

Note: Examples in this section use an Oracle database containing a large number of stored procedures.

You can select any combination of database tables, views, and stored procedures. If you select one or several stored procedures, the Metadata Import Wizard will guide you through the additional steps required to turn a stored procedure into a data service. These steps are:

Select one or several stored procedures. A data service can represent only one stored procedure. In other words, if you have five stored procedures, you will create five data services.

Figure 3-11 Selecting Stored Procedure Database Objects to Import




 
Click Next after you have added the database objects that you want to become data services.
You next need to select, then configure each stored procedure. If your stored procedure has an OUT parameter requiring a complex element, you may need to provide a schema.

Figure 3-12 Configuring a Stored Procedure in Pre-editing Mode




 

Data objects in the stored procedure that cannot be identified by the Metadata Import wizard will appear in red, without a datatype (Figure 3-12). In such cases you need to enter Edit mode (click the Edit button) to identify the data type.

Your goal in correcting an error condition associated with a stored procedure is to bring the metadata obtained by the import wizard into conformance with the actual metadata of the stored procedure. In some cases this will be correcting the location of the return type. In others you will need to adjust the type associated with an element of the procedure or add elements that would not found during the initial introspection of the stored procedure.

Figure 3-13 Stored Procedure in Editing Mode (with Callouts)




 

The Edit Procedure dialog allows you to:

Select a stored procedure from the complete list of stored procedures that you want to turn into data services.
Edit stored procedure parameters including setting mode (in, out, inout), type, and for out parameters, schema location.
Verify and, if necessary, add, remove, or change the order of parameters.
Verify and, if necessary, add, remove, or change any editable rowset.
Supply a return type (either simple or complex through identifying a schema location) in cases the Metadata Import wizard was unable to determine the type.
Accept or cancel your changes.

You need to complete information for each selected stored procedure before you can create your data services. In particular, any procedures shown in red must be addressed.

Details for each section of the procedure import dialog box appear below.

Procedure Profile

Each element in a stored procedure is associated with a type. If the item is a simple type, you can simply choose from the pop-up list of types.

Figure 3-14 Changing the Type of an Element in a Stored Procedure

If the type is complex, you may need to supply an appropriate schema. Click on the schema location button and either enter a schema path name or browse to a schema. The schema must reside in your application.

After selecting a schema, both the path to the schema file and the URI appear. For example:

{http://temp.openuri.org/schemas/Customer.xsd}CUSTOMER

Procedure Parameters

The Metadata Import wizard, working through JDBC, identifies any stored procedure parameters. This includes the name, mode (input [in], output [out], or bidirectional [inout]) and data type. The out mode supports the inclusion of a schema.

Complex type is only supported under three conditions:

• as the output parameter
• as the return type
• as a rowset

All parameters are editable, including the name.

Note: If you make an incorrect choice you can use the Previous, then Next button to return the dialog to its initial state.

Editable Rowsets

Not all databases support rowsets. In addition, JDBC does not report information related to defined rowsets. In order to create data services from stored procedures that use rowset information, supply the correct ordinal (matching number) and a schema. If the schema has multiple global elements, you can select the one you want from the Type column. Otherwise the type will be the first global element in your schema file.

The order of rowset information is significant; it must match the order in your data source. Use the Move Up / Move Down commands to adjust the ordinal number assigned to the rowset.

Complete the importation of your procedures by reviewing and accepting items in the Summary screen (see step 4. in Importing Relational Table and View Metadata for details).

Note: XML types in data services generated from stored procedures do not display native types. However, you can view the native type in the Source View pragma (see Using Source View).

Stored Procedure Data Service and Schema Files

Imported stored procedure metadata is quite similar to imported metadata for relational tables and views.

Note: If a stored procedure has only one return value and the value is either simple type or a RowSet which is mapping to an existing schema, no schema file created.

Handling Stored Procedure Rowsets

A rowset type is a complex type. The name of the rowset type can be:

• The parameter name (in case of a input/output or output only parameter)
• An assigned name such as RETURN_VALUE (if return value)
• The referenced element name (result rowsets) in a user-specified schema

The rowset type contains a sequence of a repeatable elements (for example called CUSTOMER) with the fields of the rowset.

Note: All rowset-type definitions must conform to this structure.

In some cases the Metadata Import wizard can automatically detect the structure of a rowset and create an element structure. However, if the structure is unknown, you will need to provide it through the wizard.

Stored Procedure Support for Commonly Used Databases

Each database vendor approaches stored procedures differently. XQuery support limitations are, in general, due to JDBC driver limitations.

General Restriction

DSP does not support rowset as an input parameter.

Oracle Stored Procedure Support

Table 3-15 summarizes DSP support for Oracle database procedures.

Table 3-15 Support for Oracle Store Procedures

Sybase Stored Procedure Support

Table 3-16 summarizes DSP support for Sybase SQL Server database procedures.

Table 3-16 Support for Sybase Stored Procedures

IBM DB2 Stored Procedure Support

Table 3-17 summarizes DSP support for IBM DB2 database procedures.

Table 3-17 Support for IBM Store Procedures

Informix Stored Procedure Support

Table 3-18 summarizes DSP support for Informix database stored procedures.

Table 3-18 Support for Informix Stored Procedures

Microsoft SQL Server Stored Procedure Support

Table 3-19 summarizes DSP support for Microsoft SQL Server database procedures.

Table 3-19 DSP Support for Microsoft SQL Server Stored Procedures

Using SQL to Import Metadata

One of the relational import metadata options (see Figure 3-4) is to use an SQL statement to customize introspection of a data source. If you select this option the SQL Statement dialog appears.

Figure 3-20 SQL Statement Dialog Box

You can type or paste your SELECT statement into the statement box (Figure 3-20), indicating parameters with a "?" question-mark symbol. Using one of the DSP data samples, the following SELECT statement can be used:

SELECT * FROM RTLCUSTOMER.CUSTOMER WHERE CUSTOMER_ID = ?

RTLCUSTOMER is a schema in the data source, CUSTOMER is, in this case, a table.

For the parameter field, you would need to select a data type. In this case, CHAR or VARCHAR.

The next step is to assign a data service name.

When you run your query under Test View, you will need to supply the parameter in order for the query to run successfully.

Once you have entered your SQL statement and any required parameters click Next to change or verify the name and location of your new data service.

Figure 3-21 Relational SQL Statement Imported Data Summary Screen

The imported data summary screen identifies a proposed name for your new data service.

The final steps are no different than you used to create a data service from a table or view.

Relational Data Types-to-Metadata Conversion

The following table shows how data types provided by various relational databases are converted into XQuery data types. Types are listed in alphabetical order.

Table 3-22 Relational Data Types and Their XQuery Counterparts