Configuring JDBC driver and connection in Query2Report

First steps involved in configuring Query2Report web application is to register drivers and create connections.

Registering driver requires providing a driver class and uploading the driver jar file. The driver jar files are database vendor specific and you need to choose right version of JDBC jar file depending upon the version of the database server. Following document provide more information about driver class and jar files. These driver jar files will be uploaded to {CATALINA_BASE}/webapps/q2r/WEB-INF/lib.

Once driver is registered the second step is to create a connection. The connection uses one of the drivers registered. While creating a connection user would also need to specify the database URL, username and password. Admin users should also ensure that the username/password is for read only access.

Below are some examples on driver class, JDBC URLs for commonly used database vendors.

1. MySQL

• Driver Class Name : com.mysql.jdbc.Driver
• URL Format : jdbc:mysql://:/<database_name>
• Default Port : 3306
• More Info : https://dev.mysql.com/downloads/connector/j/

2. Oracle

• Driver Class Name : oracle.jdbc.driver.OracleDriver
• URL : jdbc:oracle:thin:@:/<service_name>
• jdbc:oracle:thin:@::
• Default Port : 1521
• More Info : https://www.oracle.com/database/technologies/appdev/jdbc.html

3. SQL Server

• Driver Class Name : com.microsoft.sqlserver.jdbc.SQLServerDriver
• URL : jdbc:sqlserver://:;databaseName=<database_name>
• Default Port : 1433
• More Info : https://docs.microsoft.com/en-us/sql/connect/jdbc/using-the-jdbc-driver

4. Sybase jTDS

• Driver Class Name : net.sourceforge.jtds.jdbc.Driver
• URL : jdbc:jtds:sybase://:/
• Default Port : 7100
• More Info : http://jtds.sourceforge.net/index.html

5. PostgreSQL

• Driver Class Name : org.postgresql.Driver
• URL : jdbc:postgresql://:/<database_name>
• Default Port : 5432
• More Info : https://jdbc.postgresql.org/documentation/head/index.html