Client Application Developer's Guide

Using SQL to Access Data Services

Applications can access data services through SQL. This is necessary in the case of many reporting tools such as Crystal Reports, Hyperion, and Business Objects. But the ability to handle SQL is also useful in other contexts.

For example, it is useful to be able to run ad hoc SQL queries against data services using tools such as DbVisualizer. Application developers also can use a standalone Query Plan Viewer utility which supports both XQuery and SQL.

BEA AquaLogic Data Services Platform supports table parameters, an extension to SQL-92.

SQL access is provided through the AquaLogic Data Services Platform JDBC driver. The driver implements the java.sql.* interface in JDK 1.4x to provide access to an AquaLogic Data Services Platform server through the JDBC interface. You can use the JDBC driver to execute SQL92 SELECT queries, or stored procedures over AquaLogic Data Services Platform applications.

This chapter explains how to use SQL to access data services as well as how to set up and use the AquaLogic Data Services Platform JDBC driver. It covers the following topics:

Publishing Data Service Functions As SQL

To access data services through SQL, data service functions first need to be published as SQL objects through a JDBC interface. These SQL objects include tables, stored procedures, and functions.

To publish data service functions as SQL Objects, the following tasks need to be performed:

Once deployed, the newly created SQL objects are available to your application through standard JDBC.

Techniques for publishing data services as SQL are described in Publishing Data Services Functions for SQL Use in the Data Services Developer's Guide.

Using Custom Database Functions through AquaLogic Data Services Platform

Built-in or custom functions in your database can be made available through data services once the function has been registered with AquaLogic Data Services Platform through a library. For more information about using database functions with AquaLogic Data Services Platform, refer to Creating and Working with XQuery Function Libraries in Data Services Developer's Guide.

SQL Support in AquaLogic Data Services Platform

This section outlines the SQL-92 support in the AquaLogic Data Services Platform JDBC driver.

Supported Features

The AquaLogic Data Services Platform JDBC driver provides SQL-92 support for the SELECT statement. INSERT, UPDATE, and DELETE statements are not supported. DDL (Data Definition Language) statements are also not supported.

The AquaLogic Data Services Platform JDBC driver implements the following interfaces from java.sql package specified in JDK 1.4x:

Additional Details

The following limitations are known to exist in the AquaLogic Data Services Platform JDBC driver:

The following table (Table 5-1) notes additional limitations that apply to SQL language features.

Table Parameter Support

Table parameters extend SQL-92 by providing the ability to add parameters to SQL FROM clauses. For example, in SQL you can encounter a situation where it is necessary to code an exact number of parameters (highlighted) into a query.

In the following query test.rtlall.CUSTOMER is the entire customer table.

SELECT cust.CUSTOMER_ID, cust.FIRST_NAME, cust.LAST_NAME
FROM test.rtlall.CUSTOMER cust 
where cust.CUSTOMER_ID in (?, ?, ?, ...)
and cust.LAST_NAME in (?, ?, ?, ... )

If a large number of parameters are involved, data entry can be slow and setting up the SQL statement tedious.

Table parameters provide an alternative. The following query uses table parameters (highlighted):

SELECT cust.CUSTOMER_ID, cust.FIRST_NAME, cust.LAST_NAME
FROM ? as id_cust(id_cust_num), test.rtlall.CUSTOMER as cust , 
? as id_cust(last_name)
WHERE id_cust.id_cust_num = cust.CUSTOMER_ID 
and cust.LAST_NAME = id_cust.last_name

The table parameter is specified through the same mechanism as a a parameter; a question mark ("?") is used in place of the appropriate table name.

Use Case for Table Parameters

A scenario: a data service contains consolidated information on all recent customer orders. A sales manager has a consolidated list of all government customers in European countries. The goal is to use a data service to obtain order information for that specific set of customers.

Stepping back from the example it is easy to see the that the scenario is a common one: a join between the manager's customer list and order information. However, if the manager's customer list is long and not already available through a database, it would be convenient to be able to pass in a list of values as if it were a column in a table.

In the SQL cited above a list of customers is passed in as a table with a single column. The clause:

? as id_cust(id_cust_num)

provides a virtual table value (id_cust) and a virtual column name (id_cust_num).

