Skip to Content

Wiki source code of 06a. Database Connections

Last modified by Kevin Strachan on 06/11/2025, 14:23
Hide last authors
Kevin Strachan 1.1 1 {{box cssClass="floatinginfobox" title="**Contents**"}}
2 {{toc/}}
3 {{/box}}
4
5 By creating Database Connections, relevant connection settings can be saved and made available across different areas of the solution.
6
7 Navigate to the Database Connection Setup screen by clicking **Search Engine**, then **Database Connections**. All currently loaded Database Connections will be displayed.
8
9 = Creating a New Database Connection =
10
11 Clicking **New** will allow the configuration of a new Database Connection.
12
13 Enter the desired name in the **Name** textbox. This name must be unique.
14
15 Enable the **Template** option to allow the saved connection details to be modified when selected. This is especially useful when, for example, Indexing a number of CSV files from the same directory. When the directory is specified for the Database Connection using the **Location** option, loading the saved connection details and using the same **Location** option will navigate straight to the required folder.
16
17 Select the source database from the **Database Type** drop-down list to reveal the relevant connection fields below. Enable the **Show Driver and URL** option to display the underlying details used to provide the database compatibility.
18
19 The following options are displayed depending on the choice of **Database Type**:
20
21 (% border="1" cellspacing="0" class="MsoTableGrid" style="border-collapse:collapse; border:1pt solid windowtext; width:1024px" %)
22 |(% style="background-color:#ed7d31; background:#ed7d31; border-style:solid; border-width:1.0pt; width:150.15pt" width="186" %)(((
23 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
24 (% style="color:white" %)Option
25 )))|(% style="background-color:#ed7d31; background:#ed7d31; border-left:none; border-style:solid; border-width:1.0pt; width:150.15pt" width="186" %)(((
26 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
27 (% style="color:white" %)Applicable To
28 )))|(% style="background-color:#ed7d31; background:#ed7d31; border-left:none; border-style:solid; border-width:1.0pt; width:150.2pt" width="193" %)(((
29 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
30 (% style="color:white" %)Description
31 )))
32 |(% style="border-style:solid; border-top:none; border-width:1.0pt; width:150.15pt" width="186" %)(((
33 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
34 Server
35 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.15pt" width="186" %)(((
36 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
37 4D, CXAIR, DB2, HSqlDB, JDBC4OLAP, MySQL, Oracle 8i, Oracle 9i/10g/11g, Oracle 12c, Postgre SQL, SQL Server 2000 – 2005, SQL Server 2008 and above, Sybase
38 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.2pt" width="193" %)(((
39 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
40 The name or IP address of the database server.
41 )))
42 |(% style="border-style:solid; border-top:none; border-width:1.0pt; width:150.15pt" width="186" %)(((
43 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
44 System
45 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.15pt" width="186" %)(((
46 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
47 DB2 AS/400
48 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.2pt" width="193" %)(((
49 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
50 (% style="color:black" %)The name of the database system.
51 )))
52 |(% style="border-style:solid; border-top:none; border-width:1.0pt; width:150.15pt" width="186" %)(((
53 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
54 Advanced Connection Details
55 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.15pt" width="186" %)(((
56 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
57 Oracle 12 Advanced
58 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.2pt" width="193" %)(((
59 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
60 See **Extra Parameters**.
61 )))
62 |(% style="border-style:solid; border-top:none; border-width:1.0pt; width:150.15pt" width="186" %)(((
63 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
64 Database Name
65 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.15pt" width="186" %)(((
66 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
67 DB2, HSqlDB, MySQL, Oracle 8i, Oracle 9i/10g/11g, Oracle 12c, Postgre SQL, SQL Server 2000 – 2005, SQL Server 2008 and above, Sybase
68 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.2pt" width="193" %)(((
69 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
70 The name of the target database.
71 )))
72 |(% style="border-style:solid; border-top:none; border-width:1.0pt; width:150.15pt" width="186" %)(((
73 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
74 Extra Parameters
75 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.15pt" width="186" %)(((
76 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
77 SQL Server 2000 – 2005, SQL Server 2008 and above, Sybase
78 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.2pt" width="193" %)(((
79 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
80 Specify the SQL Server instance or domain if connecting via Windows Authentication. Separate multiple parameters using a semicolon.
81
82 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
83 Enter the name of an instance in the following format:
84
85 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
86 **instance=<instance name>**
87
88 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
89 If using a Windows SQL Server account*, enter:
90
91 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
92 **domain=<domain name>**
93
94 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
95 Enter the following to send LMv2/NTLMv2 responses when connecting via a domain:
96
97 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
98 **useNTLMv2=true**
99
100 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
101 *Due to driver limitations, Windows Authentication is only compatible with the SQL Server 2000-2005 driver.
102 )))
103 |(% style="border-style:solid; border-top:none; border-width:1.0pt; width:150.15pt" width="186" %)(((
104 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
105 Context
106 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.15pt" width="186" %)(((
107 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
108 CXAIR
109 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.2pt" width="193" %)(((
110 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
111 See **Extra Parameters**.
112 )))
113 |(% style="border-style:solid; border-top:none; border-width:1.0pt; width:150.15pt" width="186" %)(((
114 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
115 Port
116 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.15pt" width="186" %)(((
117 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
118 CXAIR, HSqlDB, JDBC4OLAP, MySQL, Oracle 8i, Oracle 9i/10g/11g, Oracle 12c, Postgre SQL, SQL Server 2000 – 2005, SQL Server 2008 and above, Sybase
119 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.2pt" width="193" %)(((
120 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
121 The port number of the database server.
122 )))
123 |(% style="border-style:solid; border-top:none; border-width:1.0pt; width:150.15pt" width="186" %)(((
124 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
125 Account Name
126 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.15pt" width="186" %)(((
127 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
128 4D, CXAIR, DB2, DB2 AS/400, HSqlDB, JDBC4OLAP, MySQL, Oracle 8i, Oracle 9i/10g/11g, Oracle 12c, Oracle 12c Advanced, PostgreSQL, SQL Server 2000 – 2005, SQL Server 2008 and above, Sybase, U Can Access
129 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.2pt" width="193" %)(((
130 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
131 (% style="color:black" %)The account name of a database user that can execute the underlying SQL.
132 )))
133 |(% style="border-style:solid; border-top:none; border-width:1.0pt; width:150.15pt" width="186" %)(((
134 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
135 Account Password
136 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.15pt" width="186" %)(((
137 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
138 4D, CXAIR, DB2, DB2 AS/400, HSqlDB, JDBC4OLAP, MySQL, Oracle 8i, Oracle 9i/10g/11g, Oracle 12c, Oracle 12c Advanced, PostgreSQL, SQL Server 2000 – 2005, SQL Server 2008 and above, Sybase, U Can Access
139 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.2pt" width="193" %)(((
140 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
141 (% style="color:black" %)The account password for the account detailed above.
142 )))
143 |(% style="border-style:solid; border-top:none; border-width:1.0pt; width:150.15pt" width="186" %)(((
144 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
145 SQL
146 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.15pt" width="186" %)(((
147 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
148 4D, CXAIR, DB2, DB2 AS/400, HSqlDB, JDBC4OLAP, MySQL, Oracle 8i, Oracle 9i/10g/11g, Oracle 12c, Oracle 12c Advanced, PostgreSQL, SQL Server 2000 – 2005, SQL Server 2008 and above, Sybase, U Can Access
149 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.2pt" width="193" %)(((
150 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
151 (% style="color:black" %)The SQL statement that will retrieve the data from the data source.
152 )))
153 (% style="height:14.35pt" %)|(% style="border-style:solid; border-top:none; border-width:1.0pt; width:150.15pt" width="186" %)(((
154 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
155 (% style="height:14.35pt" %)Delimiter
156 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.15pt" width="186" %)(((
157 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
158 (% style="height:14.35pt" %)CSV
159 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.2pt" width="193" %)(((
160 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
161 (% style="height:14.35pt" %)The character that splits the field values. By default, this is a comma (**,**).
162 )))
163 (% style="height:14.35pt" %)|(% style="border-style:solid; border-top:none; border-width:1.0pt; width:150.15pt" width="186" %)(((
164 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
165 (% style="height:14.35pt" %)Text Qualifier
166 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.15pt" width="186" %)(((
167 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
168 (% style="height:14.35pt" %)CSV
169 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.2pt" width="193" %)(((
170 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
171 (% style="height:14.35pt" %)The character that qualifies text within a value. By default, this is quotation marks (**“** **“**).
172 )))
173 (% style="height:14.35pt" %)|(% style="border-style:solid; border-top:none; border-width:1.0pt; width:150.15pt" width="186" %)(((
174 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
175 (% style="height:14.35pt" %)First Row is Header
176 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.15pt" width="186" %)(((
177 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
178 (% style="height:14.35pt" %)CSV, Excel
179 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.2pt" width="193" %)(((
180 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
181 (% style="height:14.35pt" %)Sets the contents of the first row to be used as the column headings in the resulting output.
182 )))
183 (% style="height:16.6pt" %)|(% style="border-style:solid; border-top:none; border-width:1.0pt; width:150.15pt" width="186" %)(((
184 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
185 (% style="height:16.6pt" %)Sheet
186 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.15pt" width="186" %)(((
187 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
188 (% style="height:16.6pt" %)Excel
189 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.2pt" width="193" %)(((
190 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
191 (% style="height:16.6pt" %)Specify the required Excel sheet or type * if you would like all sheets to be indexed.
192 )))
193 (% style="height:14.35pt" %)|(% style="border-style:solid; border-top:none; border-width:1.0pt; width:150.15pt" width="186" %)(((
194 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
195 (% style="height:14.35pt" %)Location
196 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.15pt" width="186" %)(((
197 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
198 (% style="height:14.35pt" %)CSV, Excel, U Can Access
199 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.2pt" width="193" %)(((
200 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
201 (% style="height:14.35pt" %)Click the **…** icon to open the file browser, where the relevant file can be selected.
202 )))
203 (% style="height:14.35pt" %)|(% style="border-style:solid; border-top:none; border-width:1.0pt; width:150.15pt" width="186" %)(((
204 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
205 (% style="height:14.35pt" %)First Row is Header
206 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.15pt" width="186" %)(((
207 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
208 (% style="height:14.35pt" %)CSV, Excel
209 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.2pt" width="193" %)(((
210 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
211 (% style="height:14.35pt" %)Enable this option if the first row of data in the spreadsheet contains column headings.
212 )))
213 (% style="height:14.35pt" %)|(% style="border-style:solid; border-top:none; border-width:1.0pt; width:150.15pt" width="186" %)(((
214 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
215 (% style="height:14.35pt" %)Character Set
216 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.15pt" width="186" %)(((
217 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
218 (% style="height:14.35pt" %)CSV
219 )))|(% style="border-bottom:solid windowtext 1.0pt; border-left:none; border-right:solid windowtext 1.0pt; border-top:none; width:150.2pt" width="193" %)(((
220 (% style="padding:0cm 5.4pt 0cm 5.4pt" %)
221 (% style="height:14.35pt" %)The encoding of the CSV file. The default option is UTF-8, but this will differ depending on the source file.
222 )))
223
224 When the relevant options have been completed, click **Create Database Connection** to complete the process.
225
226 = Using a Database Connection =
227
228 Once configured, saved Database Connections appear at the top of the **Database Type** drop-down list when [[creating new database Data Sources manually>>doc:Technical Documentation.CXAIR.Administration Guide.4\. Manual Index Creation.a\. Creating a Data Source.WebHome]] or using the [[Wizards>>doc:Technical Documentation.CXAIR.Administration Guide.Wizards.WebHome]] functionality, and when exporting [[CXForms>>doc:Technical Documentation.CXFORMS.WebHome]] data to a database from a [[Work Queue>>doc:Technical Documentation.CXFORMS.07\. Work Queues.WebHome]]. When selected, the saved Database Connection will populate the required connection details.