wood burning stoves 2.0*
The moose likes Servlets and the fly likes Oracle9i Connectivity thru wsad5.o Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Java » Servlets
Bookmark "Oracle9i Connectivity thru wsad5.o" Watch "Oracle9i Connectivity thru wsad5.o" New topic
Author

Oracle9i Connectivity thru wsad5.o

srinivasa reddy
Greenhorn

Joined: Sep 13, 2003
Posts: 11
hi,
I am just trying one of the example in this site,i am using Oracle 9.0.01 and WSAD5.0, i could not connect to database,
java.sql.SQLException: [Microsoft][ODBC driver for Oracle][Oracle]no result
I have included classes12.zip in the path,created the odbc driver.
help appreciated
srini
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17249
    
    6

"srini"-
Welcome to the JavaRanch! Please adjust your displayed name to meet the
JavaRanch Naming Policy.
You can change it
here.
Thanks! and welcome to the JavaRanch!
Mark


Perfect World Programming, LLC - Two Laptop Bag - Tube Organizer
How to Ask Questions the Smart Way FAQ
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17249
    
    6

Here is a quick take on my gut feeling.
I see you posted two things there "Microsoft" and "ODBC". Along with your mention of classes12.zip.
Here is what I think, and it could be wrong. But classes12.zip is the thin driver JDBC by Oracle, and your error shouldn't be mentioning Microsoft or ODBC. So I think you Driver that you are using in code is not the Oracle JDBC thin driver.
Please post your code for connection/getting the driver. Make sure you "*" out your userId and pssword, you don't want to be giving that information out.
And are you sure the classes12.zip file is in your classpath?
Hope that helps a little.
Mark
srinivasa reddy
Greenhorn

Joined: Sep 13, 2003
Posts: 11
Mark,
I have changed my Profile, is that OK or do i need to change any thing else?
hey i am new to java n j2ee technologies, if i ask any silly doubts,plz excuse me.Here i am sending the code.
import java.io.*;
import java.util.*;
import javax.servlet.*;
import java.sql.*;
import javax.servlet.http.*;
public class OriellyExamples extends HttpServlet{
Connection dbConnection;

public void service(HttpServletRequest req,HttpServletResponse
res) throws ServletException,IOException {
PrintWriter pw=res.getWriter();
try
{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

dbConnection = DriverManager.getConnection("jdbc dbc:esr","scott","tiger");
Statement smt=dbConnection.createStatement();

ResultSet r = smt.executeQuery("SELECT * FROM emp");

pw.println("<html>");
pw.println("<body>");

pw.println("<h1>hello</h1>");

while(r.next())
{
int j=r.getInt("ID");
String s = r.getString("name");
pw.println("ROW = " + " " +s+ " " + j);
}
pw.println("</body>");

pw.println("</html>");
smt.close();

dbConnection.close();
}
catch(Exception e)
{
pw.println("<html>");

pw.println("<h1>"+e+"no result</h1>");

pw.println("</html>");
}
}
}
Exception is:java.sql.SQLException: [Microsoft][ODBC driver for Oracle][Oracle]no result
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17249
    
    6


Here it shows that your aren't using Oracle's thin driver, like I had posted.
2. Your name still appears as just your first name. You need to add your last name to your login. Click on the link I had provided above and change it there. Thanks.
3. If you want to post code, we have a CODE tag that will keep all your formatting in tact and looks really nice. on my post here it is using the CODE tage. You can find the CODE button under the text area where you post a reply.

Now the part of the getConnection method that states jdbc racle ci8, mighe be slightly different than your classes12.zip. The "oci8" part I mean, it might be "oci9"
server, userid, password are things that you need to put
esr, scott, tiger respectively.
Hope that helps and works for you.
The smiley's you see are really a colon and the letter o.
Also I see actually what you did in your profile. You put both your first and last name in the first name field. there are two fields for you name, one for your first name and one for your last. Good Luck
Mark
[ September 24, 2003: Message edited by: Mark Spritzler ]
srinivasa reddy
Greenhorn

Joined: Sep 13, 2003
Posts: 11
HI,
Here i am trying to access the database(oracle 9i) and retrive the data.The code which i am giving below is working fine when i run this from a command line.but if i try to run from WSAD5.0 its not working.One pop up window is appearing on screen (Connect to localhost XDB)and asking username/password.There is some thing wrong with my WSAD settings,could any one help me?
CODE:
import java.sql.*;
import oracle.jdbc.driver.*;
class JDBCVersion
{
public static void main (String args[])
throws SQLException
{
// Load the Oracle JDBC driver
DriverManager.registerDriver
(new oracle.jdbc.driver.OracleDriver());
Connection conn = DriverManager.getConnection
("jdbc racle:thin:@localhost:1521:MyDB","scott","tiger");
// Create Oracle DatabaseMetaData object
DatabaseMetaData meta = conn.getMetaData();
// gets driver info:
System.out.println("JDBC driver version is " + meta.getDriverVersion());
Statement smt=conn.createStatement();
ResultSet r = smt.executeQuery("SELECT * FROM emp");
while(r.next())
{
int j=r.getInt("empno");
String s = r.getString("ename");
System.out.println("Employee Details = " + " " +s+ " " + j);
}
}
}
Commandline output:
JDBC driver version is 9.2.0.1.0
Employee Details = SMITH 7369
Employee Details = ALLEN 7499
Employee Details = WARD 7521
Employee Details = JONES 7566
Employee Details = MARTIN 7654
Employee Details = BLAKE 7698
Employee Details = CLARK 7782
Employee Details = SCOTT 7788
Employee Details = KING 7839
Employee Details = TURNER 7844
Employee Details = ADAMS 7876
Employee Details = JAMES 7900
Employee Details = FORD 7902
Employee Details = MILLER 7934
When i try thru WSAD its saying unotherised.
Thanks alot
srini
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17249
    
    6

