How to connect to Oracle?
This is my source code, I have created the DSN with
Oracle driver, but I have got no luck to connect to
Oracle database (mine is version 8):
...
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
Connection DBCon = DriverManager.getConnection
("jdbc:odbc:customer", "scott", "tiger");
Statement Stmt = DBCon.createStatement();
ResultSet ResS1 = Stmt.executeQuery("SELECT custid,
firstname, lastname, FROM customer");
...
what is it that I miss?
Here is the message:
java.sql.SQLException: [Oracle][ODBC]General Error
please help...
thanks,
Ted.
[652 byte] By [
tedjop] at [2007-9-26 1:21:38]
remove the comma ( , ) after the lastname field.....
I thought Oracle has a type 3 or 4 JDBC driver as well?
esmo at 2007-6-29 0:57:35 >
which is totally offtopic btw. I was just curious because I see so many people using the bridge...
esmo at 2007-6-29 0:57:35 >
Yeah, Oracle has type2(OCI) and type 4(thin) drivers...But still a lot of people use ODBC-JDBC bridge.....
- Are u using Personal Oracle ?- In oracle folder there is a small application called as ODBC Test, test your DSN creation with that application firsthth~Parvinder
minku at 2007-6-29 0:57:35 >
I'm thankful of all the replies, and will post the result after testing...Sincerely,Ted.
Okay, I remove comma after lastname, still does not work
Yes, I am using Oracle Personal.
I run the Oracle ODBC Test, I select the DSN I created
and I was able to connect..no problem.
But when I run my program again, still does not work.
user=scott, password=tiger. I set service name as blank (nothing).
I just know that Oracle has so many drivers for its database. I do not know what the purpose for each driver. But somebody suggest me "thin" driver.
Why prefer "thin" driver, what is the difference?
Again, I will appreciate all comments.
Sincerely,
Ted
Alright, I have dowload thin and OCI oracle's drivers.
But I do not know how to deploy them to connect to
my Personal Oracle 8 running on Windows 98, on PC.
In the statement:
Connection con = getConnection
("jdbc:oracle:oci8:@<database>", "scott", "tiger");
where they said to subtitute <database> with:
<host>:<port>:<sid>...
I do not know what that parameters should be in my case.
Also, I do not know whether I should create DSN and
point it in my code?
Thanks,
Ted.
You need to stick the drivers (the zip files you doenloaded from Oracle) into your Oracle Home directory then add them to your classpath eg
classpath = C:\oracle\ora81\jdbc\lib\classes12.zip;C:\oracle\ora81\jdbc\lib\nls_charset12.zip;
The oracle documentation for your driver should tell you how to connect. I haven't used Personal Oracle but I guess there is tnsnames file (correct me if I'm wrong) which should have the port and host for your db. For OCI your connection should be something like:
try {
Class.forName("oracle.jdbc.driver.OracleDriver");
}
catch (ClassNotFoundException cnfe) {
System.out.println(cnfe.getMessage());
}
try {
c = DriverManager.getConnection("jdbc:oracle:oci8:@yoursid","usr","pwd");
}
catch (SQLExcepition sqle) {
System.out.println(sqle.getMessage());
}
Richard
But In oracle Personal I could not find the folder that you mean here:
c:\oracle\ora81\jdbc\lib
I have:
c:\orawin95 as the start folder...
I appreciate your reply!
Anybody knows where to put the OCI 8 and thin folders
downloaded from Oracle, and where the classpath should be pointed to in Personal Oracle 8 running on Windows98,
please post.
Thanks a lot,
Ted.
Guys, that's not too difficult. First of all do not use ODBC drivers, they are too slow. I would recommend using the Oracle Thin Driver which is 100% Java and rather quick. The class listed below may also be used with OCI8-JDBC driver. Both drivers can be downloaded from http://technet.oracle.com.
--
import java.io.*;
import java.sql.*;
import oracle.jdbc.driver.*;
/**
* connection utility to connect to an Oracle database.
*
* @author Thomas Weber (Orange Interactive GmbH)
*/
public class OraConnect
{
/**
* connects to an Oracle database
* If no registered driver is found, the method tries to register the desired driver during runtime.
* @param driverName the name of the JDBC driver to be used ("jdbc:oracle:thin:" is highly recommended here [100% Java])
* @param serverAndPort e.g. "localhost:1521:"
* @param oraSID the SID of the database, pre 8.1.5 this was called instance. Most likely ist ORCL which is the Oracle default on install.
* @param user the Oracle user, e.g. "Scott"
* @param pass the corresponding password, e.g. "Tiger"
* @return connection to Oracle or <code>null</code>, if unsuccessful
*/
public Connection connect(String driverName,
String serverAndPort,
String oraSID,
String user,
String pass) {
Connection con = null;
try {
con = DriverManager.getConnection(driverName+"@"+serverAndPort+oraSID, user, pass);
// no auto-commit for transactions and improved speed
con.setAutoCommit(false);
} catch(SQLException e) {
try {
DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver());
con = DriverManager.getConnection(driverName+"@"+serverAndPort+oraSID, user, pass);
// no auto-commit for transactions and improved speed
con.setAutoCommit(false);
} catch(SQLException ee) {
/*
include your error logging here
*/
}
}
return con;
}
}
thanks for the replies. I still have questions:
1. which one is the thin and which one is the OCI folder:
classes102 or classes111?
2. I have not successful at having connection. Here is
the code:
....
DriverManager.registerDriver(new
oracle.jdbc.driver.OracleDriver());
String cstring = "(DESCRIPTION=(ADDRESS=
(PROTOCOL=TCP)
(HOST=33186)
(PORT=1521))(CONNECT_DATA=
(SID=CUSTOMER)))";
Connection con = DriverManager.getConnection("jdbc:oracle:thin:@"+cstring, "scott", "tiger");
....
Here is the message:
java.sql.SQLException: Refused:ROR=(CODE=12505)
(EMFI=4))))
I think the error is in the CONNECT_DATA statement,
I do not know what to put in as SID. I just put CUSTOMER as it is the table name...
Any idea what to put as SID? What is SID, and where to
look for that? I am using Personal Oracle 8.
(I do not know where to look for tsname)
Sincerely,
Ted.
Hi Ted,
1. classes102.zip and classes111.zip are different versions of the same thing - you probably want to use classes111.zip
2. The SID is the Oracle System Identifier used to identify an oracle db, so no you shouldn't use the tablename. I've never used Personal Oracle so don't know if the set up is any different. Sorry I can't be of more help.
Also take a look at Oracles JDBC Driver documentation http://technet.oracle.com/doc/server.805/a58237/toc.htm
Richard
My thin driver can connect to Oracle!!
Hooray!
But, my OCBC and OCI still no success.
What strange is, seems the ODBC is success at connecting (the database is running).
But when it comes to making Statement:
...
Statement Stmt = DBCon.createStatement();
...
it generate error:
java.sq.SQLException: [Oracle][ODBC]General Error
I create the connection as follow:
Connection DBCon = DriverManager.getConnection
("jdbc:odbc:orcldev", "tiger", "scott");
I suspect there is something wrong with my Oracle ODBC
Also, my OCI connection generate error saying that
certain class could not be found, though I already gave
classpath to both classes111 and classes102...strange.
I need advice on these, please post.
Thanks,
Ted.
