CMSC424 Oracle/JDBC/Cluster FA Q Page 1Oracle Database AccessWith Java/JDBCFrequently Asked QuestionsThis note is meant to answer a lot of questions I’ve been getting, in tryingto get my project up and running for CMSC 424. My database is intendedto access the Oracle server running from the class cluster, and do this byusing the facilities of Java, and most especially the Java module called JD-BC (Java Database Connectivity). Everything I’m going to pass on is most-ly stuff I found by spending hours reading every public file on marple,poring through the online Oracle documentation, and occasionally askingsome of my project team members who had more Oracle experience than I(thanks, Brett).1. LOGGING INTO ORACLEFirst, the method used to log in to Oracle has a lot of problems with it.Instead of trying to tell you what’s wrong with the posted method, I’ll justgive you a method that I know works, explain what it does, and let it gothere. When you log into the cluster, the first thing you want to do is setyour environment up. Here’s the command that does that:source /usr/local/bin/coraenvThat will prompt you for a couple of things, just hit return, the defaultswill serve just fine. Next, you need to add the path to the Oracleexecutables to your system command path, and you need to tell the systemto update it’s hash list of those commands (so it can find Oracle’s sqlplus).This is two commands. In entering this next command, I’m making use ofa command called "dbhome". Go ahead, next time you’re on the cluster,type "dbhome". All it does is echo to the screen the path to the top of theOracle directories. I’m going to surround the dbhome command with backquotes, so I can get the result into the command line. Note that these areback quotes, and if you replace them with regular OR double quotes,shame on you!setenv PATH ‘dbhome‘/bin:$PATHrehashOK, at this point, you should be able to enter "sqlplus" at the commandprompt, and not get a rude answer from Unix (depending on yourdefinition of rude). This is where Oracle prompts you for your usernameand password, and this is also done wrong in the original documentation,so watch what I do here. I’m going to stick the values %USERNAME%and %PASSWORD% in the example. You replace %USERNAME% withyour Oracle username, and %PASSWORD% with your Oracle password.This is not your cluster password, which I don’t deal with in any way, atall, in this whole document. The last string is "js424", and is the name theygave to Dr. Saltz’s class when they arranged the network connection. Youdon’t want to change this. As an example, my own username is js42430,but I still stick just "js424" at the end of the string. OK, here’s what youenter:CMSC424 Oracle/JDBC/Cluster FA Q Page 2Enter user-name: %USERNAME%/%PASSWORD%@js424Don’t change the location of either the slash or the "@" sign, they’re finejust where they are. The system won’t even bother with the extra promptfor password, since you’ve already entered it.2. Getting Java Connected2.1 Getting The Oracle DriverI’m writing this from my own perspective, which is connecting to thecluster Oracle server from my home machine, picnic.mat.net, which runsUnix. It’s possible that some parts of what I’m going to tell you mighthave to be changed in a small way, when using Windows as yourconnection host. I’ll detail the changes you’ll need to do it from the classcluster itself.First, you need to get the Oracle driver sourced into Java. The Oracle JDBCdrivers are in a file located at (note the back quotes again):‘dbhome‘/jdbc/lib/classes111.zipIf you’re like me, and connecting from outside the cluster, then you have toactually get this file and copy it somewhere on your home machine. Istuck a copy of it in my CMSC 424 Java work directory; you’ll figuresomething similar. To get Java to notice it, though, you have to tell Javaabout it via the CLASSPATH variable. Unlike the PATH variable, whichjust wants to know the directories, the CLASSPATH needs the entirefilename. Since I have class111.zip in my local work directory, I add thatfile to my CLASSPATH like so:setenv CLASSPATH classes111.zip:$CLASSPATHUnder Windows, setting the CLASSPATH is a little different:set CLASSPATH=classes111.zip:%CLASSPATH%Under Windows, don’t add any extra spaces in that command, at all. Do itjust like I showed. If your classes111.zip isn’t in the current directory,modify that part of the command to show the entire path to theclasses111.zip. The way I’ve shown it, it defaults to using the currentdirectory, but you may not want it that way.2.2 Connecting To The Oracle DriverThe JDBC statement that does the connection looks like this:Class.forName("oracle.jdbc.driver.OracleDriver");This throws an SQLException, so you should arrange to catch it, and readthe error return. If you get an error at this point, it’s a file access errorgetting to classes111.zip, not a network error. Whatever you do, don’tunzip the classes111.zip file, it’s fine just the way it is, Java knows andunderstands zip files.CMSC424 Oracle/JDBC/Cluster FA Q Page 32.3 Connecting To OracleThis next part is the login sequence. Again, I’m going to paste in%USERNAME% and %PASSWORD%, and again, these are your Oracleusername and password, NOT your cluster username or password. I don’tneed or want your cluster password, and neither does Oracle.If you’re located on the cluster, you have two choices of drivers to usenow: either the oci8 driver, or the thin driver. The oci8 driver is a highperformance driver, but it uses a local shared C library. This means, if youtry to use this driver on your home machine, it won’t work. Don’t bothercopying the library onto your home machine, unless you happen to berunning a Digital Alpha machine running Digital Unix (DUX) at home.The shared library is code compiled only for that environment, and won’twork without it. To get it to work on the cluster, you have to modify yourLD_LIBRARY_PATH variable, just like the PATH and CLASSPATH havebeen changed. Here’s what you’d enter to get the shared library for theoci8 driver enabled:setenv LD_LIBRARY_PATH ‘dbhome‘/lib:$LD_LIBRARY_PATHI don’t use the oci8 library, because while it’s high performance, it’s asportable as a lead weight. The other driver, also in the classes111.zip file, iscalled the ’thin’ driver, and works just fine. I think most folks reading thisFAQ will find that it works just fine for them.Note
View Full Document
Unlocking...