• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Connection to Oracle

 
Aru Ven
Ranch Hand
Posts: 199
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All,
I am new to Servlets.... I need ur help.
I have a oracle database.I need to setup a connection from my servlet to the oracle database.
Oracle DB name = tstdb
loginname = user1
password = pswd.
Using the above parameters...
Can anyone help me how to make the connection to the database along with a small code of example.
thanks
Aruna
 
Aru Ven
Ranch Hand
Posts: 199
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi All,
I have written a piece of code

This code compiles fine. But When I run the servlet...nothing comes up..... not even a error message...
Please help me...
Aruna
 
Annette L'Heureux
Ranch Hand
Posts: 135
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It may be in your connection line. I had to specify more things. Here's mine:
"jdbc racle:thin:@hostname :portnumber:dbname","user","password"
You already have the last three things, but you might need to specify the hostname and the portnumber. that's how I got mine to work. What kind of oracle driver did you get?
Annette
-NOTE FROM ANGELA - I disabled smilies in this post to make it readable! This is an option available to you when you post if your post contains a series of characters that may produce a smilie!

[This message has been edited by Angela Poynton (edited January 04, 2001).]
 
Aru Ven
Ranch Hand
Posts: 199
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Anette,


But still it dosent work.
Am I missing anything here
Aruna
 
Annette L'Heureux
Ranch Hand
Posts: 135
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
yes, ":" between jdbc and Oracle and between localhost and 8080.
Try this and let me know!
annette
 
Aru Ven
Ranch Hand
Posts: 199
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ann,
I tried it like this...

but still nothing happens when I run the servlet. no error message.
Aruna
 
Annette L'Heureux
Ranch Hand
Posts: 135
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
How are you calling your servlet? Are you typing in the path in the browser? What happens? Does the browser just keep looking for something to display? You don't get a message saying that there is an error?
I can show you my code if you want. One difference is that my stuff is in a doPost instead of a doGet, but that's because I'm passing variables from a web page.
 
Annette L'Heureux
Ranch Hand
Posts: 135
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I have also included some extra files (at the very top).
These are the 4 I have:
import javax.servlet.*;
import javax.servlet.http.*;
import java.io.*;
import java.sql.*;
 
Aru Ven
Ranch Hand
Posts: 199
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ann,
I call the servlet as
http://localhost:8080/servlet/db1
db1 is the alias name for Db1.java file.
When I try to run the servlet a popup window displays
"You have started to download a file of type html/text
click "More Info" to extend Navigators Capabilities."
And it shows 4 buttons
"More Info"
"Pick App"
"Save File"
"Cancel"
if I click save file.... it says
Document Contained no data... Try later...

Aruna
 
Aru Ven
Ranch Hand
Posts: 199
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ann I have already imported all those files.....
Aruna
 
Annette L'Heureux
Ranch Hand
Posts: 135
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I think your web server is not set up properly, or you are not placing your files in the right place. When I type in the path, it knows to go check in a servlet folder. In that folder, I save the .class files, not the .java files
Try putting the compiled version in the right folder and then call it.
 
Aru Ven
Ranch Hand
Posts: 199
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Ann,
This was a part of the message displayed at the command prompt where the server was started with "httpd"

