JDBC Drivers
  • 11 Aug 2022
  • 3 Minutes to read
  • Dark
    Light

JDBC Drivers

  • Dark
    Light

Article summary

JDBC Drivers

DB2 (AS/400)

Driver: jt400.jar
Supports: DB2/400 (tested on v6r1)
jdbcDriverClass: com.ibm.as400.access.AS400JDBCDriver
jdbcURL: jdbc:as400://[server]/[library]

Hibernate does support DB2/400, but the dialect is not automatically detected; so a hibernate dialect must be specified and must be passed into the openDatabaseConnection action as an extra parameter. org.hibernate.dialect.DB2400Dialect will work perfectly.

DB2 (Universal)

Driver: db2jcc4.jar
Supports: DB2 (tested on DB2/LINUXX8664 - SQL09077)

jdbcDriverClass: com.ibm.db2.jcc.DB2Driver
jdbcURL: jdbc:db2://[server]:
[port]/NCANCV:retrieveMessagesFromServerOnGetMessage=true;
currentSchema=[database];currentFunctionPath="[database]";

Filemaker

Driver: fmjdbc.jar
Supports: Filemaker 11

jdbcDriverClass: com.filemaker.jdbc.Driver
jdbcURL: jdbc:filemaker://[server]/[database]

Hibernate does NOT support Filemaker, so a hibernate dialect must be selected that is similar, and must be passed into the openDatabaseConnection action as an extra parameter. org.hibernate.dialect.ProgressDialect has successfully worked to READ from a Filemaker Pro 11 database.

Informix

Driver: multiple .jar files in lib.zip

jdbcDriverClass: com.informix.jdbc.IfxDriver
jdbcURL: jdbc:informix-sqli://[host]:[port]/DB_name:informixserver=server_name

MS SQL (Sun JTDS recommended)

Driver: sqljdbc4.jar
Supports: MS SQL Server 2000, 2005 & 2008

jdbcDriverClass: com.microsoft.sqlserver.jdbc.SQLServerDriver
jdbcURL: jdbc:sqlserver://[host]:[port];databaseName=[db]

Additional MS SQL Help.

When Connecting to a MS SQL 2012 Server, use Sun JTDS.

For a Normal Session:

jdbc:jtds:sqlserver://"+Global.dbHostStudent+":"+Global.dbPort+"/
;domain="+Global.dbHostDomain.

For a Session with an instance, use:

jdbc:jtds:<server_type>://<server>[:<port>][/<database>];
instance=<instance_name>.

MySQL

driver: Included in RapidIdentity Connect

jdbcDriverClass: org.mariadb.jdbc.Driver
jdbcURL: jdbc:mysql://[host]:[port]/[database] Advanced Options

Amazon Aurora DB Cluster and RapidIdentity 2018.6.19

Beginning with RapidIdentity 2018.6.19, when using the Amazon Aurora DB Cluster with either MySQL or PostgreSQL, use the Cluster endpoint directly instead of indirectly with a DNS CNAME record.

Oracle

Driver: ojdbc6.jar
Supports: Oracle 10g and 11g up to 11.2.0.2.0

jdbcDriverClass: oracle.jdbc.OracleDriver
jdbcURL: jdbc:oracle:thin:@[host]:[port]:[sid]

PostgreSQL

Driver: Included in RapidIdentity Connect
Supports: postgreSQL

jdbcDriverClass: org.postgresql.Driver
jdbcURL: jdbc:postgresql://[host]:[port]/[db]?stringtype=unspecified

postgreSQL connections can occasionally take a long time to process. To speed up the connection, metadata can be added in the corresponding Action Set as follows.

hibernate = createRecord()
setRecordFieldValue(hibernate, "hibernate.dialect",
"org.hibernate.dialect.PostgreSQLDialect")
setRecordFieldValue(hibernate, "hibernate.temp.use_jdbc_metadata_defaults",
"false")

Progress/OpenEdge (Skyward)

Driver: openedge.jar, util.jar, base.jar (ALL REQUIRED)
Supports: Progress/OpenEdge

jdbcDriverClass: com.ddtek.jdbc.openedge.OpenEdgeDriver
jdbcURL: jdbc:datadirect:openedge://[host]:[port];databaseName=[db]

The current driver supports JDBC logging. An acceptable Action Set is shown:

