jdbc & Oracle


~user/.profile
==============
CATALINA_HOME=/usr/local/jakarta-tomcat-4.1.12
HZ=100
CLASSPATH=/usr/local/jdbc/oracle817/jdbc_oracle817.jar:/usr/local/java/lib/rt.jar:/usr/local/java/lib/java-getopt-1.0.9.jar:/usr/local/decompiler/jode-1.1.1.jar:/usr/local/hsqldb/lib/hsqldb.jar:/usr/local/webdavClient/org/bcholmes/webdav/client/:/usr/local/webdavClient/org/bcholmes/webdav/client/test/:/usr/local/webdavClient/HTTPClient:/usr/local/webdavClient/HTTPClient/HTTPClient.zip:/usr/local/j2sdk1.3.1/src.jar:/usr/local/j2sdk1.3.1/src/:/usr/local/xerces-2_2_0/:/usr/local/skunkdav/:/usr/local/lucene-1.2/lucene-1.2.jar:/usr/local/lucene-1.2/lucene-demos-1.2.jar:/usr/local/jakarta-tomcat-4.1.12/webapps/cocoon/WEB-INF/lib/avalon-framework-20020627.jar:/usr/local/jakarta-tomcat-4.1.12/webapps/cocoon/WEB-INF/lib/avalon-excalibur-vm12-20020705.jar:PS1=\h:\w\$
SLIDE_HOME=/usr/local/jakarta-slide-1.0.16
ANT_HOME=/usr/local/jakarta-ant-1.5.1
USER=root
JAKARTA_HOME=/usr/local
MAIL=/var/mail/root
JAVA_HOME=/usr/local/j2sdk1.3.1
LOGNAME=root
SHLVL=1
SHELL=/bin/bash
TERM=xterm
HOME=/root
PATH=/usr/local/jakarta-ant-1.5.1/bin:/usr/local/j2sdk1.3.1/bin:/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin:/usr/bin/X11
_=/usr/bin/env
OLDPWD=/root

Command Line
============
jikes query1.class
java query1

where query1.java is here ./query.java
Or display :

=======================
=======================
/* Connect to Oracle Database using JDBC URL */
import java.sql.*;
public class query1 {
  public static void main(String argv[]) {

       // Load the Oracle JDBC driver
    try
    {
      DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
    }
    catch (Exception e)
    {
      e.printStackTrace(System.out);
    }
    try
    {

// Chage the getConnection parameters to your specific database, username and password
// Note: 172.16.1.234 is an private ip, and of course couple login/passwd is already disable...
      Connection con = DriverManager.getConnection ("jdbc:oracle:thin:@172.16.1.234:1521:SCPEMOL","dbascp", "dbascp");
      Statement stmt = con.createStatement();

// Below is an example SQL query which displays the Customer ID, Order Number and Product ID of all entries in the table Orders
      String sqlqry = "SELECT res_code, res_nomc, res_nom FROM ressource";
      ResultSet rs = stmt.executeQuery(sqlqry);

      while(rs.next())
      {
        String tblCust = rs.getString("res_code");
        String tblOrd = rs.getString("res_nomc");
        String tblProd = rs.getString("res_nom");

        System.out.println("            - - -             ");
        System.out.println("Customer ID: " +tblCust);
        System.out.println("Order Number: " + tblOrd);
        System.out.println("Product ID: " + tblProd);
      }
      rs.close();
      stmt.close();
      con.close();
    }
    catch (SQLException se)
    {
      System.out.println("SQL Exception: " + se.getMessage());
      se.printStackTrace(System.out);
    }

  }
}

=======================
=======================

Example of error:
	
	Exception in thread "main" java.lang.NoClassDefFoundError: query1 
	_________________________________________________________________
	
	
If you receive this error during java  interpretation, 
java cannot find your bytecode file, query1.class. Place your actual diurectory position in the same directory than your bytecode.






Use jdbc Oracle Driver with Jakarta-TomCat (TC4.1.12)
======================================================

