dbi Blog

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

Firebird

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

$ wget https://github.com/FirebirdSQL/jaybird/releases/download/v5.0.0/jaybird-5.0.0.java11.zip
$ unzip -x jaybird-5.0.0.java11.zip jaybird-5.0.0.java11.jar

Our Firebird database was installed embedded, i.e. locally without a server (this is functionally similar to SQLite). In this configuration, the JDBC drivers need the JNA classes, so download them too:

$ wget https://repo1.maven.org/maven2/net/java/dev/jna/jna-platform-jpms/5.13.0/jna-5.13.0.jar

Let’s invoke the tester:

$ java -cp jna-5.13.0.jar:jaybird-5.0.0.java11.jar:. jdbc_tester_generic org.firebirdsql.jdbc.FBDriver SYSDBA SYSDBA "jdbc:firebird:embedded:/path/to/database/sampledb.fb" "select 'now in firebird db: ' || cast('NOW' as timestamp) from rdb\$database;"
The command-line parameters are:
arg 0 = org.firebirdsql.jdbc.FBDriver
arg 1 = SYSDBA
arg 2 = SYSDBA
arg 3 = jdbc:firebird:embedded:/path/to/database/sampledb.fb
arg 4 = select 'now in firebird db: ' || cast('NOW' as timestamp) from rdb$database;

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

Connecting to url [jdbc:firebird:embedded:/media/sf_customers/dbi/odbc4gawk/sampledb.fb] as user [SYSDBA]:
Successful connection to database

Executing SQL query [select 'now in firebird db: ' || cast('NOW' as timestamp) from rdb$database;]:

Retrieving the result set:
now in firebird db: 2023-01-23 22:43:45.1910

JDBC connection successfully tested, quitting ...

No surprise with Firebird.

Admittedly, the above is is not your typical configuration but the documentation lists lots of syntax variants to connect via JDBC when Firebird is configured with a server process. E.g.:

$ java -cp jaybird-5.0.0.java11.jar:. jdbc_tester_generic org.firebirdsql.jdbc.FBDriver SYSDBA SYSDBA "jdbc:firebird:/media/sf_customers/dbi/odbc4gawk/sampledb.fb?serverName=localhost&portNumber=3050" "select 'now in firebird db: ' || cast('NOW' as timestamp) from rdb$database;"

$ java -cp jna-5.13.0.jar:jaybird-5.0.0.java11.jar:. jdbc_tester_generic org.firebirdsql.jdbc.FBDriver SYSDBA SYSDBA "jdbc:firebird:native:inet4://locahost//path/to/database/sampledb.fb?serverName=localhost&portNumber=3050" "select 'now in firebird db: ' || cast('NOW' as timestamp) from rdb$database;"

$ java -cp jaybird-5.0.0.java11.jar:. jdbc_tester_generic org.firebirdsql.jdbc.FBDriver SYSDBA SYSDBA "jdbc:firebird:?serverName=localhost&portNumber=3050&databaseName=/path/to/database/sampledb.fb" "select 'now in firebird db: ' || cast('NOW' as timestamp) from rdb$database;"

$ java -cp jaybird-5.0.0.java11.jar:. jdbc_tester_generic org.firebirdsql.jdbc.FBDriver SYSDBA SYSDBA "jdbc:firebird:?databaseName=//localhost///path/to/database/sampledb.fb" "select 'now in firebird db: ' || cast('NOW' as timestamp) from rdb$database;"

$ java -cp jaybird-5.0.0.java11.jar:. jdbc_tester_generic org.firebirdsql.jdbc.FBDriver SYSDBA SYSDBA "jdbc:firebirdsql://localhost:3050/path/to/database/sampledb.fb" "select 'now in firebird db: ' || cast('NOW' as timestamp) from rdb$database;"

$ java -cp jaybird-5.0.0.java11.jar:. jdbc_tester_generic org.firebirdsql.jdbc.FBDriver SYSDBA SYSDBA "jdbc:firebirdsql://localhost/path/to/database/sampledb.fb" "select 'now in firebird db: ' || cast('NOW' as timestamp) from rdb$database;"

etc…

