Skip Headers
Oracle® Application Server Reports Services Publishing Reports to the Web
10g Release 2 (10.1.2)
B14048-02
  Go To Documentation Library
Home
Go To Product List
Solution Area
Go To Table Of Contents
Contents
Go To Index
Index

Previous
Previous
Next
Next
 

9 Configuring and Using the JDBC PDS

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

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:

9.1 JDBC Configuration File

The jdbcpds.conf file, located in the ORACLE_HOME\reports\conf directory, is the Oracle Reports JDBC PDS configuration file. This file is preconfigured for the:

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

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.

Reports Builder reads and caches the entries in the jdbcpds.conf when it is invoked. Restart 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:

Example

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

    <!-- DTD section - Not to be modified --> 
    
    <!DOCTYPE jdbcpds [
    <!ELEMENT jdbcpds  (driverInfo)>
    <!ELEMENT driverInfo (driver+)>
    <!ELEMENT driver (property*)>
    <!ATTLIST driver  name          CDATA  #REQUIRED
                     sourceDatabase (oracle    |
                                     sqlserver |
                                     sybase    |
                                      db2       |
                                      informix  |
                                      odbc      |
                                      other) "oracle"
                      mainProtocol        ( jdbc ) "jdbc"
                      subProtocol   CDATA  #REQUIRED
                      connectString     CDATA  #REQUIRED
                      class         CDATA  #REQUIRED 
                      connection    CDATA  #REQUIRED
                      loginTimeout      CDATA  "5"  
    >
    <!ELEMENT property EMPTY>
    <!ATTLIST property   name  CDATA  #REQUIRED
                         value CDATA  #REQUIRED >
    
    ]>
    
    
    <!-- Add or modify the following section for your driver information -->
    <!-- Following drivers are available out-of-box in 9iAS -->
    
    <jdbcpds>
     <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 9-1 outlines the various attributes that can be associated with a driver.

Table 9-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 9-3, Table 9-4, Table 9-5, Table 9-6, and Table 9-7 for more information on sample connection information.

Figure 9-1 shows a list of all drivers configured in the jdbcpds.conf file.

Figure 9-1 JDBC Connect Dialog Box in Reports Builder

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

9.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 need to create an ODBC data source. Refer to Windows help, for more information on how to create an ODBC data source.


    Note:

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

9.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 9-1) lists the entries for the set of Merant DataDirect drivers provided by Oracle.

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

The drivers provided by Oracle for use with Oracle Application Server / Oracle Developer Suite are:

You can also install and configure a Custom Driver for use with Oracle Application Server 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 Application Server and Oracle Developer Suite directory.

  2. Include an entry in REPORTS_CLASSPATH to make the files available to Reports Builder and OracleAS 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. 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_HOME\reports\conf directory. Refer to Table 9-1 for more information on the parameters. Refer to the relevant driver in this section for an example.

9.1.2.1 Sybase Driver

  1. Install the relevant JAR files in your Oracle Application Server 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 Reports Builder and OracleAS Reports Services.


    Note:

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

    1. 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_HOME\reports\conf directory. Refer to Table 9-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>
      

9.1.2.2 DB2 Driver

  1. Install the relevant JAR files in your Oracle Application Server 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 Reports Builder and OracleAS Reports Services.


    Note:

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

    1. 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_HOME\reports\conf directory. Refer to Table 9-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> 
      

9.1.2.3 SQL Server Driver

  1. Install the relevant .jar files in your Oracle Application Server 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 Reports Builder and OracleAS Reports Services.


    Note:

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

    1. 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_HOME\reports\conf directory. Refer to Table 9-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> 
      

9.1.2.4 Informix Driver

  1. Install the relevant JAR files in your Oracle Application Server 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 Reports Builder and OracleAS Reports Services.


    Note:

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

    1. 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_HOME\reports\conf directory. Refer to Table 9-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> 

9.1.2.5 Custom Driver

Any driver that is not provided by Oracle must be installed and configured:

  1. Install the relevant JAR files in your Oracle Application Server and Oracle Developer Suite directory.

  2. Include an entry in REPORTS_CLASSPATH to make the files available to Reports Builder and OracleAS 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. 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_HOME\reports\conf directory. Add relevant driver configuration information to the jdbcpds.conf file. Refer to Table 9-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

9.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 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 9-2 Select a Data Source Type

    Description of Figure 9-2  follows
    Description of "Figure 9-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 9-3 Calling a stored procedure

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

    • Table 9-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.3.69, "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 OracleAS Reports Services.

    2. Enter the connection information (user name, password, and database name) for the driver type. Refer to Table 9-3, Table 9-4, Table 9-5, Table 9-6, and Table 9-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 9-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 connectString.

  7. Click OK to execute the JDBC query.

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

    Figure 9-4 Query Description

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

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

9.2.1 Sample Connection Information

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

Table 9-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 9-4 Oracle Thick Driver

Property Value

Username

Reports

Password

Welcome

Database

n123

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


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


9.3 Running a JDBC Report Using OracleAS 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:

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.

9.4 Troubleshooting Information

This section lists:

9.4.1 Error Messages

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

Table 9-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 9-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 9-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 reports/conf directory.

Ensure that the jdbcpds.conf file is available in the reports/conf 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.


9.4.2 Trace Information

Use the detailed trace information (ORACLE_HOME\reports\logs\) 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 9-1 for sample design-time trace output.

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

Sample trace output

Example 9-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 9-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

9.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 10g 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:

9.5.1 Configuring the jdbcpds.conf File

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

9.5.2 Installing the Driver's JAR Files

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