System API

Supported Databases

UnityJDBC supports any JDBC-accessible data source including full-featured databases such as Oracle, Microsoft SQL Server, PostgreSQL, MySQL, Sybase, and IBM DB2 as well as text-based sources, generic JDBC/ODBC sources, and NoSQL sources such as MongoDB and Cassandra. Each data source has a different SQL dialect and supports certain functions. Data sources without their own database id inherit features from the SQL99 standard dialect (id=99) and the Common SQL functions dialect (id=1000). A dialect may inherit the features of another dialect then modify or add its own features.

A database identifier is specified in the databaseId tag of the schema file to identify the source dialect. The dialect can also be set in code using the following statements:

SourceDatabase db = con.getDatabase("OrderDB");   // Retrieve the database called OrderDB
db.setDatabaseId(10100000);                       // Set database dialect for Microsoft SQL Server 2008

Click to see a list of supported database identifiers

Database Identifiers

Database IdNameInherited Dialect
1Generic NoSQL System
10Single Table
1000Common SQL99
10060500Microsoft SQL Server 6.51000
10070000Microsoft SQL Server 7.010060500
10080000Microsoft SQL Server 200010070000
10090000Microsoft SQL Server 200510080000
10100000Microsoft SQL Server 200810090000
10100500Microsoft SQL Server 201010100000
10110000Microsoft SQL Server 201210100500
10120000Microsoft SQL Server 2014/Azure SQL10110000
10130000Microsoft SQL Server 201610120000
10140000Microsoft SQL Server 201710130000
10150000Microsoft SQL Server 201910140000
15080000Microsoft Access 971000
15090000Microsoft Access 200015080000
15100000Microsoft Access 200215090000
15110000Microsoft Access 200315100000
15120000Microsoft Access 200715110000
15140000Microsoft Access 201015120000
15150000Microsoft Access 201315140000
15160000Microsoft Access 201615150000
15160100Microsoft Access 201915150000
16120000Microsoft Excel99
17010000Microsoft Excel Driver10
30090000IBM DB2 91000
30100000IBM DB2 1030090000
30100500IBM DB2 10.530100000
30110500IBM DB2 11.530100000
40060503PostgreSQL 61000
40070430PostgreSQL 740060503
40080408PostgreSQL 840070430
40090004PostgreSQL 940080408
40100000PostgreSQL 1040090004
40110000PostgreSQL 1140100000
40120000PostgreSQL 1240110000
50120000Teradata 121000
50130000Teradata 1350120000
50140000Teradata 1450130000
50150000Teradata 1550140000
50160000Teradata 1650150000
60050091MySQL 5.01000
60050143MySQL 5.160050091
60050503MySQL 5.560050143
60050700MySQL 5.760050503
60080000MySQL 860050503
61100400MariaDB 1060050503
70070000Sybase SQL Anywhere 71000
70080000Sybase SQL Anywhere 870070000
70090000Sybase SQL Anywhere 970080000
70100000Sybase SQL Anywhere 1070090000
70110000Sybase SQL Anywhere 1170100000
70120000Sybase SQL Anywhere 1270110000
71150500Sybase Adaptive Enterprise Server 15.51000
80020202MongoDB 2.21
80020400MongoDB 2.480020202
80030000MongoDB v380020400
80040000MongoDB v480030000
88010000Cognos TM11
90070000Pervasive.SQL 71000
90080000Pervasive.SQL v890070000
90090000Pervasive PSQL v990080000
90100000Pervasive PSQL v1090090000
90110000Pervasive PSQL v11/1290100000
92010032SQLite v11000
92020816SQLite v292010032
92030706SQLite v392020816
94020500Firebird v21000
95010800HSQLDB 1.81000
95020100HSQLDB 2.195010800
95020200HSQLDB 2.295020100
95020500HSQLDB 2.595020200
97001100Apache Pig1

Functions and Function Translation

UnityJDBC automatically translates functions and SQL syntax to the appropriate SQL dialect for the underlying database. Functions are renamed and parameters are re-ordered as required. If a function is not supported by the database, it is executed automatically inside UnityJDBC. Each database system is assigned a database identifier that is specified in the schema file describing the source. The database id allows UnityJDBC to determine which functions are supported on the database and the appropriate mappings.

Function Categories