[code]
javawebserver: at com.sun.server.HandlerThread.run(Compiled Code)
javawebserver: java.lang.ClassNotFoundException: Unable to find class oracle.jdbc.driver.OracleDriver .
[code
I have imported java.sql.*;
Aruna
[This message has been edited by Aru Ven (edited January 03, 2001).]
 
Annette L'Heureux
Ranch Hand
Posts: 135
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I'm not sure what your problem is then. Your code compiles fine, you installed the jdbc driver properly, you put the compiled (.class) file in the right folder.
It should run by calling it. What is "httpd"? Were those errors you mentioned displayed in the browser screen or at a command prompt?
 
Aru Ven
Ranch Hand
Posts: 199
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It was displayed at the Command prompt.
 
Annette L'Heureux
Ranch Hand
Posts: 135
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Don't run it from there. Open up internet explorer or netscape and type it in address bar at the top. See if that runs.
 
Aru Ven
Ranch Hand
Posts: 199
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes I am running it from the browser. But As I said previously
It gives a popup window "Unknown File Type" & the message which I have described earlier.

And this is displayed at the command prompt


Aruna
 
Tano Ortiga
Greenhorn
Posts: 9
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi There,
for the first part, you were getting the message:
"You have started to download a file of type html/text Click "More Info" to extend Navigators Capabilities."
because the ContentType you used is "html/text" instead of "text/html". Try using "text/html". Or alternatively you may modify your preferrences in Netscape to include this type of application.
for the oracle error that you're getting, you need to download the jdbc driver for oracle (or probably its already in your box) and have this driver's directory included in your webserver's classpath.
I hope this helps.
Jonathan
 
Daniel Olson
Ranch Hand
Posts: 81
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
It might depend of what version of Oracle you are using. The connection string I use (for Oracle 8) looks a little different. Notice the user name and password location.

Connection con = DriverManager.getConnection("jdbcracleci8:user1/pswd@tstdb");
Originally posted by Aru Ven:
[B]Anette,


But still it dosent work.
Am I missing anything here
Aruna[/B]

 
Aru Ven
Ranch Hand
Posts: 199
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hey guys...
Thanks for ur replies....
But still I'm stuck. I have Oracle 7.3. Please tell me what would be the connect String. & Should I create An ODBC DataSource
for it ....
Can anyone tell me from where can I download the "jdbc driver for oracle ", & where should I include the path...
Aruna

[This message has been edited by Aru Ven (edited January 05, 2001).]
[This message has been edited by Aru Ven (edited January 05, 2001).]
 
Annette L'Heureux
Ranch Hand
Posts: 135
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Aru, I'm sorry you are so stuck. I know what it feels like.
I'm using oracle 7.3.4
I downloaded the oracle thin jdbc driver from the oracle website. You will need to have a membership (it's free, just sign up if you don't have one). Go to downloads, then select oracle jdbc drivers from the list. Download the driver and follow the instructions for installing it.
You need to use the connection string that I showed you the first time for your servlet.
Let me know how it goes!
Annette
[This message has been edited by Annette L'Heureux (edited January 05, 2001).]
 
Aru Ven
Ranch Hand
Posts: 199
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thanks Ann,
Lemme download it & get Back to U.
Aruna
 
Kruger Brent
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hello there,
why do you need to make connection with oracle with
Connection con = DriverManager.getConnection("jdbc Oracle:thin:@localhost 8080:tstdb","user1", "pswd");?,
where as connection is successfully made with
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection con = DriverManager.getConnection("jdbc dbc:iDiary","tsht","tsht");
Let me know if any specific reason exists?
KB
 
srikas
Greenhorn
Posts: 2
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am also getting some errors while using personal oracle 8i. i have a database called srikas and using net-config i added a listener listening on port 1521 to srikas database. the database url i am using is
"jdbc racle:thin:@localhost:1521:srikas"
The error i am getting is
java.sql.SQLException: The Network Adapter could not establish the connection
at oracle.jdbc.dbaccess.DBError.check_error(DBError.java:407)
at oracle.jdbc.driver.OracleConnection.<init>(OracleConnection.java:152)
at oracle.jdbc.driver.OracleDriver.getConnectionInstance(OracleDriver.java:214)
at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:193)
at java.sql.DriverManager.getConnection(Unknown Source)
at java.sql.DriverManager.getConnection(Unknown Source)
at dec10.main(dec10.java:54)
i tried adding another listener at port 9595, but i get the same error. can anyone help pls.
Thanks
 
Aru Ven
Ranch Hand
Posts: 199
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Kruger,
I tried ur method....
Database Name : tstdb
User : user1
Password : pswd


But When I run the servlet. A popup box comes up saying
"Document Contained No Data...
Please try later Or Contact Server Administrator"
Let me know if I am coding it properly....
Thanks in advance
Aruna

[This message has been edited by Aru Ven (edited January 05, 2001).]
 
Kruger Brent
Greenhorn
Posts: 26
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Aruna,
well check in your code that you have given earlier
res.setContentType...should be ("text/html") and not
("html/text")...
hope this is the mistake.
KB
 
Aru Ven
Ranch Hand
Posts: 199
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Yes Kruger,
I have coded it "text/html"
But Still I get the same popup box.
Aruna
 
Sean MacLean
author
Ranch Hand
Posts: 621
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Aru,
I would try getting the basic servlet running and then adding in the DB stuff. Try this,

Once this is ok, try loading the driver

If the driver doesn't load (and you've installed it) make sure that the oracle package is in the class path so the the servlet JVM can find it. The thing is, even though it compiles, the servlet is running in the JVM and, if the oracle driver classes are not available to the JVM, then the dynamic class load will fail. Also, notice that I've written the exception out to the servlet log file. This will help with the debugging. If you can't locate this log file to see the result, try getting a Writer from the responce object and write the error to the browser. Once this is sorted out, the connection stuff should be easier to figure out if there are any problems. I hope this is useful.

