5 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:

• Section 5.1, "Introducing SQL Access to Data Services"

• Section 5.2, "JDBC and SQL Support in Oracle Data Service Integrator"

• Section 5.3, "Preparing to Use SQL to Access Data Services"

• Section 5.4, "Accessing Data Services Using SQL From a Java Application"

• Section 5.5, "Advanced Features"

• Section 5.6, "Accessing Data Services Using SQL-Based Applications"

5.1 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

Description of "Figure 5-1 SQL Access to Data Services"

5.1.1 Features of the Oracle Data Service Integrator JDBC Driver

The Oracle Data Service Integrator JDBC driver implements the java.sql.* interface in JDK 1.7x 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 4.0 Application Programming Interface (API)

• Supports Oracle Data Service Integrator with JDK 1.7

• 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 Oracle Fusion Middleware Administering Data Service Integrator guide.

5.1.2 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-1 shows the equivalent terminology.

Table 5-1 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.

5.2 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

5.2.1 JDBC Support

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

• 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-2 Oracle Data Service Integrator JDBC Driver Methods

Interface	Supported Methods	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 getTransactionIsolation getTypeMap	getWarnings isClosed isReadOnly nativeSQL prepareCall prepareStatement setAutoCommit setCatalog setHoldability setReadOnly
java.sql.DatabaseMetaData	allProceduresAreCallable allTablesAreSelectable dataDefinitionCausesTransactionCommit dataDefinitionIgnoredInTransactions deletesAreDetected doesMaxRowSizeIncludeBlobs getAttributes getBestRowIdentifier getCatalogs getCatalogSeparator getCatalogTerm getColumnPrivileges getColumns getConnection getCrossReference getDatabaseMajorVersion	getDatabaseMinorVersion getDatabaseProductName getDatabaseProductVersion getDefaultTransactionIsolation getDriverMajorVersion getDriverMinorVersion getDriverName getDriverVersion getExportedKeys getExtraNameCharacters getIdentifierQuoteString getImportedKeys getIndexInfo getJDBCMajorVersion getJDBCMinorVersion
java.sql.DatabaseMetaData	getMaxBinaryLiteralLength getMaxCatalogNameLength getMaxCharLiteralLength getMaxColumnNameLength getMaxColumnsInGroupBy getMaxColumnsInIndex getMaxColumnsInOrderBy getMaxColumnsInSelect getMaxColumnsInTable getMaxConnections getMaxCursorNameLength getMaxIndexLength getMaxProcedureNameLength getMaxRowSize getMaxSchemaNameLength getMaxStatementLength getMaxStatements getMaxTableNameLength getMaxTablesInSelect getMaxUserNameLength getNumericFunctions getPrimaryKeys getProcedureColumns getProcedures getProcedureTerm getResultSetHoldability getSchemas getSchemaTerm	getSearchStringEscape getSQLKeywords getSQLStateType getStringFunctions getSuperTables getSuperTypes getSystemFunctions getTablePrivileges getTables getTableTypes getTimeDateFunctions getTypeInfo getUDTs getURL getUserName getVersionColumns insertsAreDetected isCatalogAtStart isReadOnly locatorsUpdateCopy nullPlusNonNullIsNull nullsAreSortedAtEnd nullsAreSortedAtStart nullsAreSortedHigh nullsAreSortedLow othersDeletesAreVisible othersInsertsAreVisible othersUpdatesAreVisible
java.sql.DatabaseMetaData	ownDeletesAreVisible ownInsertsAreVisible ownUpdatesAreVisible storesLowerCaseIdentifiers storesLowerCaseQuotedIdentifiers storesMixedCaseIdentifiers storesMixedCaseQuotedIdentifiers storesUpperCaseIdentifiers storesUpperCaseQuotedIdentifiers supportsAlterTableWithAddColumn supportsAlterTableWithDropColumn supportsANSI92EntryLevelSQL supportsANSI92FullSQL supportsANSI92IntermediateSQL supportsBatchUpdates supportsCatalogsInDataManipulation supportsCatalogsInIndexDefinitions supportsCatalogsInPrivilegeDefinitions supportsCatalogsInProcedureCalls supportsCatalogsInTableDefinitions	supportsColumnAliasing supportsConvert supportsCoreSQLGrammar supportsCorrelatedSubqueries supportsDataDefinitionAndDataManipulationTransactions supportsDataManipulationTransactionsOnly supportsDifferentTableCorrelationNames supportsExpressionsInOrderBy supportsExtendedSQLGrammar supportsFullOuterJoins supportsGetGeneratedKeys supportsGroupBy supportsGroupByBeyondSelect supportsGroupByUnrelated supportsIntegrityEnhancementFacility supportsLikeEscapeClause supportsLimitedOuterJoins
java.sql.DatabaseMetaData	supportsMinimumSQLGrammar supportsMixedCaseIdentifiers supportsMixedCaseQuotedIdentifiers supportsMultipleOpenResults supportsMultipleResultSets supportsMultipleTransactions supportsNamedParameters supportsNonNullableColumns supportsOpenCursorsAcrossCommit supportsOpenCursorsAcrossRollback supportsOpenStatementsAcrossCommit supportsOpenStatementsAcrossRollback supportsOrderByUnrelated supportsOuterJoins supportsPositionedDelete supportsPositionedUpdate supportsResultSetConcurrency supportsResultSetHoldability supportsResultSetType	supportsSavepoints supportsSchemasInDataManipulation supportsSchemasInIndexDefinitions supportsSchemasInPrivilegeDefinitions supportsSchemasInProcedureCalls supportsSchemasInTableDefinitions supportsSelectForUpdate supportsStatementPooling supportsStoredProcedures supportsSubqueriesInComparisons supportsSubqueriesInExists supportsSubqueriesInIns supportsSubqueriesInQuantifieds supportsTableCorrelationNames supportsTransactionIsolationLevel supportsTransactions supportsUnion supportsUnionAll
java.sql.DatabaseMetaData	updatesAreDetected usesLocalFilePerTable	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	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 getResultSetConcurrency getResultSetHoldability getResultSetType getUpdateCount getWarnings setCursorName setEscapeProcessing setFetchDirection setFetchSize setMaxFieldSize setMaxRows setQueryTimeout