Although aliasing is not mandatory, it is generally recommended since default parameter column names follow numerical sequence (0, 1, and so forth) and as such are subject to unexpected name conflicts.

The one case where defaults are appropriate is when wildcards are employed such as:

select * from ?

and other simple cases involving wildcards.

Setting Table Parameters Using JDBC

Table parameters are passed to data services through the AquaLogic Data Services Platform JDBC driver, specifically through its TableParameter class. The class (shown in its entirety in Listing 5-1) represents an entire table parameter as well as the rows it represents.

public class TableParameter implements Serializable {      
    /**
     * Constructor
     * 
     * @schema      the schema for the table
     */
    public TableParameter(ValueType[] schema);
    
    /** 
     * Creates a new a row and adds it to the list of rows in this table
     */
    public Row createRow();
    /** 
     * Gets the rows of this table
     */
    public List/*Row*/ getRows();
    /** 
     * Gets the schema of this table
     */
    public ValueType[] getSchema();
    /**
     * Represents a row in the table
     */
    public class Row implements Serializable {
        /**
         * Sets a value to a particular column
         * @param colIdx    the index of the column to set
         * @param val       the value for the column
         * @exception       if index is out of bounds
         */
        public void setObject(int colIdx,Object val) throws SQLException;
        Object getObject(int colIdx);
    }

Creating Table Parameters

The following steps show how to create a TableParameter class:

JDBC Usage

TableParameters are passed through JDBC just like any other parameter, through a PreparedStatement.

For example, you would first create a PreparedStatement with the query:

SELECT cust.CUSTOMER_ID, cust.FIRST_NAME, cust.LAST_NAME
FROM ? as id_cust(id_cust_num), test.rtlall.CUSTOMER as cust ,
? as id_cust(last_name)
WHERE id_cust.id_cust_num = cust.CUSTOMER_ID 
and cust.LAST_NAME = id_cust.last_name
and cust.ORDER_AMT > ? 

Table Parameter Example

The following simplified example illustrates the use of a table parameter. An in-memory list contains three customers: CUST_1, CUST_2, and CUST_3.

SELECT cust.cust_num, order.item, order.price
FROM ? as cust(cust_num), Order as order
WHERE cust.cust_num = order.cust_id

The supporting JDBC code is shown in listing Listing 5-2:

//first create the table parameter
ValueType[] schemaList = new ValueType[1];
schemaList[0] = ValueType.REPEATING_VARCHAR_TYPE;
TableParameter tableParam = new TableParameter(schemaList);
// then create the rows in your virtual table
// (in practice you would read data in from a file 
// or some other data stream)

TableParameter.Row row1 = tableParam.createRow();
row1.setObject(1," CUSTOMER _1");
TableParameter.Row row2 = tableParam.createRow();
Row2.setObject(1," CUSTOMER _2");

// repeat for second table parameter
//	(...)

// execute the query 
PreparedStatement objPreparedStatement = objConnection.prepareStatement(
 "SELECT cust.CUSTOMER_ID, cust.FIRST_NAME, cust.LAST_NAME
FROM ? as id_cust(id_cust_num), test.rtlall.CUSTOMER as cust , ? as id_cust(last_name)
WHERE id_cust.id_cust_num = cust.CUSTOMER_ID 
and cust.LAST_NAME = id_cust.last_name
and cust.ORDER_AMT > ? ");

// and set table parameters
objPreparedStatement.setObject(1,tableParam); 
objPreparedStatement.setObject(2,tableParam2); 
objResultSet = objPreparedStatement.executeQuery();

XML and SQL Type Mappings

When data service information is accessed from a JDBC client, the data is mapped from its XML schema format to SQL types.

The XML types are defined by:

xmlns:xs="http://www.w3.org/2001/XMLSchema"

The Java types are defined by:

java.sql.Types

XML types that can be mapped to SQL Type Mappings are shown in Table 5-2.

Accessing Data Services Functions Through JDBC

The AquaLogic Data Services Platform JDBC driver enables JDBC and ODBC clients to access information available from data services using SQL. The JDBC driver increases the flexibility of the AquaLogic Data Services Platform integration layer by enabling access from database visualization and reporting tools, such as DbVisualizer, Crystal Reports, Hyperion, and Business Objects. For the client, the AquaLogic Data Services Platform integration layer appears as a relational database, with each data service function comprising a table. Internally, AquaLogic Data Services Platform translates SQL queries into XQuery.

Some constraints associated with the AquaLogic Data Services Platform JDBC driver include the following:

You can create flat views to be used from the JDBC driver to expose non-flat data services,.

This section discusses the SQL Name Mapping technique used to map SQL functions to AquaLogic Data Services Platform functions, along with the steps to configure the JDBC Driver connection using Java and non-Java applications. It includes the following topics:

About the AquaLogic Data Services Platform JDBC Driver

The AquaLogic Data Services Platform JDBC driver has the following features:

Using AquaLogic Data Services Platform JDBC Driver, you can control the metadata accessed through SQL based on the access rights set at the JDBC driver level. This ensures that authorized users can view only those tables and procedures, which they are authorized to access. However, to be able to use this feature, the AquaLogic Data Services Platform console configuration should be set to check access control. For more information, refer to the " Securing Data Services Platform Resources" section in the Administration Guide.

Note:

Data Service Functions and Corresponding JDBC Artifacts

AquaLogic Data Services Platform views data retrieved from a database in the form of data sources and functions. The following table (Table 5-3) shows the equivalent terminology.

For example, if you have a project TestDataServices and CUSTOMERS.ds with a function getCustomers() under the schema MySchema, then you can map getCustomers as an SQL object as follows:

TestDataServices.MySchema.getCustomer

where TestDataServices is the catalog and MySchema is the name of the schema folder. This mapping is based on mapping the AquaLogic Data Services Platform functions to SQL objects. For more information about mapping AquaLogic Data Services Platform functions as SQL objects, refer to Publishing Data Services Functions for SQL Use in the Data Services Developer's Guide.

Supported Functions

AquaLogic Data Services Platform supports many functions that can be used to access data services through various reporting tools. In the following tables functions are divided into the following types:

Numeric Functions

The following numeric operation functions are provided:

String Functions

The following string management functions are provided:

Datetime Functions

The following datetime functions are provided:

Aggregate Functions

The following aggregation functions are provided:

JDBC Metadata Search Patterns

The AquaLogic Data Services Platform JDBC driver supports standard JDBC API search patterns, as shown in Table 5-8.

Assuming that the default_catalog is catalog1 and default_schema is schema1, Table 5-9 shows some common matching patterns.

Configuring the AquaLogic Data Services Platform JDBC Driver

AquaLogic Data Services Platform JDBC driver is located in an archive file named ldjdbc.jar, which is available in the following directory after you install BEA AquaLogic Data Services Platform:

<bea_home>\weblogic81\liquiddata\lib\

To use the AquaLogic Data Services Platform JDBC driver on a client computer, you need to configure the classpath, class name, and the URL for the JDBC driver. To configure the driver on a client computer, perform the following steps:

Configuring the Connection Using the Properties Object:

You can configure the JDBC driver connection using the properties object as follows:

props.put("user", "weblogic");
props.put("password", " weblogic ");
props.put("application", "RTLApp");
Connection objConnection = DriverManager.getConnection("jdbc:dsp@localhost:7001", props);

Alternatively, you can specify the AquaLogic Data Services Platform application name, RTLApp, in the connection object itself, as shown in the following snippet:

props.put("user", " weblogic");
props.put("password", " weblogic ");
Connection objConnection = DriverManager.getConnection("jdbc:dsp@localhost:7001/RTLApp",props);

Configuring the Connection in the JDBC URL:

You can also configure the JDBC driver connection without creating a properties object, as shown in the following code:

Connection objConnection =
DriverManager.getConnection("jdbc:dsp@localhost:7001/RTLApp;logFile=c:\output.txt; ", <username>, <password>);

Accessing AquaLogic Data Services Platform JDBC Driver Using a Java Application

The steps to connect an application to AquaLogic Data Services Platform as a JDBC/SQL data source are substantially the same as connecting to any JDBC/SQL data source directly. In the database URL, use the AquaLogic Data Services Platform application name as the database identifier with "dsp" as the sub-protocol, in the form:

jdbc:dsp@<WLServerAddress>:<WLServerPort>/<DSPApplicationName> 

For example:

jdbc:dsp@localhost:7001/RTLApp

The name of the AquaLogic Data Services Platform JDBC driver class is:

com.bea.dsp.jdbc.driver.DSPJDBCDriver 

Obtaining a Connection

This section describes how to connect using the driver class in a client application.

A JDBC client application can connect to a deployed AquaLogic Data Services Platform application in the same way as it can connect to any database. It loads the AquaLogic Data Services Platform JDBC driver and then establishes a connection to AquaLogic Data Services Platform.

For example:

Properties props = new Properties();
props.put("user", "weblogic");
props.put("password", "weblogic");
props.put("application", "RTLApp");

// Load the driver
Class.forName("com.bea.dsp.jdbc.driver.DSPJDBCDriver");

//get the connection
Connection con = DriverManager.getConnection("jdbc:dsp@localhost/7001", props);

Using the preparedStatement Interface

The storedQueryWithParameters method explained in this section, demonstrates how to use the preparedStatement interface using a connection object (con). It is a valid connection obtained through the java.sql.Connection interface to the WebLogic Server, which hosts AquaLogic Data Services Platform.

In the method, CUSTOMER refers to CUSTOMER.ds.

public ResultSet storedQueryWithParameters() throws java.sql.SQLException

{
	PreparedStatement preStmt = 	
		con.prepareStatement (
		    "SELECT * FROM DataServices.MySchema.CUSTOMER WHERE CUSTOMER.LAST_NAME=?");
	preStmt.setString(1,"SMITH");
	ResultSet res = preStmt.executeQuery();
	return res;
}

In the SELECT query, DataServices is the catalog name and MySchema is the name of the schema folder.

Using the CallableStatement Interface

Once a connection is established to a server where AquaLogic Data Services Platform is deployed, you can call a data service function to obtain data by using a parameterized data service function call.

The following method demonstrates calling a stored query with a parameter (where con is a connection to the AquaLogic Data Services Platform server obtained through the java.sql.Connection interface). In the snippet, a stored query named dtaQuery is executed where custid is the parameter name and CUSTOMER2 is the parameter value.

public ResultSet storedQueryWithParameters(String paramName) 
	throws java.sql.SQLException {

	//prepare a stored query to execute
	con.prepareCall("call  DataServices.MySchema.getCustomerById(?) "); 
	call.setString(1, "CUSTOMER2");
	ResultSet resultSet = call.executeQuery();
	return resultSet;

con.prepareCall(" { call DataServices.MySchema.getCustomerById(?)}");

Accessing Data Service Functions from DbVisualizer

You can also use the AquaLogic Data Services Platform JDBC driver from client Java applications. This is a good way to learn how AquaLogic Data Services Platform exposes its artifacts through its JDBC/SQL driver.

This section describes how to connect to the driver from DBVisualizer. Figure 5-1 shows a sample application as viewed from DbVisualizer.

To use DBVisualizer, perform the following steps:

Once you have configured your ODBC-JDBC Bridge, you can use your application to access the data source presented by AquaLogic Data Services Platform. The usual reason for doing so is to connect AquaLogic Data Services Platform to the reporting tool that you need to use.

Connecting to AquaLogic Data Services Platform Client Using ODBC-JDBC Bridge from Non-Java Applications

You can use an ODBC-JDBC bridge to connect to AquaLogic Data Services Platform JDBC driver from non-Java applications. This section describes how to configure the OpenLink and EasySoft ODBC-JDBC bridges to connect non-Java applications to the AquaLogic Data Services Platform JDBC driver.

Using OpenLink ODBC-JDBC Bridge

The Openlink ODBC-JDBC driver can be used to interface with the AquaLogic Data Services Platform JDBC driver to query AquaLogic Data Services Platform applications with client applications, such as Crystal Reports 10, Business Objects 6.1, and MS Access 2003.

To use the OpenLink bridge, you will need to install the bridge and create a system DSN using the bridge. The following are the steps for these two tasks:

Using the EasySoft ODBC-JDBC Bridge

Applications can also communicate with the AquaLogic Data Services Platform JDBC Driver using EasySoft's ODBC-JDBC Gateway. The installation and use of the EasySoft Bridge is similar to the OpenLink bridge discussed in the previous section.

To use the EasySoft bridge, perform the following steps:

Accessing Data Services Data from Reporting Tools

This section describes how to configure the following reporting tools to use the AquaLogic Data Services Platform ODBC-JDBC driver:

Crystal Reports XI

This section describes the steps to connect Crystal Reports to the AquaLogic Data Services Platform JDBC driver along with information about standard configuration files that are available with AquaLogic Data Services Platform installation. It also describes the limitations of using Crystal Reports with AquaLogic Data Services Platform. It includes the following topics:

Crystal Reports Configuration File Support

Before you start using Crystal Reports with AquaLogic Data Services Platform, you must modify the default Crystal Reports configuration file, CRConfig.xml, to verify that Crystal Reports is able to access data services through JDBC.

The sample AquaLogic Data Services Platform CRConfig.xml file is provided with the standard installation of AquaLogic Data Services Platform. The sections of the configuration file that need to be modified contain the string "ALDSP". This file is located at:

<weblogic81>/LiquidData/resources/ReportingToolConfigs/CrystalReports

You cannot use the sample CRConfig.xml directly unless Crystal Reports is installed in the same directory as the path specified in the sample CRConfig.xml and AquaLogic Data Services Platform is installed in the same directory as the path specified in the sample CRConfig.xml. Therefore, you need to modify the default CRConfig.xml file available with Crystal Reports according to the sample CRConfig.xml file available with AquaLogic Data Services Platform.

Table 5-10 identifies some restrictions and specifies configuration changes you need to make to your Crystal Reports configuration file when accessing data using the AquaLogic Data Services Platform JDBC driver.

The following code snippet is a sample of the <JDBC> element with the JDBC driver URL and user name configuration settings:

<!-- ALDSP2.5 : The following <JDBC configuration is specific to ALDSP. -->
<JDBC>
<CacheRowSetSize>100</CacheRowSetSize>
<!-- ALDSP: Please replace <JDBCURL> with your URL. -->
<JDBCURL>jdbc:dsp@localhost:7001/YOUR_APP</JDBCURL>
<JDBCClassName>com.bea.dsp.jdbc.driver.DSPJDBCDriver</JDBCClassName>
<!-- ALDSP: Please replace <JDBCUserName> with your user name. -->
<JDBCUserName>ENTER_USER_NAME_HERE</JDBCUserName>
<JNDIURL></JNDIURL>
<JNDIConnectionFactory></JNDIConnectionFactory>
<JNDIInitContext>/</JNDIInitContext>
<JNDIUserName></JNDIUserName>
<GenericJDBCDriver>
	<Option>Yes</Option>
<DatabaseStructure>catalogs,schemas,tables</DatabaseStructure>
	<StoredProcType>Standard</StoredProcType>
	<LogonStyle>Standard</LogonStyle>
</GenericJDBCDriver>

</JDBC>

Limitations

Before you use Crystal Reports to access data services, ensure that you consider the following facts:

Connecting to Crystal Reports Using JDBC

To connect Crystal Reports to the JDBC driver and access data services to generate reports, perform the following steps:

Business Objects XI-Release 2 (ODBC)

Business Objects allows you to create a Universe and generate reports based on the specified Universe. In addition, you can execute pass-through SQL queries against Business Objects that do not need the creation of a Universe.

SELECT C.Name FROM DataServices.MySchema.CUSTOMER C
where
DataServices is the catalog name
MySchema is the schema name
CUSTOMER is the table name
Name is the column name
C is the table alias for CUSTOMER

This section provides information on configuring Business Objects to access the AquaLogic Data Services Platform JDBC driver. It includes the following topics:

Business Objects Configuration File Support

There are two BusinessObjects configuration files, odbc.prm and odbc.sbo, available with the standard BusinessObjects installation, which need to be replaced with the odbc.prm and odbc.sbo configuration files available with AquaLogic Data Services Platform, to access data services using BusinessObjects.

When you install BusinessObjects, these files are copied to the following location:

<Business Objects Home >\BusinessObjects Enterprise 11.5\
	win32_x86\dataAccess\connectionServer\odbc

With the AquaLogic Data Services Platform installation, these configuration files are available at the following location:

<weblogic81>/LiquidData/resources/ReportingToolConfigs/BusinessObjects

After installing AquaLogic Data Services Platform, save the original configuration files available with BusinessObjects at a different location and then replace them with the files packaged with AquaLogic Data Services Platform.

The BusinessObjects configuration files provided with AquaLogic Data Services Platform should be reviewed for comments. (Relevant comments contain the string "ALDSP").

Table 5-11 identifies some restrictions and specifies configuration changes you may want to make to your BusinessObjects configuration files when accessing data using the AquaLogic Data Services Platform JDBC driver.

Prerequisites and Limitations

Before you start using Business Objects to access data services, ensure that you consider the following facts:

For details about configuring OpenLink, refer to Using OpenLink ODBC-JDBC Bridge on page 5-26.

Generating a Business Objects Report

To generate a report, perform the following steps:

Hyperion-ODBC

Hyperion allows you to generate interactive and production reports using its Interactive Reporting Studio and Production Reporting Studio. This section describes the steps to access AquaLogic Data Services Platform data sources using an ODBC-JDBC bridge and generate interactive and production reports.

It includes the following topics:

Limitations

Before you start using Hyperion to access data services, ensure that you consider the following facts:

Using Hyperion Production Reporting Studio

To establish the connection and view results using the Hyperion Production Reporting Studio, perform the following steps:

Using Hyperion Interactive Reporting Studio

To generate reports using Interactive Reporting Studio, perform the following steps:

Microsoft Access 2003-ODBC

This section describes the procedure to connect Microsoft Access 2003 to AquaLogic Data Services Platform through an ODBC-JDBC bridge. It includes the following topics:

Limitations and Usage Notes

Generating Reports Using MS Access

To connect MS Access to the bridge, perform the following steps.

Microsoft Excel 2003-ODBC

This section describes the procedure for connecting Microsoft Excel 2003 to AquaLogic Data Services Platform through an ODJB-JDBC bridge using EasySoft.

To connect MS Excel to AquaLogic Data Services Platform, perform the following steps:

Limitations

When passing a generated SQL string to Excel, there are situations where Excel inserts single quotes around an alias, resulting in an exception from the AquaLogic Data Services Platform JDBC driver. Here is an example:

SELECT Sum(CREDIT.AMOUNT) AS 'Sum of AMOUNT' FROM Xtreme.CREDIT CREDIT

Although you can edit your query post-generation, another option is to install a patch from Microsoft which is designed to address the problem. The current URL for accessing information on this problem and patch is:

http://support.microsoft.com/kb/298955/en-us

Using the Query Plan Viewer Utility

You can review the plan for the execution of a SQL query or XQuery using a standalone Query Plan Viewer utility. The functionality is similar to that described in "Using Query Plan View" in the Testing Query Plan Functions and Viewing Query Plans chapter of Data Services Developer's Guide.

Installing Query Plan Utility Components

In the absence of a full installation of AquaLogic Data Services Platform, a specific set of component files must be installed to run the Query Plan Viewer utility. Default locations for these are assumed, but these default can be easily modified.

You can adjust the default location settings by editing the aldspqpv.cmd (Windows) or aldspqpv.sh (unix/linux) files.

Command Line Syntax

The command line syntax for the Query Plan Viewer utility is:

Java -classpath [path1/]wlclient.jar;[path2/]ldjdbc.jar;[path3/]aldspqpv.jar
	com.bea.dsp.client.ui.shell.Shell 
	[Server host name][Server port number][Application name]
			[User id][Password]

Here is an example using default settings:

Java -classpath ../../server/lib/wlclient.jar;../lib/ldjdbc.jar;../lib/aldspqpv.jar

com.bea.dsp.client.ui.shell.Shell

localhost 7001 RTLApp weblogic weblogic

Invoking the Query Plan Viewer Utility

Once your classpath is properly set you can invoke the Query Plan Viewer utility from the command-line using either:

On Windows systems you can easily associate the invocation command by associating a start menu option with aldspqpv.cmd.

Query Plan Viewer Login Dialog

Before you can view query plans, you need to log in to an AquaLogic Data Services Platform-enabled server (Figure 5-25).

In the dialog enter the following information:

Once you have logged into the Query Plan Viewer, all information but the password is saved on your system.

Entering an SQL Query or XQuery

Once you login to your server successfully, you are ready to enter a query by selecting whether to enter either an SQL query or an XQuery.

For details on entering an XQuery or SQL Query and working with the resulting query plan, see Creating Ad Hoc Queries in the Data Services Developer's Guide.

Additional Query Plan Viewer Utility Options

The utility offers several options, available from its File menu. The options are described in Table 5-13.