Download jdbc Oracle Driver for your Oracle Binaries version (See http://technet.oracle.com/software/content.html)
Place a copy in $CATALINA_HOME/common/lib in jar format.
Example:

	mkdir ./tmp
	mv ./classes12.zip ./tmp
	cd ./tmp
	unzip classes12.zip 			/// will create two directory oracle and javax
	mv classes12.zip ../
	jar -cvf jdbc_oracle817.jar *
	cp *.jar $CATALINA_HOME/common/lib


Now a simple example of jdbc Oracle connection by using servlet:
Please add all necessary packages in your $CLASSPATH in order to pre-compile by jikes or javac, etc..
For examples, here we shall need javax packages, these could be found in $CATALINA_HOME/common/lib/servlet.jar
add this path in your $CLASSPATH
Here a sample example of servlet:
By assuming good $CLASSPATH, just the following:
	cd $CATALINA_HOME/webapps/examples/WEB-INF/classes/
	cp  HelloWorldExample.java HelloWorldExample.javaOld
Replace HelloWorldExample.java by the following code.

		import java.sql.*;
		import java.io.*;
		import java.text.*;
		import java.util.*;
		import javax.servlet.*;
		import javax.servlet.http.*;
		
		public class HelloWorldExample extends HttpServlet {
		
		    public void doGet(HttpServletRequest request,
		                      HttpServletResponse response)
		        throws IOException, ServletException
		    {
		        ResourceBundle rb =
		            ResourceBundle.getBundle("LocalStrings",request.getLocale());
		        response.setContentType("text/html");
		        PrintWriter out = response.getWriter();
		
		        out.println("<html>");
		        out.println("<head>");
		
		            String title = rb.getString("helloworld.title");
		
		            out.println("<title>" + title + "</title>");
		        out.println("</head>");
		        out.println("<body bgcolor=\"white\">");
		
		    try
		    	{
		      	DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
		    	}
		    catch (Exception e)
		    	{
		      	e.printStackTrace(out);
		    	}
		
		
		    try
		    	{
		      	Connection con = DriverManager.getConnection ("jdbc:oracle:thin:@172.16.1.234:1521:SCPEMOL","dbascp", "dbascp");
		      	Statement stmt = con.createStatement();
		      	String sqlqry = "SELECT res_code, res_nomc, res_nom FROM ressource";
		      	ResultSet rs = stmt.executeQuery(sqlqry);
		      	while(rs.next())
		      		{
		        	String tblCust = rs.getString("res_code");
		        	String tblOrd = rs.getString("res_nomc");
		        	String tblProd = rs.getString("res_nom");
		
		        	out.println("<br>            - - -             ");
		        	out.println(" Customer ID: " +tblCust);
		        	out.println(" Order Number: " + tblOrd);
		        	out.println(" Product ID: " + tblProd);
		      		}
		
		      	rs.close();
		      	stmt.close();
		      	con.close();
			}//try
		    catch (SQLException se)
		    	{
		      	System.out.println("SQL Exception: " + se.getMessage());
		      	se.printStackTrace(out);
			}
		
			out.println("  A ");
			out.println("<img src=\"/examples/images/code.gif\" height=24 " +
		                    "width=24 align=right border=0 alt=\"view code\"></a>");
		        out.println("<a href=\"/examples/servlets/index.html\">");
		        out.println("<img src=\"/examples/images/return.gif\" height=24 " +
		                    "width=24 align=right border=0 alt=\"return\"></a>");
		        out.println("<h1>" + title + "</h1>");
		        out.println("</body>");
		        out.println("</html>");
			}//method
		
		}//class
		
___________________________________________
In this code, you must change 3 things:
	1. Connection con = DriverManager.getConnection ("jdbc:oracle:thin:@172.16.1.234:1521:SCPEMOL","dbascp", "dbascp");
	Replace by your ip, your instance name( see $ORACLE_HOME/network/admin/tnsname.ora), your login, passwd

	2. String sqlqry = "SELECT res_code, res_nomc, res_nom FROM ressource";
	Replace by your sql request

	3.		        String tblCust = rs.getString("res_code");
		        	String tblOrd = rs.getString("res_nomc");
		        	String tblProd = rs.getString("res_nom");
	Replace by your column name
		        	
		        	
jikes HelloWorldExample.java
Go to you browser web enter 
http://your_server:8080/examples/servlet/HelloWorldExample