Sean
 
Danny Mui
Ranch Hand
Posts: 42
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Just a note from somebody that posted why the approach "Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");" isn't used...because it's slower! bridging jdbc over odbc may work but it's not a very scalable solution. The oracle Jdbc driver is tuned for oracle natively.
 
Frank Carver
Sheriff
Posts: 6920
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
"srikas",
The Java Ranch has thousands of visitors every week, many with surprisingly similar names. To avoid confusion we have a naming convention, described at http://www.javaranch.com/name.jsp . We require names to have at least two words, separated by a space, and strongly recommend that you use your full real name. Please choose a new name which meets the requirements.
Thanks.
 
Aru Ven
Ranch Hand
Posts: 199
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sean,
Any simple servlet works fine. This servlet throws an exception at this line...
Connection con = DriverManager.getConnection("jdbc dbc:tstdb", "user1", "pswd");
Please help me ....
Aruna
 
Sean MacLean
author
Ranch Hand
Posts: 621
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Aru,
If you certain that the class loading is not failing, then the probelem is either the DB setup itself or the DB connection string. Check to make sure that the DB is functioning using the command line or the Oracle Net8 Assistant test connection facility. If this works then have a look
at http://oradoc.photo.net/ora816/java.816/a81354/basic1.htm#1004265. This is the Oracle JDBC tutorial on the getConnection connection string formatting. Perhaps you're having a conflict with the SID and/or port that your DB is using. Let see if the problem is related to these issues.
Sean

[This message has been edited by Sean MacLean (edited January 08, 2001).]
 
Aru Ven
Ranch Hand
Posts: 199
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Sean,

Can u please enlighten me as of how to test the DB from the commandline or otherwise...
Thank You
Aruna
 
