Solved

Connecting to Oracle from JSP via JDBC

Posted on 2002-03-27
17
4,310 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
  • 9
  • 3
  • 2
  • +2
17 Comments
 
LVL 2

Expert Comment

by:coreyit
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 19

Expert Comment

by:Jim Cakalic
Comment Utility
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
Comment Utility
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
Comment Utility
i am still using Tomcat 3, guess it's outdated
0
 

Author Comment

by:barnarp
Comment Utility
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
Comment Utility
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 

Author Comment

by:barnarp
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
Viewers will learn about arithmetic and Boolean expressions in Java and the logical operators used to create Boolean expressions. We will cover the symbols used for arithmetic expressions and define each logical operator and how to use them in Boole…
Viewers will learn about basic arrays, how to declare them, and how to use them. Introduction and definition: Declare an array and cover the syntax of declaring them: Initialize every index in the created array: Example/Features of a basic arr…

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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now