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
99SQL99
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
15080000Microsoft Access 971000
15090000Microsoft Access 200015080000
15100000Microsoft Access 200215090000
15110000Microsoft Access 200315100000
15120000Microsoft Access 200715110000
15140000Microsoft Access 201015120000
16120000Microsoft Excel 201099
17010000Microsoft Excel Driver10
20080000Oracle8i1000
20090000Oracle9i20080000
20100000Oracle10g20090000
20110000Oracle11g20100000
30090000IBM DB2 91000
30100000IBM DB2 1030090000
30100500IBM DB2 10.530100000
40060503PostgreSQL 61000
40070430PostgreSQL 740060503
40080408PostgreSQL 840070430
40090004PostgreSQL 940080408
50120000Teradata 121000
50130000Teradata 1350120000
51030000VectorWise1000
60050091MySQL 5.01000
60050143MySQL 5.160050091
60050503MySQL 5.560050143
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
82030000ServiceNow10
83020007Cassandra10
84060000Splunk1
85010000Quandl1
86010000Hazelcast1
87010000Cognos10
90070000Pervasive.SQL 71000
90080000Pervasive.SQL v890070000
90090000Pervasive PSQL v990080000
90100000Pervasive PSQL v1090090000
90110000Pervasive PSQL v1190100000
92010032SQLite v11000
92020816SQLite v292010032
92030706SQLite v392020816
94020500Firebird v21000
95010800HSQLDB 1.81000
95020100HSQLDB 2.195010800
95020200HSQLDB 2.295020100
97001100Apache Pig1
98010000ParStream1000

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