Re: Cocoon and Oracle

Mon, 09 Aug 2004 18:48:38 -0700

Schultz, Gary - COMM wrote:
I would like to thank all who have responded to my inquiry.

Here is some additional information.

Connection is defined in cocoon.xconf as

jdbc:oracle:thin:@servername:1521:oracleserviceID

I checked with our DBA and the server hosting cocoon successfully connected
to the server hosting Oracle via Sqlplus. Windows ODBC uses the Oracle
service ID when creating DSN's, so we know the network communication is
functioning properly through that. In PHP ADODB, I use the DSN to connect to
the Oracle database and it works. No timeouts. 

Could it be related to the location of the jdbc? I've tried with
classes12.jar in Cocoon web-inf/lib folder, in Tomcat Common/lib folder and
in the classpath.

To recall, my original inquiry was that we want to query a table in Oracle
and display the results in a html table. It has been decided to go with PHP
and ADODB. We can get this done in a relatively quick manner. Time
permitting, we will take another look at doing this through Cocoon via SQL
Transformer or ESQL. This is my first Cocoon set back. Otherwise, Cocoon has
been great for our organization. Keep up the good work.

Gary T. Schultz
Web Technical Administrator / GIS Coordinator
Wisconsin Department of Commerce
6th Floor
P.O. Box 7970
Madison, WI 
1-608-266-1283

-----Original Message-----
From: Ugo Cei [mailto:[EMAIL PROTECTED]]
Sent: Monday, August 09, 2004 12:05 PM
To: [EMAIL PROTECTED]
Subject: Re: Cocoon and Oracle


Il giorno 09/ago/04, alle 18:49, Schultz, Gary - COMM ha scritto:

  
Is there a simple way I can check to make sure the Cocoon - Oracle
connection is working?
    

How is your connection defined in cocoon.xconf? Your JDBC URL should be 
something like:

jdbc:oracle:thin:@<hostname>:<port>:<sid>

If it is, try to do a "telnet <hostname> <port>" and see if you can 
connect. You shouldn't actually see anything but "Connected to 
<hostname>" and then telnet should quit when you press Enter.

If you have the Oracle client installed, try to see if you can connect 
with Sqlplus. The fact that the connection times out without giving any 
error makes me think you have some problems with your network setup.

	Ugo
Gary,

I think I can help here.  First of all, sign up for an account at Oracle Technology Network, http://otn.oracle.com , its free.  Next, go to http://www.oracle.com/technology/software/tech/java/sqlj_jdbc/htdocs/jdbc101020.html .  If you are using a version of J2SE prior to 1.4, then download class12.jar. Otherwise if you are using J2SE 1.4 or later, then download ojdbc14.jar.  While you are there, you might as well download demo.tar, the JavaDoc, and README.  These drivers will work with Oracle 7.3.3 to the latest version of 10g.

Next, talk to your DBA and get three items of information:

hostname - this is the machine name of the server that is running Oracle as it would be found using ping via DNS
port - by default this is 1521 (or sometimes 1526 for a second listener), but the DBA could have used another port
SID - this is the Oracle SID if using 8 or earlier, otherwise it is know as the service name for 8i and later.  if the service name is using a dot notation, such as orcl.acme.com, you can usually use just orcl for this value.  The default is orcl.

Once done, your connect string will look similar to:
jdbc:oracle:thin:@oraclehostmachine.acme.com:1521:orcl

If you have a local version of the Oracle client on your machine that you are using with an ODBC DSN, then you can get some of the information there.  You will have line called, "TNS Service Name".   Remember that name.   Next, go to where your Oracle client install is, oftentimes (for 9i) it is c:\ora92.  In that directory structure, you will find network/admin/tnsnames.ora.  You will find your TNS Service Name there along with the information you need above.


Test your work with a program similar to:

import java.sql.*;
public class Test
{
    private static final String ORACLE_JDBC_DRIVER = "oracle.jdbc.driver.OracleDriver";
    private static final String ORACLE_DATABASE_URL = "jdbc:oracle:thin:@yourhost:1521:orclSID";
    private static final String ORACLE_DATABASE_USERNAME = "scott";
    private static final String ORACLE_DATABASE_PASSWORD = "tiger";
    private static Connection conn;
public static test
 {

        try
        {
            Class.forName(JDBC_DRIVER);
            conn = DriverManager.getConnection(DATABASE_URL, DATABASE_USERNAME, DATABASE_PASSWORD);
            conn.close();
        }
        catch (Exception e)
        {
            System.err.println(e.getMessage());
            e.printStackTrace();
        }
    }
    
    public static void main(String[] args)
    {
        test();
    }
}

If if works here without throwing an exception, then it will work in Cocoon.

Ed

Reply via email to