Loading Driver
hi all,
I have a problem....
I have Oracle9i R2 db on my PC....
Firstly
which of the following do i use to load my driver..
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
OR
Class.forName("oracle.jdbc.driver.OracleDriver");
Secondly
In the above code i assume "JdbcOdbcDriver" and "OracleDriver"...
are the already created/existing drivers on the PC/server of the database.
How do I create these on WIN-xp ..pls provide a step-by-step procedure pls.....
ta.
sunny
# 1
Add the oracle jdbc driver to your classpath (ojdbc14.jar for example) and use the following code:
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
public class TestOracleConnection {
public static void main(String[] args) {
Connection connection = null;
Statement stmt = null;
ResultSet rs = null;
try {
// Load the JDBC driver
String driverName = "oracle.jdbc.driver.OracleDriver";
Class.forName(driverName).newInstance();
// Create a connection to the database
String serverName = "127.0.0.1";
String portNumber = "1521";
String sid = "mydatabase";
String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber + ":" + sid;
String username = "username";
String password = "password";
connection = DriverManager.getConnection(url, username, password);
stmt = connection.createStatement();//Create statement
String sql = "SELECT column1, column1 FROM users";
rs = stmt.executeQuery(sql);//execute query
while (rs.next()) {
System.out.print("column1 = "+rs.getString("column1")+", ");
System.out.println("column2 = "+rs.getString("column2"));
}
} catch (ClassNotFoundException e) {
// Could not find the database driver
e.printStackTrace();
} catch (SQLException e) {
// Could not connect to the database
e.printStackTrace();
} catch (Exception e) {
e.printStackTrace();
}finally{//release resources
if(rs!=null){
try {
rs.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(stmt!=null){
try {
stmt.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
if(connection!=null){
try {
connection.close();
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
}
}
Hope That Helps
# 2
Step-1:
set classpath of jar files (path=i.e. oracle\ora90\jdbc\lib\*.jar)
Step-2:
try{
Class.forName("oracle.jdbc.driver.OracleDriver");
cnn=DriverManager.getConnection("jdbc:oracle:thin:@localhost:1521:sid","dbname", "dbpassword");
stm=cnn.createStatement();
ResultSet rs;
}catch(Exception e){
System.out.println(e.toString());
}
}
Cheers!
# 3
hi Java_2006,
you are a GEM....
MANY THANKS for your help....code is working now...
If i put the same code in JSP ......where will I have to put the ojdbc14.jar file ?
i am using the tomcat webserver ....will it be web-inf folder ?
is there anything additional i need to do ?
TA
sunny
# 4
hello farakhMANY THANKS for your help as well ....code is working now...TAsunny
# 6
ojdbc14.jar is in the oracle installation directory. It should be in a directory called jdbc or something like that.
To use this jar, add it to TOMCAT_DIRECTORY/common/lib/ directory. You can also add this jar in /WEB-INF/lib/ directory of your web app
Hope That Helps
(don't forget the dukes :O)
Message was edited by:
java_2006
# 7
Hello Dukes.....
What is this concept of DUKES ? enlighten me ?
As per you instructions above I added the jar files to the respective directories.....
then I converted the java program to jsp program as below ....
<%@ page import="java.sql.Connection"%>
<%@ page import="java.sql.DriverManager" %>
<%@ page import="java.sql.ResultSet" %>
<%@ page import="java.sql.SQLException" %>
<%@ page import="java.sql.Statement" %>
<%! Connection connection = null; %>
<%!Statement stmt = null;%>
<%!ResultSet rs = null;%>
<%
try {
String driverName = "oracle.jdbc.driver.OracleDriver";
Class.forName(driverName).newInstance();
String serverName = "127.0.0.1";
String portNumber = "1521";
String sid = "oratest";
String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber + ":" + sid;
String username = "scott";
String password = "tiger";
connection = DriverManager.getConnection(url, username, password);
stmt = connection.createStatement();
String sql = "SELECT empno, ename FROM emp";
rs = stmt.executeQuery(sql);
%>
<HTML>
<BODY>
<H1> List of Employees </H1>
<TABLE BORDER="1">
<TR>
<TH>EMPNO</TH>
<TH>ENAME</TH>
</TR>
<%
while (rs.next())
{
%>
<TR>
<TD><% rs.getInt("empno");%> </TD>
<TD><% rs.getString("ename"); %> </TD>
</TR>
<%
}
}
catch (ClassNotFoundException e)
{
e.printStackTrace();
}
catch (SQLException e)
{
e.printStackTrace();
}
catch (Exception e)
{
e.printStackTrace();
}
}
finally
{
if(rs!=null)
{
try {
rs.close();
}
catch (SQLException e)
{
e.printStackTrace();
}
}
if(stmt!=null)
{
try {
stmt.close();
}
catch (SQLException e)
{
e.printStackTrace();
}
}
if(connection!=null)
{
try {
connection.close();
}
catch (SQLException e)
{
e.printStackTrace();
}
}
}
%>
</TABLE>
</BODY>
</HTML>
The below error is what I receive .....can you suggest pls ?
HTTP Status 500 -
--
type Exception report
message
description The server encountered an internal error () that prevented it from fulfilling this request.
exception
org.apache.jasper.JasperException: Unable to compile class for JSP:
Stacktrace:
org.apache.jasper.compiler.DefaultErrorHandler.javacError(DefaultErrorHandler.java:85)
org.apache.jasper.compiler.ErrorDispatcher.javacError(ErrorDispatcher.java:330)
org.apache.jasper.compiler.JDTCompiler.generateClass(JDTCompiler.java:415)
org.apache.jasper.compiler.Compiler.compile(Compiler.java:308)
org.apache.jasper.compiler.Compiler.compile(Compiler.java:286)
org.apache.jasper.compiler.Compiler.compile(Compiler.java:273)
org.apache.jasper.JspCompilationContext.compile(JspCompilationContext.java:566)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:308)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:320)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:266)
javax.servlet.http.HttpServlet.service(HttpServlet.java:803)
note The full stack trace of the root cause is available in the Apache Tomcat/6.0.10 logs.
ta
sunny
can you pls tell me
# 8
Incent forum members to help you find solutions to your problems. Answer questions with Duke Stars and you can help the community while being recognized as an active participant in the exchange of technical knowledge. (http://developers.sun.com/forums/dukestars.jsp)
Try this instead :
<%@ page language="java" contentType="text/html; charset=ISO-8859-1"
pageEncoding="ISO-8859-1"%>
<%@ page import="java.sql.Connection"%>
<%@ page import="java.sql.DriverManager"%>
<%@ page import="java.sql.ResultSet"%>
<%@ page import="java.sql.SQLException"%>
<%@ page import="java.sql.Statement"%>
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<%! Connection connection = null; %>
<%! Statement stmt = null; %>
<%! ResultSet rs = null; %>
<%
try {
String driverName = "oracle.jdbc.driver.OracleDriver";
Class.forName(driverName).newInstance();
String serverName = "127.0.0.1";
String portNumber = "1521";
String sid = "oratest";
String url = "jdbc:oracle:thin:@" + serverName + ":" + portNumber + ":" + sid;
String username = "scott";
String password = "tiger";
connection = DriverManager.getConnection(url, username, password);
stmt = connection.createStatement();
String sql = "SELECT empno, ename FROM emp";
rs = stmt.executeQuery(sql);
%>
<HTML>
<BODY>
<H1>List of Employees</H1>
<TABLE BORDER="1">
<TR>
<TH>EMPNO</TH>
<TH>ENAME</TH>
</TR>
<%
while (rs.next()){
%>
<TR>
<TD>
<% rs.getInt("empno"); %>
</TD>
<TD>
<% rs.getString("ename"); %>
</TD>
</TR>
<%
}
}catch (ClassNotFoundException e){
e.printStackTrace();
}catch (SQLException e){
e.printStackTrace();
}catch (Exception e){
e.printStackTrace();
}finally{
if(rs!=null){
try {
rs.close();
}catch (SQLException e){
e.printStackTrace();
}
}
if(stmt!=null){
try {
stmt.close();
}catch (SQLException e){
e.printStackTrace();
}
}
if(connection!=null){
try {
connection.close();
}catch (SQLException e){
e.printStackTrace();
}
}
}
%>
</TABLE>
</BODY>
</HTML>
Hope That Helps
# 9
Hello Dukes....
The pages is now getting displayed with the table headings .....
but the table data is not available....
1) The Database is up and running .( I queried).
2) The webserver is up and running ( I used my test jsp)....
3) I restarted the database and web server ...
I think the .jar files might not have taken effect ....( for which You had provided instructions earlier)
how can I test this ?
ta
sunny
# 10
verify data exists in the table
check System.out (err) <i>on the server</i> - as written exceptions would be there (maybe redirected to a log?)
you could also access the column values ordinally, e.g. rs.getInt(1), rs.getString(2)
you could also getString for all column values
for 10 dukes, someone should have mentioned that declaring db resources in a jsp is questionable design
http://java.sun.com/blueprints/corej2eepatterns/Patterns/DataAccessObject.html
