06a. Database Connections
By creating Database Connections, relevant connection settings can be saved and made available across different areas of the solution.
Navigate to the Database Connection Setup screen by clicking Search Engine, then Database Connections. All currently loaded Database Connections will be displayed.
Creating a New Database Connection
Clicking New will allow the configuration of a new Database Connection.
Enter the desired name in the Name textbox. This name must be unique.
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.
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.
The following options are displayed depending on the choice of Database Type:
Option | Applicable To | Description |
Server | 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 | The name or IP address of the database server. |
System | DB2 AS/400 | The name of the database system. |
Advanced Connection Details | Oracle 12 Advanced | See Extra Parameters. |
Database Name | DB2, HSqlDB, MySQL, Oracle 8i, Oracle 9i/10g/11g, Oracle 12c, Postgre SQL, SQL Server 2000 – 2005, SQL Server 2008 and above, Sybase | The name of the target database. |
Extra Parameters | SQL Server 2000 – 2005, SQL Server 2008 and above, Sybase | Specify the SQL Server instance or domain if connecting via Windows Authentication. Separate multiple parameters using a semicolon. Enter the name of an instance in the following format: instance=<instance name> If using a Windows SQL Server account*, enter: domain=<domain name> Enter the following to send LMv2/NTLMv2 responses when connecting via a domain: useNTLMv2=true *Due to driver limitations, Windows Authentication is only compatible with the SQL Server 2000-2005 driver. |
Context | CXAIR | See Extra Parameters. |
Port | CXAIR, HSqlDB, JDBC4OLAP, MySQL, Oracle 8i, Oracle 9i/10g/11g, Oracle 12c, Postgre SQL, SQL Server 2000 – 2005, SQL Server 2008 and above, Sybase | The port number of the database server. |
Account Name | 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 | The account name of a database user that can execute the underlying SQL. |
Account Password | 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 | The account password for the account detailed above. |
SQL | 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 | The SQL statement that will retrieve the data from the data source. |
Delimiter | CSV | The character that splits the field values. By default, this is a comma (,). |
Text Qualifier | CSV | The character that qualifies text within a value. By default, this is quotation marks (“ “). |
First Row is Header | CSV, Excel | Sets the contents of the first row to be used as the column headings in the resulting output. |
Sheet | Excel | Specify the required Excel sheet or type * if you would like all sheets to be indexed. |
Location | CSV, Excel, U Can Access | Click the … icon to open the file browser, where the relevant file can be selected. |
First Row is Header | CSV, Excel | Enable this option if the first row of data in the spreadsheet contains column headings. |
Character Set | CSV | The encoding of the CSV file. The default option is UTF-8, but this will differ depending on the source file. |
When the relevant options have been completed, click Create Database Connection to complete the process.
Using a Database Connection
Once configured, saved Database Connections appear at the top of the Database Type drop-down list when creating new database Data Sources manually or using the Wizards functionality, and when exporting CXForms data to a database from a Work Queue. When selected, the saved Database Connection will populate the required connection details.