dbi Blog

This is part II of the article. See Part I.

A tale of 2 Oracle drivers

Oracle’s drivers support 2 implementations: the pure java one as used above, also named Thin drivers, with the following URL syntax:

jdbc:oracle:thin:@//db_machine:listener_port/service_name

but also the so-called OCI-based or Thick Type 2 drivers that rely on SQL*Net and the connect strings defined in tnsnames.ora, with the following syntax:

jdbc:oracle:oci:@connect_string

Since they use OCI native libraries, they are said to be faster than their pure java implementation counterpart. To have those libraries, an InstantClient is needed at the minimum. Here, I’m using an existing InstantClient v21.7 for Linux installation /u01/dctm/repo01/oracle/instantclient_21_7. It comes with its own jdbc drivers which are incompatible with the ones downloaded before (but can do both Thin and OCI), so let’s switch to the bundled ones. As they depend on native OCI libraries, those must be made accessible through the LD_LIBRARY_PATH.

Here is the definition of the connect string mypdb1 we will use; it has the same settings as the ones explicitly used with the Thin drivers:

mypdb1 =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.21)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = pdb1)
    )
  )

Let’s adapt the environment and syntax, and try:

$ export LD_LIBRARY_PATH=/u01/dctm/repo01/oracle/instantclient_21_7
$ java -cp /u01/dctm/repo01/oracle/instantclient_21_7/ojdbc8.jar:. jdbc_tester_generic oracle.jdbc.driver.OracleDriver repo01 repo01 jdbc:oracle:oci:@mypdb1 "select 'now in Oracle db: ' || to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') as now from dual"
The command-line parameters are:
arg 0 = oracle.jdbc.driver.OracleDriver
arg 1 = repo01
arg 2 = repo01
arg 3 = jdbc:oracle:oci:@mypdb1
arg 4 = select 'now in Oracle db: ' || to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') as now from dual

Loading driver [oracle.jdbc.driver.OracleDriver]:
JDBC Driver oracle.jdbc.driver.OracleDriver successfully loaded

Connecting to url [jdbc:oracle:oci:@mypdb1] as user [repo01]:
Successful connection to database

Executing SQL query [select 'now in Oracle db: ' || to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') as now from dual]:

Retrieving the result set:
now in Oracle db: 22/01/2023 15:51:23

JDBC connection successfully tested, quitting …

As illustrated, since those drivers are based on OCI, they go through the SQL*Net stack to read the connectivity configuration behind the connect string mypdb1, namely from the tnsnames.ora file; hence this information does not need to be provided any more, which reflects in the simpler JDBC URL. Whenever the Oracle client is installed along with JDBC programs, it is preferable to use the OCI drivers to avoid replicating connection information instead of accessing it from one single place, although when working with RDBMS from different vendors, it is understandable to standardize to the Thin drivers for generality and portability. Anyway, there are sufficient differences in the URL syntax across RDBMS that one more does not change much. For example, Oracle Thin drivers also allows to specify the URL through the full, inlined tnsnames.ora syntax, e.g.:

$ export LD_LIBRARY_PATH=/u01/dctm/repo01/oracle/instantclient_21_7
$ java -cp /u01/dctm/repo01/oracle/instantclient_21_7/ojdbc8.jar:. jdbc_tester_generic oracle.jdbc.driver.OracleDriver repo01 repo01 jdbc:oracle:thin:@"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) (HOST=192.168.0.21)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=pdb1)))" "select 'now in Oracle db: ' || to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') as now from dual"
The command-line parameters are:
arg 0 = oracle.jdbc.driver.OracleDriver
arg 1 = repo01
arg 2 = repo01
arg 3 = jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) (HOST=192.168.0.21)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=pdb1)))
arg 4 = select 'now in Oracle db: ' || to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') as now from dual

Loading driver [oracle.jdbc.driver.OracleDriver]:
JDBC Driver oracle.jdbc.driver.OracleDriver successfully loaded

