1

So I was using JDBC with mySQL to connect to a test mySQL database in a java app and everything worked just fine. Production will end up being on an oracle database. It seems I can just change the Driver and Connection to make this work.

I was given this: /usr/local/oracle/product/10.1.0/client_1/bin/sqlplus -S user/password@SERVICE @something.sql

So I am assuming this is how it is accessed locally. I was given the host as well. So I thought the connection url would translate to the following and I'd be able to do this:

String url = "jdbc:oracle:thin:user/password@//host/SERVICE"

Connection c = DriverManager.getConnection(url);

but this does not seem to be working. I am confused by the @something.sql part. How would I include that in my connection url? Also are there any obvious errors in my current connection URL, "jdbc:oracle:thin:user/password@//host/SERVICE"?

John Powers
  • 267
  • 5
  • 13
  • 1
    check out this post -- http://stackoverflow.com/questions/4832056/java-jdbc-how-to-connect-to-oracle-using-service-name-instead-of-sid – mazaneicha Apr 13 '12 at 16:42
  • I got it working using jdbc:oracle:thin:@host:1521:SERVICE syntax and supplying the username and password separately. Thank you. – John Powers Apr 13 '12 at 19:09

2 Answers2

2

First, sqlplus is a command line utility from Oracle, and they gave you an example where values are supposed to be substituted in their example.

If you had a user named "ababa" who had a password "sesame". Suppose the service is "genie" on server "bottle". If the SQL one ran from a command line was "SELECT * FROM wishes LIMIT 3", which is in a file called "/tmp/wishes.sql".

From the command line, one might type the following:

/usr/local/oracle/product/10.1.0/client_1/bin/sqlplus -S ababa/sesame@genie @/tmp/wishes.sql

From Java, one would call:

Connection conn = DriverManager.getConnection("jdbc:oracle:thin://bottle/genie",
                                              "ababa",
                                              "sesame");
Marlin Pierce
  • 9,931
  • 4
  • 30
  • 52
0

I don't normally use connection strings directly in my code anymore, but that looks mostly right. The real problem is that Oracle isn't standard, which is why you have the silly @ sign in there.

You might also find that some of your SQL won't work either. That's because Oracle has some quirks. You really should test your code against an oracle database before tossing it into production.

Jim Barrows
  • 3,634
  • 1
  • 25
  • 36
  • 2
    There is no such thing as a "standard" JDBC URL. It **is** vendor specific (by definition). And btw: MySQL has far more quirks than Oracle (allowing to store February 31st is just one of them) –  Apr 13 '12 at 16:51
  • Oh I am testing it before production, for sure. What is your alternative to using the connection string directly in the code? – John Powers Apr 13 '12 at 19:08
  • Springs JDBC stuff. Apache dbUtils. There's a few others out there that will abstract out the messiness of JDBC for you. – Jim Barrows Apr 17 '12 at 16:49