list archives . users mailing list
 

Subject: RE: OGSA-DAI & Oracle9i

Date view Thread view Subject view Author view Attachment view

From: Brian Hills (brianh@nesc.ac.uk)
Date: Apr 07, 2003 15:43

Hi,

I thought it may be useful to share some information with the list on
the approach I used to enable OGSA-DAI 1.5 to query an Oracle 9i
database. Initially I looked at changes to OGSA-DAI configuration, but
now I dont believe its quite a simple as that :( A summary of what I
have done is listed below. I'm not advocating this is either the only
or the 'ideal' solution, but I thought it may help some of you in the
future.

Regards,

Brian

www.edikt.org

 
 

Code Changes

Changes to two source files are required, before rebuilding the code:

1) The class that initializes the JDBC connection
(JDBCDataResourceManager).

The JDBCDataResourceManager initializes a connection to a database that
uses JDBC. The OGSA-DAI 1.5 release uses this class to connect to
MySQL. Unfortunately the format of the string used to connect to MySQL:

        jdbc:mysql://host:port:database

is different to that used to connect to Oracle:

        jdbc:oracle:thin:@host:port:sid

Any attempt to connect to Oracle using the supplied code will result in
an "Invalid URL" Oracle error.

The 'initialise' method must be changed to accommodate different
database types - this can be achieved using the 'dbmsType' property.

2) The class that processes the results set from the database
(SQLStatementHandler).

The OGSA-DAI code creates a 'SQLResults' object to handle results
returned from the database. In the class constructor a call is made to
the 'beforeFirst' method to ensure that the pointer to the current row
of the results set is set to the very first row. This is known as
'scrolling' through the result set as one can move back and forward
through the results.

In order to use this 'beforeFirst' method to scroll through the results
set using the Oracle driver, some configuration is required when
creating the statement to be sent to the database. The 'setStatement'
method in the SQLStatementHandler class must be changed. Replace the
line:

 

   stmt = con.createStatement();

            

with

 

   stmt = con.createStatement(

              java.sql.ResultSet.TYPE_SCROLL_INSENSITIVE,

              java.sql.ResultSet.CONCUR_UPDATABLE);

       

If this change is not made, any query to Oracle using OGSA-DAI will
report:

 

  GRIDServiceUserException : java.lang.NullPointerException

 

Oracle will also output a message stating that 'beforeFirst' cannot be
used with a forward only results set (i.e. one can only iterate from the
first to the last row in the results set).

Libraries

The "ojdbc14.jar" file contains the driver required to connect to
Oracle. This comes with Oracle 9i or can be downloaded from the Oracle
website. This file should be placed in the
<CATALINA_HOME>\webapps\ogsa\WEB-INF\lib directory

Configuration

Oracle is configured as per the other database types in the GDSF
Configuration file except for the "location" property. This usually
takes the form of <IP>:<Port> but for Oracle use <IP>:<Port>:<SID>

Port Clashes

By default both Tomcat and Oracle use port number 8080. One of these
programs must be configured to use another port number.

 

Resolution 1: Change the Tomcat Port Number

To change the port number Tomcat uses, ensure Tomcat is not running and
edit the file:

 

<CATALINA_HOME>/conf/server.xml

 

Search for "8080" and replace this with another free port number e.g.
"8100". Note that all OGSA-DAI scripts/clients that explicitly specify
the port number must also be changed to use the new Tomcat port number.

 

Resolution 2: Change the Oracle Port Number

An article describing various mechanisms to change the Oracle port
numbers can be found at:

 

http://www.interealm.com/technotes/roby/xdb_ports.html
<http://www.interealm.com/technotes/roby/xdb_ports.html>

 

Date view Thread view Subject view Author view Attachment view