Solved

Connecting to Oracle from JSP via JDBC

Posted on 2002-03-27
17
4,325 Views
Last Modified: 2013-11-23
hi,

I am trying to connect to an Oracle 8.0.6 database through JDBC and a JSP page.

I am running XP with JDK1.3,Tomcat 4.0 and IIS.

I have tried the following code in JSP:

Connection conn = null;
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
OR
Class.forName("oracle.jdbc.driver.OracleDriver");

and

conn = DriverManager.getConnection(
                          "jdbc:oracle:oci:@alias",
                          "user", "pass");

I get various errors in the browser including:

Class oracle.jdbc.driver.OracleDriver not found.

SQLException: Sub Protocol must be specified in connection URL

I have changed classes files around, changed the classpath, changed the code, but everytime I get some or other error message!!


Maybe I can start with the simpler things like:

Which oracle classes file should I be using? 111,102 or 12?

I am clueless and very frustrated at this stage.

Any help will be appreciated.

Pierre


0
Comment
Question by:barnarp
[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
  • 9
  • 3
  • 2
  • +2
17 Comments
 
LVL 2

Expert Comment

by:coreyit
ID: 6898766
First thoughts,

It seems that oracle.jdbc.driver.OracleDriver is not found in your classpath. If you are using a ".zip" file containing the the Oracle packages, you may need to do either of the following:
- include the path to the zip file (not just the directory it's contained in) in your classpath
- rename the zip file to .jar which will enable the app server to access it as long as it resides within a directory in the classpath

I had some initial troubles getting Tomcat 4 to pick up additions to the classpath that were not in the default locations. Eventually I found that adding to the CP variable in catalina.sh and catalina.bat and then running from the command line as
    $CATALINA_HOME/bin/catalina.sh run &
or
    %CATALINA_HOME%/bin/catalina.bat run
seems to work best.

-corey
0
 

Author Comment

by:barnarp
ID: 6898830
hi,

I have setup a classpath by adding a system variable called 'CLASSPATH' with the following value:
e:\tomcat\classes\classes12.zip;e:\jbuilder2\java\lib\classes.zip;e:\tomcat\classes;e:\jdk1.3.1_02\bin;e:\jdk1.3.1_02\lib

I assume this is correct?

I also have the file classes12.zip in two locations:
e:\tomcat\classes and
e:\jdk1.3.1_02\jre\lib\ext

It seems whenever I startup the Tomcat server the classes12.zip in the JRE folder is used instead of e:\tomcat\classes.

I am not sure if this is correct?

I still get the following message:

Generated servlet error:
E:\tomcat\work\localhost\_\db$jsp.java:67: Class oracle.jdbc.driver.OracleDriver not found.
                      DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
                                                                         ^
1 error


Any ideas?

should I still rename classes12.zip to classes12.jar?
should I then also add e:\tomcat\classes\classes12.jar to the CLASSPATH?
How do I add to the cp variable into the catalina bat and sh file?

0
 
LVL 3

Expert Comment

by:absong
ID: 6899621
aha !!!
your classpath did not include oracle Java classes. in my computer, it is set up to be:

CLASSPATH=...(all the other junk)...;d:\oracle\ora81\jdbc\lib\nls_charset12.zip;d:\oracle\ora81\jdbc\lib\classes12.zip
0
Independent Software Vendors: 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 19

Expert Comment

by:Jim Cakalic
ID: 6899852
The Tomcat docs indicate that the standard Tomcat 4 startup scripts ($CATALINA_HOME/bin/catalina.sh or %CATALINA_HOME%\bin\catalina.bat) totally ignore the contents of the CLASSPATH environment variable itself, and instead build the System class loader from the following repositories:
 
$CATALINA_HOME/bin/bootstrap.jar - Contains the main() method that is used to initialize the Tomcat 4 server, and the class loader implementation classes it depends on.

$JAVA_HOME/lib/tools.jar - Contains the "javac" compiler used to convert JSP pages into servlet classes.

For that reason, simply adding classes12.zip to your system CLASSPATH has no effect. The ClassLoader HOWTO at http://jakarta.apache.org/tomcat/tomcat-4.0-doc/class-loader-howto.html gives an overview of Tomcat's classloading and recommendations on where to put classes and jars. I'd suggest that you might want to put something like classes12.zip in either the Shared ClassLoader space which is under $CATALINA_HOME/lib, or in the WEB-INF/lib of your webapp. However, if you put it in one of these locations, you should change the name to classes12.jar. The ClassLoaders will automatically find jar files in their managed lib directories but will not automatically find zip files.

Best regards,
Jim Cakalic
0
 
LVL 2

Expert Comment

by:coreyit
ID: 6900315
absong,
actually barnap's classpath *did* include the Oracle classes. It was the first item.

barnap,
As jim_cakalic clarified, your system classpath won't be used by tomcat. As I mentioned, you *can* add to the classpath that tomcat uses by modifying the CP variable in the startup scripts (catalina.sh and catalina.bat).

The slicker (and more portable) alternative is the renaming and specific placement of the package, again as jim_cakalic clarified.

I think his description of that technique covers it pretty well, and that's really the best method.

If you do want to modify Tomcat's runtime classpath, I'd be happy to help with that. It's not often that useful though and the downside is that you must run Tomcat from the command line that by calling one of those scripts. Another drawback is that you would need to remember to perform the same change to any environment you move your webapp too (the portability issue).

-corey
0
 
LVL 3

Expert Comment

by:absong
ID: 6900427
i am still using Tomcat 3, guess it's outdated
0
 

Author Comment

by:barnarp
ID: 6901753
Thanks guys for all your comments thusfar.

Before I try some of the suggestions, just a quick question. I need to connect to a Oracle 806 database, and have Oracle client 805 on my machine (which acts as the webserver and client at the moment).

I have downloaded:

Oracle9i 9.0.1 JDBC Drivers for use with JDK 1.2.x/JDK 1.3.x for NT from the oracle technet and got the classes12.zip file and placed it in the jre and tomcat dirs.

Now when I run the page, I get:

oracore9.dll not found WITH
E:\orant\BIN\ocijdbc9.dll: Can't find dependent libraries

obviously, I dont have 9i installed, I thought that classes12.zip was backwards compatible, but somewhere it picks that its a 9i class and want to load it?

I also copied the two dll's ocijdbc9 etc to [oracle_home]\bin, which propably is not the problem, cause when I remove them and run again, I get "ocijdbc9" not found in ora home.

when I use classes111.zip or classes102.zip which I downloaded from oratech:
 Version 8.0.5.2 JDBC(Thin and OCI) for NT  

and restart tomcat, I get the following message in the browser:

SQLException: Sub Protocol must be specified in connection URL

I also noticed that if I startup tomcat, it uses the classes*.zip file in the e:\jdk1.3_02\jre\ext. Is this correct?

Sorry for the long comment, I just need to try and figure this out!


Pierre
0
 
LVL 3

Expert Comment

by:shyamkumarreddy
ID: 6901763
Hey barnarp
I can see OCI in the code. So does Oracle and OCI drivers are installed in Web server which is accessing this oracle. If not u need to install the OCI drivers in the Web Server. And one more thing. I don't think u need to worry more abt classes.zip. Since most of the OCI are machine dependent like dll.
Can you be more clear.

Shyam
0
 

Author Comment

by:barnarp
ID: 6901818
Thanks guys for all your comments thusfar.

Before I try some of the suggestions, just a quick question. I need to connect to a Oracle 806 database, and have Oracle client 805 on my machine (which acts as the webserver and client at the moment).

I have downloaded:

Oracle9i 9.0.1 JDBC Drivers for use with JDK 1.2.x/JDK 1.3.x for NT from the oracle technet and got the classes12.zip file and placed it in the jre and tomcat dirs.

Now when I run the page, I get:

oracore9.dll not found WITH
E:\orant\BIN\ocijdbc9.dll: Can't find dependent libraries

obviously, I dont have 9i installed, I thought that classes12.zip was backwards compatible, but somewhere it picks that its a 9i class and want to load it?

I also copied the two dll's ocijdbc9 etc to [oracle_home]\bin, which propably is not the problem, cause when I remove them and run again, I get "ocijdbc9" not found in ora home.

when I use classes111.zip or classes102.zip which I downloaded from oratech:
 Version 8.0.5.2 JDBC(Thin and OCI) for NT  

and restart tomcat, I get the following message in the browser:

SQLException: Sub Protocol must be specified in connection URL

I also noticed that if I startup tomcat, it uses the classes*.zip file in the e:\jdk1.3_02\jre\ext. Is this correct?

Sorry for the long comment, I just need to try and figure this out!


Pierre
0
 

Author Comment

by:barnarp
ID: 6901875
hi,

It seems I am slowly getting somewhere.

I am using e:\tomcat\lib to put in the oracle classes*.jar files, and it seems tomcat is now using them instead of the .zip file in the jre dir, as I have removed any other oracle classes**.* files from the pc.

I still have the same problem now as stated in the previous comment.

If i use classes.jar/classes111.jar in the tomcat\lib directory I still get:

SQLException: Sub Protocol must be specified in connection URL

If i use classes12.jar in the tomcat\lib directory I get:

'This application has failed to start because oracore9.dll was not found.'

Any ideas?

0
 

Author Comment

by:barnarp
ID: 6901952
hi,

It seems I am slowly getting somewhere.

I am using e:\tomcat\lib to put in the oracle classes*.jar files, and it seems tomcat is now using them instead of the .zip file in the jre dir, as I have removed any other oracle classes**.* files from the pc.

I still have the same problem now as stated in the previous comment.

If i use classes.jar/classes111.jar in the tomcat\lib directory I still get:

SQLException: Sub Protocol must be specified in connection URL

If i use classes12.jar in the tomcat\lib directory I get:

'This application has failed to start because oracore9.dll was not found.'

Any ideas?

0
 

Author Comment

by:barnarp
ID: 6901987
hi,

It seems I am slowly getting somewhere.

I am using e:\tomcat\lib to put in the oracle classes*.jar files, and it seems tomcat is now using them instead of the .zip file in the jre dir, as I have removed any other oracle classes**.* files from the pc.

I still have the same problem now as stated in the previous comment.

If i use classes.jar/classes111.jar in the tomcat\lib directory I still get:

SQLException: Sub Protocol must be specified in connection URL

If i use classes12.jar in the tomcat\lib directory I get:

'This application has failed to start because oracore9.dll was not found.'

Any ideas?

0
 

Author Comment

by:barnarp
ID: 6902071
sorry for all the duplicates,

I keep refreshing the page, and didn't release it gets posted each time.

Pierre
0
 
LVL 19

Accepted Solution

by:
Jim Cakalic earned 250 total points
ID: 6902548
Hi, Pierre.

The OCI DLLs are involved because your database connection URL, from your 1st post, is "jdbc:oracle:oci:@alias". Oracle has multiple drivers. The OCI driver is type 2 -- native code with a JDBC-compliant wrapper. The so-called "thin" driver is type 4 -- 100% Java. The URL is what indicates whether you want to use the oci or thin driver. If you use the thin driver then all you need is classes12.zip/jar.

To use the thin driver instead of oci, change the URL to "jdbc:oracle:thin:@alias" and voila!

If you really want to use the oci drivers then you need to be sure the required DLLS are in a folder named by your system's PATH environment variable. (It shouldn't be necessary to reboot after changing this system setting but your mileage may vary.)

Why would you choose the oci driver over the thin driver? Primarily if you are running on a non-TCP network. The thin driver only works on TCP networks. There are a few other minor differences that you can ferret out from the JDBC FAQ and fact sheets here:
    http://otn.oracle.com/tech/java/sqlj_jdbc/htdocs/jdbc_faq.htm
    http://otn.oracle.com/tech/java/sqlj_jdbc/htdocs/factsht.htm

Many people seem to think that simply because it uses native code the oci driver will outperform the thin driver. A whitepaper from Oracle originally made that claim, although the performance differences were bordering on the negligible. More recent research does not seem to support any oci performance benfit. For your reference:
    http://www.oreilly.com/catalog/jorajdbc/chapter/ch19.html

So to summarize. You did the right thing by cleansing your system of various versions of the driver zip/jar files. Make sure that you have one version and that it is the version you want. The 9.0.1 version is backwards compatible to all 8.x database releases. Per my previous instructions, use the jar extension and have a copy in one of the "auto-searched" lib directories referenced by the Tomcat ClassLoaders. Use the thin driver to eliminate your dependence on DLLs (easiest) or put the DLLs in a folder named by your system's PATH variable.

Best regards,
Jim Cakalic
0
 

Author Comment

by:barnarp
ID: 6912398
Jim,

Thanks for the info, but

When I use the thin driver, I get the following error message:

javax.servlet.ServletException: Io exception: SO Exception was generated ?


Pierre
0
 

Author Comment

by:barnarp
ID: 6912442
Thanks Jim and Corey for all your help.

I forgot to include the hostname and port with the thin driver connection, Eventually all is working now with the thin driver!

For old times sake:

I am struggling to comprehend the following :

Why do I get the following message when using the OCI driver?

no ocijdbc9 in java.library.path

My question really is, why is it looking for an oracle 9 dll in the first place?

What script or command in jre/jdk/tomcat says to look for oracle 9 dll instead of oracle 8 which I have in the
path?

Regards

Pierre
0
 
LVL 19

Expert Comment

by:Jim Cakalic
ID: 6913151
Hi, Pierre.

It is the Java driver code itself in classes12.zip that is looking for a specific dll. Since the oci driver is the native oracle driver (oracle call interface is a C-language API to Oracle) with a JDBC-compliant wrapper, the Java part needs to request the JVM to find and load a specific dll containing the necessary native code. Usually this is done by making a call to System.loadLibrary in the static initializer of a class requiring the native code. If the API doesn't change over time, then this library might stay constant across releases of the driver. But if the native implementation changes (actually when the JNI bridge to the oci dll changes) then a new version of the library is necessary. So short answer is, some Java class in classes12.zip is calling System.loadLibrary("ocijdbc9.dll"). Specifically which dll is requested will be dependent on the version of the driver.

Best regards,
Jim Cakalic
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

Java Flight Recorder and Java Mission Control together create a complete tool chain to continuously collect low level and detailed runtime information enabling after-the-fact incident analysis. Java Flight Recorder is a profiling and event collectio…
In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
Viewers will learn about if statements in Java and their use The if statement: The condition required to create an if statement: Variations of if statements: An example using if statements:
Suggested Courses

734 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