This week's book giveaway is in the Servlets forum.
We're giving away four copies of Murach's Java Servlets and JSP and have Joel Murach on-line!
See this thread for details.
The moose likes JDBC and the fly likes JDBC Oracle connection...not working Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Murach's Java Servlets and JSP this week in the Servlets forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "JDBC Oracle connection...not working" Watch "JDBC Oracle connection...not working" New topic
Author

JDBC Oracle connection...not working

Ann-Marie Russell
Greenhorn

Joined: Sep 29, 2004
Posts: 6
Ok, so I have just been introduced to the wonderful world of Java, and I am admittedly a little lost. I am trying to set a connection with an Oracle database, i just Tomcat and am running the latest Java SDK.

I have copied my classes12.jar file out to every possible folder in the world. I have set up my JAVA_HOME, ORACLE_HOME, CLASSPATH, and any other possible variable mentioned in every other forum I have seen. I have these variables looking in my c:\oracle folder, not the C:\tomcat folder. I've seen that different people have them referencing different folders...does any one know the difference, or is it just personal preference??

ok, I've tried using both thin and oci8 to connect.

I am using Class.forName("oracle.jdbc.driver.OracleDriver")

When using Thin -- Connection conn = DriverManager.getConnection("jdbc: Oracle:thin:@hostname:1521: Database)");

I get the following error: invalid arguments in call

When using oci8 -- Connection conn = DriverManager.getConnection("jdbc: Oracle: oci8:@hostname:1521:sdwc2","user","password");

I get the following error: java.lang.UnsatisfiedLinkError: get_env_handle

at this point I'm feeling a little and and oh and some more

needless to say, I could use some input from anyone with more of a clue than me. thanks.
[ September 29, 2004: Message edited by: Ann-Marie Russell ]

amr04
Scott Dunbar
Ranch Hand

Joined: Sep 23, 2004
Posts: 245

Yes it matters if it is an upper or lower case "o". The [] are not part of the connection string, just there to visually pull things apart.


I get the following error: java.lang.UnsatisfiedLinkError: get_env_handle


As a general statement an UnsatisfiedLinkError means that the JVM is trying to load a shared library/DLL and it can't find something. In this case it looks like it found the library but not a method in the library. I'd say that that is because of a mismatch between your JDBC library and the OCI library. Check your $PATH on Windows or $LD_LIBRARY_PATH (Solaris, Linux) and make sure that the OCI .so or .dll is referenced there.
[ September 29, 2004: Message edited by: Scott Dunbar ]

<a href="http://forums.hotjoe.com/forums/list.page" target="_blank" rel="nofollow">Java forums using Java software</a> - Come and help get them started.
Mike Rainville
Ranch Hand

Joined: May 29, 2004
Posts: 36
The Oracle 9i and 10g driver for the current version of Java: 1.4 is
in your choice these jar files:
ojdbc14.jar OR ojdbc14_g.jar ( produces more output messages for debugging).

You would need a different version depending on your database and Java versions (see the FAQ below). The name of the jar in a classpath effectively adds the contents (package folders) to your classpath). If you elect to include the jars as extensions to Java, put the SAME jars in the JRE AND JDK "ext" folder, of the JVM you will use to compile and run.

The details you need to connect with OCI are kept in an Oracle file called TNSNAMES.ORA, assuming you have been successful in connecting with any Oracle tool, the settings should be most and possibly all you need. You can use TNSPING to test your connection

For the thin driver, all you need is Java, the JDBC jar, a TCP/IP network connection and your program. For OCI, I think you need the Oracle SQL Client software installed, as well.

There can be additional security settings that your DBA could help with.

The FAQ from Oracle is very, very helpful.
http://www.oracle.com/technology/tech/java/sqlj_jdbc/htdocs/jdbc_faq.htm

Best of luck,
Mike
Adeel Ansari
Ranch Hand

Joined: Aug 15, 2004
Posts: 2874
wht i know is,

thin driver requires SID
oci driver requires Service_Name