Connecting to url [jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) (HOST=192.168.0.21)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=pdb1)))] as user [repo01]:
Successful connection to database
...
JDBC connection successfully tested, quitting …

and so do the OCI drivers:

$ export LD_LIBRARY_PATH=/u01/dctm/repo01/oracle/instantclient_21_7
$ java -cp /u01/dctm/repo01/oracle/instantclient_21_7/ojdbc8.jar:. jdbc_tester_generic oracle.jdbc.driver.OracleDriver repo01 repo01 jdbc:oracle:oci:@"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) (HOST=192.168.0.21)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=pdb1)))" "select 'now in Oracle db: ' || to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') as now from dual"
The command-line parameters are:
arg 0 = oracle.jdbc.driver.OracleDriver
arg 1 = repo01
arg 2 = repo01
arg 3 = jdbc:oracle:oci:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) (HOST=192.168.0.21)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=pdb1)))
arg 4 = select 'now in Oracle db: ' || to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') as now from dual

Loading driver [oracle.jdbc.driver.OracleDriver]:
JDBC Driver oracle.jdbc.driver.OracleDriver successfully loaded

Connecting to url [jdbc:oracle:oci:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) (HOST=192.168.0.21)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=pdb1)))] as user [repo01]:
Successful connection to database
...
JDBC connection successfully tested, quitting ...

Using a SID with Oracle JDBC

Oracle JDBC drivers allow to specify a service name, as shown so far, but also the deprecated, old-style SID. The URL syntax changes slighty. Here is a summary of the all the Oracle JDBC URL syntaxes:

# with a service name;
# Thin:
jdbc:oracle:thin:@//192.168.0.21:1521/pdb1
# OCI:
jdbc:oracle:oci:@mypdb1

# with a sid;
# Thin:
jdbc:oracle:thin:@192.168.0.21:1521:orcl
# OCI:
jdbc:oracle:oci:@mysid

with the following mysid‘s definition in tnsnames.ora:

mysid =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = db)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = orcl)
    )
  )

where orcl is the sid of the container database. As we are accessing the container database now, and the repo01 account does not exist there, we must switch to a different account; let’s use system as it is defined everywhere (however, it is still possible to access a pluggable database through its SID, and use the repo01 account; for this, set the listener’s parameter USE_SID_AS_SERVICE_listener=on; I did not try it though and I suspect that the service syntax will have to be used but with a SID instead of a service name).

Let’s adapt the environment and syntax, and try first with the OCI drivers:

$ export LD_LIBRARY_PATH=/u01/dctm/repo01/oracle/instantclient_21_7
$ java -cp /u01/dctm/repo01/oracle/instantclient_21_7/ojdbc8.jar:. jdbc_tester_generic oracle.jdbc.driver.OracleDriver system manager jdbc:oracle:oci:@mysid "select 'now in Oracle db: ' || to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') as now from dual"
The command-line parameters are:
arg 0 = oracle.jdbc.driver.OracleDriver
arg 1 = system
arg 2 = manager
arg 3 = jdbc:oracle:oci:mysid
arg 4 = select 'now in Oracle db: ' || to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') as now from dual

Loading driver [oracle.jdbc.driver.OracleDriver]:
JDBC Driver oracle.jdbc.driver.OracleDriver successfully loaded

Connecting to url [jdbc:oracle:oci:@mysid] as user [system]:
Successful connection to database
...
JDBC connection successfully tested, quitting ...

Using the full inline definition behind the mysid connect string:

$ export LD_LIBRARY_PATH=/u01/dctm/repo01/oracle/instantclient_21_7
$ java -cp /u01/dctm/repo01/oracle/instantclient_21_7/ojdbc8.jar:. jdbc_tester_generic oracle.jdbc.driver.OracleDriver system manager jdbc:oracle:oci:@"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) (HOST=192.168.0.21)(PORT=1521))(CONNECT_DATA=(SID = orcl)))" "select 'now in Oracle db: ' || to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') as now from dual"
The command-line parameters are:
arg 0 = oracle.jdbc.driver.OracleDriver
arg 1 = system
arg 2 = manager
arg 3 = jdbc:oracle:oci:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) (HOST=192.168.0.21)(PORT=1521))(CONNECT_DATA=(SID = orcl)))
arg 4 = select 'now in Oracle db: ' || to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') as now from dual

