JDBC Driver

From DBSight Full-Text Search Engine/Platform Wiki

Table of contents

1 General Instructions 2 DBSight 2.0 new structure 3 Installation 3.1 MySQL 3.2 SQL Server 3.3 DB2 4 Using Type 2 Jdbc Drivers in windows Services mode

General Instructions

DBSight only requires the most simple SQL select statement. All JDBC drivers should be supported, including DB2, C-jdbc, etc.

DBSight 2.0 new structure

This directory, "WEB-INF/lib/ext/jdbc", should have this kind of dictory structure

There are several subdirectories, each containing

 1. jdbcdriver.xml, which has infomration like driver name, url-format, url-example, validate-sql, etc. 
 2. one or several jdbc library files, including *.jar, *.zip, or *.properties files.

These *.jar, *.zip, or *.properties files are loaded into classpath first, overriding system classpath, files under WEB-INF/lib and WEB-INF/lib/ext/indexing

For example, sub directories should look like this:

|- mysql20
|    |- mysql-connector-java-2.0.14-bin.jar
|    |- jdbcdriver.xml
|- postgres
|    |- postgresql-81-404-jdbc3.jar
|    |- jdbcdriver.xml
|- sqlsever
|    |- msbase.jar
|    |- msutil.jar
|    |- mssqlserver.jar
|    |- jdbcdriver.xml
|- ...

Installation

If you need any specific JDBC drivers, you need to

1. drop them to this directory and restart. If it's a new one driver, just create one directory.

 WEB-INF/lib/ext/jdbc/<driver-name>

2. Create XML file WEB-INF/lib/ext/jdbc/<driver-name>/jdbcdriver.xml . You should easily copy one from some neighbor directory and modify it. For example, here is a SQL Server one.

   <jdbcdriver name="SQLServer">
     <class>com.microsoft.jdbc.sqlserver.SQLServerDriver</class>
     <url-format><![CDATA[]]></url-format>
     <url-example><![CDATA[jdbc:microsoft:sqlserver://hostname:1433]]></url-example>
     <validate-sql>SELECT 1</validate-sql>
   </jdbcdriver>

MySQL

MySQL latest JDBC driver is under GPL license. We can not ship it with dbsight. But you can download them for free:

http://www.mysql.com/products/connector/j/

http://www.mysql.com/downloads/api-jdbc.html

Unzip the file and put something like "mysql-connector-java-3.2.0-alpha-bin.jar" to WEB-INF/lib/ext/jdbc/mysql . Remember to restart your application server.

SQL Server

We strongly recommend jTDS driver. If you don't like the default jTDS driver for some reason. You can use SQL Server's own driver. At least for SQL Server 2000, the jdbc driver is packaged to 3 jar files

msbase.jar
msutil.jar
mssqlserver.jar

You can drop them to WEB-INF/lib/ext/jdbc/sqlserver directory.

DB2

Using the DB2 Universal driver in Type 4 mode requires only the db2jcc.jar and a license file, db2jcc_license_cu.jar. They are located in the DB2_root\IBM\SQLLIB\java directory of the client.

Put db2jcc.jar and db2jcc_license_cu.jar under one of the 2 directories

WEB-INF/lib/ext/jdbc/ibm_db2_type2 
WEB-INF/lib/ext/jdbc/ibm_db2_universal

directory. The jdbcdriver.xml should already be there.

Using Type 2 Jdbc Drivers in windows Services mode

If you want to use jdbc type 2 driver, and you want to install DBSight as services, you may need to add additional path that contains .dll files. To do so, you can modify file:

C:\Program Files\DBSight\etc\dbsight.conf

And find this piece of code and add something similar:

# Java Library Path (location of Wrapper.DLL or libwrapper.so)
wrapper.java.library.path.1=../lib
wrapper.java.library.path.2=....