Moreover, you need oracle client installed if using oci driver.
[ September 30, 2004: Message edited by: adeel ansari ]
satish sathineni
Ranch Hand

Joined: May 03, 2004
Posts: 46
Hi,

DDLExample.java
// -----------------------------------------------------------------------------

import java.sql.DriverManager;
import java.sql.Connection;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;


/**
* -----------------------------------------------------------------------------
* The following class provides an example of using JDBC to perform DDL
* statements in an Oracle database.
* -----------------------------------------------------------------------------
*/

public class DDLExample {

final static String driverClass = "oracle.jdbc.driver.OracleDriver";
final static String connectionURL = "jdbc racle:thin:@localhost:1521:CUSTDB";
final static String userID = "system";
final static String userPassword = "manager";
Connection con = null;


/**
* Construct a DDLExample object. This constructor will create an Oracle
* database connection.
*/
public DDLExample() {

try {

System.out.print(" Loading JDBC Driver -> " + driverClass + "\n");
Class.forName(driverClass).newInstance();

System.out.print(" Connecting to -> " + connectionURL + "\n");
this.con = DriverManager.getConnection(connectionURL, userID, userPassword);
System.out.print(" Connected as -> " + userID + "\n");

} catch (ClassNotFoundException e) {
e.printStackTrace();
} catch (InstantiationException e) {
e.printStackTrace();
} catch (IllegalAccessException e) {
e.printStackTrace();
} catch (SQLException e) {
e.printStackTrace();
}

}


/**
* Method used to create a table, insert rows into it, query all records,
* delete all records and finally drop the example table.
*/
public void performDDL() {

Statement stmt = null;
ResultSet rset = null;
int insertResults;
int deleteResults;

try {

System.out.print(" Creating Statement...\n");
stmt = con.createStatement ();


/*
* -----------------------------------------------------------------
* CREATE TABLE
* -----------------------------------------------------------------
*/
System.out.print("\n");
System.out.print("+-------------------------------+\n");
System.out.print("| CREATE TABLE |\n");
System.out.print("+-------------------------------+\n");
System.out.print("\n");

System.out.print("Creating Table [TEST_JDBC]\n");

stmt.executeUpdate("CREATE TABLE test_jdbc (" +
" test_jdbc_intr_no NUMBER(15) " +
" , test_jdbc_name VARCHAR2(100) " +
" , test_jdbc_null_value VARCHAR2(100))");
System.out.print("Table Created...\n");


/*
* -----------------------------------------------------------------
* INSERT INTO TABLE
* -----------------------------------------------------------------
*/
System.out.print("\n");
System.out.print("+-------------------------------+\n");
System.out.print("| INSERT VALUES |\n");
System.out.print("+-------------------------------+\n");
System.out.print("\n");

insertResults = stmt.executeUpdate("INSERT INTO test_jdbc VALUES(" +
" 100" +
" , 'James Smith'" +
" , null)");
System.out.print(" RESULTS -> " + insertResults + " row created.\n");
insertResults = stmt.executeUpdate("INSERT INTO test_jdbc VALUES(" +
" 200" +
" , 'Amy Miller'" +
" , null)");
System.out.print(" RESULTS -> " + insertResults + " row created.\n");
insertResults = stmt.executeUpdate("INSERT INTO test_jdbc VALUES(" +
" 300" +
" , 'Andy Phillips'" +
" , null)");
System.out.print(" RESULTS -> " + insertResults + " row created.\n");
insertResults = stmt.executeUpdate("INSERT INTO test_jdbc VALUES(" +
" 400" +
" , 'Jimmy Black'" +
" , null)");
System.out.print(" RESULTS -> " + insertResults + " row created.\n");
insertResults = stmt.executeUpdate("INSERT INTO test_jdbc VALUES(" +
" 500" +
" , 'Jane Dee'" +
" , null)");
System.out.print(" RESULTS -> " + insertResults + " row created.\n");


/*
* -----------------------------------------------------------------
* COMMIT TRANSACTION
* -----------------------------------------------------------------
*/
System.out.print("Commiting Transaction...\n");
con.commit();


/*
* -----------------------------------------------------------------
* EXECUTE QUERY
* -----------------------------------------------------------------
*/
System.out.print("\n");
System.out.print("+-------------------------------+\n");
System.out.print("| EXECUTE QUERY (Forward) |\n");
System.out.print("+-------------------------------+\n");
System.out.print("\n");

System.out.print("Opening ResultsSet...\n");
rset = stmt.executeQuery ("SELECT * FROM test_jdbc ORDER BY test_jdbc_intr_no");

while (rset.next ()) {

int rowNumber;
int test_jdbc_intr_no;
String test_jdbc_name;
String test_jdbc_null_value;

rowNumber = rset.getRow();

test_jdbc_intr_no = rset.getInt(1);
if ( rset.wasNull() ) {
test_jdbc_intr_no = -1;
}

test_jdbc_name = rset.getString(2);
if ( rset.wasNull() ) {
test_jdbc_name = "<null>";
}

test_jdbc_null_value = rset.getString(3);
if ( rset.wasNull() ) {
test_jdbc_null_value = "<null>";
}

System.out.print(
" RESULTS -> [R" + rowNumber + "] " +
test_jdbc_intr_no + " - " + test_jdbc_name +
" - " + test_jdbc_null_value + "\n");
}


/*
* -----------------------------------------------------------------
* DELETE ALL RECORDS FROM TABLE
* -----------------------------------------------------------------
*/

System.out.print("\n");
System.out.print("+-------------------------------+\n");
System.out.print("| DELETE ALL RECORDS FROM TABLE |\n");
System.out.print("+-------------------------------+\n");
System.out.print("\n");

deleteResults = stmt.executeUpdate("DELETE FROM test_jdbc");
System.out.print(" RESULTS -> " + deleteResults + " rows deleted.\n");


/*
* -----------------------------------------------------------------
* COMMIT TRANSACTION
* -----------------------------------------------------------------
*/
System.out.print("Commiting Transaction...\n");
con.commit();


/*
* -----------------------------------------------------------------
* DROP TABLE
* -----------------------------------------------------------------
*/
System.out.print("\n");
System.out.print("+-------------------------------+\n");
System.out.print("| DROP TABLE |\n");
System.out.print("+-------------------------------+\n");
System.out.print("\n");

System.out.print("Dropping Table\n");
stmt.executeUpdate("DROP TABLE test_jdbc");


/*
* -----------------------------------------------------------------
* CLOSE RESULTSET AND STATEMENT OBJECTS
* -----------------------------------------------------------------
*/
System.out.println();
System.out.print(" Closing ResultSet...\n");
rset.close();

System.out.print(" Closing Statement...\n");
stmt.close();

} catch (SQLException e) {

e.printStackTrace();

}

}


/**
* Close down Oracle connection.
*/
public void closeConnection() {

try {
System.out.print(" Closing Connection...\n");
con.close();

} catch (SQLException e) {

e.printStackTrace();

}

}


/**
* Sole entry point to the class and application.
* @param args Array of String arguments.
* @exception java.lang.InterruptedException
* Thrown from the Thread class.
*/
public static void main(String[] args)
throws java.lang.InterruptedException {

DDLExample ddlExample = new DDLExample();
ddlExample.performDDL();
ddlExample.closeConnection();

}

}





satish
Ann-Marie Russell
Greenhorn

Joined: Sep 29, 2004
Posts: 6
Thank you all for your help. I have managed to get it to work with the following code:

String url ="jdbcracle:thin:@(DESCRIPTION=" +
"(ADDRESS_LIST=" +
"(ADDRESS=(PROTOCOL=TCP)" +
"(HOST=IPaddress)"+ "(PORT=1521)" +
")" +
")" +
"(CONNECT_DATA=" +
"(SERVICE_NAME=something)" +
")" +
")";

Connection conn = DriverManager.getConnection(url,"user","pwd");


Now can someone please explain to me what the difference between the code above and the following code is, as if i use the code below, it doesn't work??

Connection conn = DriverManager.getConnection("jdbcracle:thin:@IPaddress:1521:something","user","pwd");
Mike Rainville
Ranch Hand

Joined: May 29, 2004
Posts: 36
The successful try, using the TNSNAMES-like syntax, proves that "something" is a service name, but it might not be same as the system identifier (SID also known as a database instance name).

What you would need is the service connect string (see the Oracle JDBC FAQ). It shown bolded under the failing connect string in the code below. but I don't think it's supported yet in the thin driver, though I am not positive. Service names are definitely the preferred method. You may want to have a look at using DataSource to connect.