Satish Kasala
Greenhorn
Posts: 16
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi there,
Try this out.
/**
* @(#) OracleDBServlet.java
*
* (C) Copyright Satish Kumar Kasala
*
*/
import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;
import oracle.jdbc.driver.*;
public class OracleDBServlet extends HttpServlet{
Connection con;
public void init(ServletConfig sc) throws ServletException
{
super.init(sc);
}
public void doGet(HttpServletRequest req, HttpServletResponse res) throws ServletException, IOException
{
res.setContentType("text/html");
ServletOutputStream out = res.getOutputStream();
String TestValue=req.getParameter("TestValue");
String ServerName = req.getParameter("ServerName");
out.println("<html>");
out.println("<head><title>Oracle Database Servlet</title></head>");
out.println("<body>");
//Establish connection to the Database (Oracle JDBC)
try{
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
String ConnectString = "jdbc racle:thin:@(description=(address=(host="+ServerName+")(protocol=tcp)(port=1521))(connect_data=(sid=orcl)))";
con = DriverManager.getConnection (ConnectString,"scott", "tiger");
}
catch(Exception e){
out.println(e.toString());
}
//Execute SQL Statement
try{
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("select * from tab");
out.println("<h3>Table Names : </h3>");
while(rs.next()){
String TableName = rs.getString(1);
out.println("<P>"+TableName+"</p>");
}
}
catch(Exception e){
out.println(e.toString());
}
//Calling a Stored Procedure
try{
CallableStatement cs = con.prepareCall("{call TestProcedure(?)}");
cs.setString(1,TestValue);
cs.execute();
}
catch(Exception e){
out.println(e.toString());
}
//Using REFCURSOR
try{
CallableStatement cs = con.prepareCall("{? = call PackageName.ProcedureName(?)}");
cs.registerOutParameter (1, OracleTypes.CURSOR);
cs.setString(2,TestValue);
cs.execute ();
ResultSet rs = (ResultSet)cs.getObject (1);
while(rs.next()){
String ReturnValue=rs.getString("1");
out.println("<p>"+ReturnValue+"</p>");
}
}
catch(Exception e) {
out.println(e.toString());
}

out.println("</body></html>");
try{
con.close();
}
catch(Exception e){
out.println(e.toString());
}
}
}
the difference to be noted here is the connect string used to connect to the oracle database.
String ConnectString = "jdbc racle:thin:@(description=(address=(host="+ServerName+")(protocol=tcp)(port=1521))(connect_data=(sid=orcl)))";
ServerName : specify the name of the box on which oracle db is running eg: oracleserver
sid: specify the oracle instance name.
Hope this helps
Satish
 
pk mast
Greenhorn
Posts: 5
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
goto control panel and make a dsn name using microsoft driver for oracle(say dsnname).now modify your code as folllows:
Class.forName("sun.java.Jdbc.Odbc.JdbcOdbcDriver");
Connection con=DriverManager.getConnaction("jdbcdbc:dsnname");
[This message has been edited by pk mast (edited January 09, 2001).]
 
Sean MacLean
author
Ranch Hand
Posts: 621
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
If you're working on a unix box, for example, (and have sqlplus installed) you just type
sqlplus username/password
Then do a simple select to make sure that everything is ok.
Otherwise, if you have the Oracle tools on you own machine you can use Oracle Net8 Easy Config. Launch that and then create a new Service using you DB params. At the end of the setup you can select "test" to make sure that a connection is possible. If you already have a service setup through this Oracle tool you can simply select "test" from the first menu. Once you insure that this is ok then you can focus on the code.
Sean
 
Gangadhara Thippaiah
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Aruna,
I made following changes in your code(2nd post of this discussion) & got the expected output.
1. Imported java.io.*; & java.sql.*;
2. Set content type "text/html" instead of "html/text".
3. Class.forName("sun.jdbc.odbc.JdbcOdbcDriver") instead of
Class.forName("oracle.jdbc.driver.OracleDriver")
4. DriverManager.getConnection("jdbc dbc:myname","uname", "pword")
instead of
DriverManager.getConnection("jdbc.Oracle:tstdb","user1", "pswd")
5. String bc = rs.getString(2) instead of
int bc = rs.getInt(2)
Make sure that "myname" exists in ODBC datasource admin of control panel.
Code is compiled & displaying the table without any exceptions.
Gangadhara
 
Gangadhara Thippaiah
Greenhorn
Posts: 7
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Aruna,
I made following changes in your code(2nd post of this discussion) & got the expected output.
1. Imported java.io.*; & java.sql.*;
2. Set content type "text/html" instead of "html/text".
3. Class.forName("sun.jdbc.odbc.JdbcOdbcDriver") instead of
Class.forName("oracle.jdbc.driver.OracleDriver")
4. DriverManager.getConnection("jdbc dbc:myname","uname", "pword")
instead of
DriverManager.getConnection("jdbc.Oracle:tstdb","user1", "pswd")
5. String bc = rs.getString(2) instead of
int bc = rs.getInt(2)
Make sure that "myname" exists in ODBC datasource admin of control panel.
Code is compiled & displaying the table without any exceptions.
Gangadhara
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic