# Client Application Developer’s Guide

# Using SQL to Access Data Services

This chapter explains how to use SQL to access data services and how to set up and use the Oracle Data Service Integrator JDBC driver. The chapter covers the following topics:

- Introducing SQL Access to Data Services
- JDBC and SQL Support in Oracle Data Service Integrator
- Preparing to Use SQL to Access Data Services
- Accessing Data Services Using SQL From a Java Application
- Advanced Features
- Accessing Data Services Using SQL-Based Applications

## Introducing SQL Access to Data Services

Many reporting tools, such as Crystal Reports, Business Objects, Microsoft Access, and Microsoft Excel, can access data using SQL. SQL can also be useful in other contexts. Java applications, for example, can access data using SQL. You can also run ad hoc SQL queries using development tools such as Data Tools Platform (DTP) or SQL Explorer.

The Oracle Data Service Integrator JDBC driver enables JDBC and ODBC clients to access information from data services using SQL. The Oracle Data Service Integrator JDBC driver thereby increases the flexibility of the Oracle Data Service Integrator integration layer by enabling access from a range of database applications and reporting tools.

For the client, the Oracle Data Service Integrator integration layer appears as a relational database, with each data service operation comprising a table or a stored procedure. Internally, Oracle Data Service Integrator translates SQL queries into XQuery. Figure 5-1 illustrates SQL access to data using the Oracle Data Service Integrator JDBC driver.

As Figure 5-1 shows, source data can be consolidated, integrated, and transformed using Oracle Data Service Integrator data services. The source data itself can come from disparate sources throughout the enterprise, including relational databases and Web services, among others.

You can then, in turn, expose the data service operations as a relational data source accessible using SQL queries. This enables JDBC clients to access data consolidated through Oracle Data Service Integrator.

Note that the Oracle Data Service Integrator JDBC driver does impose the following constraints on data services:

- You can use the Oracle Data Service Integrator JDBC driver to access data only through data services that have a flat data shape, which means that the data service type cannot have nesting. SQL provides a traditional, two-dimensional approach to data access, as opposed to the multi-level, hierarchical approach defined by XML.
- The Oracle Data Service Integrator JDBC driver exposes non-parameterized flat data service operations as tables because SQL tables do not have parameters. Parameterized flat data services are exposed as SQL stored procedures.
Figure 5-1 SQL Access to Data Services

### Features of the Oracle Data Service Integrator JDBC Driver

The Oracle Data Service Integrator JDBC driver implements the java.sql.* interface in JDK 1.5x to provide access to an Oracle Data Service Integrator server through the JDBC interface. The driver has the following features:

- Supports SQL-92 SELECT statements
- Implements the JDBC 3.0 Application Programming Interface (API)
- Supports Oracle Data Service Integrator with JDK 1.5
- Supports both Java and ODBC bridge software clients
- Supports table parameters, an extension to SQL-92.
- Allows metadata access control at the JDBC driver level

Using the Oracle Data Service Integrator JDBC Driver, you can control the metadata accessed through SQL based on the access rights set at the JDBC driver level. This access control ensures that users can view only those tables and procedures that they are authorized to access.

However, to use this feature, the Oracle Data Service Integrator 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.

### Exploring Oracle Data Service Integrator and JDBC Artifacts

The Oracle Data Service Integrator views data retrieved from a database in the form of data sources and operations. Table 5-2 shows the equivalent terminology.

Table 5-2 Oracle Data Service Integrator and JDBC Driver Artifacts
Oracle Data Service Integrator | JDBC
--- | ---
Dataspace Project | JDBC connection parameter (Driver URL)
Operation with parameters | Stored procedure
Operation without parameters | Table or stored procedure

For example, if you have a project `SQLHowTo` and a data service `EmpInfo.ds` with an operation `getAll()`, you can use SQL Mapper to expose it as `JDBCdemo.empData.empinfo`. The JDBC driver would then see a table called `empinfo` with schema `empData` and catalog `JDBCdemo`.

## JDBC and SQL Support in Oracle Data Service Integrator

This section describes the JDBC and SQL support in the Oracle Data Service Integrator JDBC driver

### JDBC Support

The Oracle Data Service Integrator JDBC driver implements the following interfaces from the `java.sql` package as specified in JDK 1.5x:

`java.sql.Blob` `java.sql.CallableStatement` `java.sql.Connection` `java.sql.DatabaseMetaData` `java.sql.ParameterMetaData` | `java.sql.PreparedStatement` `java.sql.ResultSet` `java.sql.ResultSetMetaData` `java.sql.Statement`

The Oracle Data Service Integrator JDBC driver supports the following methods:

Table 5-3 Oracle Data Service Integrator JDBC Driver Methods
Interface | Supported Methods |
--- | --- | ---
`java.sql.Blob` | getBinaryStream getBytes length | position truncate
`java.sql.CallableStatement` | `clearParameters` executeQuery setAsciiStream setBigDecimal setBoolean setByte setBytes setCharacterStream setDate setDouble | setFloat setInt setLong setNull setObject setShort setString setTime setTimestamp
`java.sql.Connection` | `clearWarnings` close createStatement getAutoCommit getCatalog getHoldability getLogPrintWriter getMetaData getSchema getTransaction Isolation | getTypeMap getWarnings isClosed isReadOnly nativeSQL prepareCall prepareStatement setAutoCommit setCatalog setHoldability setReadOnly
`java.sql.DatabaseMetaData` | allProceduresAre Callable allTablesAre Selectable dataDefinitionCauses TransactionCommit dataDefinitionIgnoredInTransactions deletesAreDetected doesMaxRowSizeIncludeBlobs getAttributes getBestRowIdentifier getCatalogs getCatalogSeparator getCatalogTerm getColumnPrivileges getColumns getConnection getCrossReference getDatabaseMajor Version getDatabaseMinor Version | getDatabaseProduct Name getDatabaseProduct Version getDefault Transaction Isolation getDriverMajor Version getDriverMinor Version getDriverName getDriverVersion getExportedKeys getExtraName Characters getIdentifierQuote String getImportedKeys getIndexInfo getJDBCMajor Version getJDBCMinor Version
`java.sql.DatabaseMetaData` (Continued) | getMaxBinaryLiteral Length getMaxCatalogName Length getMaxCharLiteral Length getMaxColumnName Length getMaxColumnsIn GroupBy getMaxColumnsInIndex getMaxColumnsIn OrderBy getMaxColumnsInSelect getMaxColumnsInTable getMaxConnections getMaxCursorName Length getMaxIndexLength getMaxProcedureName Length getMaxRowSize getMaxSchemaName Length getMaxStatementLength getMaxStatements getMaxTableNameLength getMaxTablesInSelect getMaxUserNameLength getNumericFunctions getPrimaryKeys getProcedureColumns getProcedures getProcedureTerm getResultSet Holdability | getSchemas getSchemaTerm getSearchString Escape getSQLKeywords getSQLStateType getStringFunctions getSuperTables getSuperTypes getSystemFunctions getTablePrivileges getTables getTableTypes getTimeDateFunctions getTypeInfo getUDTs getURL getUserName getVersionColumns insertsAreDetected isCatalogAtStart isReadOnly locatorsUpdateCopy nullPlusNonNull IsNull nullsAreSortedAtEnd nullsAreSortedAt Start nullsAreSortedHigh nullsAreSortedLow othersDeletesAre Visible othersInsertsAre Visible othersUpdatesAre Visible
`java.sql.DatabaseMetaData` (Continued) | ownDeletesAreVisible ownInsertsAreVisible ownUpdatesAreVisible storesLowerCase Identifiers storesLowerCase QuotedIdentifiers storesMixedCase Identifiers storesMixedCaseQuotedIdentifiers storesUpperCase Identifiers storesUpperCaseQuotedIdentifiers supportsAlterTable WithAddColumn supportsAlterTable WithDropColumn supportsANSI92Entry LevelSQL supportsANSI92FullSQL supportsANSI92 IntermediateSQL supportsBatchUpdates supportsCatalogsIn DataManipulation supportsCatalogsIn IndexDefinitions supportsCatalogs InPrivilege Definitions supportsCatalogsIn ProcedureCalls supportsCatalogsIn TableDefinitions | supportsColumn Aliasing supportsConvert supportsCoreSQL Grammar supportsCorrelated Subqueries supportsData DefinitionAndData Manipulation Transactions supportsData Manipulation TransactionsOnly supportsDifferent TableCorrelation Names supportsExpressions InOrderBy supportsExtendedSQL Grammar supportsFullOuter Joins supportsGetGeneratedKeys supportsGroupBy supportsGroupBy BeyondSelect supportsGroupBy Unrelated supportsIntegrity Enhancement Facility supportsLikeEscape Clause supportsLimited OuterJoins
`java.sql.DatabaseMetaData` (Continued) | supportsMinimumSQL Grammar supportsMixedCase Identifiers supportsMixedCase QuotedIdentifiers supportsMultipleOpen Results supportsMultiple ResultSets supportsMultiple Transactions supportsNamed Parameters supportsNonNullable Columns supportsOpenCursors AcrossCommit supportsOpenCursors AcrossRollback supportsOpen StatementsAcross Commit supportsOpen StatementsAcross Rollback supportsOrderBy Unrelated supportsOuterJoins supportsPositioned Delete supportsPositioned Update supportsResultSet Concurrency supportsResultSet Holdability | supportsResultSet Type supportsSavepoints supportsSchemasIn DataManipulation supportsSchemasIn IndexDefinitions supportsSchemasIn Privilege Definitions supportsSchemasIn ProcedureCalls supportsSchemasIn TableDefinitions supportsSelectFor Update supportsStatement Pooling supportsStored Procedures supportsSubqueriesInComparisons supportsSubqueriesInExists supportsSubqueries InIns supportsSubqueriesInQuantifieds supportsTable CorrelationNames supportsTransaction IsolationLevel supports Transactions supportsUnion supportsUnionAll
`java.sql.DatabaseMetaData` (Continued) | updatesAreDetected usesLocalFilePer Table | usesLocalFiles
`java.sql.ParameterMetaData` | close getParameterClassName getParameterCount getParameterMode getParameterType | getParameterTypeName getPrecision getScale isNullable isSigned
`java.sql.PreparedStatement` | addBatch clearParameters close execute executeQuery getMetaData getParameterMetaData setAsciiStream setBigDecimal setBlob setBoolean setByte setBytes | setCharacterStream setDate setDouble setFloat setInt setLong setNull setObject setShort setString setTime setTimestamp
`java.sql.ResultSet` | clearWarnings close findColumn getAsciiStream getBigDecimal getBlob getBoolean getByte getBytes getCharacterStream getConcurrency getDate | getDouble getFetchDirection getFetchSize getFloat getInt getLong getMetaData getObject getRow getShort getStatement getString
`java.sql.ResultSet` (Continued) | getTime getTimestamp getType getWarnings next | setFetchDirection setFetchSize setMaxRows wasNull
`java.sql.ResultSetMetaData` | close getCatalogName getColumnClassName getColumnCount getColumnDisplaySize getColumnLabel getColumnName getColumnType getColumnTypeName getPrecision getScale | getSchemaName getTableName isAutoIncrement isCaseSensitive isCurrency isDefinitelyWritable isNullable isReadOnly isSearchable isSigned isWritable
`java.sql.Statement` | cancel clearWarnings close execute executeQuery getConnection getFetchDirection getFetchSize getGeneratedKeys getLogPrintWriter getMaxFieldSize getMaxRows getMoreResults getQueryTimeout getResultSet | getResultSet Concurrency getResultSet Holdability getResultSetType getUpdateCount getWarnings setCursorName setEscapeProcessing setFetchDirection setFetchSize setMaxFieldSize setMaxRows setQueryTimeout

### SQL Support

This section outlines SQL-92 support in the Oracle Data Service Integrator JDBC driver, and contains the following sections:

- Supported SQL Statements
- Supported SQL Functions
- Table Parameter Support

#### Supported SQL Statements

The Oracle Data Service Integrator JDBC driver provides support for the SQL-92 SELECT statement. The INSERT, UPDATE, and DELETE statements are not supported. Additionally, the driver does not support DDL (Data Definition Language) statements.

#### Supported SQL Functions

The Oracle Data Service Integrator JDBC driver supports functions that you can use to access and process data. This section describes the following supported Oracle Data Service Integrator SQL-92 query language functions:

- Numeric Functions
- String Functions
- Datetime Functions
- Aggregate Functions
- JDBC Metadata Search Patterns

##### Numeric Functions

The Oracle Data Service Integrator JDBC driver supports the numeric functions described in Table 5-4.

Table 5-4 Numeric Functions
Function | Signature | Comment
--- | --- | ---
ABS | `numeric ABS (numeric n)` | ABS returns the absolute value of n. If n is NULL, the return value is NULL.
CEIL | `numeric CEIL(numeric n)` | CEIL returns the smallest integer greater than or equal to n. If n is NULL, the return value is NULL.
FLOOR | `numeric FLOOR(numeric n)` | FLOOR returns largest integer equal to or less than n. If n is NULL, the return value is NULL.
ROUND | `numeric ROUND (numeric n)` | ROUND returns n rounded to 0 decimal places. If n is NULL, the return value is NULL.

##### String Functions

The Oracle Data Service Integrator JDBC driver supports the string functions described in Table 5-5.

