|
|
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:
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:
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:
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.
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.
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.
This section describes the JDBC and SQL support in the Oracle Data Service Integrator JDBC driver
The Oracle Data Service Integrator JDBC driver implements the following interfaces from the java.sql package as specified in JDK 1.5x:
The Oracle Data Service Integrator JDBC driver supports the following methods:
This section outlines SQL-92 support in the Oracle Data Service Integrator JDBC driver, and contains the following sections:
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.
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:
The Oracle Data Service Integrator JDBC driver supports the numeric functions described in Table 5-4.
CEIL returns the smallest integer greater than or equal to n. If n is NULL, the return value is NULL. |
||
The Oracle Data Service Integrator JDBC driver supports the string functions described in Table 5-5.
The Oracle Data Service Integrator JDBC driver supports the datetime functions described in Table 5-6.
The Oracle Data Service Integrator JDBC driver supports the aggregation functions described in Table 5-7.
The Oracle Data Service Integrator JDBC driver supports standard JDBC API search patterns, as shown in Table 5-8.
| Note: | For more information about using the JDBC metadata API, refer to the Java documentation at: |
Assuming that the default_catalog is catalog1 and default_schema is schema1, Table 5-9 shows some common matching patterns.
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.
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.
This section describes the tasks you need to perform prior to using SQL to access data services, and contains the following topics:
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:
After the dataspace is deployed, the newly created SQL objects are available to the dataspace through the ALSDSP 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:
ldjdbc.jar and weblogic.jar (in the <ALDSP_HOME>/lib and <WL_HOME>/server/lib directories respectively) to the client computer.ldjdbc.jar and weblogic.jar to the classpath on the client computer. %JAVA_HOME%/jre/bin to the path on the client computer.com.bea.dsp.jdbc.driver.DSPJDBCDriverjdbc:dsp@<DSPServerName>:<ALDSPServerPortNumber>/<DataspaceName>For example the driver URL could be:
jdbc:dsp@localhost:7001/Test_DataSpaceAlternatively, 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>.CUSTOMERlogFile 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. |
For more information, see Configuring the Connection Using the Properties Object or Configuring the Connection in the JDBC URL respectively.
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:
For more information, see Obtaining a Connection.
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.
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;)?
jdbc:dsp@localhost:7001/Test_DataSpaceThe name of the Oracle Data Service Integrator JDBC driver class is:
com.bea.dsp.jdbc.driver.DSPJDBCDriver 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);
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>);
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. |
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(?)}");
This section describes advances features and uses of the Oracle Data Service Integrator JDBC driver and contains the following sections
This section describes how to use the Oracle Data Service Integrator JDBC driver to pass table parameters to data services.
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. |
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.
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);
}
The following steps show how to create a TableParameter instance and populate the instance with data:
| Note: | At present only one column is supported for table parameters. |
createRow( ) method on TableParameter to create a new Row object representing a tuple in the table.setObject(1,val) call to set the column on the Row object.createRow( ) again to create as many rows as the table requires.You can pass table parameters through JDBC just like any other parameter, using the PreparedStatement interface.
To pass table parameters using the PreparedStatement interface:
PreparedStatement ps = c.prepareStatement("SELECT * " +
"FROM ? as EMP(empid), JDBCdemo.empData.contact CONTACT " +
"WHERE CONTACT.empid = EMP.empid AND CONTACT.zip=?");ps.setObject(2,"98765");
ValueType[] tableType = new ValueType[1];
tableType[0] = ValueType.REPEATING_INTEGER_TYPE;
TableParameter p = new TableParameter(tableType);
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);ps.setObject(1,p);
The following simplified example illustrates the use of a table parameter. The supporting JDBC code is shown in Listing 5-2:
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 5-11 lists the table parameter ValueTypes supported by the Oracle Data Service Integrator JDBC driver.
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:
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.TRIM( TRAILING ' ' FROM $column) as custom database functions.SYSDATE, ROWNUM, or similar columns as parameters to custom database functions.
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:
| 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 |
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: |
To use SQL Explorer, perform the following steps:
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:
com.bea.dsp.jdbc.driver.DSPJDBCDriverThe data displays in the Database Structure and Database Detail window.
Show View Other SQL Explorer Database Structure OK to display the client data.
jdbc:dsp@<DSPServerName>:<DSPServerPortNumber>/<DSPDataspaceName>
/<Your_CatalogName>/<Your_SchemaName>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:
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. |
ldjdbc.jar and weblogic.jar. A typical CLASSPATH might look as follows:<ALDSP_HOME>/lib/ldjdbc.jar;
<WL_HOME>/server/lib/weblogic.jar;
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. |
Administrative Tools Data Sources (ODBC). The ODBC Data Source Administrator window displays.com.bea.dsp.jdbc.driver.DSPJDBCDriver.
jdbc:dsp@<machine_name>:<port>/<dataspace_name>/<catalog_name>/
<schema_name>
This section describes how to configure and use the following reporting tools with the Oracle Data Service Integrator ODBC-JDBC driver:
| 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. |
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:
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.
Before you use Crystal Reports to access data services, consider the following information:
This section assumes that you have successfully completed the following:
http://diamond.businessobjects.com/node/432
CRConfig.xml file (see Configuring Crystal Reports), To connect Crystal Reports to the JDBC driver and access data to generate reports, perform the following steps:
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:
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/BusinessObjectsYou 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.
Before you start using Business Objects to access data services, consider the following information:
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:
To generate a report, perform the following steps:
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.
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.
You can also run the pass-through queries using the Desktop Intelligence application.
To run pass-through queries, perform the following steps:
| 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. |
SELECT E.Name FROM JDBCdemo.empData.empinfo E
whereJDBCdemo is the catalog nameempData is the schema nameempinfo is the table nameName is the column nameE is the table alias for empinfo
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:
To connect MS Access to the bridge, perform the following steps.
Open, then select ODBC Databases as the file type. The Select Data Source dialog displays.Pass-Through.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:This implicit type conversion by MS Access causes some errors when retrieving data from Oracle Data Service Integrator using MS Access.
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:
Import External Data New Database Query. The Choose Data Source dialog box displays.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.
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:
|