• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 421
  • Last Modified:

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
0
BigSi
Asked:
BigSi
  • 5
  • 3
  • 2
  • +2
1 Solution
 
thanassisCommented:
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
0
 
YVSCommented:
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
0
 
YVSCommented:
Missing this lines...

//To close the connection
Recordset.close();
StatementRecordset.close();
ConnRecordset.close()
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
kennethxuCommented:
private String bnDbDriver = "oracle.jdbc.driver.OracleDriver";
private String bnDbID = "jdbc:oracle:thin:@serverip:1521:SID";
private String bnDbUser = "MyUserName";
private String bnDbPassword = "MyPassword";

serverip the ip address of your oracle server, if you install oracle locally, you have use "localhost", by default oracle run one port 1521, if you changed it, please change that accordingly. SID is oracle server id, it also know as database name.

Important: if you haven't did it, you must find classes12.zip, copy it to <tomcat-install-dir>/common/lib, and MUST rename it to classes12.jar
0
 
BigSiAuthor Commented:
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


0
 
kennethxuCommented:
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" />
0
 
allahabadCommented:
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());
   }
%>
0
 
BigSiAuthor Commented:
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!
0
 
kennethxuCommented:
if you changed the tomcat port, just start TNSListner and you should be on your way with your existing code.
0
 
BigSiAuthor Commented:
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!
0
 
kennethxuCommented:
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.
0
 
kennethxuCommented:
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.
......
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 5
  • 3
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now