2.9 - Installing an ODBC driver and configuring an ODBC connection

Installing the SQL version of moveon or the moveonline modules requires an ODBC connection to the SQL database on each workstation using moveon. This section describes how to install the ODBC driver corresponding to the SQL database used and how to configure the ODBC connection.

1- Installing the ODBC driver

The ODBC driver corresponding to the SQL database used must be installed on each workstation.

• For MySQL, the ODBC driver can be downloaded from the following address: http://www.mysql.com/products/connector/odbc/. The minimum version required is 5.1.5.
• For PostgreSQL, the ODBC driver can be downloaded from the following address: http://www.postgresql.org/ftp/odbc. The minimum version required is 8.3.4.
• For MS SQL Server, the ODBC driver is installed automatically with Windows.
• For Oracle, you must install the Instant Client and its additional ODBC libraries. These programs can be downloaded from the following address: http://www.oracle.com/technology/software/tech/oci/instantclient/htdocs/winsoft.html. The minimum version required is 10.2.0.4. The "Microsoft for Oracle" client is not compatible.

2- Configuring the ODBC connection to the SQL database

After installing the ODBC driver you must configure a new ODBC connection. To do this, click on Start - Control Panel - Administrative Tools - Data Sources (ODBC). On the System DSN tab, click the Add button. Select the previously installed driver (MySQL ODBC, PostgreSQL Unicode, Oracle or SQL Server) from the list of available drivers and then click on Finish. The configuration window will open.

Configuring an ODBC connection for MySQL

Enter the following details in the connection configuration window:

• Data Source Name: name of the connection: "moveon"/"moveonline"
• Description: description of the connection: "moveon"/"moveonline"
• Server: name or IP address of the database server
• Port: port of the database server: the default setting is 3306
• User: name of the moveon database user
• Password: password of the moveon database user
• Database: database name: "moveon"/"moveonline"

Click Test to check that the connection is working.

Then click on Details to view the advanced settings for the ODBC connection. You must select the option "Return matched rows instead of affected rows".

Then click OK to confirm. The connection is configured.

Configuring an ODBC connection for PostgreSQL

Enter the following details in the connection configuration window:

• Data Source: name of the connection: "moveon"/"moveonline"
• Database: database name: "moveon"/"moveonline"
• Server: name or IP address of the database server
• User Name: name of the moveon database user
• Description: description of the connection: "moveon"/"moveonline"
• SSL-Mode: SSL connection mode: the default setting is "allow"
• Port: port of the database server: the default setting is 5432
• Password: password of the moveon database user

Click Test to check that the connection is working.

Then click Save to confirm. The connection is configured.

Configuring an ODBC connection for MS SQL Server

Step 1: Server

Step 1 is to enter the following connection details:

• Name: name of the connection: "moveon"/"moveonline"
• Description: description of the connection: "moveon"/"moveonline"
• Server: name or IP address of the database server

Step 2: Authentication mode

Step 2 is to specify the authentication mode to use (Windows authentication/SQL Server authentication). If you choose SQL Server authentication you must specify the user ID and password for the connection.

Step 3: Database

Step 3 is to select the "moveon"/"moveonline" database as the database if it is not set as the default database. A number of advanced settings are also displayed. Keep the default settings.

Step 4: Advanced settings

In step 4 you are shown a number of advanced settings. Keep the default settings and click on Finish.

A summary of the connection configuration will be displayed. Click Test data source to check that the connection is working.

Then click OK to confirm. The connection is configured.

Configuring an ODBC connection for Oracle

Enter the following details in the connection configuration window:

• Data Source Name: name of the connection: "moveon"
• Description: description of the connection: "moveon"
• TNS Service Name: name of the TNS service: e.g. "MOVEON"
• UserID: name of the moveon database user (mandatory)

Click Test Connection to check that the connection is working.

Then click OK to confirm. The connection is configured.

If the TNS has not been configured since the ODBC driver was installed, the configuration file "tnsnames.ora" will have to be edited as follows:

MOVEON =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = oracleserver)(PORT = 1522))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = MOVEON))
 )


You also need to make a number of changes to the Windows registry table (regedit function) in the HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE directory.

The parameters for the Oracle client must contain the following elements:

NLS_LANG = FRENCH_FRANCE.UTF8 (choose the Language and Territory appropriate to the country of use)

NLS_NUMERIC_CHARACTERS = “.,”

For more information about Oracle NLS variables, please refer to the Oracle documentation: http://download.oracle.com/docs/cd/A84870_01/doc/server.816/a76966/ch2.htm#745.