5.2.2 SQL Support

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

• Section 5.2.2.1, "Supported SQL Statements"

• Section 5.2.2.2, "Supported SQL Functions"

• Section 5.2.3.1, "Table Parameter Support"

5.2.2.1 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.

5.2.2.2 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:

• Section 5.2.2.3, "Numeric Functions"

• Section 5.2.2.4, "String Functions"

• Section 5.2.2.5, "Datetime Functions"

• Section 5.2.2.6, "Aggregate Functions"

• Section 5.2.3, "JDBC Metadata Search Patterns"

5.2.2.3 Numeric Functions

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

Table 5-3 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.

5.2.2.4 String Functions

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

Table 5-4 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.

5.2.2.5 Datetime Functions

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

Table 5-5 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.

5.2.2.6 Aggregate Functions

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

Table 5-6 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.

5.2.3 JDBC Metadata Search Patterns

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

Table 5-7 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 "%"

For more information about using the JDBC metadata API, refer to the Java documentation.

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

Table 5-8 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.

5.2.3.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 Section 5.5.1, "Using Table Parameters."

5.2.4 Additional Details and Limitations

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

Table 5-9 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)

5.3 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:

• Section 5.3.1, "Publishing Data Service Operations"

• Section 5.3.2, "Configuring the Oracle Data Service Integrator JDBC Driver"

5.3.1 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.

5.3.2 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 Section 5.6.1, "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>
      

      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
   

   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.

5.4 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 Section 5.4.1, "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 Section 5.4.1.1, "Using the PreparedStatement Interface" and Section 5.4.1.2, "Using the CallableStatement Interface" respectively.

5.4.1 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>);

5.4.1.1 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 Section 5.3.1, "Publishing Data Service Operations."

5.4.1.2 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;
}

You can also use the prepareCall method as follows:

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

5.5 Advanced Features

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

• Section 5.5.1, "Using Table Parameters"

• Section 5.5.2, "Accessing Custom Database Functions Using JDBC"

5.5.1 Using Table Parameters

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

5.5.1.1 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.

5.5.1.2 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 Example 5-1) represents an entire table parameter and the rows it represents.

Example 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 Example 5-2:

Example 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-10 lists the table parameter ValueTypes supported by the Oracle Data Service Integrator JDBC driver.

Table 5-10 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

5.5.2 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.

5.6 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:

• Section 5.6.1, "Accessing Data Services Using SQL Explorer"

• Section 5.6.2, "Connecting to the Oracle Data Service Integrator Client Using OpenLink ODBC-JDBC Bridge"

• Section 5.6.3, "Using OpenLink with Reporting Tools"

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: http://wiki.eclipse.org/index.php/Getting_Started_with_DTP.

5.6.1 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-2 Create New Driver Dialog

   
   Description of "Figure 5-2 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-3 Create New Connection Profile Dialog

    
    Description of "Figure 5-3 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-4 Connection Dialog

    
    Description of "Figure 5-4 Connection Dialog"
    
    

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

1. If the JDBC Driver window is not open, choose Window > Show View > Other > SQL Explorer Database Structure > OK to display the client data.

2. 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-5 Change Connection Profile Dialog

Description of "Figure 5-5 Change Connection Profile Dialog"

5.6.2 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.

   Note:

   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.

4. Launch Control Panel > Administrative Tools > Data Sources (ODBC). The ODBC Data Source Administrator window displays.

5. Click the System DSN tab and then click Add.

6. Select JDBC Lite for JDK 1.7 (6.0) and click Finish.

7. Specify the DSN name, for example, openlink-odsi.

8. Click Next. Then on the next screen, enter the following next to the JDBC driver:

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

9. Type the following in the URL string field:

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

10. 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.

11. Select any additional parameters, and click Next.

12. Click Next and specify the connection compatibility parameters.

13. Click Next, and then click Test Data Source. Verify that the setup was successful.

14. Click Finish.

5.6.3 Using OpenLink with Reporting Tools

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

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.

5.6.3.1 Microsoft Access 2003-ODBC

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

Generating Reports Using Microsoft Access

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

1. Run MS Access, choose File > Open, 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 Specific > Pass-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

Note the following:

• 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.

5.6.3.2 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 Section 5.6.2, "Connecting to the Oracle Data Service Integrator Client Using OpenLink ODBC-JDBC Bridge."

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 here: http://support.microsoft.com/kb/298955/en-us.