Download Oracle Database Access with Java/JDBC: A FAQ for CMSC424 Students and more Study Guides, Projects, Research Principles of Database Management in PDF only on Docsity!
Oracle Database Access With Java/JDBC
Frequently Asked Questions
This note is meant to answer a lot of questions I’ve been getting, in trying to get my project up and running for CMSC 424. My database is intended to access the Oracle server running from the class cluster, and do this by using 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 asking some of my project team members who had more Oracle experience than I (thanks, Brett).
1. LOGGING INTO ORACLE
First, 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 just give you a method that I know works, explain what it does, and let it go there. When you log into the cluster, the first thing you want to do is set your environment up. Here’s the command that does that:
source /usr/local/bin/coraenv
That will prompt you for a couple of things, just hit return, the defaults will serve just fine. Next, you need to add the path to the Oracle executables to your system command path, and you need to tell the system to 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 of a 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 the Oracle directories. I’m going to surround the dbhome command with back quotes, so I can get the result into the command line. Note that these are back quotes, and if you replace them with regular OR double quotes, shame on you!
setenv PATH ‘dbhome‘/bin:$PATH rehash
OK, at this point, you should be able to enter "sqlplus" at the command prompt, and not get a rude answer from Unix (depending on your definition of rude). This is where Oracle prompts you for your username and 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% with your Oracle username, and %PASSWORD% with your Oracle password. This is not your cluster password, which I don’t deal with in any way, at all, in this whole document. The last string is "js424", and is the name they gave to Dr. Saltz’s class when they arranged the network connection. You don’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 you enter:
Enter user-name: %USERNAME%/%PASSWORD%@js
Don’t change the location of either the slash or the "@" sign, they’re fine just where they are. The system won’t even bother with the extra prompt for password, since you’ve already entered it.
2. Getting Java Connected
2.1 Getting The Oracle Driver
I’m writing this from my own perspective, which is connecting to the cluster Oracle server from my home machine, picnic.mat.net, which runs Unix. It’s possible that some parts of what I’m going to tell you might have to be changed in a small way, when using Windows as your connection host. I’ll detail the changes you’ll need to do it from the class cluster itself.
First, you need to get the Oracle driver sourced into Java. The Oracle JDBC drivers are in a file located at (note the back quotes again):
‘dbhome‘/jdbc/lib/classes111.zip
If you’re like me, and connecting from outside the cluster, then you have to actually get this file and copy it somewhere on your home machine. I stuck a copy of it in my CMSC 424 Java work directory; you’ll figure something similar. To get Java to notice it, though, you have to tell Java about it via the CLASSPATH variable. Unlike the PATH variable, which just wants to know the directories, the CLASSPATH needs the entire filename. Since I have class111.zip in my local work directory, I add that file to my CLASSPATH like so:
setenv CLASSPATH classes111.zip:$CLASSPATH
Under 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 it just 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 the classes111.zip. The way I’ve shown it, it defaults to using the current directory, but you may not want it that way.
2.2 Connecting To The Oracle Driver
The 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 read the error return. If you get an error at this point, it’s a file access error getting to classes111.zip, not a network error. Whatever you do, don’t unzip the classes111.zip file, it’s fine just the way it is, Java knows and understands zip files.
Class.forName("oracle.jdbc.driver.OracleDriver"); Connection conn = DriverManager.getConnection( "jdbc:oracle:thin:%USERNAME%/%PASSWORD%" + "@lungs.csc.umd.edu:1521:js424"); Statement stmt = conn.createStatement(); ResultSet rs = stmt.executeQuery( "SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE, DATA_LENGTH " + "FROM USER_TAB_COLUMNS");
System.out.println("\n Table"+"Column"+ " Type"+" Length"); System.out.println("================== "+"================="+ " ============"+" ======"); String oldtabname = "&%"; /* designed NOT to show initial match / while(rs.next()) { String tabname = rs.getString("TABLE_NAME"); / next section prints no table name if continuation of existing table. */ if(oldtabname.equals(tabname)) { oldtabname = tabname; tabname = " "; } else { oldtabname = tabname; while(tabname.length() <20) tabname = tabname + " "; } String colname = rs.getString("COLUMN_NAME"); while(colname.length() <20) colname = colname + " "; String typename = rs.getString("DATA_TYPE"); while(typename.length() <16) typename = typename + " "; String lenname = rs.getString("DATA_LENGTH"); System.out.println(tabname+colname+typename+lenname); }
/* warnings don’t pop exceptions */ SQLWarning dbwarn = stmt.getWarnings(); while( dbwarn != null ) { System.out.println("==========SQLWarning received!=========="); System.out.println("SQLWarning:" + dbwarn.getMessage()); System.out.println("SQLState:" + dbwarn.getSQLState()); System.out.println("SQLerrorcode:" + dbwarn.getErrorCode()); dbwarn = dbwarn.getNextWarning(); } } catch (java.lang.ClassNotFoundException e) { System.err.println(e); System.err.print("attach to driver here failed\n"); System.exit(0);
} catch (SQLException e) { while( e != null ) { System.out.println("==========SQLException received!========"); System.out.println("Exception:" + e.getMessage()); System.out.println("SQLState:" + e.getSQLState()); System.out.println("SQLerrorcode:" + e.getErrorCode());
/* uncomment next to show exact line that died. */
// e.printStackTrace(System.out); e = e.getNextException(); } System.exit(0); } finally { System.err.println("\nTable specs listed, closing application."); } } public static void main(String argv[]) { dbList tryit = new dbList(); } }
