DBeaver – Database Connection Configuration – Oracle – Oracle JDBC Driver

Background

Towards a couple of sample Oracle Database sample exercises that we have in mind wanted to make sure that our all purpose database client, DBeaver, is able to communicate with an Oracle Database.

 

Preparation

Oracle

Java Version

Objective

The version of JDBC Client that we need will be based on the version of JRE that is installed or will be installed on the computer.

And, so let us establish that first; that is determine the JRE Version Number.

Outline

Using Control Panel, find and launch the Java applet.

Once Java Control Panel window appears, please access the “General” tab.

Within the “General” tab, click the “About” button

Review the Version Numbers listed and exit the “About” panel, once satisfied.

Next access the “Java” tab and review the System and User settings.

Within the Java tab, we are able to enable and disable each installed JRE package.  They are listed by Version #.

Image

Image – About

controlPanel_java_about__20171013__0831PM

 

Explanation
  1. The version number is “Version 8 Update 40” ( build 1.8.0_40)
    • We have Version 8
    • Build 1.8
Image – Java Runtime Environment Settings
Image – Java Runtime Environment Settings – System

Image – Java Runtime Environment Settings – User

Image – Java Runtime Environment Settings – System

Download

Download URL

Please visit Oracle’s JDBC download web site.

The URL is Link.

And, the current version# is 12.1.0.2.

 

Image

Matrix

Jar Package Description Detail
ojdbc7.jar JDBC driver classes except classes for NLS support in Oracle Object and Collection types. NLS Support which enables Internalization are excluded
ojdbc7_g.jar Same as ojdbc7.jar except compiled with “javac -g” and contains tracing code. Tracing & Debugging support included
ojdbc7dms.jar Same as ojdbc7.jar, except that it contains instrumentation to support DMS and limited java.util.logging calls. Instrumentation & Logging Supported included
ojdbc7dms_g.jar Same as ojdbc7_g.jar except that it contains instrumentation to support DMS. Instrumentation included

 

Explanation

We do not need support for NLS, National Language, english is sufficient.

And, we do not need tracing and logging as will not be developing code and thus need to capture code related errors and instrumentation.

In short, the base package, ojdbc7.jar, is sufficient.

Download

Please download the Jar file and move it to a location where you will like to have it available to our Client, DBeaver.

Configuration

Please launch DBeaver and let us review and configure the list of JDBC Drivers.  And, also configure connections to our various Oracle Database hosts.

Outline

  1. Driver Manager
  2. Connection

 

Driver Manager

The list of Drivers currently availed is accessible through the menu items Database \ Driver Manager.

Outline

  1. Please click the menu item Database / Driver Manager
  2. In the “Driver Manager” window
    • Choose Oracle
    • Click the “Edit” button
  3. In the “Edit Driver Oracle” window
    • Tab – Libraries
      • Review the list of libraries registered
      • Add
        • If the ojdbc driver is missing
          • Please click the “Add File” button
          • Navigate to the folder where the JDBC Driver is kept
          • And, select the jdbc jar file
          • Ensure that jar file is listed
      • Driver Class
        • Click the “Find Class” button
        • In the “Driver Class” dropdown, the Driver classes are availed
          • Driver Class
            • oracle.jdbc.OracleDriver
            • oracle.jdbc.driver.OracleDriver
          • The original driver class is oracle.jdbc.driver.OracleDriver
          • And, the modern one is oracle.jdbc.OracleDriver
          • Please choose the modern one
      • Please press the OK button once you are satisfied

Images

Driver Manager – Oracle

Edit Driver “Oracle” – Before Adding Jar File

Edit Driver “Oracle” – After adding Jar file

Edit Driver “Oracle” – Choose Driver Class – Choosing

Edit Driver “Oracle” – Choose Driver Class – Chosen

 

Connection

New Connection

Please click the “Database” / “New Connection” menu item to create a new connection.