Table 5-5 String Functions
Function | Signature | Comment
--- | --- | ---
CONCAT | `varchar CONCAT(varchar s1, varchar s2)` | CONCAT returns s1 concatenated with s2. If any argument is NULL, it is considered to be equivalent to the empty string.
Left | `varchar left (varchar s, numeric n)` | Left returns the left n characters of s.
LENGTH | `numeric LENGTH(varchar s)` | LENGTH returns the length of s. The function returns 0 if s is NULL.
LOWER | `varchar LOWER(varchar s)` | LOWER returns s, with all letters lowercase. If s is NULL, the function returns an empty string.
LPAD | `varchar lpad(varchar v, numeric n, varchar p)` | LPAD returns v, with n characters of an infinitely repeating p appended to the left.
LTRIM | `varchar LTRIM(varchar s)` | LTRIM trims leading blanks from s. If s is NULL, the function returns NULL.
Right | `varchar right (varchar s, numeric n)` | Right returns the right n characters of s.
RPAD | `varchar rpad(varchar v, numeric n, varchar p)` | RPAD returns v, with n characters of an infinitely repeating p appended to the right.
RTRIM | `varchar RTRIM(varchar s)` | RTRIM trims trailing blanks from s. If s is NULL, the function returns NULL.
SUBSTR | `varchar SUBSTR(varchar s, numeric start)` | SUBSTR with two arguments returns substring of s starting at start, inclusive. The first character in s is located at index 1. If s is NULL, the function returns an empty string.
TRIM | `varchar TRIM(varchar s)` | TRIM trims leading and trailing blanks from s. If s is NULL, TRIM returns NULL.
UPPER | `varchar UPPER(varchar s)` | UPPER returns s, with all letters uppercase. If s is NULL, UPPER returns the empty string.

##### Datetime Functions

The Oracle Data Service Integrator JDBC driver supports the datetime functions described in Table 5-6.

Table 5-6 Datetime Functions
Function | Signature | Comment
--- | --- | ---
DAYS | `numeric DAYS(T value)` | DAYS returns the days component from value. T can be a date, timestamp, or duration. If value is NULL, the result is NULL.
HOUR | `numeric HOUR(T value)` | HOUR returns the hour component from value. T can be one of time, timestamp, or duration. If value is NULL, the result is NULL.
MINUTE | `numeric MINUTE(T value)` | MINUTE returns the minute component from value. T can be a time, timestamp, or duration. If value is NULL, the result is NULL.
MONTH | `numeric MONTH(T value)` | MONTH returns the month component from value. T can be one of date, timestamp, or duration. If value is NULL, the result is NULL.
SECOND | `numeric SECOND(T value)` | SECOND returns the seconds component from value. T can be a time, timestamp, or duration. If value is NULL, the result is NULL.
YEAR | `numeric YEAR(T value)` | YEAR returns the year component from value. T can be one of date, timestamp, or duration. If value is NULL, the result is NULL.

##### Aggregate Functions

The Oracle Data Service Integrator JDBC driver supports the aggregation functions described in Table 5-7.

Table 5-7 Aggregate Functions
Function | Signature | Comment
--- | --- | ---
COUNT | `numeric COUNT(ROWS r)` | COUNT returns the number of rows in r.
AVG | `T AVG(T r)` | AVG returns the average values of all values in r. T can be a numeric or duration type.
SUM | `T SUM(T r)` | SUM returns the sum of all values in r. T can be a numeric or duration type.
MAX | `T MAX(T r)` | MAX returns a value from r that is greater than or equal to every other value in r. T can be a numeric, varchar, date, timestamp, or duration type.
MIN | `T MIN(T r)` | MIN returns a value from r that is less than or equal to every other value in r. T can be a numeric, varchar, date, timestamp, or duration type.

##### JDBC Metadata Search Patterns

The Oracle Data Service Integrator JDBC driver supports standard JDBC API search patterns, as shown in Table 5-8.

Table 5-8 JDBC Driver Metadata Search Patterns
Pattern | Purpose
--- | ---
“string” | Matches the identified string.
“ “ | Uses the default catalog/schema.
“%” | Wildcard; equivalent to * in regular expressions.
“_” | Matches a single character; equivalent to . (period) in regular expressions.
null | Wildcard; same as “%”

Note: | For more information about using the JDBC metadata API, refer to the Java documentation at:

    http://java.sun.com/j2se/1.5.0/docs/api/java/sql/
    DatabaseMetaData.html

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

Table 5-9 JDBC Driver Metadata Search Patterns
Pattern | Matching Example
--- | ---
“Oracle” | Matches the identified string, Oracle.
“abc%d” | Matches: `abc10d` `abcd` `abc_practically anything_d` But not: `abc10e` abc10def
abc%d_ | Matches: `abc10d` `abcd` `abc_practically anything_d` `abc10dg` But not: `abc10dgh` `abc10dgPattern`
“” and null | A call to: `DBDatabaseMetadata.getTables(““,null,”abc%”)` would return all tables starting with abc under catalog 1.

#### Table Parameter Support

The Oracle Data Service Integrator JDBC driver extends the standard SQL-92 parameter model by providing the ability to add table parameters to SQL FROM clauses. For example, in SQL you might encounter a situation where it is necessary to specify a list of parameters (highlighted) in a query.

In the following query, `JDBCdemo.empData.empinfo` is the entire customer table.

    SELECT emp.empid, emp.name, emp.salary
    FROM JDBCdemo.empData.empinfo emp
    WHERE emp.empid in (?, ?, ?, ...)
    or emp.name in (?, ?, ?, ...)

If the number of parameters can vary, you need to specify a query for each case. Table parameters provide an alternative by enabling you to specify that the query accept a list of values (the list can be of variable length). The following query uses table parameters (highlighted):

    SELECT emp.empid, emp.name, emp.salary
    FROM JDBCdemo.empData.empinfo emp
    WHERE emp.empid in (SELECT * FROM ? as emp(empid))
    or emp.name in (SELECT * FROM ? as emp(empname))

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

Note: | You can only pass a table with a single column as a table parameter. If you specify more than one column, an exception is thrown.

For more information about using table parameters, see Using Table Parameters.

### Additional Details and Limitations

When using the ALSDSP JDBC driver, each connection points to one Oracle Data Service Integrator dataspace. Table 5-10 notes the Oracle Data Service Integrator JDBC driver limitations that apply to SQL language features.

Table 5-10 Oracle Data Service Integrator JDBC Driver Limitations Applying to SQL Language Features
Feature | Comments | Example
--- | --- | ---
Assignment in select | Not supported. | `SELECT MYCOL = 2 FROM VTABLE WHERE COL4 IS NULL`
The CORRESPONDING BY construct with the set-Operations (UNION, INTERSECT and EXCEPT) | The SQL-92 specified default column ordering in the set operations is supported. Both the table-expressions (the operands of the set-operator) must conform to the same relational schema. | `(SELECT NAME, CITY FROM CUSTOMER1) UNION CORRESPONDING BY (CITY, NAME) (SELECT CITY, NAME FROM CUSTOMER2)` The supported query is: `(SELECT NAME, CITY FROM CUSTOMER1) UNION (SELECT NAME, CITY FROM CUSTOMER2)`

## Preparing to Use SQL to Access Data Services

This section describes the tasks you need to perform prior to using SQL to access data services, and contains the following topics:

- Publishing Data Service Operations
- Configuring the Oracle Data Service Integrator JDBC Driver

### Publishing Data Service Operations

To access data services using SQL, you first need to publish the data service operations as SQL objects within the Oracle Data Service Integrator-enabled project. These SQL objects include tables, stored procedures, and functions.

Note: | SQL objects published through Oracle Data Service Integrator need to be enclosed in double quotes when used in an SQL query, if the object name contains a hyphen. For example `SELECT “col-name” FROM “table-name”`.