By the way, rdb$database, really? Could Firebird by a close parent of DEC’s RDB, the former RDBMS purchased by Oracle when DEC was on the brink of bankruptcy?

Microsoft SQL Server

The JDBC drivers for SQL Server for be downloaded by following this link https://go.microsoft.com/fwlink/?linkid=2222207 as there is not direct link to the jar apparently. Once the archive has been downloaded, the jar mssql-jdbc-11.2.3.jre17.jar must be extracted.

Let’s now invoke the tester:

$ java -cp mssql-jdbc-11.2.3.jre17.jar:. jdbc_tester_generic com.microsoft.sqlserver.jdbc.SQLServerDriver SA mypassord "jdbc:sqlserver://localhost:1433;databaseName=sampledb;encrypt=false;" "select concat('now in sqlsrver db: ', format(getdate(), 'dd/MM/yyyy HH:mm:ss')) as now"
The command-line parameters are:
arg 0 = com.microsoft.sqlserver.jdbc.SQLServerDriver
arg 1 = SA
arg 2 = mypassord
arg 3 = jdbc:sqlserver://localhost:1433;databaseName=sampledb;encrypt=false;
arg 4 = select concat('now in sqlsrver db: ', format(getdate(), 'dd/MM/yyyy HH:mm:ss')) as now

Loading driver [com.microsoft.sqlserver.jdbc.SQLServerDriver]:
JDBC Driver com.microsoft.sqlserver.jdbc.SQLServerDriver successfully loaded