Outline

  1. In the “Select new connection type” panel
    • Please select the Oracle driver
  2. In the “Oracle Connection Settings” panel
    • Tabs
      • Tab – General
        • Group Box
          • Group Box – Connection Type
            • In the “Basic” group box
              • Host
                • Please enter the Oracle DB Server Hostname
                  • In our case localhost
              • Port
                • Please enter the Port Number for the Oracle Instance
                  • In our case 1521
              • Database
                • Please enter the database name
                  • As we are local, we clicked on the database name scroll bar and we chose XE from the discovered local instances
              • Service or SID
                • Chose SID
            • Test Connection
              • Please click the “Test Connection” button to validate your connection settings
                • If connection attempt fails, you will get an error message similar to
                  • I/O error :- The Network adapter could not establish a connection
      • Tab – Oracle Properties
        • Group Box
          • Group Box – Session Settings
            • Language
              • Please choose Default from the list of languages
            • Territory
              • Please choose Default from the list of territories
            • NLS Date Format
              • We left the “NLS Date Format” textbox empty
                • As there is no need for Internalization
          • Group Box – Content
            • Hide empty schemas
              • Please unclick the default setting of hiding empty schemas
                • Hiding empty schemas could create potential for not seeing the entire schema footprint
            • Always Show DBA Objects
              • Left unchecked as we will are not currently interested in seeing metadata information availed through the DBA schema
          • Tab – Driver Properties
            • Will not expand on this tab at this time
    • Click the Next button
  3. In the “Network” panel
    • No need for SSH Tunnel / Proxy

Image

Select new connection type

Oracle Connection Settings
Oracle Connection Settings – General – Initial

Oracle Connection Settings – General – Completed – Incorrect

Oracle Connection Settings – General – Completed – Correct

 

Create new Connection – Network

Create new Connection – Test Connection

When test connection fails…

Create new Connection – Test Connection – IO Error : The Network Adapter could not establish the connection – Image

Explanation
  1. Oracle services not started
Create new Connection – Test Connection – ORA-12514, TNS:listener does not currently know of service requested in connect descriptor – Image

 


Create new Connection – Test Connection – ORA-12514, TNS:listener does not currently know of service requested in connect descriptor – Textual

Listener refused the connection with the following error:
ORA-12514, TNS:listener does not currently know of service requested in connect descriptor

Explanation

When test connection fails…

Create new Connection – Test Connection – When things are good

Create new Connection – Finish Connection Creation
Create new Connection – Finish Connection Creation – Initial

Create new Connection – Finish Connection Creation – Completed

 

Usage

Sample Query

Session Information

Query


SELECT 
         SYSDATE AS currentDate 
       , SYS_CONTEXT ('USERENV', 'SESSION_USER') AS currentUser
       , SYS_CONTEXT('USERENV','HOST') AS host       
       , SYS_CONTEXT('USERENV','TERMINAL') AS terminal
       
FROM SYS.DUAL

Output

References

  1. Oracle Docs
  2.  Oracle-Base
    • Identifying Host Names and IP Addresses
      Link

Oracle \ Sun Java’s Interaction with MS SQL Server – Quick Prototype – Test out Connectivity along with Stored Procedure Invocation

Needed to review Oracle\Sun Java’s Interaction with MS SQL Server:

And so did the following:

  1. Downloaded Microsoft JDBC Driver. JDBC Version 3 is the latest out
    there and so downloaded that version.
  2. TextWrangler – Text Editor
  3. Created SQL Account on the DB Server. Granted minimum required access – SQL login Access – Specific Database Access – Permission to run Stored Procedure
  4. Settled on an “un-well” known TCP Port to facilitate SQL Server Remote Access.
    Opened Firewall Rules to support it.

Now that the Environment is a bit setup, took the Easy Street and combed the .Net
for HelloDB code.

But, kept getting the error pasted below:

———————————————————————————

java.lang.ClassNotFoundException: com.microsoft.jdbc.sqlserver.SQLServerDriver
at java.net.URLClassLoader$1.run(URLClassLoader.java:202)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(URLClassLoader.java:190)
at java.lang.ClassLoader.loadClass(ClassLoader.java:306)
at sun.misc.Launcher$AppClassLoader.loadClass(Launcher.java:301)
at java.lang.ClassLoader.loadClass(ClassLoader.java:247)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Class.java:169)
at DBConnect.getConnection(DBConnect.java:70)
at DBConnect.getConn(DBConnect.java:46)
at helloDB.main(helloDB.java:21)
Error Trace in getConnection() : com.microsoft.jdbc.sqlserver.SQLServerDriver

