Link to home
Start Free TrialLog in
Avatar of BigSi
BigSi

asked on

Creating a connection with Oracle 9i

Hi,
I am running tomcat 4.1.18 and I am trying to create a connection with my oracle database in jsp. I have done this before using mySQL but am not sure how to get it to work with Oraccle 9i.

Previously my connection was made like this (for mysql)
(the java class)
--------------------------------------------------------
private String bnDbDriver = "org.gjt.mm.mysql.Driver@;
private String bnDbID = "jdbc.msql://localhost/hc99sl1db"
private String bnDbUser = "MyUserName"
private String bnDbPassword = "MyPassword"

Class.forName(bnDbDriver);
Connection myConnection = DriverManager.getConnection(bnDbId, bnDbUser, bnDbPassword);
------------------------------------------------------
this would then allow me to access my database through queries!

I assume it's just my connection properties that have to be modified to get this working, but have not managed it so far. it would be great if anyone could help!
Thanks!
Si
Avatar of thanassis
thanassis

import java.sql.*;
import oracle.jdbc.driver.OracleDriver;

public class Database
{
  Connection con;
   
  public Database()
  {

  }
 
 public void makeConnection() throws Exception
  {
    DriverManager.registerDriver(new OracleDriver());
    con = DriverManager.getConnection("jdbc:oracle:thin:@xxx.xxx.xxx.xxx:pppp:sss", "uuu", "zzz");
  }

  public void closeConnection() throws SQLException
  {
   this.con.close();
  }  

  public Connection getConnection()
  {
    return(this.con);
  }
}

// where xxx.xxx.xxx.xxx: the Server's IP
         pppp: the port
         sss: the SID
         uuu: username
         zzz: password
String Connection_DRIVER = "oracle.jdbc.driver.OracleDriver";
String Connection_STRING = "jdbc:oracle:thin:@[host]:[port]:[schema]";
String Connection_USERNAME = [username]
String Connection_PASSWORD = [password]

Obs: I use port 1521.

//Select the driver
Driver DriverRecordset = (Driver)Class.forName(Connection_DRIVER).newInstance();
//Get the connection
Connection ConnRecordset = DriverManager.getConnection(Connection_STRING,Connection_USERNAME,Connection_PASSWORD);
//Prepare the SQL
PreparedStatement StatementRecordset = ConnRecordset.prepareStatement([SQL]);
//Execute the SQL
ResultSet Recordset = StatementRecordset.executeQuery();
boolean Recordset_isEmpty = !Recordset.next();
boolean Recordset_hasData = !Recordset_isEmpty;

//To get an object
Recordset.getObject([FIELD NAME]);
//To proceed with next record
Recordset_hasData = Recordset.next();

Hope it helps
Missing this lines...

//To close the connection
Recordset.close();
StatementRecordset.close();
ConnRecordset.close()
ASKER CERTIFIED SOLUTION
Avatar of kennethxu
kennethxu

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of BigSi

ASKER

Thanks very much for your responses, you have been very helpfull, and I really appreciate it, allthough as yet I am still unable to create a successfull connection :(

I think this may be because I am getting a port clash on port :8080, because after I had installed tomcat 4.1.18 I then installed oracle 9i. I noticed tomcat had problems when starting up and when I accessed localhost:8080 it prompted me to enter a user name and password for XDB.

The only way I could work out to prevent this was to disable the service "oraclehc99sl1OdbTNSListener", this then enabled me to run tomcat ok until now.

I have successfully extracted the oracle connection classes to the correct folders which seem to have changed a previous error message I catch which now returns:

Invalid Oracle URL specified

I am wondering if this is due to me closing the TNSListner service or would it be due to me getting my connection string wrong?
----------------------------------------------------------
String Connection_DRIVER = "oracle.jdbc.driver.OracleDriver";
String Connection_STRING = "jdbc:oracle:thin:@localhost:1521:oracDB";
String Connection_USERNAME = "MyUsernameHere"
String Connection_PASSWORD = "MyPasswordHere"
----------------------------------------------------------

Im pretty sure my SID is correct (oracDB) as it the one present when I enter the enterprise manager console and is under the Databases node, but not 100% sure on the port - is there any way I could confirm what port I should be using?

other than that, I do think it has got some thing to do with that lsitener service - maybe I need to configure that XDB thingy to use a different port (if thats possible!)

Thanks very much for your help, I hope I get sorted soon!
Si


you must enable oralce TNSListener, otherwise, you won't be able to connect.

you can config tomcat to use another port, edit <tomcat-install-dir>/config/server.xml, for example, change to 80:

    <!-- Define a non-SSL Coyote HTTP/1.1 Connector on port 8080 -->
    <Connector className="org.apache.coyote.tomcat4.CoyoteConnector"
               port="80" minProcessors="5" maxProcessors="75"
               enableLookups="true" redirectPort="8443"
               acceptCount="100" debug="0" connectionTimeout="20000"
               useURIValidationHack="false" disableUploadTimeout="true" />
Try this sample JSP to test the connection. I have tested it with Weblogic 5.1 and oracle 9i.

<%@ page language="java" session="true" %>
<%@ page import="java.sql.*,oracle.jdbc.driver.*,oracle.sql.*" %>

<%
    String DRIVER = "oracle.jdbc.driver.OracleDriver";
    // i think you may have to change sid name here.
   // Typically port is 1521.    
String URL = "jdbc:oracle:thin:@localhost:1521:orcl";
 
    String USERID = "scott"; // change here
    String PASSWORD = "tiger"; // change here
    try {
         out.println("Trying to get Connection...");
         Class.forName("oracle.jdbc.driver.OracleDriver");
         Connection con = DriverManager.getConnection(URL,USERID,PASSWORD);
         out.println("Got Connection...");

   }
   catch (Exception e){
      out.println(e.getMessage());
   }
%>
Avatar of BigSi

ASKER

Ok - I managed to change my port for tomcat, but i'll try that piece of code tomorrow night & i'll let you know how I get on!

Thanks once again for your input, its really appreciated!
if you changed the tomcat port, just start TNSListner and you should be on your way with your existing code.
Avatar of BigSi

ASKER

Thanks everyone for your help, I now have managed to get it all working which was down to changing my port and unzipping those drivers.

I think it failed to work oce after I done that due to me either having part of my oracle SID in uppercase (which is how it should of been) or it was probably down to me being loggged in to oracle allready in sqlplus with the username & password I was using in my java file!

Thanks once again for your help!
glad to kwon your code is working.
>> unzipping those drivers.
you actually don't have to unzip them, all you need to do is rename the class12.zip to class12.jar and put the jar into tomcat common/lib dir as I mentioned in my first comment.
BTW, is there any reason a "B" grade? is there something wrong in my comments?
http://www.cityofangels.com/Experts/Member_Tips.htm
Tip #11:
......
When you grade, remember that this is not school. The letter grades have a completely different meaning and scale. An A should be given when the problem is solved, or when there is no solution but workarounds or alternatives were provided.


If the experts did not directly solve the problem, but gave information that led to the solution, you should still consider an A. If a B is given, you should include an explanation of the inferior grade, or even an opportunity for the expert to upgrade to an A with additional information.

Never give a C grade unless you first let the expert know your intention, and give them an option to decline receiving the grade. Many experts will not accept a C grade as it represents a failure. Most experts consider a C grade to be insulting and abusive. Many of the top-experts will refuse to comment in questions where the member has a history of awarding poor grades. The grades you give are carried in your profile, and many top-experts look at that grading history in setting the priorities where they will commit their time.
......