10 Replies Latest reply: Oct 4, 2012 12:10 PM by sb92075 RSS

    Which JDBC driver do I want?

    user8816970
      I have the oracle database on a oracle linux guest VM on a Windows 7 host.

      I want to run queries on the windows 7 host that execute in the linux oracle guest OS running the oracle database.

      I would like to run [http://squirrel-sql.sourceforge.net/|http://squirrel-sql.sourceforge.net/] and execute SQL queries and write java programs that use the jdbc drivers to execute queries on windows 7.

      Sooo.... I go to this page:

      [http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-112010-090769.html|http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-112010-090769.html]

      And I'm overwhelmed with choices! Can someone help me identify which JDBC driver I want?


      Oracle Database 11g Release 2 (11.2.0.3) JDBC Drivers
      SimpleFAN
      simplefan.jar (20,345 bytes), simplefan policy, javadoc

      JDBC Thin for All Platforms
           JavaDoc (6,456,081 bytes)
           README
           
           ojdbc5.jar (2,095,932 bytes) - Classes for use with JDK 1.5. It contains the JDBC driver classes, except classes for NLS support in Oracle Object and Collection types.
           ojdbc5_g.jar (3,426,203 bytes) - Same as ojdbc5.jar, except that classes were compiled with "javac -g" and contain tracing code.
           ojdbc6.jar (2,714,189 bytes) - Classes for use with JDK 1.6. It contains the JDBC driver classes except classes for NLS support in Oracle Object and Collection types.
           ojdbc6_g.jar (4,463,274 bytes) - Same as ojdbc6.jar except compiled with "javac -g" and contains tracing code.
           ojdbc5dms.jar (2,617,374 bytes) - Same as ojdbc5.jar, except that it contains instrumentation to support DMS and limited java.util.logging calls.
           ojdbc5dms_g.jar (3,447,502 bytes) - Same as ojdbc5_g.jar, except that it contains instrumentation to support DMS.
           ojdbc6dms.jar (3,327,932 bytes) - Same as ojdbc6.jar, except that it contains instrumentation to support DMS and limited java.util.logging calls.
           ojdbc6dms_g.jar (4,486,082 bytes) - Same as ojdbc6_g.jar except that it contains instrumentation to support DMS.
           orai18n.jar (1,656,248 bytes) - NLS classes for use with JDK 1.5, and 1.6. It contains classes for NLS support in Oracle Object and Collection types. This jar file replaces the old nls_charset jar/zip files.
           demo.zip (606,505 bytes) - contains sample JDBC programs.
           xdb6.jar (262,740 bytes) - To use the standard JDBC4.0 java.sql.SQLXML interface with JDBC 11.2.0.3, you need to use xdb6.jar (instead of xdb.jar) from the 11.2.0.3 distribution..

      Note: For platform specific JDBC-OCI libraries See Instant Client



      Thanks!
      Siegfried
        • 1. Re: Which JDBC driver do I want?
          rp0428
          >
          Can someone help me identify which JDBC driver I want?
          >
          Use the ojdbc6.jar to talk to your 11.2 database. The thin driver in the jar doesn't need the Oracle client; that would only be needed if you used the OCI driver in the jar.

          See the section Test JDBC and the Database Connection in the JDBC Developer's Guide
          http://docs.oracle.com/cd/B28359_01/java.111/b31224/getsta.htm#i1003811

          It has example code to test connectivity.
          • 2. Re: Which JDBC driver do I want?
            sb92075
            I was under the impression that SQLSquirrel was self-contained & just used JDBC:Thin under the hood.

            below is a self-contain JDBC:Thin connection test
            bcm@bcm-laptop:~$ cat Conn.java
            import java.sql.*;
            class Conn {
              public static void main (String[] args) throws Exception
              {
               Class.forName ("oracle.jdbc.OracleDriver");
            
               Connection conn = DriverManager.getConnection
                 ("jdbc:oracle:thin:@//localhost:1521/v112", "scott", "tiger");
                                    // @//machineName:port/SID,   userid,  password
               try {
                 Statement stmt = conn.createStatement();
                 try {
                   ResultSet rset = stmt.executeQuery("select BANNER from SYS.V_$VERSION");
                   try {
                     while (rset.next())
                       System.out.println (rset.getString(1));   // Print col 1
                   } 
                   finally {
                      try { rset.close(); } catch (Exception ignore) {}
                   }
                 } 
                 finally {
                   try { stmt.close(); } catch (Exception ignore) {}
                 }
               } 
               finally {
                 try { conn.close(); } catch (Exception ignore) {}
               }
              }
            }
            bcm@bcm-laptop:~$ javac Conn.java
            bcm@bcm-laptop:~$ export CLASSPATH=/u01/app/oracle/product/11.2.0/dbhome_1/owb/wf/lib/ojdbc14.jar:.
            bcm@bcm-laptop:~$ java Conn
            Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
            PL/SQL Release 11.2.0.1.0 - Production
            CORE     11.2.0.1.0     Production
            TNS for Linux: Version 11.2.0.1.0 - Production
            NLSRTL Version 11.2.0.1.0 - Production
            bcm@bcm-laptop:~$
            • 3. Re: Which JDBC driver do I want?
              Mark Malakanov (user11181920)
              Can someone help me identify which JDBC driver I want?
              rules are quite simple.
              1. Take a version of driver equal or greater than version of Oracle you will connect. 11g Driver will work for 10g and 9i.
              2. Take a driver with ojdbcN according to JVM you will run it on. If JVM version is 6, take ojdbc6*.
              3. You do not need to install Oracle Client if you are going to use Thin Driver only. In most cases you do not need "thick" OCI driver.
              4. Nowadays JDBC drivers are "pure java", they work on any platform where JVM works - Win, Lin, Unix ... .
              5. Read docs.
              • 4. Re: Which JDBC driver do I want?
                Mark Malakanov (user11181920)
                I was under the impression that SQLSquirrel was self-contained & just used JDBC:Thin under the hood.
                below is a self-contain JDBC:Thin connection test
                How this can be under the SQLSquirrel hood?
                /u01/app/oracle/product/11.2.0/dbhome_1/owb/wf/lib/ojdbc14.jar

                It is under Oracle OWB hood.
                • 5. Re: Which JDBC driver do I want?
                  user8816970
                  OK, I modified the source by changing "localhost" to the ip address of my vm. When I run, I get these stack traces. Can someone tell me what I'm doing wrong?
                  This is executed on my windows machine and I am attempting to connect to oracle running on oracle linux as a guest of windows 7.

                  Thanks
                  Siegfried

                  set CLASSPATH=bin;.;.\ojdbc6.jar
                  javac -g Conn.java -d bin
                  java Conn
                  Exception in thread "main" java.sql.SQLException: Listener refused the connection with the following error:
                  ORA-12514, TNS:listener does not currently know of service requested in connect descriptor


                       at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:458)

                       at oracle.jdbc.driver.PhysicalConnection.<init>(PhysicalConnection.java:546)

                       at oracle.jdbc.driver.T4CConnection.<init>(T4CConnection.java:236)

                       at oracle.jdbc.driver.T4CDriverExtension.getConnection(T4CDriverExtension.java:32)

                       at oracle.jdbc.driver.OracleDriver.connect(OracleDriver.java:521)

                       at java.sql.DriverManager.getConnection(Unknown Source)

                       at java.sql.DriverManager.getConnection(Unknown Source)

                       at Conn.main(Conn.java:44)

                  Caused by: oracle.net.ns.NetException: Listener refused the connection with the following error:
                  ORA-12514, TNS:listener does not currently know of service requested in connect descriptor


                       at oracle.net.ns.NSProtocol.connect(NSProtocol.java:395)

                       at oracle.jdbc.driver.T4CConnection.connect(T4CConnection.java:1102)

                       at oracle.jdbc.driver.T4CConnection.logon(T4CConnection.java:320)

                       ... 7 more


                  Process compilation finished
                  • 6. Re: Which JDBC driver do I want?
                    sb92075
                    user8816970 wrote:
                    OK, I modified the source by changing "localhost" to the ip address of my vm. When I run, I get these stack traces. Can someone tell me what I'm doing wrong?
                    This is executed on my windows machine and I am attempting to connect to oracle running on oracle linux as a guest of windows 7.

                    Thanks
                    Siegfried

                    set CLASSPATH=bin;.;.\ojdbc6.jar
                    javac -g Conn.java -d bin
                    java Conn
                    Exception in thread "main" java.sql.SQLException: Listener refused the connection with the following error:
                    ORA-12514, TNS:listener does not currently know of service requested in connect descriptor
                    ORA-12514 ALWAYS only occurs due to a problem on DB Server system.
                    One cause of this problem is when the Oracle database is down & needs to be started.
                    A remote client send a request to the Listener asking to be connected to a specific service.
                    If/when the listener does not know anything about that service, the listener responds with ORA-12514
                    
                    post results from the following two OS commands:
                    lsnrctl status
                    lsnrctl service
                    
                    Since every connection request to the listener gets logged, listener.log file will contain a line with 12514 as status code.
                    
                    *This line contains valuable debugging details. So post this line & surrounding lines.*
                    
                    for additional debugging suggestions read the URL below:
                    http://edstevensdba.wordpress.com/2011/03/19/ora-12514/
                    • 7. Re: Which JDBC driver do I want?
                      user8816970
                      I restored the original source and compiled it on my linux VM and recieved a similar stack trace.

                      Here is the result of the first command:
                      [oracle@localhost conn]$ lsnrctl status

                      LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 28-SEP-2012 20:57:19

                      Copyright (c) 1991, 2010, Oracle. All rights reserved.

                      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1521)))
                      STATUS of the LISTENER
                      ------------------------
                      Alias LISTENER
                      Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production
                      Start Date 28-SEP-2012 19:24:47
                      Uptime 0 days 1 hr. 32 min. 32 sec
                      Trace Level off
                      Security ON: Local OS Authentication
                      SNMP OFF
                      Listener Parameter File /home/oracle/app/oracle/product/11.2.0/dbhome_2/network/admin/listener.ora
                      Listener Log File /home/oracle/app/oracle/diag/tnslsnr/localhost/listener/alert/log.xml
                      Listening Endpoints Summary...
                      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=1521)))
                      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=80))(PROTOCOL_STACK=(PRESENTATION=HTTP)(SESSION=RAW)))
                      (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=0.0.0.0)(PORT=21))(PROTOCOL_STACK=(PRESENTATION=FTP)(SESSION=RAW)))
                      Services Summary...
                      Service "orcl" has 1 instance(s).
                      Instance "orcl", status READY, has 2 handler(s) for this service...
                      Service "orclXDB" has 1 instance(s).
                      Instance "orcl", status READY, has 1 handler(s) for this service...
                      The command completed successfully
                      [oracle@localhost conn]$

                      Here is the second
                      [oracle@localhost conn]$ lsnrctl service

                      LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 28-SEP-2012 20:58:42

                      Copyright (c) 1991, 2010, Oracle. All rights reserved.

                      Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1521)))
                      Services Summary...
                      Service "orcl" has 1 instance(s).
                      Instance "orcl", status READY, has 2 handler(s) for this service...
                      Handler(s):
                      "DEDICATED" established:34 refused:0 state:ready
                      LOCAL SERVER
                      "N000" established:0 refused:0 current:0 max:20000 state:ready
                      CMON <machine: localhost.localdomain, pid: 2750>
                      (ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=22398))
                      Service "orclXDB" has 1 instance(s).
                      Instance "orcl", status READY, has 1 handler(s) for this service...
                      Handler(s):
                      "D000" established:0 refused:0 current:0 max:1022 state:ready
                      DISPATCHER <machine: localhost.localdomain, pid: 2700>
                      (ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=31281))
                      The command completed successfully
                      [oracle@localhost conn]$



                      Thanks!
                      • 8. Re: Which JDBC driver do I want?
                        sb92075
                        Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=0.0.0.0)(PORT=1521)))
                        (ADDRESS=(PROTOCOL=tcp)(HOST=localhost.localdomain)(PORT=31281))
                        I refuse to continue when you are obviously obfuscating reality.

                        I don't know what is real & what you change before posting here.
                        You're On Your Own (YOYO)!
                        • 9. Re: Which JDBC driver do I want?
                          user8816970
                          sb92075:

                          Ahhh! I did not mean to offend anyone! I may be stupid but I'm not dishonest! Since other databases like MSAccess, MSSQLServer, Derby, MYSQL don't require you to encode the SID in the connection, I'm not accustomed to doing this and finally discovered that in the connection string

                          "jdbc:oracle:thin:@//localhost:1521/v112"

                          "v112" is a SID and I needed to change that!

                          So I beg for forgiveness! What can I do to redeem myself?

                          Now this java program works from my windows host connecting to my oracle linux guest via jdbc. Finally.

                          Thank you everyone.

                          Now back to trying to install sqlplus on windows...

                          Siegfried
                          • 10. Re: Which JDBC driver do I want?
                            sb92075
                            user8816970 wrote:
                            sb92075:

                            Ahhh! I did not mean to offend anyone! I may be stupid but I'm not dishonest! Since other databases like MSAccess, MSSQLServer, Derby, MYSQL don't require you to encode the SID in the connection, I'm not accustomed to doing this and finally discovered that in the connection string

                            "jdbc:oracle:thin:@//localhost:1521/v112"

                            "v112" is a SID and I needed to change that!
                            my posted working Java code example clearly documented this reality.
                            You can lead some folks to knowledge, but you can't make them think.