ObdalibPluginJDBC
In Java, database connections are established using the Java DataBase Connectivity Framework, (JDBC). Quest and ontop use JDBC connections to connect to data sources, and so, they require JDBC parameters.
In particular, for each data source you will need to define 4 parameters: Driver class, JDBC URL, username and password. The last two are self explanatory, we now elaborate on the first two.
The Driver Class is a string that indicates which JDBC Driver to use when establishing a JDBC connection. JDBC Driver's are software implemented by third parties (often the same developers of the database system) that handle interaction with the DB in their own proprietary protocols.
Note that there often are be many drivers suitable for any DB, e.g., there are official and open source drivers for DB2.
The following is the list of drivers that we have used, and for which we provide support in -ontop- and Quest:
| Database | Driver name | Version | Class | Download link | Notes |
|---|---|---|---|---|---|
| MySQL | Connector/J | 5.1.20 | com.mysql.jdbc.Driver | download | |
| PostgreSQL | PostgreSQL JDBC4 Driver | 9.1-902 | org.postgresql.Driver | download | |
| H2 | H2 JDBC Driver | 1.3.166 | org.h2.Driver | download | |
| DB2 | DB2 Universal Driver | com.ibm.db2.jcc.DB2Driver | download | ||
| Microsoft SQL Server | MS JDBC Driver for SQL Server | 4.0.2206.100 | com.microsoft.sqlserver.jdbc.SQLServerDriver | download | |
| Oracle | Thin JDBC Driver for Java 6 | 11.2.0.3 | oracle.jdbc.driver.OracleDriver | download | File: ojdbc6.jar |
| Teiid | Teiid JDBC Driver | 7.4.0 | org.teiid.jdbc.TeiidDriver | download | JBOSS_PATH/server/default/lib/teiid-7.4.0.Final-client.jar |
Note: You may use other drivers to connect to your database but in that case we won't be able to offer your support.
The JDBC URL is a URI that determines the type, location and name of the database. Additionally, it is possible to pass connection parameters to the JDBC driver by means of the JDBC URL. Connection parameters might be important when creating OBDA applications, e.g., to define a default schema, default fetch size and query timeouts.
Now we provide a quick overview of common JDBC URLs for the supported DBs. Please read the documentation provided by your DB vendor for further information. Items in square brackets ([,]) being optional.
The JDBC URL format for MySQL Connector/J is as follows,
jdbc:mysql://<host>[:port]/[database][?propertyName1][=propertyValue1][&propertyName2][=propertyValue2]...
Examples:
jdbc:mysql://localhost:3306/sakila jdbc:mysql://localhost:3306/sakila?profileSQL=true jdbc:mysql://192.168.174.1/mydb?useCursorFetch=true&defaultFetchSize=150 jdbc:mysql://10.7.20.39/si_test?sessionVariables=sql_mode='ANSI'
MySQL uses quotes and double quotes in a non-standard way which can give problems to ontop. Please connect ONLY using ANSI mode, to do this, just use the following connection string (replace with your server, port and database)
jdbc:mysql://myserver:port/mydatabase?sessionVariables=sql_mode='ANSI'
More information, including all possible connection properties, can be found here.
The JDBC URL format for the PostgreSQL driver is as follows,
jdbc:postgresql://<host>[:port]/<database>[?propertyName1][=propertyValue1][&propertyName2][=propertyValue2]...
Examples:
jdbc:postgresql://localhost/mydb jdbc:postgresql://localhost:5432/mydb?ssl=true jdbc:postgresql://localhost:5432/mydb?ssl=true&loginTimeout=15
More information, including all possible connection properties, can be found here.
The JDBC URL format for the H2 in in-memory mode (volatile DB) is as follows,
jdbc:h2:mem:<databaseName>
Examples:
jdbc:h2:mem:test_mem
The JDBC URL format for the H2 working as a stand alone server (persistent DB) is as follows,
# For TCP/IP connections (normal) jdbc:h2:tcp://<server>[:<port>]/[<path>]<databaseName> # For SSL/TLS connections (secure) jdbc:h2:ssl://<server>[:<port>]/<databaseName>
Examples:
jdbc:h2:tcp://localhost/~/test;DATABASE_TO_UPPER=FALSE jdbc:h2:tcp://dbserv:8084/~/sample;DATABASE_TO_UPPER=FALSE jdbc:h2:tcp://localhost/mem:test;DATABASE_TO_UPPER=FALSE jdbc:h2:ssl://localhost:8085/~/sample;DATABASE_TO_UPPER=FALSE
More information and connection parameters can be found here.
Note that by default H2 is not case sensitive. However, Quest and -ontop- are case sensitive. To avoid incompatibilities you need to use turn on case sensitivity in H2 using the ;DATABASE_TO_UPPER=FALSE sufix in your connection string.
The JDBC URL format for DB2 using the Universal Driver is as follows,
jdbc:db2://<host>[:<port>]/<database_name>
Examples:
jdbc:db2://obdalin.inf.unibz.it/helloworld
More information here and here. See notes about the driver's license.
The JDBC URL format for SQL Server is as follows,
jdbc:sqlserver://<host>[:port];databaseName=<database>
Examples:
jdbc:sqlserver://obdawin.inf.unibz.it;databaseName=helloworld
The JDBC URL format for Oracle is as follows,
jdbc:oracle:thin:@//<host>[:port]/<service>
Examples:
jdbc:oracle:thin:@//obdalin.inf.unibz.it:1521/xe
The JDBC URL format for Teiid is as follows,
jdbc:teiid:<database>@mm://<host>[:port]
Examples:
jdbc:teiid:bookselling@mm://localhost:31000
Quest works properly, and offers great performance, with any version and any driver for PostgreSQL 8 and 9 (recommended). An example of the JDBC connection parameters for postgres are:
JDBC URL: jdbc:postgresql://localhost/stockclient1 Username: user Password: '''''' Driver: org.postgresql.Driver
Using MySQL in Virtual Mode is not recommended. The query planner of MySQL is very simple and often has problems making good query plans for the queries generated by the system. In particular, if there are nested-subqueries. Because of this, use cases with large volumes of data and MySQL will perform poorly in virtual mode.
Another reason to not use MySQL is that it lacks proper support for server side cursors. MySQL by default sends all results to the client, or, if cursors are enable, it creates temporary tables to hold the results. This can slow down query answering considerably. See ObdalibPluginTroubleshooting for more information.
An example of the JDBC connection parameters for mysql are:
JDBC URL: jdbc:mysql://localhost/stockclient1 Username: user Password: '''''' Driver: com.mysql.jdbc.Driver
The following is a sample connection for H2:
JDBC URL: jdbc:h2:mem:stockclient1 Username: sa Password: Driver: org.h2.Driver
We tested DB2 using the DB2 Universal Driver, which can be downloaded from here. The performance we get with DB2 is in many cases superior to the performance we get with PostgreSQL or MySQL, even the free version of DB2 Express-C.
The following is an example of the connection parameters for this driver:
JDBC URL: jdbc:db2://192.168.8.130:50000/SAMPLE:currentSchema=DB2INST1; Username: user Password: '''''' * Driver: com.ibm.db2.jcc.DB2Driver
JDBC URL: jdbc:sqlserver://obdawin.inf.unibz.it;databaseName=helloworld Username: user Password: password Driver: com.microsoft.sqlserver.jdbc.SQLServerDriver
We have tested Oracle using the thin driver. However, the current version of Quest is has not been properly tested with Oracle and errors have been reported. The following is an example of the connection parameters for this driver:
JDBC URL: jdbc:oracle:thin:@192.168.8.131:1521 Username: user Password: password Driver: oracle.jdbc.driver.OracleDriver