So then the question I have is...
Does the classpath for wsad have your classes12.zip file?
Also, if you do put in the scott/tiger user/password in the pop up does the code then work?
Mark
srinivasa reddy
Greenhorn

Joined: Sep 13, 2003
Posts: 11
I have added the Jar file in WSAD in the following locations.
1)Window-->Preferences-->Classpath Variables-->CLASSPATH=C:/oracle/ora9i/jdbc/lib/classes12.zip
2)pROJECT-->pROPERTIES-->Java Build Path-->Libraries-->addJars
Is that ok?
After that i have created a class and a servlet,the code is running fine in class but in servlet its giving an exception.Here is the class file code and result
import java.sql.*;
import oracle.jdbc.driver.*;
class JDBCVersion
{
public static void main (String args[])
throws SQLException
{
// Load the Oracle JDBC driver
DriverManager.registerDriver
(new oracle.jdbc.driver.OracleDriver());
Connection conn = DriverManager.getConnection
("jdbc racle:thin:@localhost:1521:esr","scott","tiger");
// Create Oracle DatabaseMetaData object
DatabaseMetaData meta = conn.getMetaData();
// gets driver info:
System.out.println("JDBC driver version is " + meta.getDriverVersion());
Statement smt=conn.createStatement();
ResultSet r = smt.executeQuery("SELECT * FROM emp");
while(r.next())
{
int j=r.getInt("empno");
String s = r.getString("ename");
String d = r.getString("job");
System.out.println("Employee Details = " + " " +s+ " " + j+ " " +d);
}
}
}
RESULT:
JDBC driver version is 9.2.0.1.0
Employee Details = SMITH 7369 CLERK
Employee Details = ALLEN 7499 SALESMAN
Employee Details = WARD 7521 SALESMAN
Employee Details = JONES 7566 MANAGER
Here is Servlet Code and result:
import java.util.*;
import javax.servlet.*;
import java.sql.*;
import javax.servlet.http.*;
import oracle.jdbc.driver.*;
//import oracle.jdbc.driver.*;
class MyConnection extends HttpServlet{
public void doPost(HttpServletRequest req, HttpServletResponse res)
throws ServletException, IOException {
try{
DriverManager.registerDriver
(new oracle.jdbc.driver.OracleDriver());
Connection conn = DriverManager.getConnection
("jdbc racle:thin:@localhost:1521:esr","scott","tiger");
DatabaseMetaData meta = conn.getMetaData();
System.out.println("JDBC driver version is " + meta.getDriverVersion());
Statement smt=conn.createStatement();
ResultSet r = smt.executeQuery("SELECT * FROM emp");
while(r.next())
{
int j=r.getInt("empno");
String s = r.getString("ename");
System.out.println("Employee Details = " + " " +s+ " " + j);
}
}
catch(Exception e){
System.out.println("exception");
}
}
}
rESULT:THE PAGE CANNOT BE FOUND
My doubt is why the same code is working fine in Java class but not in the servlet?
Thanks.
Mark Spritzler
ranger
Sheriff

Joined: Feb 05, 2001
Posts: 17249
    
    6

OK, so now that we have the code working in a class, but not Servlet, then lets look at the Servlet configuration and locations.
Obviously because Servlets are run through the "web" there are some constraints to what you can and can't do, and which directories you can have access to on the Web Server.
Now is the Servlet being deployed in the correct directories, and can the Servlet get to the classes.zip file, or is that in a directory that cannot be accessed bya Servlet.
Those are the next questions. Unfortunately I can't be relyed on as an expert as this is entering an area which I am not very knowledgable in, Yet.
Mark
Jeanne Boyarsky
internet detective
Marshal

Joined: May 26, 2003
Posts: 30116
    
150

You need to add the zip file as a datasource in the server perspective for it to be found when running the servlet.


[Blog] [JavaRanch FAQ] [How To Ask Questions The Smart Way] [Book Promos]
Blogging on Certs: SCEA Part 1, Part 2 & 3, Core Spring 3, OCAJP, OCPJP beta, TOGAF part 1 and part 2
 
jQuery in Action, 2nd edition
 
subject: Oracle9i Connectivity thru wsad5.o
 
Similar Threads
connection url
error while creating datasource userdsn for oracle 10g Errorcode (998)
Establishing Connections to a Data base on a different machine
Oracle ODBC Driver Problems. Please help
Connecting to mSQL using VisualAge