14.1 Configuring and Using the JDBC PDS

The JDBC pluggable data source (PDS) enables you to access any JDBC data sources, such as:

  • An RDBMS like Oracle, DB2, Sybase, or SQL Server

  • A non-relational data source like Microsoft Excel

  • Any ODBC data source through the JDBC-ODBC bridge

The JDBC PDS is installed by default with Oracle Reports to allow access to all of the JDBC supported data sources.

This chapter contains the following sections:

14.1.1 JDBC Configuration File

The jdbcpds.conf file is the Oracle Reports JDBC PDS configuration file. It is located in the following directories:

  • For Reports Server: ORACLE_INSTANCE\config\ReportsServerComponent\server_name

  • For Oracle Reports Builder: ORACLE_INSTANCE\config\ReportsToolsComponent\ReportsTools

  • For Reports Application (in-process Reports Server) deployed in Oracle WebLogic Server: fmw_home/user_projects/domains/domain_name/servers/WLS_REPORTS/stage/reports/reports/configuration

This file is preconfigured for the:

You must add or modify relevant entries in the jdbcpds.conf file to include any other JDBC drivers that you want to use.

Oracle Reports Builder displays a list of drivers in the JDBC Query Connection dialog box based on the entries in the jdbcpds.conf file. Use this list to select specific drivers for your report's JDBC query.

Oracle Reports Builder reads and caches the entries in the jdbcpds.conf when it is invoked. Restart Oracle Reports Builder to view the result of any changes made to the jdbcpds.conf file, for example, adding a new JDBC driver entry.

The jdbcpds.conf file has two sections:

  • An Internal DTD section describing the XML format and driver configuration information

    Caution:

    This section should not be modified.
  • An XML section detailing the driver information like driver name, connect string format, driver class, and so on.

Note:

You can modify or add your driver information in this section.

Example

The following sample illustrates the contents of the jdbcpds.conf file:

  <!-- Add or modify the following section for your driver information -->
    <!-- Following drivers are available out-of-box in OracleAS -->
    
    <jdbcpds>
xmlns="http://xmlns.oracle.com/reports/pdsjdbc"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
xsi:schemaLocation="http://xmlns.oracle.com/reports/pdsjdbc file:c:\orawin/reports/dtd/jdbcpds.xsd"
   >
     <driverInfo>
      <driver name = "oracleThin"
              sourceDatabase = "oracle"
              subProtocol = "oracle:thin"
              connectString = "mainProtocol:subProtocol:@databaseName"
              class= "oracle.jdbc.driver.OracleDriver" 
              connection = "oracle.reports.plugin.datasource.jdbcpds.
              JDBCConnectionHandling">
      </driver>
    
      <driver name = "oracle" 
              sourceDatabase = "oracle"
              subProtocol = "oracle:oci8"
              connectString = "mainProtocol:subProtocol:@databaseName"
              class = "oracle.jdbc.driver.OracleDriver" 
              connection = "oracle.reports.plugin.datasource.jdbcpds.
              JDBCConnectionHandling">
      </driver>
      
      <driver name = "jdbc-odbc"
              sourceDatabase = "odbc"
              subProtocol = "odbc" 
              connectString = "mainProtocol:subProtocol:databaseName"
              class = "sun.jdbc.odbc.JdbcOdbcDriver"
              connection = "oracle.reports.plugin.datasource.jdbcpds.
              JDBCConnectionHandling">
      </driver>    
    
      <driver name = "sqlserver-merant"
              sourceDatabase = "sqlserver"
              subProtocol = "merant:sqlserver" 
              connectString = "mainProtocol:subProtocol://databaseName"
              class = "com.oracle.ias.jdbc.sqlserver.SQLServerDriver"
              connection = "oracle.reports.plugin.datasource.jdbcpds.
              JDBCConnectionHandling">
      </driver>
        
      <driver name = "sybase-merant"
              sourceDatabase = "sybase"
              subProtocol = "merant:sybase" 
              connectString = "mainProtocol:subProtocol://databaseName"
              class = "com.oracle.ias.jdbc.sybase.SybaseDriver"
              connection = "oracle.reports.plugin.datasource.jdbcpds.
              JDBCConnectionHandling" 
              loginTimeout = "0">
      </driver>
        
      <driver name = "db2-merant"
              sourceDatabase = "db2"
              subProtocol = "merant:db2" 
              connectString = "mainProtocol:subProtocol://databaseName"
              class = "com.oracle.ias.jdbc.db2.DB2Driver"
              connection = "oracle.reports.plugin.datasource.jdbcpds.
              JDBCConnectionHandling" 
              loginTimeout = "0">
      </driver>     
        
      <driver name = "informix-merant"
              sourceDatabase = "informix"
              subProtocol = "merant:informix" 
              connectString = "mainProtocol:subProtocol://databaseName"
              class = "com.oracle.ias.jdbc.informix.InformixDriver"
              connection = "oracle.reports.plugin.datasource.jdbcpds.
               JDBCConnectionHandling">              
      </driver>    
    
  </driverInfo>
 </jdbcpds>

Table 14-1 outlines the various attributes that can be associated with a driver.

Table 14-1 Driver Attributes

Attribute Name Description Sample

name

A unique user-defined value used to refer to a specific JDBC driver in Oracle Reports.

sybase-merant

sourceDatabase

Database referenced by the driver. The valid entries are:

oracle

sqlserver

sybase

db2

informix

odbc

other

oracle

subProtocol

Driver sub protocol added with the database URL before creating a database connection. This is driver-specific information and can be found in the driver documentation. Example: The sub protocol used for connecting to the Merant driver:

Sybase is merant:sybase

SQL Server is merant:sqlserver

merant:sybase

connectString

Format of the driver's connect string format is mainProtocol:sub Protocol://databaseURL. For example, jdbc:subProtocol://databaseName. Do not specify the actual values for subProtocol or databaseName,use the fixed placeholder names instead.

mainProtocol:subProtocol://databaseName

class

Driver class name used to register to REPORTS_CLASSPATH and load the driver. This is driver-specific information and can be found in the driver documentation.

com.oracle.ias.jdbc.informix.InformixDriver

connection

Driver's connection handling class. The JDBC PDS can have different connection handling classes for each driver. Oracle Reports' default connection handling class, which is sufficient for most drivers, is oracle.reports.plugin.datasource.jdbcpds.JDBCConnectionHandling

Refer to the Oracle Reports Java API Reference for more information on how to extend your JDBC Connection class

oracle.reports.plugin.datasource.jdbcpds. JDBCConnectionHandling

loginTimeout (Optional)

Driver-specific parameter. Specify the value in seconds. Please refer to the driver documentation for more information.

0

property

Specify any additional properties of your driver as Attribute Name and Value.

-


When you submit your report's connection details, the connection information is combined with the driver's configuration information specified in the jdbcpds.conf file. The resulting connection information is submitted to the database as a complete connection URL. Refer to Table 14-3, Table 14-4, Table 14-5, Table 14-6, and Table 14-7 for more information on sample connection information.

Figure 14-0 shows a list of all drivers configured in the jdbcpds.conf file.

Figure 14-1 JDBC Connect Dialog Box in Oracle Reports Builder

Description of Figure 14-1 follows
Description of "Figure 14-1 JDBC Connect Dialog Box in Oracle Reports Builder"

14.1.1.1 Verifying Pre-installed Driver Entries

Drivers like SQL Server and Excel with JDBC-ODBC, Oracle JDBC Thin, and Oracle JDBC OCI (thick) are installed and configured with Oracle Reports. These drivers do not require any additional JAR files to be installed.

  • Oracle JDBC Thin driver

  • Oracle JDBC OCI (thick) driver

  • JDBC-ODBC driver

    You can use SQL Server / Excel with the JDBC-ODBC driver. This entry is preconfigured in the jdbcpds.conf file. Before you can use SQL Server or Excel with JDBC-ODBC, you must create an ODBC data source. Refer to Windows help, for more information on how to create an ODBC data source.

    Note:

    Oracle Fusion Middleware provides Merant DataDirect drivers which can also be used to access SQL Server.

14.1.1.2 Installing and Configuring Merant DataDirect Drivers

Oracle provides a set of Merant DataDirect drivers (Version 3.2) that can be downloaded from OTN (http://www.oracle.com/technology/index.html). The driver configuration file; that is, jdbcpds.conf contains relevant entries for the Merant DataDirect drivers. Additionally, the JDBC Connect dialog (Table 14-1) lists the entries for the set of Merant DataDirect drivers provided by Oracle.

However, you must install the appropriate JAR files and specify them in Oracle Reports specific classpath entries, in order to make them available to Oracle Reports Builder and Oracle Reports Services

The drivers provided by Oracle for use with Oracle Fusion Middleware / Oracle Developer Suite are:

You can also install and configure a Custom Driver for use with Oracle Fusion Middleware and Oracle Developer Suite.

The following procedure outlines the generic steps involved in configuring the Merant DataDirect drivers. To configure specific Merant DataDirect drivers refer to the appropriate sections.

To configure the Merant DataDirect drivers:

  1. Install the relevant JAR files in your Oracle Fusion Middleware and Oracle Developer Suite directory.

  2. Include an entry in REPORTS_CLASSPATH to make the files available to Oracle Reports Builder and Oracle Reports Services.

    Note:

    The REPORTS_CLASSPATH variable is located in the reports.sh file for all UNIX platforms.

    Refer to the relevant driver in this section for information on the required JAR files.

    1. Oracle Reports Builder: Prefix the driver location to the existing entries in REPORTS_CLASSPATH.This variable is located in the registry for Windows users and in the reports.sh file for UNIX users. Refer to the relevant driver in this section for an example.

    2. rwbuilder.conf: Append the driver location to the engine classPath attribute in the rwbuilder.conf configuration file. Refer to the relevant driver in this section for an example.

    3. Reports Server: Append the driver location to the classPath attribute of the engine, in the Reports Server configuration file. Refer to the relevant driver in this section for an example

    4. jdbcpds.conf: Located in the ORACLE_INSTANCE\config\ReportsServerComponent\server_name directory. Refer to Table 14-1 for more information on the parameters. Refer to the relevant driver in this section for an example.

14.1.1.2.1 Sybase Driver

  1. Install the relevant JAR files in your Oracle Fusion Middleware and Oracle Developer Suite directory.

    Jar files required: YMutil.jar, YMsybase.jar, and YMbase.jar.

  2. Include an entry in the REPORTS_CLASSPATH to make the files available to Oracle Reports Builder and Oracle Reports Services.

    Note:

    The REPORTS_CLASSPATH variable is located in the reports.sh file for all UNIX platforms.
    1. Oracle Reports Builder: Prefix the driver location to the existing entries in REPORTS_CLASSPATH.This variable is located in the registry for Windows users and in the reports.sh file for UNIX users.

      Example:
      D:\sybase_installed\YMutil.jar;D:\sybase_installed\YMsybase.jar;D:\sybase_
      installed\YMbase.jar;existing classpath entries
      
    2. rwbuilder.conf: Append the driver location to the engine classPath attribute in the rwbuilder.conf configuration file.

      Example:
      <engine id="rwEng" class="oracle.reports.engine.EngineImpl" initEngine="1"
      maxEngine="1"   minEngine="0" engLife="50" maxIdle="30"
      callbackTimeOut="60000"  classPath="D:\sybase_
      installed\YMutil.jar;D:\sybase_installed\YMsybase.jar;D:\sybase_
      installed\YMbase.jar;">
      ... 
      </engine>
      
    3. Reports Server: Append the driver location to the classPath attribute of the engine in the Reports Server configuration file.

      Example:
      <engine id="rwEng" class="oracle.reports.engine.EngineImpl" initEngine="1"
      maxEngine="1"   minEngine="0" engLife="50" maxIdle="30"
      callbackTimeOut="60000"        classPath="D:\sybase_
      installed\YMutil.jar;D:\sybase_installed\YMsybase.jar;D:\sybase_
      installed\YMbase.jar;">        
      ... 
      </engine>
      
    4. jdbcpds.conf: Located in the ORACLE_INSTANCE\config\ReportsServerComponent\server_name directory. Refer to Table 14-1 for more information on the required parameters.

      Example:
      <driver   name = "sybase-merant"
                sourceDatabase = "sybase"
                    subProtocol = "merant:sybase" 
                    connectString = "mainProtocol:subProtocol://databaseName"
                    class = "com.oracle.ias.jdbc.sybase.SybaseDriver"
                    connection = "oracle.reports.plugin.datasource.jdbcpds.
                    JDBCConnectionHandling"
                    loginTimeout = "0">
       </driver>
      

14.1.1.2.2 DB 2 Driver

  1. Install the relevant JAR files in your Oracle Fusion Middleware and Oracle Developer Suite directory.

    JAR files required: YMutil.jar, YMdb2.jar, and YMbase.jar

  2. Include an entry in REPORTS_CLASSPATH to make the files available to Oracle Reports Builder and Oracle Reports Services.

    Note:

    The REPORTS_CLASSPATH variable is located in the reports.sh file for all UNIX platforms.
    1. Oracle Reports Builder: Prefix the driver location to the existing entries in REPORTS_CLASSPATH. This variable is located in the registry for Windows users and in the reports.sh file for UNIX users.

      Example: 
      D:\db2_installed\YMutil.jar;D:\db2_installed\YMdb2.jar;D:\db2_
      installed\YMbase.jar;existing classpath entries
      
    2. rwbuilder.conf: Append the driver location to the engine classPath attribute in the rwbuilder.conf configuration file.

      Example:
      <engine id="rwEng" class="oracle.reports.engine.EngineImpl" initEngine="1"
      maxEngine="1" minEngine="0" engLife="50"        maxIdle="30"
      callbackTimeOut="60000" classPath="D:\db2_installed\YMutil.jar;D:\db2_
      installed\YMdb2.jar;D:\db2_installed\YMbase.jar">       
      ... 
      </engine>
      
    3. Reports Server: Append the driver location to the classPath attribute of the engine in the Reports Server configuration file.

      Example:
      <engine id="rwEng" class="oracle.reports.engine.EngineImpl" initEngine="1"
      maxEngine="1" minEngine="0" engLife="50"        maxIdle="30"
      callbackTimeOut="60000" classPath="D:\db2_installed\YMutil.jar;D:\db2_
      installed\YMdb2.jar;D:\db2_installed\YMbase.jar">       
      ... 
      </engine>
      
    4. jdbcpds.conf: Located in the ORACLE_INSTANCE\config\ReportsServerComponent\server_name directory. Refer to Table 14-1 for more information on the parameters.

      Example:
      <driver   name = "db2-merant"                   
                    sourceDatabase = "db2"
                    subProtocol = "merant:db2" 
                    connectString = "mainProtocol:subProtocol://databaseName"
                    class = "com.oracle.ias.jdbc.db2.DB2Driver"
                    connection = "oracle.reports.plugin.datasource.jdbcpds.
                    JDBCConnectionHandling" 
                    loginTimeout = "0">
        </driver> 
      

14.1.1.2.3 SQL Server Driver

  1. Install the relevant .jar files in your Oracle Fusion Middleware and Oracle Developer Suite directory.

    Jar files required: YMutil.jar, YMsqlserver.jar, and YMbase.jar

  2. Include an entry in the REPORTS_CLASSPATH to make the files available to Oracle Reports Builder and Oracle Reports Services.

    Note:

    The REPORTS_CLASSPATH variable is located in the reports.sh file for all UNIX platforms.
    1. Oracle Reports Builder: Prefix the driver location to the existing entries in REPORTS_CLASSPATH.This variable is located in the registry for Windows users and in the reports.sh file for UNIX users.

      Example:
      D:\sqlserver_installed\YMutil.jar;D:\sqlserver_
      installed\YMsqlserver.jar;D:\sqlserver_installed\YMbase.jar;existing
      classpath entries
      
    2. rwbuilder.conf: Append the driver location to the engine classPath attribute in the rwbuilder.conf configuration file.

      Example:
      <engine id="rwEng" class="oracle.reports.engine.EngineImpl" initEngine="1"
      maxEngine="1" minEngine="0" engLife="50"        maxIdle="30"
      callbackTimeOut="60000" classPath="D:\sqlserver_
      installed\YMutil.jar;D:\sqlserver_installed\YMsqlserver.jar;D:\sqlserver_
      installed\YMbase.jar;">        
      ... 
      </engine>
      
    3. Reports Server: Append the driver location to the classPath attribute of the engine in the Reports Server configuration file.

      Example:
      <engine id="rwEng" class="oracle.reports.engine.EngineImpl" initEngine="1"
      maxEngine="1" minEngine="0" engLife="50"        maxIdle="30"
      callbackTimeOut="60000" classPath="D:\sqlserver_
      installed\YMutil.jar;D:\sqlserver_installed\YMsqlserver.jar;D:\sqlserver_
      installed\YMbase.jar;">        
      ... 
      </engine> 
      
    4. jdbcpds.conf: Located in the ORACLE_INSTANCE\config\ReportsServerComponent\server_name directory. Refer to Table 14-1 for more information on the parameters.

      Example:
      <driver  name = "sqlserver-merant"
                    sourceDatabase = "sqlserver"
                    subProtocol = "merant:sqlserver" 
                    connectString = "mainProtocol:subProtocol://databaseName"
                    class = "com.oracle.ias.jdbc.sqlserver.SQLServerDriver"
                    connection = "oracle.reports.plugin.datasource.jdbcpds.
                    JDBCConnectionHandling">
      </driver> 
      

14.1.1.2.4 Informix Driver

  1. Install the relevant JAR files in your Oracle Fusion Middleware and Oracle Developer Suite directory.

    JAR files required: YMutil.jar, YMinformix.jar, and YMbase.jar

  2. Include an entry in the REPORTS_CLASSPATH to make the files available to Oracle Reports Builder and Oracle Reports Services.

    Note:

    The REPORTS_CLASSPATH variable is located in the reports.sh file for all UNIX platforms.
    1. Oracle Reports Builder: Prefix the driver location to the existing entries in REPORTS_CLASSPATH.This variable is located in the registry for Windows users and in the reports.sh file for UNIX users.

      Example: 
      D:\informix_installed\YMutil.jar;D:\informix_
      installed\YMinformix.jar;D:\informix_installed\YMbase.jar;existing
      classpath entries
      
    2. rwbuilder.conf:Append the driver location to the engine classPath attribute in the rwbuilder.conf configuration file.

      Example:
      <engine id="rwEng" class="oracle.reports.engine.EngineImpl" initEngine="1"
      maxEngine="1" minEngine="0" engLife="50"        maxIdle="30"
      callbackTimeOut="60000" classPath="D:\informix_
      installed\YMutil.jar;D:\informix_installed\YMinformix.jar;D:\informix_
      installed\YMbase.jar">       
      ... 
      </engine>
      
    3. Reports Server: Append the driver location to the classPath attribute of the engine in the Reports Server configuration file.

      Example:
      <engine id="rwEng" class="oracle.reports.engine.EngineImpl" initEngine="1"
      maxEngine="1" minEngine="0" engLife="50"        maxIdle="30"
      callbackTimeOut="60000" classPath="D:\informix_
      installed\YMutil.jar;D:\informix_installed\YMinformix.jar;D:\informix_
      installed\YMbase.jar">       
      ... 
      </engine>
      
    4. jdbcpds.conf: Located in the ORACLE_INSTANCE\config\ReportsServerComponent\server_name directory. Refer to Table 14-1 for more information on the parameters.

      Example:
      <driver name = "informix-merant"
      sourceDatabase = "informix"
      subProtocol = "merant:informix"
      connectString = "mainProtocol:subProtocol://databaseName"
      class = "com.oracle.ias.jdbc.informix.InformixDriver"
      connection = "oracle.reports.plugin.datasource.jdbcpds.
      JDBCConnectionHandling">
      </driver>
      

14.1.1.2.5 Custom Driver

Any driver that is not provided by Oracle must be installed and configured. For example, you can use BEA JDBC drivers if you have license. To install and configure a custom driver, complete the following steps:

  1. Install the relevant JAR files in your Oracle Fusion Middleware and Oracle Developer Suite directory.

  2. Include an entry in REPORTS_CLASSPATH to make the files available to Oracle Reports Builder and Oracle Reports Services.

    Note:

    The REPORTS_CLASSPATH variable is located in the reports.sh file for all UNIX platforms.

    Jar files required: Refer to the relevant driver documentation.

    1. Oracle Reports Builder: Prefix the driver location to the existing entries in REPORTS_CLASSPATH. This variable is located in the registry for Windows users and in the reports.sh file for UNIX users.

      Example:
      driver location\1st jar file;driver location\2nd jar file2;existing classpath entries
      
    2. rwbuilder.conf: Append the driver location to the engine classPath attribute in the rwbuilder.conf configuration file.

      Example:
      <engine id="rwEng" class="oracle.reports.engine.EngineImpl" initEngine="1"
      maxEngine="1" minEngine="0" engLife="50"        maxIdle="30"
      callbackTimeOut="60000" classPath="driver location\1st jar file;driver
      location\2nd jar file;">
      ... 
      </engine>
      
    3. Reports Server: Append the driver location to the classPath attribute of the engine in the Reports Server configuration file.

      Example:
      <engine id="rwEng" class="oracle.reports.engine.EngineImpl" initEngine="1"
      maxEngine="1" minEngine="0" engLife="50"        maxIdle="30"
      callbackTimeOut="60000" classPath="driver location\1st jar file;driver
      location\2nd jar file;">
      ... 
      </engine> 
      
    4. jdbcpds.conf: Located in the ORACLE_INSTANCE\config\ReportsServerComponent\server_name directory. Add relevant driver configuration information to the jdbcpds.conf file. Refer to Table 14-1 for more information on the required parameters.

      Example:
      <driver  name = "<driver name>"
             sourceDatabase = "<sourceDatabase>"
                    subProtocol = "<subProtocol>" 
                    connectString = "mainProtocol:subProtocol://databaseName"
                    class = "<driver class name>"
                    connection ="<connection handling class">
      </driver> 
      

      Note:

      This value can still be connection = "oracle.reports.plugin.datasource.jdbcpds.JDBCConnectionHandling"for your custom drivers, if you do not want to implement a custom connection dialog

14.1.2 Defining and Running a JDBC Query

After configuring the relevant JDBC drivers, you can define and run a JDBC query using either SQL or a stored procedure.

To define a JDBC query:

  1. Start Oracle Reports Builder.

  2. Invoke the Reports Wizard.

  3. Select the data source type as JDBC Query and click Next. For more information on how to work with the Report Wizard, refer to the Oracle Reports online Help.

    Figure 14-2 Select a Data Source Type

    Description of Figure 14-2 follows
    Description of "Figure 14-2 Select a Data Source Type"

  4. In the Data Source Definition window, click Query Definition.

  5. Define one of the following:

    • A SQL query:

      SELECT * FROM DEPARTMENT;
      
    • A stored procedure:

      Enter the complete call syntax of your database's stored procedure. For example:

      TestProc(40)

      For more information on the call syntax, refer to your database documentation.

      JDBC PDS submits the calling statement to the driver as specified, to invoke the stored procedure.

      Figure 14-3 Calling a stored procedure

      Description of Figure 14-3 follows
      Description of "Figure 14-3 Calling a stored procedure "

    • Table 14-2 Specifying an Excel Data Source

      Query (Single Worksheet) Query (Muitiple Worksheets)

      SELECT * FROM [SHEET1$] or SELECT COL1, COL2, ...COLn FROM [SHEET1$]

      Where SHEET1$ is the name of a .xls file

      Where the first worksheet row value is taken as a column name for the query

      Note: If a value is not mentioned in any of the columns in the first row, then the default name is FcolumnNumber. For example, the 8th column will be F8, the ninth column will be F9, and so on.

      SELECT * FROM [WORKSHEETNAME$]

      Where [WORKSHEETNAME$] is the name of the worksheet

      Where the first worksheet row is taken as a column name for the query

      Note: If a value is not mentioned in any of the columns in the first row, then the default name is FcolumnNumber. For example, the 8th column will be F8, the ninth column will be F9, and so on.


  6. Specify a sign-on parameter name. This sign-on parameter is associated with the connection information when run against a database. The default sign-on parameter name is P_JDBCPDS (see Section A.7.10, "P_JDBCPDS"):

    1. Enter a new sign-on name and click Connect. Use this sign-on parameter to specify a database connection when you are running your report using Oracle Reports Services.

    2. Enter the connection information (user name, password, and database name) for the driver type. Refer to Table 14-3, Table 14-4, Table 14-5, Table 14-6, and Table 14-7 for sample connection information.

    3. Select the driver type. The driver list is displayed based on the values entered in the jdbcpds.conf file.

    4. Click Connect to gain access to the database using the new sign-on. The connect string formed internally is a combination of:

      • The connectString driver attribute (Table 14-1) defined in the jdbcpds.conf file

      • The connection information supplied in the Connect dialog will be used to fill the database name portion of the connect string.

  7. Click OK to execute the JDBC query.

  8. The Reports Wizard displays the query description (Figure 14-4).

    Figure 14-4 Query Description

    Description of Figure 14-4 follows
    Description of "Figure 14-4 Query Description"

  9. Follow the steps in the wizard to define the layout and to run the report based on your JDBC query.

14.1.2.1 Sample Connection Information

Table 14-3, Table 14-4, Table 14-5, Table 14-6, Table 14-7,Table 14-8, and Table 14-9 lists sample connection information for use with:

  • Pre-installed drivers; that is, Oracle JDBC Thin, Oracle JDBC OCI (thick), and JDBC-ODBC.

  • DataDirect Merant drivers available on Oracle Technology Network, (http://www.oracle.com/technology/index.html).

    Table 14-3 Oracle Thin Driver

    Property Value

    Username

    Reports

    Password

    Welcome

    Database

    hostname: The TCP/IP address or TCP/IP host name of the server you are connecting to.

    port: The TCP/IP port number.

    property: The connection properties. Refer to the driver documentation for a list of connection properties and their valid values.

    Example: server1.us.oracle.com:1300:session1


    Table 14-4 Oracle Thick Driver

    Property Value

    Username

    Reports

    Password

    Welcome

    Database

    n123

    where n123 is a tnsname entry in the tnsnames.ora file


    Table 14-5 JDBC-ODBC Driver

    Property Value

    Username

    N/A

    Password

    This password is set at the time of establishing an ODBC connection.

    Database

    SQLSVR

    where SQLSVR is the ODBC Data entry in the ODBC data source.


    Table 14-6 Sybase

    Property Value

    Username

    Reports

    Password

    Welcome

    Database

    hostname: The TCP/IP address or TCP/IP host name of the server you are connecting to.

    port: The number of the TCP/IP port.

    Example: server1.us.oracle.com:1300


    Table 14-7 DB2

    Property Value

    Username

    Reports

    Password

    Welcome

    Database

    hostname: The TCP/IP address or TCP/IP host name of the server you are connecting to.

    port: The TCP/IP port number.

    property: The connection properties. Refer to the driver documentation for a list of connection properties and their valid values.

    Example1: server1:1654

    Example2: server2:1721;PackageName=pkg1


    Table 14-8 SQL Server

    Property Value

    Username

    Reports

    Password

    Welcome

    Database

    hostname: The TCP/IP address or TCP/IP host name of the server you are connecting to.

    port: The TCP/IP port number.

    Example1: server1:1654


    Table 14-9 Informix

    Property Value

    Username

    Reports

    Password

    Welcome

    Database

    hostname: The TCP/IP address or TCP/IP host name of the server you are connecting to.

    port: The TCP/IP port number.

    InformixServer name: The Informix server name.

    Database name: The database name you are connected to.

    Example2: server_name:2003;InformixServer=myinformix_server;DatabaseName=scott/tiger@mydb


14.1.3 Running a JDBC Report Using Oracle Reports Services

When you run a report containing a JDBC query (Reports Server or rwrun engine), use the sign-on parameter to submit the connection information for the JDBC data source. This sign-on parameter is defined for your JDBC query in Reports Builder during design time.

For example, if your report has a JDBC query to a Sybase data source, a JDBC query to a DB2 data source, and a SQL query to an Oracle data source, then the request could be defined as:

http://your_ias_
server:port//reports/rwservlet?report=my.rdf&userid=user/pwd@oracledb
&desformat=pdf&destype=cache&p_sybasepds=sybaseuser/pw@sybasehost:port 
&p_db2pds=db2user/pwd@db2host:port 

where:

  • userid is the value for connecting the SQL query to the Oracle database. You need not specify the userid if your report does not have a SQL query or a REF CURSOR query.

  • p_sybasepds is the sign-on parameter associated with the Sybase JDBC query.

  • p_db2pds is the sign-on parameter associated with the DB2 JDBC query defined in the report at design time.

    The default sign-on parameter name P_JDBCPDS will be used if you have not specified a name in the JDBC Query dialog box while designing the report in Reports Builder.

14.1.4 TroubleShooting Information

This section lists:

14.1.4.1 Error Messages

Table 14-10, Table 14-11, and Table 14-12 lists troubleshooting information related to the JDBC PDS.

Table 14-10 Error Messages Related to the Database Connection

Error Message Cause Action

Connection class {0} can't be loaded

Invalid connection class specified in the jdbcpds.conf file for the selected driver.

Ensure that the driver connection class specified in the jdbcpds.conf file is both valid and available.

Failed to connect to the datasource

Invalid connection information.

Ensure the validity of the username, password, database, and driver type.

Invalid sign-on parameter {0}

Invalid sign-on parameter for the specified query or procedure.

Ensure the sign-on parameter is available and valid for the report's JDBC query type.

Invalid value is given to the sign-on parameter {0}

Invalid connect string for the specified sign-on parameter.

Ensure that the specified connect string for this sign-on parameter is valid for the selected driver.


Table 14-11 Error messages Related to Executing the Data Source

Error Message Cause Action

Reference parameter of type Date is not supported by JDBC driver used.

The driver used to connect to database does not support the Date data type as a reference parameter.

Use either:

The String data type as the reference parameter.

A different JDBC driver that supports the Date data type as a reference parameter.

Invalid lexical parameter {0} is used in the query

Invalid lexical parameter used in the query or procedure.

Ensure that the query or procedure uses valid lexical parameters. Create a new parameter if it is not available.

SQL Error:

SQL syntax error in the specified query or procedure.

Ensure that the syntax of the query or procedure is valid. Refer to the relevant data source's documentation.

Invalid query/procedure for the specified datasource.

Invalid query or procedure syntax.

Ensure that the syntax of the query or procedure is valid. Refer to the relevant data source's documentation.

Invalid reference parameter value

Invalid reference parameter value.

Verify that the reference column types and values are correct.

No query/procedure is entered.

The query or procedure text field is empty.

Enter a valid query or procedure in the text field.

Database URL:

Invalid database URL.

Verify the validity of the specified database name and the selected driver type.

Either the number of columns or the types of columns does not match the query definition

The data fetched does not match the number of columns or column types specified in the query definition.

Ensure that the number of columns and the column types match the query definition.

The column type {0} used in the query/procedure is not supported by Reports JDBC query.

This column type is not supported by the Oracle Reports JDBC query interface.

Ensure that only column types supported by the Oracle Reports JDBC query interface are used. Refer to the JDBC specification and Oracle Reports documentation for a list of all supported types.


Table 14-12 Isolating Driver / PDS Issues

Error Message Cause Action

The inline DTD section of the configuration file jdbcpds.conf has been modified.

The format of the inline DTD section in the jdbcpds.conf file has been altered.

If the DTD format is modified, ensure the validity of configuration file against the JDBC PDS requirement.

Line Number:

An error was found on the specified line of the jdbcpds.conf file.

Correct the error on the specified line.

Configuration file jdbcpds.conf is not found

The jdbcpds.conf file is not found under the ORACLE_INSTANCE\config\ReportsServerComponent\server_name directory.

Ensure that the jdbcpds.conf file is available in the file is available in the ORACLE_INSTANCE\config\ReportsServerComponent\server_name directory.

Parsing error in the configuration file jdbcpds.conf. Number of errors:{0}

The XML section in the jdbcpds.conf file does not conform with its inline DTD.

Ensure that the XML section in the jdbcpds.conf file refers to the correct inline DTD.

No entry is present for the driver {0} in the jdbcpds.conf file.

The driver used in the query is not specified in the jdbcpds.conf file.

Ensure that the entry for the required driver along with the related driver information is in the jdbcpds.conf file.


14.1.4.2 Trace Information

Use the detailed trace information generated by Oracle Reports to debug your JDBC query.

  • Design time (building a JDBC query) and run time (running a JDBC query)

    The trace information generated is helpful to find out the following:

    • Lexical and bind parameters.

    • Final connect string formed to connect to the driver.

    • Metadata information received from the driver.

    • Final query submitted to the database.

    See Example 14-1 for sample design-time trace output.

    See Example 14-2 for sample run-time trace output.

    For more information on the location of the logging.xml file, see Section 24.3.7, "Tracing Report Execution"

Sample trace output

Example 14-1 Building a JDBC Query from JDBC Query Dialog

Connection handling trace showing final connect string
[2003/4/7 5:41:38:686] Debug 50103 (jdbcpds): handleConnectButtonEvent : start
[2003/4/7 5:41:38:686] Debug 50103 (jdbcpds): handleConnectButtonEvent : subProtocol :sybase-merant
[2003/4/7 5:41:38:686] Debug 50103 (jdbcpds): handleConnectButtonEvent :
connection class :oracle.reports.plugin.datasource.jdbcpds.JDBCConnectionHandling
[2003/4/7 5:41:38:696] Debug 50103 (jdbcpds): handleConnectButtonEvent : combine
string :jdbc:merant:sybase://server1.us.oracle.com:1300
[2003/4/7 5:41:38:696] Debug 50103 (jdbcpds): JDBCDataSource : setJDBCQueryType: sybase 
[2003/4/7 5:41:41:350] Debug 50103 (jdbcpds): JDBCUIEventHandler : handleConnectEvent : Valid Connection
com.oracle.ias.jdbc.sybase.SybaseConnection@56fc16
[2003/4/7 5:41:41:350] Debug 50103 (jdbcpds): JDBCUIEventHandler :
handleConnectEvent : END com.oracle.ias.jdbc.sybase.SybaseConnection@56fc16

Design time metadata of query
[2003/3/31 6:35:46:363] Debug 50103 (jdbcpds): JDBCUIEventHandler : handleOKEvent : Serialize XML<jdbcpds DTDVersion="
1.0"><JDBCQuery>jdbcpdspkg.proc_with_param(1,2,3,4,5)</JDBCQuery><QueryDefinition>1</QueryDefinition><driverType>oracle
</driverType><connectionClass>oracle.reports.plugin.datasource.jdbcpds.JDBCConnect
ionHandling</connectionClass><SignOnParameter>P_JDBCPDS</SignOnParameter><jdbcElements><elementname = "EMPNO"  type = "2"  
typeName = "NUMBER"  columnSize = "4"  columnScale = "0" /><element name = "ENAME" 
type = "12"   typeName ="VARCHAR2"  columnSize = "10"  columnScale = "0"
/><element name = "JOB"  type = "12"   typeName = "VARCHAR2"  columnSize = "9" 
columnScale ="0" /><element name = "MGR"  type = "2"   typeName = "NUMBER" 
columnSize = "4"  columnScale = "0" /><element name = "HIREDATE"  type = "93"  
typeName = "DATE"  columnSize = "16"  columnScale = "0" /><element name = "SAL" 
type = "2"   typeName = "NUMBER"  columnSize = "7"  columnScale= "2" /><element
name = "COMM"  type = "2"   typeName = "NUMBER"  columnSize = "7"  columnScale =
"2" /><element name = "DEPTNO"  type = "2" typeName = "NUMBER"  columnSize = "2" 
columnScale = "0" /></jdbcElements><referenceColumns></referenceColumns></jdbcpds>
[2003/3/31 6:35:46:383] Debug 50103 (jdbcpds): JDBCUIEventHandler :handleOKEvent
END

Example 14-2 Running a JDBC Query

[2003/3/18 5:45:17:707] Debug 50103 (jdbcpds): JDBCDataSource : startRuntime
method : START

Describing the JDBC Query:
[2003/3/18 5:45:17:707] Debug 50103 (jdbcpds):  JDBCDataSource : describe :  START
[2003/3/18 5:45:17:707] Debug 50103 (jdbcpds):  applyXML: Extract the Serilzed XML
containing Query Meta Data <jdbcpds DTDVersion=" 1.0"><JDBCQuery>select * from
emp</JDBCQuery><QueryDefinition>0</QueryDefinition><driverType>oracle</driverType>
<connectionClass>oracle.reports.plugin.datasource.jdbcpds.JDBCConnectionHandling</
connectionClass>...

ConnectionHandling At Runtime:
[2003/3/18 5:45:17:737] Debug 50103 (jdbcpds): JDBCDataSource : startRuntime :
Create a new connection and handle it 
[2003/3/18 5:45:17:737] Debug 50103 (jdbcpds):  JDBCExecuteQuerySource :
handleConnection : START
[2003/3/18 5:45:17:778] Debug 50103 (jdbcpds): JDBCExecuteQuerySource :
handleConnection : set driver
[2003/3/18 5:45:17:778] Debug 50103 (jdbcpds): JDBCExecuteQuerySource :
handleConnection : Check if Connection for the sign on parameter is pooled
[2003/3/18 5:45:17:778] Debug 50103 (jdbcpds): JDBCExecuteQuerySource
:handleConnection :  connection available in pool
[2003/3/18 5:45:17:778] Debug 50103 (jdbcpds): handleConnection : END
[2003/3/18 5:45:17:778] Debug 50103 (jdbcpds): JDBCDataSource : startRuntime : END

Runtime execution of jdbc query
[2003/3/31 6:36:2:836] Debug 50103 (jdbcpds): JDBCDataSource : execute : run Query
[2003/3/31 6:36:2:836] Debug 50103 (jdbcpds): JDBCExecuteQuerySource :
getOutputFromDatabase : START
[2003/3/31 6:36:2:836] Debug 50103 (jdbcpds): JDBCExecuteQuerySource :
getOutputFromDatabase: start Query stringto be submitted
jdbcpdspkg.proc_with_param(1,2,3,4,5)
[2003/3/31 6:36:2:836] Debug 50103 (jdbcpds): JDBCExecuteQuerySource :
getOutputFromDatabase : check connection
[2003/3/31 6:36:2:836] Debug 50103 (jdbcpds): JDBCExecuteQuerySource :
getOutputFromDatabase : QSource Id: 1
[2003/3/31 6:36:2:836] Debug 50103 (jdbcpds): JDBCExecuteQuerySource:
executeOracleProcedure:Start
[2003/3/31 6:36:2:836] Debug 50103 (jdbcpds): JDBCExecuteQuerySource:
executeOracleProcedure:Procedure to be submitted { call
jdbcpdspkg.proc_with_param(?,?,?,?,?,?) }
[2003/3/31 6:36:2:836] Debug 50103 (jdbcpds): JDBCExecuteQuerySource:
executeOracleProcedure:  Set parameters for the procedure call
[2003/3/31 6:36:2:836] Debug 50103 (jdbcpds): JDBCExecuteQuerySource:
executeOracleProcedure:  execute procedure
[2003/3/31 6:36:2:847] Debug 50103 (jdbcpds): JDBCDataSource : execute : query
execution over andresulset object is oracle.jdbc.driver.OracleResultSetImpl@751a9e
[2003/3/31 6:36:2:847] Debug 50103 (jdbcpds): JDBCDataSource : execute : END

Running Report trace with Result set info

2003/4/7 5:26:6:996] Debug 50103 (jdbcpds): JDBCDataSource : execute : replace
lexical columns withactual string for the query
[2003/4/7 5:26:6:996] Debug 50103 (jdbcpds): JDBCDataSource : execute : run Query
[2003/4/7 5:26:6:996] Debug 50103 (jdbcpds): JDBCExecuteQuerySource :
getOutputFromDatabase : START
[2003/4/7 5:26:6:996] Debug 50103 (jdbcpds): JDBCExecuteQuerySource :
getOutputFromDatabase: start Query stringto be submitted select * from reports
[2003/4/7 5:26:7:6] Debug 50103 (jdbcpds): JDBCExecuteQuerySource :
getOutputFromDatabase : check connection
[2003/4/7 5:26:7:6] Debug 50103 (jdbcpds): JDBCExecuteQuerySource :
getOutputFromDatabase : QSource Id: 4
[2003/4/7 5:26:7:6] Debug 50103 (jdbcpds): JDBCExecuteQuerySource :
getOutputFromDatabase : Query source is SQL query 
[2003/4/7 5:26:7:6] Debug 50103 (jdbcpds): JDBCExecuteQuerySource:executeQuery
Start
[2003/4/7 5:26:7:6] Debug 50103 (jdbcpds): executeQuery prepareStatement select *
from reports
[2003/4/7 5:26:7:6] Debug 50103 (jdbcpds): executeQuery : bind parameters set for
the query 
[2003/4/7 5:26:7:6] Debug 50103 (jdbcpds): executeQuery : JDBC Query executed
[2003/4/7 5:26:7:387] Debug 50103 (jdbcpds): JDBCExecuteQuerySource :
getOutputFromDatabase : Query result  col 0  test col 1  10
[2003/4/7 5:26:7:387] Debug 50103 (jdbcpds): JDBCExecuteQuerySource:executeQuery
Start
[2003/4/7 5:26:7:387] Debug 50103 (jdbcpds): executeQuery prepareStatement select
* from reports
[2003/4/7 5:26:7:387] Debug 50103 (jdbcpds): executeQuery : bind parameters set
for the query 
[2003/4/7 5:26:7:387] Debug 50103 (jdbcpds): executeQuery : JDBC Query executed
[2003/4/7 5:26:7:767] Debug 50103 (jdbcpds): JDBCDataSource : execute : query
execution over andresulset object is com.oracle.ias.jdbc.base.BaseResultSet@56c3cf
[2003/4/7 5:26:7:767] Debug 50103 (jdbcpds): JDBCDataSource : execute : END

14.1.5 Adding Your Own JDBC Driver

Note:

Oracle Reports exposes the PDS API and also contains a tutorial that describes in detail how to implement or customize your own PDS. For more information, refer to the Reports Software Development Kit (RSDK), available on the Oracle Technology Network (OTN): on the Oracle Reports page (http://www.oracle.com/technology/products/reports/index.html), click SDK. Using this API, you can implement an unlimited number of PDSs to access any kind of data sources that you have.

The main tasks you must perform to add your JDBC PDS are:

14.1.5.1 Configuring the jdbcpds.conf File

For information on how to configure the jdbcpds.conf file, refer to Section 14.1.1, "JDBC Configuration File".

14.1.5.2 Installing the Driver's JAR Files

For information on how to install the driver's JAR files, refer to Section 14.1.1.2.5, "Custom Driver" .