To publish data service operations as SQL Objects, perform the following steps:

1. Publish the data service operations to a schema that models the operations as SQL objects.
2. Build and deploy the Oracle Data Service Integrator dataspace.

After the dataspace is deployed, the newly created SQL objects are available to the dataspace through the ALSDSP JDBC driver.

### Configuring the Oracle Data Service Integrator JDBC Driver

The Oracle Data Service Integrator JDBC driver is located in the `ldjdbc.jar` file, which is available in the `<`ALDSP_HOME`>/lib` directory after you install Oracle Data Service Integrator. To use the Oracle Data Service Integrator JDBC driver on a client computer, you need to configure the classpath, class name, and the URL for the JDBC driver.

Note: | You will need gateway software to enable connectivity between the JDBC driver and DSP to configure the JDBC driver. For more information, refer to the section entitled Accessing Data Services Using SQL Explorer.

To configure the driver on a client computer, perform the following steps:

1. Copy the `ldjdbc.jar` and `weblogic.jar` (in the `<ALDSP_HOME>/lib` and `<WL_HOME>/server/lib` directories respectively) to the client computer.
2. Add `ldjdbc.jar` and `weblogic.jar` to the classpath on the client computer.
3. Set the appropriate supporting path by adding `%JAVA_HOME%/jre/bin` to the path on the client computer.
4. To set the JDBC driver, do the following:

1. Set the driver class name to the following:

    com.bea.dsp.jdbc.driver.DSPJDBCDriver

2. Set the driver URL to the following:

    jdbc:dsp@<DSPServerName>:<ALDSPServerPortNumber>/<DataspaceName>

For example the driver URL could be:

    jdbc:dsp@localhost:7001/Test_DataSpace

Alternatively, set the default catalog name and schema name in the URL while connecting to the JDBC driver using the following syntax:

    	jdbc:dsp@<DSPServerName>:<ALDSPServerPortNumber>/<DataspaceName>/
    	<catalogname>/<schemaname>

Note: | If you do not specify the CatalogName and SchemaName in the JDBC driver URL, then you need to specify the three-part name for all queries. For example:

    	select * from <catalogname>.<schemaname>.CUSTOMER

3. Optionally, enable debugging using the `logFile` property. To log debugging information, use the following JDBC driver URL syntax:

    jdbc:dsp@localhost:7001/test;logFile=c:\output.txt

In this case, the log file is created in the `c:\output.txt` file. You can also specify the debug property separately instead of specifying it with the URL.

Note: | If you build an SQL query using a reporting tool, the unqualified JDBC function name is used in the generated SQL. Consequently, to enable application developers to invoke an database function, the default catalog and schema name must be defined in the JDBC connection URL. It is also a requirement that any JDBC connection utilize those functions available from a single SQL catalog:schema pair location.

The following is an example URL defining a default catalog and schema for a JDBC connection:

    jdbc:dsp@localhost:7001/myDataspace/myCatalog/mySchema

Note: | You can specify the default schema and catalog name using the default_catalog and default_schema property fields in case you do not specify it in the properties. If `dataspace`, `default_catalog`, or `default_schema` appears in both the connection properties and the URL, the variable in the URL takes precedence.

5. To configure the connection object for the Oracle Data Service Integrator dataspace, you can specify the configuration parameters as a Properties object or as a part of the JDBC URL.

For more information, see Configuring the Connection Using the Properties Object or Configuring the Connection in the JDBC URL respectively.

## Accessing Data Services Using SQL From a Java Application

You can have a Java application access information from data services using SQL through the Oracle Data Service Integrator JDBC driver.

To access the data from a Java application, perform the following steps:

1. Obtain a connection to the Oracle Data Service Integrator dataspace.

For more information, see Obtaining a Connection.

2. Specify and submit an SQL query to the JDBC datasource.

You can use either the PreparedStatement or CallableStatement interface to specify and submit the query to the datasource. For more information, see Using the PreparedStatement Interface and Using the CallableStatement Interface respectively.

#### Obtaining a Connection