——————————————————————————-

Threw everything at the Problem:

  • Copied Microsoft’s JDBC Driver (sqljdbc4.jar) locally
  • Tweaked the CLASSPATH environment variable. As the Client is a Unix box
  • Learned to play “nice” with BASH
    • Used Export and unset to set & reset variable
  • Tried passing in the jar files via the -cp argument list
    • java -cp ./ helloDB
    • …. Note -cp ./ means that Java should check the local directory for Jar files
  • Copied the files to OS’s native location for JDBC Jar files

Finally gave up with trying to use Microsoft JDBC Driver. Downloaded JTDS changed a couple of lines:

1) Changed the name of the “Driver” class

from:

Microsoft’s MS SQL Server Driver:


Class.forName("com.microsoft.jdbc.sqlserver.SQLServerDriver");

to:
jDTS Driver:

Class.forName("net.sourceforge.jtds.jdbc.Driver");

2) Changed JDBC base URL

from:
Microsoft’s MS SQL Server JDBC Driver:
jdbc:microsoft:sqlserver://

to:
jDTS Driver:
jdbc:jtds:sqlserver://

jDTS worked well, and without much changes either.

And, so now it is getting late and I am getting tired.

Did what I did not want to do, copied the files to a MS Windows box.

Really no additional setup needed as the Database Connectivity Software
are self-contained in the Jar files.

Reviewed the CLASSPATH and Invoked the App. The error message read:

 

——————————————————————————

java.lang.ClassNotFoundException: com.microsoft.jdbc.sqlserver.SQLServerDriver

at java.net.URLClassLoader$1.run(Unknown Source)
at java.net.URLClassLoader$1.run(Unknown Source)
at java.security.AccessController.doPrivileged(Native Method)
at java.net.URLClassLoader.findClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at sun.misc.Launcher$AppClassLoader.loadClass(Unknown Source)
at java.lang.ClassLoader.loadClass(Unknown Source)
at java.lang.Class.forName0(Native Method)
at java.lang.Class.forName(Unknown Source)
at DBConnect_v2000.getConnection(DBConnect_v2000.java:70)
at DBConnect_v2000.getConn(DBConnect_v2000.java:46)
at helloDB.main(helloDB.java:23)
Error Trace in getConnection() : com.microsoft.jdbc.sqlserver.SQLServerDriver

——————————————————————————

Goggled for that error message:

and found http://blogs.msdn.com/b/jdbcteam/archive/2007/06/15/java-lang-classnotfoundexception-com-microsoft-jdbc-sqlserver-sqlserverdriver.aspx

MS Doc clearly states that:

The class name has changed between the SQL Server 2000 JDBC driver and the SQL Server 2005 JDBC driver.  This particular class “com.microsoft.jdbc.sqlserver.SQLServerDriver” is the class name for the SQL Server 2000 JDBC driver.  The SQL Server 2005 JDBC driver class name is “com.microsoft.sqlserver.jdbc.SQLServerDriver“.
Note the change: from “microsoft.jdbc.sqlserver” to “microsoft.sqlserver.jdbc”

In addition, the SQL Server 2005 JDBC driver has a different URL prefix from the SQL Server 2000 JDBC driver.  The SQL Server 2000 JDBC driver uses an URL prefix of “jdbc:microsoft:sqlserver://”, while the SQL Server 2005 JDBC driver uses an URL prefix of “jdbc:sqlserver://“.
Note the removal of “microsoft” from the URL prefix.


—————————————————————————————————

So now we know what works:

Changed the name of the “Driver” class

from:

Microsoft’s MS SQL Server Driver (v2000):

Class.forName(“com.microsoft.jdbc.sqlserver.SQLServerDriver“);

to:

Microsoft’s MS SQL Server Driver (v2005):

Class.forName(“com.microsoft.sqlserver.jdbc.SQLServerDriver“);

(
please be extra careful with this one as the Class name looks the same – but the fact is that a couple of entries are inter-changed –  jdbc.sqlserver is now sqlserver.jdbc
)