Connecting to url [jdbc:sqlserver://localhost:1433;databaseName=sampledb;encrypt=false;] as user [SA]:
Successful connection to database

Executing SQL query [select concat('now in sqlsrver db: ', format(getdate(), 'dd/MM/yyyy HH:mm:ss')) as now]:

Retrieving the result set:
now in sqlsrver db: 23/01/2023 23:33:28

JDBC connection successfully tested, quitting ...

No surprise with SQL Server.

HSQLDB

As HSQLDB is a RDBMS written in java, it already comes with its JDBC drivers, so no need to download them. We will just copy them from their installation directory into the current directory. The jar name is hsqldb-jdk8.jar.

Let’s invoke the tester:

$ java -cp hsqldb-jdk8.jar:. jdbc_tester_generic org.hsqldb.jdbcDriver sa sa jdbc:hsqldb:localhost/runtime,user=sa "select concat('now in hsqldb: ', TO_CHAR(current_timestamp, 'YYYY-MM-DD HH:MM:SS')) as now from INFORMATION_SCHEMA.SYSTEM_USERS;"
The command-line parameters are:
arg 0 = org.hsqldb.jdbcDriver
arg 1 = sa
arg 2 = sa
arg 3 = jdbc:hsqldb:localhost/runtime,user=sa
arg 4 = select concat('now in hsqldb: ', TO_CHAR(current_timestamp, 'YYYY-MM-DD HH:MM:SS')) as now from INFORMATION_SCHEMA.SYSTEM_USERS;

Loading driver [org.hsqldb.jdbcDriver]:
JDBC Driver org.hsqldb.jdbcDriver successfully loaded

Connecting to url [jdbc:hsqldb:localhost/runtime,user=sa] as user [sa]:
Successful connection to database

Executing SQL query [select concat('now in hsqldb: ', TO_CHAR(current_timestamp, 'YYYY-MM-DD HH:MM:SS')) as now from INFORMATION_SCHEMA.SYSTEM_USERS;]:

Retrieving the result set:
now in hsqldb: 2023-01-23 11:01:31

JDBC connection successfully tested, quitting …

No surprise with HSQLDB either.

We’re done with the relational databases and so far the tester has behaved like a charm. Let’s now tackle non-relational data sources.

MongoDB

MongoDB is a noSQL database and this reflects in its JDBC drivers: they don’t support SQL function calls sent by the java SQL libraries such as sqlGetResultSchema() and therefore fail as soon as the tester attempts a connection, e.g.:

...
Loading driver [com.mongodb.jdbc.MongoDriver]:
JDBC Driver com.mongodb.jdbc.MongoDriver successfully loaded

Connecting to url [jdbc:mongodb://localhost:27017/sampledb] as user [sa]:
Exception occurred connecting to database: Connection failed.
java.sql.SQLException: Connection failed.
at com.mongodb.jdbc.MongoDriver.connect(MongoDriver.java:175)
at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:677)
at java.sql/java.sql.DriverManager.getConnection(DriverManager.java:228)
at jdbc_tester_generic.main(jdbc_tester_generic.java:43)
Caused by: java.util.concurrent.ExecutionException: com.mongodb.MongoCommandException: Command failed with error 59 (CommandNotFound): 'no such command: 'sqlGetResultSchema'' on server localhost:27017. The full response is {"ok": 0.0, "errmsg": "no such command: 'sqlGetResultSchema'", "code": 59, "codeName": "CommandNotFound"}
at java.base/java.util.concurrent.FutureTask.report(FutureTask.java:122)
at java.base/java.util.concurrent.FutureTask.get(FutureTask.java:205)
at com.mongodb.jdbc.MongoConnection.testConnection(MongoConnection.java:510)
at com.mongodb.jdbc.MongoDriver.connect(MongoDriver.java:171)
... 3 more
Caused by: com.mongodb.MongoCommandException: Command failed with error 59 (CommandNotFound): 'no such command: 'sqlGetResultSchema'' on server localhost:27017. The full response is {"ok": 0.0, "errmsg": "no such command: 'sqlGetResultSchema'", "code": 59, "codeName": "CommandNotFound"}
at com.mongodb.internal.connection.ProtocolHelper.getCommandFailureException(ProtocolHelper.java:175)
…

Fortunately, there are commercial versions that map SQL statements to MongoDB’s NoSQL syntax. One of these is UnityJDBC. It is free to try for 30 days without limits and after that, it is limited to a maximum of 100 results (per query I guess !). It can also work with ServiceNow and Splunk. For our purpose, it is sufficient.

It can be downloaded from here: https://unityjdbc.com/download.php?type=mongodb.

To install it, launch the downloaded jar file:

$ java -jar UnityJDBC_Trial_Install.jar

Download also the logging API slf4j-api-2.0.6.jar and a logger such as slf4j-simple-2.0.6.jar or slf4j-jdk14-2.0.6.jar:

$ wget https://repo1.maven.org/maven2/org/slf4j/slf4j-api/2.0.6/slf4j-api-2.0.6.jar

$ wget https://repo1.maven.org/maven2/org/slf4j/slf4j-simple/2.0.6/slf4j-simple-2.0.6.jar

$ wget https://repo1.maven.org/maven2/org/slf4j/slf4j-jdk14/2.0.6/slf4j-jdk14-2.0.6.jar

Those are not required but if provided, they get rid of the warning:

WARNING: SLF4J not found on the classpath. Logging is disabled for the 'org.mongodb.driver' component

We will use an account sa/sa in database sampledb for the tests below but you are free to use whatever account in whatever database you have, of course. If there are no accounts that can be used, the sa one can be created thusly (example in sampledb, adapt as needed):

$ mongosh
test> show dbs
admin       41 kB
config    73.7 kB
local     81.9 kB
sampledb   516 kB
test      81.9 kB

# select our sampledb;
use sampledb

# list existing users;
db.getUsers()

# create sa in sampledb if needed;
db.createUser(
   {
     user: "sa",
     pwd: "sa",
     roles: [  ]
   }
)

# list existing users;
sampledb>  db.getUsers()
{
  users: [
    {
      _id: 'sampledb.sa',
      userId: UUID("ed042a6a-cd89-47de-a3e7-77589e015746"),
      user: 'sa',
      db: 'sampledb',
      roles: [],
      mechanisms: [ 'SCRAM-SHA-1', 'SCRAM-SHA-256' ]
    }
  ],
  ok: 1
}

Those drivers support 2 syntaxes:

URL format:
jdbc:mongo://<serverName>/<databaseName>

MongoDB URL format:
jdbc:mongodb://[username:password@]host1[:port1][,...hostN[:portN]][/[defaultauthdb][?options]]
OR:
jdbc:mongodb+srv://[username:password@]host1[:port1][,...hostN[:portN]][/[defaultauthdb][?options]]

Execute the tester with both syntaxes:

$ java -cp UnityJDBC/mongodb_unityjdbc_full.jar:slf4j-api-2.0.6.jar:slf4j-simple-2.0.6.jar:. jdbc_tester_generic mongodb.jdbc.MongoDriver sa sa jdbc:mongodb://localhost:27017/sampledb "select 'now in mongodb: ' || CURRENT_TIMESTAMP"
The command-line parameters are:
arg 0 = mongodb.jdbc.MongoDriver
arg 1 = sa
arg 2 = sa
arg 3 = jdbc:mongodb://localhost:27017/sampledb
arg 4 = select 'now in mongodb: ' || CURRENT_TIMESTAMP

Loading driver [mongodb.jdbc.MongoDriver]:
JDBC Driver mongodb.jdbc.MongoDriver successfully loaded

Connecting to url [jdbc:mongodb://localhost:27017/sampledb] as user [sa]:
[main] INFO org.mongodb.driver.cluster - Cluster created with settings {hosts=[localhost:27017], mode=SINGLE, requiredClusterType=UNKNOWN, serverSelectionTimeout='30000 ms'}
[cluster-rtt-ClusterId{value='63d051525cbedc5285b70614', description='null'}-localhost:27017] INFO org.mongodb.driver.connection - Opened connection [connectionId{localValue:2, serverValue:139}] to localhost:27017
[cluster-ClusterId{value='63d051525cbedc5285b70614', description='null'}-localhost:27017] INFO org.mongodb.driver.connection - Opened connection [connectionId{localValue:1, serverValue:140}] to localhost:27017
[cluster-ClusterId{value='63d051525cbedc5285b70614', description='null'}-localhost:27017] INFO org.mongodb.driver.cluster - Monitor thread successfully connected to server with description ServerDescription{address=localhost:27017, type=STANDALONE, state=CONNECTED, ok=true, minWireVersion=0, maxWireVersion=13, maxDocumentSize=16777216, logicalSessionTimeoutMinutes=30, roundTripTimeNanos=28536232}
Successful connection to database

Executing SQL query [select 'now in mongodb: ' || CURRENT_TIMESTAMP]:

Retrieving the result set:
now in mongodb: Tue Jan 24 22:00:28 CET 2023

JDBC connection successfully tested, quitting …

The log above was output in json format.

Let’s try also the second syntax but with an alternate logger this time:

$ java -cp UnityJDBC/mongodb_unityjdbc_full.jar:slf4j-simple-2.0.6.jar:slf4j-jdk14-2.0.6.jar:. jdbc_tester_generic mongodb.jdbc.MongoDriver unused unused jdbc:mongodb://sa:sa@localhost:27017/sampledb "select 'now in mongodb: ' || CURRENT_TIMESTAMP"
The command-line parameters are:
arg 0 = mongodb.jdbc.MongoDriver
arg 1 = unused
arg 2 = unused
arg 3 = jdbc:mongodb://sa:sa@localhost:27017/sampledb
arg 4 = select 'now in mongodb: ' || CURRENT_TIMESTAMP

Loading driver [mongodb.jdbc.MongoDriver]:
JDBC Driver mongodb.jdbc.MongoDriver successfully loaded

Connecting to url [jdbc:mongodb://sa:sa@localhost:27017/sampledb] as user [unused]:
Jan 24, 2023 10:46:23 PM com.mongodb.diagnostics.logging.SLF4JLogger info
INFO: Cluster created with settings {hosts=[localhost:27017], mode=SINGLE, requiredClusterType=UNKNOWN, serverSelectionTimeout='30000 ms'}
Jan 24, 2023 10:46:23 PM com.mongodb.diagnostics.logging.SLF4JLogger info
INFO: Opened connection [connectionId{localValue:1, serverValue:142}] to localhost:27017
Jan 24, 2023 10:46:23 PM com.mongodb.diagnostics.logging.SLF4JLogger info
INFO: Opened connection [connectionId{localValue:2, serverValue:141}] to localhost:27017
Jan 24, 2023 10:46:23 PM com.mongodb.diagnostics.logging.SLF4JLogger info
INFO: Monitor thread successfully connected to server with description ServerDescription{address=localhost:27017, type=STANDALONE, state=CONNECTED, ok=true, minWireVersion=0, maxWireVersion=13, maxDocumentSize=16777216, logicalSessionTimeoutMinutes=30, roundTripTimeNanos=23671572}
Successful connection to database

Executing SQL query [select 'now in mongodb: ' || CURRENT_TIMESTAMP]:

Retrieving the result set:
now in mongodb: Tue Jan 24 22:02:02 CET 2023

JDBC connection successfully tested, quitting ...

The log above was output in traditional JDK format.

The jdbc tester works fine with MongoDB too when JDBC drivers with SQL support are provided.

Excel

Like MongoDB, Excel spreadsheets are NoSQL, or more exactly no nothing actually because they’re not even databases. However, the correspondence with a relational database can be established very simply: some drivers map the directory containing spreadsheet files as a server, spreadsheet files as databases, sheets with their lines and columns as tables. The drivers from HXTT below don’t use the notion of server for files but want the full path name in one piece as the database. Other drivers want just the dirname of the file and SELECT against the file_name.sheet_name. The JDBC drivers interpret the SQL statements into operations applied to the spreadsheet to perform selections, projections, joins, etc… Let’s see if the tester can work with one of such unconventional drivers.

Several commercial JDBC drivers for Excel are available with a 30-day trial time. I picked the ones here http://www.hxtt.com/excel.html for no particular reason. Let’s download them:

$ wget http://www.hxtt.com/excel.zip
$ unzip excel.zip lib/Excel_JDBC30.jar

and try the tester:

$ java -cp lib/Excel_JDBC30.jar:. jdbc_tester_generic com.hxtt.sql.excel.ExcelDriver unused unused "jdbc:excel:////home/debian/jdbc_tester_generic/SampleWorkbook.xls" "select distinct top 1 concat('now in SampleWorkbook.xls: ', now()) from countries;"
The command-line parameters are:
arg 0 = com.hxtt.sql.excel.ExcelDriver
arg 1 = unused
arg 2 = unused
arg 3 = jdbc:excel:////home/debian/jdbc_tester_generic/SampleWorkbook.xls
arg 4 = select distinct top 1 concat('now in SampleWorkbook.xls: ', now()) from countries;

Loading driver [com.hxtt.sql.excel.ExcelDriver]:
JDBC Driver com.hxtt.sql.excel.ExcelDriver successfully loaded

Connecting to url [jdbc:excel:////home/debian/jdbc_tester_generic/SampleWorkbook.xls] as user [unused]:
Successful connection to database

Executing SQL query [select distinct top 1 concat('now in SampleWorkbook.xls: ', now()) from countries;]:

Retrieving the result set:
now in SampleWorkbook.xls: 2023-01-25 01:17:08.003

JDBC connection successfully tested, quitting ...

Those drivers look extremely capable. Unfortunately, they only display one column at a time; it may be because it is a trial version but I could not find any mention of this limitation in the somewhat messy documentation. Anyway, jdbc_tester_generic works well with these JDBC drivers for Excel too.

And that concludes the tests with non-relational databases.

Conclusion

As usual, what was supposed to be a quick hack and a by-product ended up being a lot more than that with 9 data sources, RDBMS or NoSQL, all successfully tested. By factoring out the vendor-specific parameters, and they vary a lot between vendors, jdbc_tester_generic has proved itself to be truly universal. I hope it will useful to you as well.

Post Views: 2,791

A generic jdbc tester (part III)

Firebird

Microsoft SQL Server

HSQLDB

MongoDB

Excel

Conclusion

Cesare Cervini

Leave a Reply:

Related blog articles

Firebird

Microsoft SQL Server

HSQLDB

MongoDB

Excel

Conclusion

Cesare Cervini

Leave a Reply:

Related blog articles

Build SQL Server audit reports with Powershell

SQL-Server: What are VLF’s and why should I care about them?

SQL Server Security: Ensure that SQL Server service accounts are not a member of the Windows local Administrator group

SQL Server: Manage large data ranges using partitioning