A JDBC client application can connect to a deployed Oracle Data Service Integrator dataspace by loading the Oracle Data Service Integrator JDBC driver and then establishing a connection to the dataspace. In the database URL, use the Oracle Data Service Integrator dataspace name as the database identifier with “dsp” as the sub-protocol, using the following form:

    jdbc:dsp@<WLServerAddress>:<WLServerPort>/<DataspaceName>
    (/default catalog/default schema;
    param(=value1; param2=value2;)?

For example:

    jdbc:dsp@localhost:7001/Test_DataSpace

The name of the Oracle Data Service Integrator JDBC driver class is:

    com.bea.dsp.jdbc.driver.DSPJDBCDriver

##### Configuring the Connection Using the Properties Object

You can establish a connection to an Oracle Data Service Integrator dataspace using the Properties object as follows:

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

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

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

Alternatively, you can specify the Oracle Data Service Integrator dataspace name, `TestProjectDataSpace`, in the connection object itself, as shown in the following segment:

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

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

    // Get the connection
    Connection objConnection = DriverManager.getConnection(
    	"jdbc:dsp@localhost:7001/TestProjectDataSpace",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 segment:

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

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

#### Using the PreparedStatement Interface

You can use the `preparedQueryWithParameters` method to specify a query to the JDBC datasource using the connection object (`conn`), obtained earlier. The connection object is obtained through the `java.sql.Connection` interface to the Oracle WebLogic Server, which hosts Oracle Data Service Integrator.

Note: | You can create a preparedStatement for a non-parametrized query as well. The statement is used in the same manner.

In this query, the data service function `getAll()` in the data service `EmpInfo.ds` under the `SQLHowTo` project is mapped using SQL Mapper to `JDBCdemo.empData.empinfo`.

    public ResultSet preparedQueryWithParameters(Connection conn) throws
    			java.sql.SQLException {
    	PreparedStatement ps = conn.prepareStatement("SELECT *
    		FROM JDBCdemo.empData.empinfo emp WHERE emp.salary >= ?");
    	ps.setInt(1,275000);
    	ResultSet rs = ps.executeQuery();
    	return rs;
    }

In the SELECT query, `JDBCdemo` is the catalog name, `empData` is the schema name, and `empinfo` is the table name.

Note: | For more information about how to map data service operations to SQL objects, refer to Publishing Data Service Operations on page 5-20.

#### Using the CallableStatement Interface

After you establish a connection to a server where Oracle Data Service Integrator is deployed, you can call a data service operation to obtain data using a parameterized data service operation call.

The following example shows how to call a stored query with a parameter (where `conn` is a connection to the Oracle Data Service Integrator server obtained through the `java.sql.Connection` interface). In the segment, a stored query named `getBySalary` is called passing a parameter with a value of `275000`.

    public ResultSet storedQueryWithParameters(Connection conn) throws
    			java.sql.SQLException {
    	CallableStatement ps =
    		conn.prepareCall("call JDBCdemo.empData.getBySalary(?)");
    	ps.setInt(1,275000);
    	ResultSet rs = ps.executeQuery();
    	return rs;
    }

Note: | You can also use the `prepareCall` method as follows:

    conn.prepareCall("{call JDBCdemo.empData.getBySalary(?)}");

## Advanced Features

This section describes advances features and uses of the Oracle Data Service Integrator JDBC driver and contains the following sections

- Using Table Parameters
- Accessing Custom Database Functions Using JDBC

### Using Table Parameters

This section describes how to use the Oracle Data Service Integrator JDBC driver to pass table parameters to data services.

#### When to Use Table Parameters

Consider the case in which a data service contains consolidated information of all employee contact information. A manager further has a consolidated list of all government employees in European countries. The goal is to use a data service to obtain contact information for that specific subset of employees.

The scenario is a common one involving the need for a join between the manager’s employee list and contact information. However, if the manager’s employee list is long and not already available through a database, it is convenient to pass a list of values as if it were a column in a table.

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

    ? as emp(empid)

provides a virtual table value (emp) and a virtual column name (empid).

Note: | You should alias all table parameters since the default table/column names are undefined and may produce unexpected name conflicts.

#### Setting Table Parameters Using JDBC

The Oracle Data Service Integrator JDBC driver passes table parameters to data services through its TableParameter class. The class (shown in its entirety in Listing 5-1) represents an entire table parameter and the rows it represents.

Listing 5-1 Table Parameter Interface

    public class TableParameter implements Serializable
        /**
         * Constructor
         *
         * @schema      the schema for the table parameter
         */
        public TableParameter(ValueType[] schema);

        /**
         * Creates a new a row and adds it to the list of rows in this
         * table parameter
         */
    public Row createRow();
        /**
         * Gets the rows of this table parameter
         */
        public List/*Row*/ getRows();
        /**
         * Gets the schema of this table parameter
         */

        public ValueType[] getSchema();
        /**
         * Represents a row in the table parameter
         */
        public class Row implements Serializable {
            /**
             * Sets a value to a particular column
             * @param colIdx    the index of the column to set, always 1
             * @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 instance and populate the instance with data:

1. Instantiate a TableParameter with the schema of your table.

Note: | At present only one column is supported for table parameters.

2. Call the `createRow( )` method on TableParameter to create a new Row object representing a tuple in the table.
3. Use the `setObject(1,val)` call to set the column on the Row object.
4. Call `createRow( )` again to create as many rows as the table requires.

##### JDBC Usage

You can pass table parameters through JDBC just like any other parameter, using the PreparedStatement interface.

To pass table parameters using the PreparedStatement interface:

1. Create a PreparedStatement with the query, as shown in the following:

    PreparedStatement ps = c.prepareStatement("SELECT * " +
       "FROM ? as EMP(empid), JDBCdemo.empData.contact CONTACT " +
       "WHERE CONTACT.empid = EMP.empid AND CONTACT.zip=?");

2. Set the value of the normal parameter on the PreparedStatement, as shown in the following:

    ps.setObject(2,"98765");

3. Create a table parameter of a specific type, as shown in the following:

    ValueType[] tableType = new ValueType[1];
    tableType[0] = ValueType.REPEATING_INTEGER_TYPE;
    TableParameter p = new TableParameter(tableType);

4. Fill the table parameter by reading rows from a file or other input stream, as shown in the following:

    String empidlist = FileUtils.slurpFile("empidlist.txt");
    StringTokenizer empids = new StringTokenizer(empidlist,"\n");
    while(empids.hasMoreTokens()) {
       TableParameter.Row r = p.createRow();
       r.setObject(1,new Integer(empids.nextToken()));
    }
    ps.setObject(1,p);

5. Set the table parameter as a property of the prepared statement, as shown in the

    ps.setObject(1,p);

##### Table Parameter Example

The following simplified example illustrates the use of a table parameter. The supporting JDBC code is shown in Listing 5-2:

Listing 5-2 JDBC Code Supporting Table Parameter Example

    import java.sql.DriverManager;
    import java.sql.ResultSet;
    import java.sql.PreparedStatement;
    import java.sql.Connection;
    import java.sql.Driver;
    import java.util.Properties;
    import java.util.StringTokenizer;

    import com.bea.ld.sql.types.ValueType;
    import com.bea.ld.sql.data.TableParameter;

    import weblogic.xml.query.util.FileUtils;

    public class TableParameterTest {

    	/**
    	 * Establish a connection to the Oracle Data Service Integrator
    	 * JDBC driver and return it
    	 */
    	protected static Connection connect() throws Exception {
    		// Attempt to locate the JDBC driver
    		Class.forName("com.bea.dsp.jdbc.driver.DSPJDBCDriver");
    		Driver driver =
    			DriverManager.getDriver("jdbc:dsp@localhost:7001");
    		if(driver == null)
    			throw new IllegalStateException("Unable to find driver.");

    		//Set the connection properties to the driver
    		Properties props = new Properties();
    		props.setProperty("user", "weblogic");
    		props.setProperty("password", "weblogic");
    		props.setProperty("application", "SQLHowTo");

    		// Try to connect to the driver using the properties set above
    		Connection c =
    			driver.connect("jdbc:dsp@localhost:7001", props);
    		if(c == null)
    			throw new IllegalStateException("Unable to establish a
    					connection.");
    		return c;
    	}

    	/**
    	 * Prints a result set to system out
    	 * @param rs 	the result set to print
    	 */
    	protected static void printResultSet(ResultSet rs)
    			throws Exception{
    		while(rs.next()) {
    			for(int i = 1; i < rs.getMetaData().getColumnCount()+1; i++) {
    				rs.getObject(i);
    				System.err.print(rs.getObject(i) + "   ");
    			}
    			System.err.println();
    		}
    		rs.close();
    	}

    	public static void main(String args[]) throws Exception {
    		Connection c = connect();

    		// Create the query
    		PreparedStatement ps = c.prepareStatement("SELECT * " +
    			"FROM ? as EMP(empid), JDBCdemo.empData.contact CONTACT " +
    			"WHERE CONTACT.empid = EMP.empid AND CONTACT.zip=?");

    		// Set the normal parameter
    		ps.setObject(2,"98765");

    		// Create the table parameter
    		ValueType[] tableType = new ValueType[1];
    		tableType[0] = ValueType.REPEATING_INTEGER_TYPE;
    		TableParameter p = new TableParameter(tableType);

    		// Create the rows of the table parameter from values in a file
    		String empidlist = FileUtils.slurpFile("empidlist.txt");
    		StringTokenizer empids = new StringTokenizer(empidlist,"\n");
    		while(empids.hasMoreTokens()) {
    			TableParameter.Row r = p.createRow();
    			r.setObject(1,new Integer(empids.nextToken()));
    		}
    		ps.setObject(1,p);
    		// Run the query and print the results
    		ResultSet rs = ps.executeQuery();
    		printResultSet(rs);
    	}
    }

##### Table Parameter ValueTypes

Table 5-11 lists the table parameter ValueTypes supported by the Oracle Data Service Integrator JDBC driver.

Table 5-11 TableParameter ValueTypes
Type Name | Type Value | Java Type
--- | --- | ---
ValueType.REPEATING_SMALLINT | 16 bit signed integer | Short
ValueType.REPEATING_INTEGER | 32 bit signed integer | Integer
ValueType.REPEATING_BIGINT | 64 bit signed integer | Long
ValueType.REPEATING_REAL | 32 bit floating point | Float
ValueType.REPEATING_DOUBLE | 64 bit floating point | Double
ValueType.REPEATING_DECIMAL | decimal | BigDecimal
ValueType.REPEATING_VARCHAR | string | String
ValueType.REPEATING_DATE | date | java.sql.Date
ValueType.REPEATING_TIME | time | java.sql.Time
ValueType.REPEATING_TIMESTAMP | datetime | java.sql.Timestamp
ValueType.REPEATING_BLOB | byte array | char[]
ValueType.REPEATING_BOOLEAN | Boolean | Boolean
ValueType.REPEATING_YMINTERVAL | year month interval | weblogic.xml.query.datetime.YearMonthDuration
ValueType.REPEATING_DTINTERVAL | day time interval | weblogic.xml.query.datetime.DayTimeDuration
ValueType.REPEATING_INTERVAL | both year month & day time interval | weblogic.xml.query.datetime.Duration

### Accessing Custom Database Functions Using JDBC

Several relational database management systems provide mechanisms to extend the library of built-in, standard SQL functions with user-defined, custom functions, defined using another language, such as PL/SQL, that can be directly embedded in SQL statements.

You can make these built-in or custom functions in your database available through data services by registering the function with Oracle Data Service Integrator through a library. After registering the functions, you can use them in SQL statements submitted to the Oracle Data Service Integrator JDBC driver. The following example shows the use of the custom function `myLower()` in a SELECT statement:

    select * from CUSTOMER where ? = myLower( LAST_NAME )

Note that the following conditions must be met to enable Oracle Data Service Integrator to use database-specific or user-defined functions:

- The function must accept at least one argument using the standard syntax `myFunction(arg1, arg2)`. This argument must be from the data source for which the function is defined. Remaining arguments, however, may be constants or arguments from another type of data service, such as a web service.
- Oracle Data Service Integrator does not support functions of the form `TRIM( TRAILING ' ' FROM $column)` as custom database functions.
- Oracle Data Service Integrator does not support special columns such as `SYSDATE`, `ROWNUM`, or similar columns as parameters to custom database functions.
- You must explicitly expose Oracle Data Service Integrator artifacts in the SQL Map for the dataspace.

## Accessing Data Services Using SQL-Based Applications

You can access data services using both SQL-based applications and applications that connect to the Oracle Data Service Integrator JDBC driver through an ODBC-JDBC bridge. This section describes how to configure SQL and ODBC-based applications to access data services, and contains the following sections:

- Accessing Data Services Using SQL Explorer
- Connecting to the Oracle Data Service Integrator Client Using OpenLink ODBC-JDBC Bridge
- Using OpenLink with Reporting Tools

Note: | You can also use the Oracle Data Service Integrator JDBC driver with the Eclipse Data Tools Platform (DTP) plug-in. To use DTP, download the DTP software using the following link:

Note: | http://wiki.eclipse.org/index.php/Getting_Started_with_DTP

### Accessing Data Services Using SQL Explorer

You can use the Oracle Data Service Integrator JDBC driver with Eclipse SQL Explorer to access data services. This section describes how to configure SQL Explorer to use the Oracle Data Service Integrator JDBC driver and how to specify the connection settings. This section assumes that you have already defined your web server and dataspace project in Eclipse.

Note: | SQL Explorer does not support stored procedures and, therefore, data services exposed as stored procedures through the Oracle Data Service Integrator JDBC driver do not appear in SQL Explorer. For more information, refer to the Eclipse SQL Explorer web site at:

    http://eclipsesql.sourceforge.net

To use SQL Explorer, perform the following steps:

1. Download the SQL Explorer software from the following link:

    http://sourceforge.net/projects/eclipsesql

2. After you have downloaded the SQL Explorer zip file, extract two folders, Features and Plug-Ins.
3. Copy the SQL Explorer files in the Features folder into the Eclipse Features folder.
4. Copy the SQL Explorer files in the Plug-ins folder into the Eclipse Plug-ins folder.
5. Launch Eclipse in the Oracle Data Service Integrator Perspective. Start the web server within Eclipse and open the dataspace (project).
6. Choose Window Preferences, expand SQL Explorer in the left margin, and select JDBC Drivers. Click Add and type the driver name, URL, and class name, as follows:

- Type a name for the JDBC Driver, such as `odsi_jdbc_driver`.
- Set the example URL to:

    jdbc:dsp@<DSPServerName>:<DSPServerPortNumber>/<DSPDataspaceName>

Figure 5-12 Create New Driver Dialog

7. Click the Extra Class Path tab and then click Add. Enter the paths for two JAR files, as follows:

- <ALDSP_HOME>/lib/`ldjdbc.jar`
- <BEA_HOME>/wlserver_10.3/server/lib/weblogic.jar

Click OK.

8. Set the Driver Class Name to the following:

    com.bea.dsp.jdbc.driver.DSPJDBCDriver

Click OK twice.

9. Open the SQL Explorer perspective by choosing Open Perspective Other SQL Explorer. Click OK.
10. Click on the far left icon under Connections to create a new connection. Enter a name for the new connection and choose Oracle Data Service Integrator JDBC Driver from the drop-down list. Enter the URL for the JDBC Driver, then enter the user name and password, and click OK
Figure 5-13 Create New Connection Profile Dialog

11. Right-click the new JDBC Driver connection and choose Edit to ensure that you have the correct connect profile for the JDBC driver.
12. Right-click on JDBC Driver connection and choose Connect. Verify that the connection profile is correct in the Connection dialog, then click OK.
Figure 5-14 Connection Dialog

The data displays in the Database Structure and Database Detail window.

13. If the JDBC Driver window is not open, choose Window Show View Other SQL Explorer Database Structure OK to display the client data.
14. If you get an exception message, add the catalog name and schema name to the JDBC Connection URL, as follows:

    		jdbc:dsp@<DSPServerName>:<DSPServerPortNumber>/<DSPDataspaceName>
    /<Your_CatalogName>/<Your_SchemaName>

Figure 5-15 Change Connection Profile Dialog

### Connecting to the Oracle Data Service Integrator Client Using OpenLink ODBC-JDBC Bridge

You can use an ODBC-JDBC bridge to connect to the Oracle Data Service Integrator JDBC driver from non-Java applications. This section describes how to configure the OpenLink ODBC-JDBC bridge to connect to the Oracle Data Service Integrator JDBC driver.

You can use the Openlink ODBC-JDBC driver to interface with the Oracle Data Service Integrator JDBC driver to query Oracle Data Service Integrator dataspaces with client applications such as Crystal Reports, Business Objects XI, Microsoft Access 2003, and Microsoft Excel 2003.

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

1. Install the OpenLink ODBC-JDBC bridge (called ODBC-JDBC-Lite).

For information on installing OpenLink ODBC-JDBC-Lite, refer to the OpenLink Software download page for the Single-Tier (Lite Edition) ODBC to JDBC Bridge Driver (Release 6.0) for use on Windows systems. The page can be accessed at:

http://download.openlinksw.com/download/login.vsp?pform=2&pfam=1&pcat=1&prod=odbc-jdbc-bridge-st&os=i686-generic-win-32&os2=i686-generic-win-32&release-dbms=6.0-jdbc

WARNING: | For Windows platforms, be sure to save the value of your CLASSPATH before installation.

2. Create a system DSN and configure it for your Oracle Data Service Integrator dataspace. Ensure that the CLASSPATH contains the following JAR files required by ODBC-JDBC-Lite, `ldjdbc.jar` and `weblogic.jar`. A typical CLASSPATH might look as follows:

    <ALDSP_HOME>/lib/ldjdbc.jar;
    	<WL_HOME>/server/lib/weblogic.jar;

3. Update the system path to include the `jvm.dll` file, which should be in the `<ALDSP_HOME>/%javaroot%/jre/bin/server` directory.

Note: | Do not include the file name jvm.dll in the system path.

1. Launch Control Panel Administrative Tools Data Sources (ODBC). The ODBC Data Source Administrator window displays.
2. Click the System DSN tab and then click Add.
3. Select JDBC Lite for JDK 1.5 (6.0) and click Finish.
4. Specify the DSN name, for example, openlink-odsi.
5. Click Next. Then on the next screen, enter the following next to the JDBC driver:

    com.bea.dsp.jdbc.driver.DSPJDBCDriver.

6. Type the following in the URL string field:

    jdbc:dsp@<machine_name>:<port>/<dataspace_name>/<catalog_name>/
    <schema_name>

7. Select the “Connect now to verify that all settings are correct” checkbox. Type the login ID and password to connect to the Oracle Data Service Integrator Oracle WebLogic Server, and click Next.
8. Select any additional parameters, and click Next.
9. Click Next and specify the connection compatibility parameters.
10. Click Next, and then click Test Data Source. Verify that the setup was successful.
11. Click Finish.

### Using OpenLink with Reporting Tools

This section describes how to configure and use the following reporting tools with the Oracle Data Service Integrator ODBC-JDBC driver:

- Crystal Reports XI
- Business Objects XI-Release 2 (ODBC)
- Microsoft Access 2003-ODBC
- Microsoft Excel 2003-ODBC

Note: | Some reporting tools issue multiple SQL statement executions to emulate a scrollable cursor if the ODBC-JDBC bridge does not implement one. Some drivers do not implement a scrollable cursor, so the reporting tool issues multiple SQL statements, which can affect performance.

Note: | Support for third party reporting tools is deprecated in Oracle Data Service Integrator 10gR3.

#### Crystal Reports XI

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

- Configuring Crystal Reports
- Limitations
- Connecting to Crystal Reports Using JDBC

##### Configuring Crystal Reports

Before you start using Crystal Reports with Oracle Data Service Integrator, you must modify the default Crystal Reports configuration file, `CRConfig.xml` to ensure that Crystal Reports is able to access data services through JDBC. The configuration file is located in the following directory:

    <Drive>\Program Files\Business Objects\Common\3.5\java

A modified sample `CRConfig.xml` file and an associated CR_Readme.txt file are available at:

    <ALDSP_HOME>/samples/ReportingTools/config/Crystal Reports

The CR_Readme.txt file contains instructions on how to apply DSP-specific rules in an existing Crystal Reports installation and how to modify the `CRConfig.xml` file.

##### Limitations

Before you use Crystal Reports to access data services, consider the following information:

- Crystal Reports is not able to invoke stored procedures with parameters for any Oracle Data Service Integrator XQuery function that has the parameters defined using a built-in data type keyword, such as $integer. To resolve this issue, change the parameter name in the XQuery function.
- Crystal Reports supports all XML types that are supported by Oracle Data Service Integrator JDBC driver except the following:

- `yearMonthDuration`
- `dayTimeDuration`

- Certain JDBC functions used by Crystal Reports are not supported by Oracle Data Service Integrator. Refer to the Supported SQL Functions on page 5-12 for a list of supported functions.

##### Connecting to Crystal Reports Using JDBC

This section assumes that you have successfully completed the following:

- Installed Crystal Reports 11 using the Update Manager in Eclipse. For more information, refer to the instructions at the following location:

http://diamond.businessobjects.com/node/432

- Modified the `CRConfig.xml` file (see Configuring Crystal Reports),
- Built your Crystal Reports Project
- Started your Weblogic92 server running on Eclipse.

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

1. Using the Crystal Reports perspective in Eclipse, create a new connection to the JDBC driver by clicking on the New Connection icon in the menu bar of the Database Explorer window.
2. Specify the Connection parameters for the JDBC interface of Crystal Reports. The New Connection window displays.
3. Check the box under Connection Identification if you want to use the default naming convention. Otherwise, leave the checkbox and associated field blank.
4. Select the driver from the drop-down list next to the JDBC Driver, or select Database Manager from the drop-down window in the left margin.
5. Type the JDBC Driver Class name, the Class Location, and the URL for the JDBC Driver.
6. Under User Information, type your user ID and password, then click Test Connection. If the connection is successful, click Next. The Filter dialog displays.
7. Select a predicate and type a value. Alternatively, indicate whether to include or exclude a selection of items.
8. If you do not want a filter, check the Disable Filter box and click Finish.
9. The data is displayed in the Database Explorer.

#### Business Objects XI-Release 2 (ODBC)

Business Objects enables 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.

This section provides information on configuring Business Objects to access the Oracle Data Service Integrator JDBC driver. It includes the following topics:

- Configuring Business Objects
- Prerequisites and Limitations
- Generating a Business Objects Report

##### Configuring Business Objects

There are two Business Objects configuration files, `odbc.prm` and `odbc.sbo`, available with the standard Business Objects installation.

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

    	<Business_Objects_Home>/BusinessObjects Enterprise 11.5/
    	win32_x86/dataAccess/connectionServer/odbc

An Oracle Data Service Integrator installation includes samples of these configuration files along with an associated BO_Readme.txt file, available at the following location:

    	<ALDSP_HOME>/samples/ReportingTools/config/BusinessObjects

You can edit the Business Objects configuration files according to the instructions in the readme file.

Tip: | When first getting started using Business Objects with Oracle Data Service Integrator, use the included configuration file to verify the ability to access data services through JDBC.

Table 5-16 identifies some restrictions and specifies configuration changes you may want to make to your Business Objects configuration files when accessing data using the Oracle Data Service Integrator JDBC driver.

Table 5-16 Business Objects Configuration File Support for Oracle Data Service Integrator
Configuration File | Discussion
`ODBC.PRM` | Specifically supported: `EXT_JOIN` (outer join) `QUALIFIER` (table prefix) `DISTINCT` `ANSI_92` Not supported: `INTERSECT` `INTERSECT_IN_SUBQUERY` `MINUS` `MINUS_IN_SUBQUERY`
`ODBC.SBO` | Set Transactional Available option to YES

##### Prerequisites and Limitations

Before you start using Business Objects to access data services, consider the following information:

- To generate a report using Business Objects, all the tables need to be from the same catalog. (This requirement implies that in the Oracle Data Service Integrator IDE, the same project should be used to publish data services for SQL use.) This catalog needs to be defined as the default catalog when connecting to Oracle Data Service Integrator. However, the catalog can contain an arbitrary number of schemas.
- Avoid publishing the same table name in multiple schemas. Doing so will cause the same column name to appear multiple times (as many times as the table is published) in the Business Objects’ Table Browser user interface.
- Business Objects does not generate qualified names for tables. When you connect Business Objects to Oracle Data Service Integrator using OpenLink, you need to specify the default catalog and schema names in the Oracle Data Service Integrator JDBC driver URL when configuring OpenLink. If you do not specify the default catalog and schema names, then an error similar to the following is generated:

    com.bea.ld.sql.compiler.ast.SQLTypeCheckingException: Invalid table reference. No such table null.Xtreme.CUSTOMER found.

    at

    com.bea.ld.sql.compiler.ast.TableRSN.inferSchemaAndCheck(Lcom/bea/ld/

    sql/context/SQLContext;Lcom/bea/ld/sql/types/SchemaIndex;)V(TableRSN.java:149)

For details about configuring OpenLink, Connecting to the Oracle Data Service Integrator Client Using OpenLink ODBC-JDBC Bridge on page 5-37. Business Objects supports all XML types that are supported by Oracle Data Service Integrator JDBC driver except the following:

- `yearMonthDuration`
- `dayTimeDuration`
- xs:boolean
- xs:hexBinary

##### Generating a Business Objects Report

To generate a report, perform the following steps:

1. Create a Universe by Running the Business Objects Designer application and clicking Begin in the Quick Design Wizard window.
2. Type a name for your Universe and choose the appropriate DSN connection from the drop-down list.
3. If the DSN you want to use does not appear in the list (which happens if you are using the application for the first time), then click New to create a new connection.
4. In the Define a New Connection wizard, select Generic ODBC3 Datasource as the middleware. Click Next.
5. Specify the user name and password to connect to Oracle WebLogic Server and select openlink-odsi as the DSN.

For details about configuring the OpenLink ODBC-JDBC bridge, refer to Connecting to the Oracle Data Service Integrator Client Using OpenLink ODBC-JDBC Bridge on page 5-37.

6. Click Next and test if the connection with the server is successful. Follow the instructions in the wizard to complete creating the connection.
7. After creating the connection, specify this connection in the Universe and click OK. A new blank panel is displayed.
8. Choose Table from the Insert menu. After the list of tables is shown in the Table Browser, double click the tables that you want to put in the Universe.
9. Save the Universe and exit.
10. To create a new report, run the Desktop Intelligence application. Click New to open the New Report Wizard.
11. Select the report layout and report data and click Begin. Select a universe and click Next. Highlight the universe you want to use.

If you want to make the selected universe the default universe, check the box next to Set as my Default universe. Click Finish.

On the left pane, you should see the tables and their fields (columns) on expansion.

12. Double-click a column (table-field) in the left pane to select it in the result.
13. Click Run to execute the query.

You can also run the pass-through queries using the Desktop Intelligence application.

To run pass-through queries, perform the following steps:

1. In the Desktop Intelligence application, click New to create a new report.
2. In the New Report Wizard, choose Others instead of Universe.
3. Choose Free-hand SQL and click Finish.
4. Select the connection you created using Designer.
5. Type in the SQL query and click Run to generate the report.

Note: | If you need to specify a four part name in a SELECT list (such as, `<catalogname>.<schemaname>.<tablename>.<columnname>`), define a table alias using the `FROM` clause, and then use only two parts `<tablealias>.<columnname>` in the `SELECT` list. Oracle Data Service Integrator JDBC driver extracts only the last two parts from the `SELECT` list item, and ignores the rest.

For example:

    SELECT E.Name FROM JDBCdemo.empData.empinfo E

where
`JDBCdemo` is the catalog name
`empData` is the schema name
`empinfo` is the table name
`Name` is the column name
`E` is the table alias for `empinfo`

#### Microsoft Access 2003-ODBC

This section describes the procedure to connect Microsoft Access 2003 to Oracle Data Service Integrator through an ODBC-JDBC bridge. It includes the following topics:

- Generating Reports Using Microsoft Access
- Limitations and Usage Notes

##### Generating Reports Using Microsoft Access

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

1. Run MS Access, choose FileOpen, then select ODBC Databases as the file type. The Select Data Source dialog displays.
2. Click Cancel to close the Select Data Source dialog.
3. Click Queries, then Design.
4. Close the Show Table dialog box. The Select Query window should be visible.
5. Right-click in the window and choose SQL SpecificPass-Through.
6. Type the SQL query and click Run.
7. Click the Machine Data Source tab in the dialog that appears, and select openlink-odsi to connect to the Oracle Data Service Integrator JDBC driver and generate the report.

##### Limitations and Usage Notes

- The Microsoft Jet database engine, shipped with MS-Access, maps `SQL_DECIMAL` and `SQL_NUMERIC` fields to the closest Jet numeric data type, depending upon the precision and scale of the ODBC field. In certain cases, this mapping results in a map to a non-exact (floating point) numeric Jet data type, such as `Double` or a Text field. For details, refer to the following Microsoft article:

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

This implicit type conversion by MS Access causes some errors when retrieving data from Oracle Data Service Integrator using MS Access.

- In MS Access, to sort data retrieved from Oracle Data Service Integrator, select a Unique Record Identifier when you link tables imported from Oracle Data Service Integrator. If you do not select the Unique Record Identifier, then an exception occurs when you try to sort data.

#### Microsoft Excel 2003-ODBC

This section describes the procedure for connecting Microsoft Excel 2003 to Oracle Data Service Integrator through an ODJB-JDBC bridge using OpenLink.

To connect Microsoft Excel to Oracle Data Service Integrator, perform the following steps:

1. Launch Workshop for WebLogic and then start the WebLogic Server.
2. Build and deploy the Oracle Data Service Integrator dataspace.
3. Start Microsoft Excel and open a new worksheet.
4. Click Data Import External Data New Database Query. The Choose Data Source dialog box displays.
5. Select openLink-odsi from the list of data sources and then click OK.

The Query Wizard - Choose Columns dialog box displays. For details on configuring the JDBC driver using OpenLink, refer to Connecting to the Oracle Data Service Integrator Client Using OpenLink ODBC-JDBC Bridge on page 5-37.

6. Select the tables that you want to use to generate the report and click Next.
7. Follow the Query Wizard instructions and in the Query Wizard - Finish dialog box, select Return Data to Microsoft Office Excel.
8. Click Finish and import the data in a new MS Excel spreadsheet. The query results display in the spreadsheet.

##### 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 Oracle Data Service Integrator JDBC driver. Here is an example:

    SELECT Sum(EMP.SALARY) AS 'Salary Cost' FROM JDBCdemo.empData.empinfo emp

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

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