2) Changed JDBC base URL

from:

Microsoft’s MS SQL Server JDBC Driver (v2000):

jdbc:microsoft:sqlserver://

to:

Microsoft’s MS SQL Server JDBC Driver (v2005):

jdbc:sqlserver://

         (
             please be a bit careful with this one as the the middle entry
             for microsoft is dropped
        )

————————————————————————

References:
1) Using the JDBC Driver
http://msdn.microsoft.com/en-us/library/ms378526.aspx
2) How to Install Microsoft SQL Server JDBC
http://www.frank4dd.com/howto/database/howto-install-Microsoft-jdbc.htm
References (jDTS)

1) The jTDS Project
http://jtds.sourceforge.net/faq.html#urlFormat
References (Setting the Classpath)

1) Setting the Classpath in Java
http://introcs.cs.princeton.edu/java/15inout/classpath.html
References (WebObjects)

1) WebObjects 5 : How to use Oracle JDBC Drivers
http://support.apple.com/kb/TA26798?viewlocale=en_US

2) How do I install JDBC Driver on 10.5
https://discussions.apple.com/thread/1487101?start=0&tstart=0

References (MalcolmHardie Solutions)

1) Microsoft SQL Server and SQLEditor
http://www.malcolmhardie.com/support/sqleditor/mssqlserver.html

References (Java for OS X Lion)

1) Java for OS X Lion Update 1
http://support.apple.com/kb/DL1421
2) Java and the Mac OS
http://introcs.cs.princeton.edu/java/15inout/mac-cmd.html
References (Java for OS X Lion – Issues )

1) Java for OS X Lion Update 1
http://support.apple.com/kb/DL1421

References (Adobe)

1) Install the Java runtime (JRE) | Mac OS 10.7 Lion
http://kb2.adobe.com/cps/909/cpsid_90908.html

References (sqljdbc.jar)

1) How to get Started with Microsoft JDBC
http://support.microsoft.com/kb/313100
2) Working with a Connection
http://msdn.microsoft.com/en-us/library/ms378956(v=sql.90).aspx

1) Running standalone Groovy Scripts
http://www.vasanth.in/

Sun/Oracle – Java – JDBC Driver – Microsoft / JDBC Driver v3.0

Playing a bit with Java JDBC Drivers this morning.

I needed a quick avenue to play with Java\JDBC Connection URL this morning.

As I did not have a simple, free JDBC-able Query tool on the machine I was using,I downloaded JaySQL from http://synametrics.com/SynametricsWebApp/WinSQLForJdbc.jsp.

The GUI is a bit difficult to use, but nevertheless configured it to point to my recently downloaded Microsoft’s JDBC Driver – http://msdn.microsoft.com/en-us/sqlserver/aa937724.  It is version 3.0 software and it works well.

Nevertheless, within JSQL I configured the JDBC Connection String and pointed to the DB Server.

But, the tool was just not working it was stuck and not quite working.

So next I tried my more trusted Cross-Platform Database Query Tool, DBVisualizer (http://www.dbvis.com/).

I set up the Driver Manager – pointed at the same JDBC Driver.

And, thankfully, it gave me a much better, cleaner, and revealing error message:

The error message states clearly – Java RunTime Environment (JRE) version 1.6 is not supported by the driver.  Use the sqljdbc4.jar class library…

Thus, configured the Driver Manager to use sqljdbc4.jar

 

 

Things worked better.

Now re-intimated with DBViz, I probably should continue using it.

But, to close off, returned to JaySQL…

a] Terminated the existing hung process

b] Re-initiated a new JaySQL process

c] Re-configured the Driver Manager to point to sqljdbc4.jar

And, now things worked better

References:

  1. Microsoft & jTDS JDBC driver broken after update to 1.6.0_29
    http://bugs.sun.com/bugdatabase/view_bug.do?bug_id=7105007

  2. System Requirements for JDBC
    http://msdn.microsoft.com/en-us/library/ms378422.aspx 

Java – JDBC – Microsoft Drivers – Named Instance/ “Un-Default” Port Issues

Perplexingly, there are various issues one may run into while attempting to connect to a MS SQL Server Instance, especially the partner node of a mirrored-database.