hibernate = createRecord()
setRecordFieldValue(hibernate, "hibernate.dialect",
"org.hibernate.dialect.ProgressDialect")
SkywardSession = openDatabaseConnection(
"com.ddtek.jdbc.openedge.OpenEdgeDriver",
"jdbc:datadirect:openedge://IP:PORT;databaseName=SKYWARD;
SpyAttributes=(log=(file)/var/opt/idauto/dss/files/SkywardLogs/jdbc.log;
logTName=yes;timestamp=yes;)", "USERNAME",<Password>, hibernate)

Progress SSL Connection

Connecting to a Progress DB (example SKYWARD) via SSL, requires a modification to the connection string (encryptionMethod=SSL):

jdbc:datadirect:openedge://<ip> :<port>;databaseName= SKYWARD;encryptionMethod=SSL

SQLite

Driver: Included in RapidIdentity Connect
Supports: SQLite3 formatted database files

jdbcDriverClass: org.sqlite.JDBC
jdbcURL: jdbc:sqlite:<path to db file>
Hibernate Dialect: com.enigmabridge.hibernate.dialect.SQLiteDialect

SQLite is an embedded database that can be used only for a database file accessible via the RapidIdentity appliance native filesystem and writeable by the tomcat user. In the context of RapidIdentity Connect, it is primarily useful only for storing locally managed states, as opposed to being used for integration with an external system. It is not recommended for a cluster with multiple RapidIdentity Connect instances.

sqliteSession = openDatabaseConnection("org.sqlite.JDBC",
"jdbc:sqlite:/var/opt/idauto/dss/files/my.db",null,null,
{"hibernate.dialect": "com.enigmabridge.hibernate.dialect.SQLiteDialect"})
results = updateDatabaseSQL(sqliteSession, "DROP TABLE IF EXISTS contacts;")
results = updateDatabaseSQL(sqliteSession, "CREATE TABLE contacts
(\n contact_id integer PRIMARY KEY,\n first_name text NOT NULL,
\n last_name text NOT NULL,\n email text NOT NULL UNIQUE,\n phone text
NOT NULL UNIQUE\n);")
close(sqliteSession)

Sybase SQL Anywhere

Driver: jconn4.jar
Supports: Sybase SQL Anywhere

jdbcDriverClass: com.sybase.jbdc4.jdbc.sybDriver
jdbcURL:jdbc:sybase:Tds:[host]:[port]?ServiceName=[databasename]
Hibernate Dialect: org.hibernate.dialect.SybaseAnywhereDialect

Sun JTDS

Driver: Included in RapidIdentity Connect
Supports: Microsoft SQL Server (6.5, 7, 2000, 2005 and 2008) and
Sybase (10, 11, 12, 15)

jdbcDriverClass: net.sourceforge.jtds.jdbc.Driver
jdbcURL: jdbc:jtds:sqlserver://[host]:[port]/[databasename]

Additionally, to connect to Microsoft SQLServer, using Windows / NTLM authentication, rather than native SQL authentication, you must append the following to the end of the JDBC URL:

;useNTLMv2=true;domain=<domainname>;

If the database you are accessing is a NAMED INSTANCE rather than the default database, the PORT will most likely be dynamic. The SQL Server Browser service on the database server should take care of establishing the conversation, but you will need to discard the default port of 1433.

For example, to connect with a domain account to a named instance, it would look like this:

jdbc:jtds:sqlserver://<servername>/<databasename>;instance=<instancename>;
domain=<domain>

UniData/UniVerse (IBM/Unix)

Driver: unijdbc.jar and asjava.jar are both required.

Supports: UniData and UniVerse (tested on UniData 8.1.2)

jdbcDriverClass: com.rs.u2.jdbc.UniJDBCDriver
jdbcURL: jdbc:rs-u2://host[:port-num]/[account];dbmstype=UNIDATA

Hibernate does support UniData, but the dialect is not automatically detected; so a hibernate dialect must be specified and passed into the openDatabaseConnection action as an extra parameter. It will work perfectly.

hibernate = createRecord()
setRecordFieldValue(hibernate, "hibernate.dialect",
"org.hibernate.dialect.DB2Dialect")

External Documentation

Rocket API and JDBC Developer's Guide

JDBC Driver Downloads

Additional driver reference.

Specifying a Hibernate Dialect

To capitalize on the RapidIdentity Connect database adapter, it may be necessary to specify a dialect to make the connection even if the correct JDBC driver is used.

Dialects can be integrated as a record field value.

sessionDB = openDatabaseConnection("com.filemaker.jdbc.Driver",
Global.FilemakerStaffDB, Global.FilemakerDBUser,<Password>,
{"hibernate.dialect": "org.hibernate.dialect.ProgressDialect"})

Secure Database Connection with SSL

A secure database connection can be attempted by modifying the connection string by adding ?

ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory.

Complete example: 

jdbc:postgresql://"+Global.dbHostFocus+":"+Global.dbPortFocus+"/"+Global.dbNameFocus+"?ssl=true&sslfactory=org.postgresql.ssl.NonValidatingFactory

Attachments:

asjava.jar (application/java-archive)

db2jcc4.zip (application/zip)

fmjdbc.zip (application/zip)

jconn4.jar.zip (application/zip)

jt400.zip (application/zip)

jtds-1.2.5-dist.zip (application/zip)

lib.zip (application/zip)

mysql-connector-java-5.1.19-bin.zip (application/zip)

ojdbc6.jar.zip (application/zip)

openedge.zip (application/zip)

SkywardJDBCJarFiles.zip (application/zip)

sqljdbc4.jar.zip (application/zip)

unijdbc.jar (application/java-archive)


Was this article helpful?

ESC

Eddy AI, facilitating knowledge discovery through conversational intelligence