Loading driver [oracle.jdbc.driver.OracleDriver]:
JDBC Driver oracle.jdbc.driver.OracleDriver successfully loaded

Connecting to url [jdbc:oracle:oci:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) (HOST=192.168.0.21)(PORT=1521))(CONNECT_DATA=(SID = orcl)))] as user [system]:
Successful connection to database
...
JDBC connection successfully tested, quitting ...

Let’s try now this SID with the Thin driver:

$ java -cp ojdbc11.jar:. jdbc_tester_generic oracle.jdbc.driver.OracleDriver system manager jdbc:oracle:thin:@192.168.0.21:1521:orcl "select 'now in Oracle db: ' || to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') as now from dual"
The command-line parameters are:
arg 0 = oracle.jdbc.driver.OracleDriver
arg 1 = system
arg 2 = manager
arg 3 = jdbc:oracle:thin:@192.168.0.21:1521:orcl
arg 4 = select 'now in Oracle db: ' || to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') as now from dual

Loading driver [oracle.jdbc.driver.OracleDriver]:
JDBC Driver oracle.jdbc.driver.OracleDriver successfully loaded

Connecting to url [jdbc:oracle:thin:@192.168.0.21:1521:orcl] as user [system]:
Successful connection to database
...
JDBC connection successfully tested, quitting ...

Using the inline connect string definition with the Thin drivers:

$ java -cp ojdbc11.jar:.:. jdbc_tester_generic oracle.jdbc.driver.OracleDriver system manager jdbc:oracle:thin:@"(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) (HOST=192.168.0.21)(PORT=1521))(CONNECT_DATA=(SID = orcl)))" "select 'now in Oracle db: ' || to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') as now from dual" 
The command-line parameters are:
arg 0 = oracle.jdbc.driver.OracleDriver
arg 1 = system
arg 2 = manager
arg 3 = jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) (HOST=192.168.0.21)(PORT=1521))(CONNECT_DATA=(SID = orcl)))
arg 4 = select 'now in Oracle db: ' || to_char(sysdate, 'DD/MM/YYYY HH24:MI:SS') as now from dual

Loading driver [oracle.jdbc.driver.OracleDriver]:
JDBC Driver oracle.jdbc.driver.OracleDriver successfully loaded

Connecting to url [jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP) (HOST=192.168.0.21)(PORT=1521))(CONNECT_DATA=(SID = orcl)))] as user [system]:
Successful connection to database
...
JDBC connection successfully tested, quitting ...

As showed above, the utility jdbc_tester_generic is URL- and drivers-agnostic; just provide the right parameters and they will be passed unchanged to the drivers.

Other RDBMS

We tested jdbc_tester_generic with PostgreSQL and Oracle and chances are that it is really generic indeed as it could handle Oracle’s multiple specificities. Nonetheless, let’s try it with 7 more RDBMS, e.g. MariaDB/MySQL, SQLite, Firebird, Microsoft SQL Server, HSQLDB, MongoDB and Excel, and see how it behaves. If you don’t have those installed somewhere, concise instructions to do it are presented in several articles published on the dbi-services blog site https://www.dbi-services.com/blog/installing-the-odbc-driver-manager-with-*, e.g. https://www.dbi-services.com/blog/installing-the-odbc-driver-manager-with-sqlite-on-linux/ for SQLite.

After the installation of the software and the creation of the databases, their readiness can be verified via ODBC as explained in the articles. Hereafter, we assume that those databases are up and running, with valid credentials where applicable, and we will try to access them via JDBC this time.

MariaDB