This problem seems to be more common when MS SQL Server is running on something other than its default port 1433.

The problem is also well chronicled

  1. SQL 2005 JDBC Driver and Database Mirroring
    http://blogs.msdn.com/psssql/archive/2008/12/31/sql-2005-jdbc-driver-and-database-mirroring.aspx
  2. Running SQL Server ‘Default’ instance on a non-default (or non-standard) TCP port: : tips for making application connectivity work
    http://blogs.msdn.com/dataaccesstechnologies/archive/2010/03/03/running-sql-server-default-instance-on-a-non-default-or-non-standard-tcp-port-tips-for-making-application-connectivity-work.aspx
  3. The Failover Partner connection string attribute cannot connect to a mirror partner server by using the SQL Native Client OLE DB provider and the SQL Native Client ODBC driver in a .NET Framework-based application (http://support.microsoft.com/kb/941526)

 

Sample JDBC URL

Assumptions:

a) MS SQL Server Name is EPH
b) MS SQL Server Failover Partner is EphMirr
c) Database Name is intra

a)
DB_CONN_STRING=jdbc:sqlserver://Eph:2060;databaseName=intra;failoverPartner=EphMirr;
( works well)

b)DB_CONN_STRING=jdbc:sqlserver://Eph:2060;databaseName=intra;failoverPartner=EphMirr\\MSSQL2008;
( works well)

c)
DB_CONNECTION_STRING=jdbc:sqlserver://BETHEL:2060;databaseName=intra;failoverPartner=HOLYSPIRIT,2060;
( does not work)

d)DB_CONNECTION_STRING=jdbc:sqlserver://BETHEL:2060;databaseName=intra;failoverPartner=DBMIRR\\MSSQL2008
(
works — if DBMirr is resolvable thru DNS or
on MS Windows c:\windows\system32\drivers\etc\hosts

note that is is not enough to have used cliconfg.exe to create an alias
)

Issues:

The big problem seems to revolve around the fact that MS SQL Server does not properly resolve the port number for the partner node

Review of our various connection strings:

a) DB_CONN_STRING=jdbc:sqlserver://Eph:2060;databaseName=intra;failoverPartner=EphMirr;

LIFE IS GOOD

  • Since the partner is listening on MS SQL Server default port of 1433, no need to specify or indicate otherwise

b)

DB_CONN_STRING=jdbc:sqlserver://Eph:2060;databaseName=intra;failoverPartner=EphMirr\\MSSQL2008;

LIFE IS GOOD

  • It appears that the JDBC Driver (running on the client) correctly attempts to  engage the “SQL Server Browser” service on the database server – Note that this service, by default is listening on UDP Port 1434, and so Server\ DBA\Firewall Admins should ensure that this service is set to running and service mode set to “Automatic”
  • Also note that we have “escaped” the \ (the separator between the server and alias name) and have it as \\ (two backslashes)

c)

DB_CONNECTION_STRING=jdbc:sqlserver://BETHEL:2060;databaseName=intra;failoverPartner=HOLYSPIRIT,2060;


LIFE IS NOT SO GOOD

d) If one were try to create a SQL Client Alias

DB_CONNECTION_STRING=jdbc:sqlserver://BETHEL:2060;databaseName=intra;failoverPartner=DBMIRR\\MSSQL2008

  • Note that creating a SQL Client alias is not useful — Especially, as SQL Client Alias are MS OS Specifics, and does not translate to Unix
  • Also any alias created will have to resolvable at the Network\OS level (DNS or c:\windows\system32\drivers\etc\hosts)
  • Also, note that if MS SQL Server is not listening on its default port or its a named instance then make sure that “SQL Server Browser” is running on the server

Microsoft – MSSQLServer / JDBC 3.0 Driver Released

Microsoft – MSSQLServer / JDBC 3.0 Driver Released

For those who develop\support Oracle\Sun Java based Applications that interacts with MS SQL Server, you may want to consider MS latest JDBC Driver – Microsoft SQL Server JDBC Driver 3.0. 

It is available @ http://www.microsoft.com/downloads/details.aspx?FamilyID=a737000d-68d0-4531-b65d-da0f2a735707&displaylang=en