?
Solved

Creating a connection with Oracle 9i

Posted on 2003-02-25
12
Medium Priority
?
418 Views
Last Modified: 2010-04-01
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
Comment
Question by:BigSi
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
  • +2
12 Comments
 
LVL 4

Expert Comment

by:thanassis
ID: 8016213
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
 
LVL 1

Expert Comment

by:YVS
ID: 8016613
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
 
LVL 1

Expert Comment

by:YVS
ID: 8016643
Missing this lines...

//To close the connection
Recordset.close();
StatementRecordset.close();
ConnRecordset.close()
0
Technology Partners: 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!

 
LVL 14

Accepted Solution

by:
kennethxu earned 255 total points
ID: 8017544
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
 
LVL 2

Author Comment

by:BigSi
ID: 8020714
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
 
LVL 14

Expert Comment

by:kennethxu
ID: 8021052
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
 
LVL 3

Expert Comment

by:allahabad
ID: 8021729
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
 
LVL 2

Author Comment

by:BigSi
ID: 8021814
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
 
LVL 14

Expert Comment

by:kennethxu
ID: 8022165
if you changed the tomcat port, just start TNSListner and you should be on your way with your existing code.
0
 
LVL 2

Author Comment

by:BigSi
ID: 8034474
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
 
LVL 14

Expert Comment

by:kennethxu
ID: 8034733
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
 
LVL 14

Expert Comment

by:kennethxu
ID: 8034785
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This is a fine trick which I've found useful many times, when you just don't want to accidentally run a batch script or the commands needs administrator rights.
New style of hardware planning for Microsoft Exchange server.
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
Suggested Courses

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question