First, let’s install the JDBC drivers for MariaDB:

$ wget https://dbschema.com/jdbc-drivers/MariaDbJdbcDriver.zip
$ unzip MariaDbJdbcDriver.zip

The server’s default port is 3306 but let’s check it:

$ netstat -tlnp
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name
...
tcp 0 0 127.0.0.1:3306 0.0.0.0:* LISTEN 112172/mariadbd

# or:
$ grep -i 'port =' /etc/mysql/my.cnf
$ port = 3306

# or
$ mysql
show variables like 'port';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| port          | 3306  |
+---------------+-------+
1 row in set (0.006 sec)

Let’s invoke the tester now:

$ java -cp mariadb-java-client-3.0.6.jar:. jdbc_tester_generic org.mariadb.jdbc.Driver debian debian jdbc:mariadb://localhost:3306/sampledb "select concat('now in mysql db: ', DATE_FORMAT(SYSDATE(), '%d/%m/%Y %H:%i:%s')) as now;"
The command-line parameters are:
arg 0 = org.mariadb.jdbc.Driver
arg 1 = debian
arg 2 = debian
arg 3 = jdbc:mariadb://localhost:3306/sampledb
arg 4 = select concat('now in mysql db: ', DATE_FORMAT(SYSDATE(), '%d/%m/%Y %H:%i:%s')) as now;

Loading driver [org.mariadb.jdbc.Driver]:
JDBC Driver org.mariadb.jdbc.Driver successfully loaded

Connecting to url [jdbc:mariadb://localhost:3306/sampledb] as user [debian]:
Successful connection to database

Executing SQL query [select concat('now in mysql db: ', DATE_FORMAT(SYSDATE(), '%d/%m/%Y %H:%i:%s')) as now;]:

Retrieving the result set:
now in mysql db: 23/01/2023 21:13:00

JDBC connection successfully tested, quitting ...

No surprise with MariaDB.

SQLite

First, let’s install the JDBC drivers for SQLite:

$ wget https://repo1.maven.org/maven2/org/xerial/sqlite-jdbc/3.40.0.0/sqlite-jdbc-3.40.0.0.jar

Let’s invoke the tester:

$ java -cp sqlite-jdbc-3.40.0.0.jar:. jdbc_tester_generic java.sql.Driver debian debian jdbc:sqlite:/path/to/database/sampledb "select 'now in sqlite db: ' || STRFTIME('%d/%m/%Y, %H:%M:%S', datetime()) as now;"
The command-line parameters are:
arg 0 = java.sql.Driver
arg 1 = debian
arg 2 = debian
arg 3 = jdbc:sqlite:/path/to/database/sampledb
arg 4 = select 'now in sqlite db: ' || STRFTIME('%d/%m/%Y, %H:%M:%S', datetime()) as now;

Loading driver [java.sql.Driver]:
JDBC Driver java.sql.Driver successfully loaded

Connecting to url [jdbc:sqlite:/path/to/database/sampledb] as user [debian]:
Successful connection to database

Executing SQL query [select 'now in sqlite db: ' || STRFTIME('%d/%m/%Y, %H:%M:%S', datetime()) as now;]:

Retrieving the result set:
now in sqlite db: 23/01/2023, 22:02:13

JDBC connection successfully tested, quitting ...

No surprise with SQLite either.

See Part III.

Post Views: 3,146

A generic jdbc tester (part II)

A tale of 2 Oracle drivers

Using a SID with Oracle JDBC

Other RDBMS

MariaDB

SQLite

Cesare Cervini

Leave a Reply:

Related blog articles

A tale of 2 Oracle drivers

Using a SID with Oracle JDBC

Other RDBMS

MariaDB

SQLite

Cesare Cervini

Leave a Reply:

Related blog articles

ODA X10-L storage configuration is different from what you may expect

ODA patching: ASR manager stuck to old version

REKEY operation on Oracle Database configured with Oracle Key Vault

ODA X10 lineup now supports Standard Edition 2