N.B. The failing connection string is in the middle with alternatives above and below it



[ October 02, 2004: Message edited by: Mike Rainville ]

[ October 02, 2004: Message edited by: Mike Rainville ]

[ October 02, 2004: Message edited by: Mike Rainville ]

[ October 02, 2004: Message edited by: Mike Rainville ]
[ October 02, 2004: Message edited by: Mike Rainville ]
Ann-Marie Russell
Greenhorn

Joined: Sep 29, 2004
Posts: 6
Ok, so I've tried using a datasource as well...below is what I have

OracleDataSource ods = new OracleDataSource();
ods.setDriverType("thin");
ods.setServerName(something);
ods.setDatabaseName(dbname);
ods.setPortNumber(1521);
ods.setUser(user);
ods.setPassword(pwd);
Connection conn = ods.getConnection();

unfortunately it doesn't work either.

any thoughts?
Mike Rainville
Ranch Hand

Joined: May 29, 2004
Posts: 36
(1) I suggest you use the debugging version of the JDBC driver, called ojdbc14_g,
as it creates many more error messages to help you.

This turns it on (true) and off (false): oracle.jdbc.driver.OracleLog.setTrace(true);

Start with what you are SURE of or can verify. Change one line at a time and bracket doubtful statements with trace true/false to isolate their behaviour.


(2) NSLOOKUP will tell you the DNS name from an IP address
or an IP address if you know a machine's DNS name

e.g. use the HOST=IPaddress from your working example
___________________________________________________________
> Annes-Computer:~ arussel$ nslookup IPAddress
> 099.090.099.009.in-addr.arpa name = host.company.com
___________________________________________________________

ods.setServerName("host.company.com") from name = above


(3) WORKING from SUCCESS: More from your working example:

String url ="jdbc racle:thin: means this is OK: ods.setDriverType("thin");
PORT=1521 means this is OK: ods.setPortNumber(1521);

PROTOCOL=TCP means this is missing ods.setNetworkProtocol( "TCP" ); SERVICE_NAME=something means you could also include ods.setServiceName(something);

Verify SID=dbname in your TNSNAMES.ORA file to check this ods.setDatabaseName(dbname);


This following be OK, but there is another form used in most of the examples I have seen:
ods.getConnection( user, pwd ); // Either way is probably OK
OR what you have:
>ods.setUser(user);
>ods.setPassword(pwd);

(4) The next step would be to include security settings to match the database server's requirements, like this, but there are many additional settings not shown,
(use the values from your SQLNET.ORA file in a slightly different form):

Properties dataSecurityProperties = new Properties();
// ...
dataSecurityProperties.put( "oracle.net.encryption_client", "ACCEPTED" );
// ...
ods.setConnectionProperties( dataSecurityProperties )

[ October 05, 2004: Message edited by: Mike Rainville ]

[ October 08, 2004: Message edited by: Mike Rainville ]
[ October 09, 2004: Message edited by: Mike Rainville ]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: JDBC Oracle connection...not working
 
Similar Threads
how to know the url is whether correct or not
Dynamic DSN creation
getBlob() gives exception
Accessing Database
excepttion in thread "main" java.sql.SQLException: Io exception: The Network Adapter