Wiki source code of Ajout Pilote JDBC

Last modified by Aurelie Bertrand on 2026/01/12 15:14
Show last authors
1 {{ddtoc/}}
2
3 ----
4
5 This document describes the process to add a new JDBC data base driver to DigDash Enterprise.
6
7 = Requirement =
8
9 The JDBC driver as a JAR file, and its documentation
10
11 = Installation =
12
13 == Driver files deployment ==
14
15 1. (optional, if the webapps have not been already deployed). Start your DigDash Enterprise Server and wait for the webapps to be fully deployed
16 1. Stop the server
17 1. Copy the driver's JAR file into the following folders:
18 **<DD Install>/apache-tomcat/webapps/ddenterpriseapi/WEB-INF/lib
19 <DD Install>/apache-tomcat/webapps/studio/WEB-INF/lib**
20 1. The JDBC driver must be registered within DigDash Enterprise Server.
21
22 == Registering the driver in Digdash Enterprise ==
23
24 Edit the following file :
25
26 **<DD Install>\apache-tomcat\webapps\ddenterpriseapi\WEB-INF\classes\resources\config\sqldriverrepository.xml**
27
28 Add an XML entry to the **sqldriverrepository.xml **that looks like the following sample:
29
30 {{code language="XML" cssClass="notranslate"}}
31 <SQLDriver id="MY_DRIVER"
32 name="My Driver"
33 url="mydriver:"
34 manufacturer="My Driver Company"
35 class="com.mydriver.MyDriver"
36 urlsample="jdbc:mydriver:<database>?<options>"
37 availability="both">
38 <properties></properties>
39 </SQLDriver>
40 {{/code}}
41
42 (% class="box warningmessage" %)
43 (((
44 //Important: Some characters are reserved in XML: '**&**', '**"**', '**<**' or '**>**'. If you need these characters you must encode them into their corresponding XML entity.//
45 )))
46
47 //Corresponding XML entities~://
48
49 * **&** => **&amp;**
50 * **"** => **&quot;**
51 * **<** => **&lt;**
52 * **>** => **&gt;**
53
54 Example:
55
56 (% class="box" %)
57 (((
58 * (WRONG) urlsample="jdbc:mydriver:dbtest?opt1=0&opt2="value""
59 * (RIGHT) urlsample="jdbc:mydriver:dbtest?opt1=0&amp;opt2=&quot;value&quot;"
60 )))
61
62 Parameters:
63
64 * **id**: An identifier used by DigDash internally, choose any non existent string, the convention is uppercase, no spaces
65 * **name**: The driver's name to be displayed in the user interface of the administration console
66 * **url**: jdbc URL prefix (without "jdbc:"). See driver's documentation
67 * **manufacturer**: Name of the driver's vendor/developer
68 * **class**: java class path of the main driver class. See driver's documentation.
69 Optional: JDBC drivers compliant with JDBC 4 do not need a driver class. Keep the class attribute empty (class="") in that case.
70 * **urlsample**: User friendly URL sample for the user interface of the administration console.
71 * **availability**: reserved. Leave it at "both".
72
73 (% class="box warningmessage" %)
74 (((
75 //Important: The deployment is lost when upgrading ddenterpriseapi.war file.//
76 //Please follow the deployment guide each time you upgrade DigDash Enterprise//
77 )))
78
79 You can find some sample of XML for drivers not provided in DigDash enterprise in **sqldriverrepository.xml** file.
80
81 == Specific properties (advanced) ==
82
83 You can specify secific properties on some JDBC drivers. These properties are written inside the <properties></properties> tag, under the following format:
84
85 {{code cssClass="XML" language="XML"}}
86 <properties>
87
88 <property name="property_name" value="property_value" ></property>
89
90 <properties>
91 {{/code}}
92
93 Supported properties are:
94
95 === FORCE_FORWARD_ONLY (undefined | false | true) ===
96
97 Description: specifies the type of JDBC result set cursor used by the Studio for the preview of the SQL query results. By default the Studio uses a TYPE_INSENTIVE_SCROLL cursor to preview the results, but some of the databases do not support this type of cursor. If your driver or database only supports TYPE_FORWARD_ONLY cursors you can specify it with FORCE_FORWARD_ONLY property. Possible values:
98
99 * **false** (or **undefined property**): The type of cursor is automatic, TYPE_SCROLL_INSENTIVE in most of the cases, except for HIVE, IMPALA and SAPHANA
100 * **true**: The type of cursor used in the Studio is TYPE_FORWARD_ONLY
101
102 === PING_SQL (undefined | SQL query | empty string) ===
103
104 Description: DigDash Enterprise tests the connection with the database by using the JDBC method **Connection.isValid()**. On some drivers this method does not work. In that case DigDash uses an alternative method to “ping” the database, usually by sending a “select 1” statement.
105
106 The PING_SQL property allows you to specify this SQL statement depending on your driver or database. Possible values:
107
108 * **Undefined property**: The alternative ping query is automatically decided by DigDash: “select 1” except for ORACLE, FIREBIRD, SAPHANA, DB2_AS400 or DB2 drivers
109 * **SQL query (not empty)**: the specified query is used to ping the databse. Example:
110 <property name="PING_SQL" value="select 1 from all_tables" />
111 * **Empty string**: Special case used to deactivate the ping in the case the JDBC method Connection.isValid() fails. The database is considered to be always accessible. Example:
112 <property name="PING_SQL" value="" />
113
114 === USE_FETCH_FIRST_IN_STUDIO (undefined | false | true) ===
115
116 Description: This property is used only for preview the SQL result in the data source configuration dialog (Studio). It modifies the query by adding “FETCH FIRST n ROWS ONLY” to it (n is replaced byt the number of preview rows). It is useful for drivers that do not support JDBC’s **Statement.setMaxRows[[image:icon:thumb_down]]**, for example AS400 JDBC driver. Possible values:
117
118 * **false** (or **undefined property**): The preview limit is defined by using JDBC method **Statement.setMaxRows[[image:icon:thumb_down]]**
119 * **true**: Preview limit is defined by adding FERTCH FIRST n ROWS ONLY to the SQL in the Studio.
120
121 === FORBID_POOL_CONNECTION (undefined | false | true) ===
122
123 Description: Prevents DigDash Enterprise from using a JDBC connection pool for this driver. A JDBC connection pool optimizes access to the data base by keeping connections in an opened state, and reuse them for different queries. In some cases it is preferred to not use a JDBC connection pool, for example to ensure that connections will not stay opened too long on the data base after they have been used for a query. This property answers that need. Possible values:
124
125 * **false** (or **undefined property**): A JDBC connection pool may be used by the driver
126 * **true**: The JDBC connection pool will not be used by the driver and each SQL query will have its own connection.
127
128 === POOL_VALIDATION_QUERY (undefined | SQL query) ===
129
130 Description: Specify a validation query used by the JDBC Apache DBCP2 connection pool. Some drivers do not specify this query (eg. « select 1 ») and can malfunction when a connection is created by the JDBC connection pool. This property is similar to the validationQuery property which is configured in Apache DBCP2 pool configuration. Possible values:
131
132 * **undefined**: No specified validation query for this driver. The connection pool will use the default validation query, if it exists.
133 * **non-empty SQL query **: The specified query will be use to test the validity of a connection obtained from the Apache DBCP connection pool. (Example : select 1)
134
135 === DEFAULT_FETCH_SIZE (undefined | number) ===
136
137 Description: Specify a maximum number of rows to be retrieve at once by DigDash Enterprise. Some JDBC drivers (eg. Postgresql) retrieve all result rows of a request at once, instead of streaming the result to be consumed by DigDash Enterprise. This can lead to a higher memory usage (bigger memory buffer needed) and to prevent cancelling the fetch of the result mid-time. This parameter can help avoiding this case by specifying a reasonable number of rows to retrieve (eg. 100). For example, DEFAULT_FETCH_SIZE = 1000 will retrieve the first 1000 rows of result, and when DigDash will need the 1001st row, the driver will retrieve the next 1000 rows, and so on. This parameter can also by specified directly in the data source configuration (advanced) in the Studio.
138
139 = Pre-configured drivers, not included{{id name="Drivers"/}} =
140
141 The following Database connectivities are available in DigDash Enterprise by default, but their JDBC driver is not included and need additional installation:
142
143 == MySQL ==
144
145 DigDash Enterprise can connect to a MySQL 5.5 (and after) database by using **MariaDB Java connector** driver. In most of the cases, this driver is enough to connect to a MySQL (and MariaDB) database. However, to connect to a previous version of MySQL, or to use specific features of the native MySQL JDBC driver, you will need the MySQL driver corresponding to your version of the database. For these cases, you must download the driver from MySQL website: [[https:~~/~~/dev.mysql.com/downloads/connector/j/>>url:https://dev.mysql.com/downloads/connector/j/]]
146
147 (% class="box warningmessage" %)
148 (((
149 The MariaDB driver included in DigDash Enterprise handles **jdbc:mysql:~/~/** et **jdbc:mariadb:~/~/** connections. To use the MySQL native JDBC driver you must remove MariaDB driver from the **ddenterpriseapi **and s**tudio **webapps. The file to remove is **mariadb-java-client-*.jar**. Keeping both drivers can lead to issues when connecting to MySQL.
150 However, it is possible to keep the MariaDB driver, for the case when you have a MySQL database and a MariaDB database. You need to add the parameter **&disableMariaDbDriver** in the connection string to your MySQL database (jdbc:mysql:~/~/...&disableMariaDbDriver). More information on the MariaDB website: [[https:~~/~~/mariadb.com/kb/en/about-mariadb-connector-j/>>url:https://mariadb.com/kb/en/about-mariadb-connector-j/]].
151 )))
152
153 (% class="box warningmessage" %)
154 (((
155 If you decide to install the MySQL driver, you can notice that a definition for it already exist in the file **sqldriverrepository.xml** included in DigDash Enterprise (see XML below), but it is commented out. You need to un-comment this XML definition (and also comment the one for MariaDB driver).
156 This definition concerns the newer version of the MySQL driver, from MySQL Connector/J 8.0. Recent MySQL drivers are compatible with all version of MySQL database. However, if you decide to install an older version you need to remove the class property from this definition, and then rename the classOld property into class, because the driver's class name has changed in Connector/J 8.0. In any case, read the documentation corresponding to the driver you install.
157
158 {{code cssClass="XML" language="XML"}}
159 <SQLDriver id="MYSQL" name="MySQL"
160 url="mysql://"
161 class="com.mysql.cj.jdbc.Driver"
162 classOld="com.mysql.jdbc.Driver"
163 manufacturer="MySQL"
164 urlsample="$ui.DataBase.MySqlSampleUrl"
165 availability="both">
166 <properties>
167 </properties>
168 </SQLDriver>
169
170 {{/code}}
171 )))
172
173 == Teradata ==
174
175 The JDBC driver for Teradata can be downloaded on Teradata website: [[https:~~/~~/downloads.teradata.com/download/connectivity/jdbc-driver>>url:https://downloads.teradata.com/download/connectivity/jdbc-driver]]. The download needs a user account.
176
177 == IBM DB2 ==
178
179 The JDBC driver for IBM DB2 can be downloaded on IBM website: [[https:~~/~~/www.ibm.com/support/pages/db2-jdbc-driver-versions-and-downloads>>url:https://www.ibm.com/support/pages/db2-jdbc-driver-versions-and-downloads]]. The download needs a user account. The driver consist in 2 .jar files, one for the driver, one for the license. You can also find these files in your DB2 installation.
180
181 == Microsoft SQL Server ==
182
183 DigDash Enterprise is packaged with jTDS, an open source JDBC driver compatible with Microsoft SQL Server. However, if you want to use specific features of the Microsoft SQL Server driver, you need to download it from Microsoft website: [[https:~~/~~/docs.microsoft.com/fr-fr/sql/connect/jdbc/microsoft-jdbc-driver-for-sql-server?view=sql-server-ver15>>url:https://docs.microsoft.com/fr-fr/sql/connect/jdbc/microsoft-jdbc-driver-for-sql-server?view=sql-server-ver15]].
184
185 == Hadoop Hive ==
186
187 Installing Hadoop Hive driver depends on your Hadoop installation (Example: Cloudera, Hortonworks...). It it recommended to use the corresponding driver included with your Hadoop Installation (non exhaustively) :
188
189 * Cloudera : [[https:~~/~~/www.cloudera.com/downloads/connectors/hive/jdbc/2-6-5.html>>url:https://www.cloudera.com/downloads/connectors/hive/jdbc/2-6-5.html]]
190 * Hortonworks : [[https:~~/~~/www.progress.com/jdbc/hortonworks-hive>>url:https://www.progress.com/jdbc/hortonworks-hive]]
191
192 == Google BigQuery ==
193
194 Read the specific document about [[BigQuery Connector>>doc:Digdash.deployment.Connectivity_Database.bigquery_connector.WebHome]] in Digdash Enterprise.
195
196 == SAP HANA ==
197
198 Read the specific document about [[SAP HANA>>doc:Digdash.deployment.Connectivity_Database.saphana_connector.WebHome]] in Digdash Enterprise.