Using the odbctest Utility to test connection with MariaDB

About this task

The following requirements and restrictions apply to using MariaDB with Unica Campaign.

MariaDB version: 10.3.13 - 64 Bit
MariaDB ODBC Connector

Note: V12.1 FP2 onwards we need to use MariaDB version: 10.4.x - 64 Bit

Steps to Install MariaDB Connector 3.1.9 for SUSE

Download link : https://downloads.mariadb.org/mariadb/repositories/#distro=SLES&distro_release=sles12-amd64--sles12&mirror=tuna&version=10.4

sudo rpm . --import https://yum.mariadb.org/RPM-GPG-KEY-MariaDB
sudo zypper addrepo --gpgcheck --refresh https://yum.mariadb.org/10.4/sles/12/x86_64 mariadb
sudo zypper --gpg-auto-import-keys refresh
sudo zypper addrepo http://download.opensuse.org/distribution/11.4/repo/oss/ oss
sudo zypper install mariadb-connector-odbc

Steps to Install MariaDB Connector 3.1.0 for Windows

Download links
- [ https://downloads.mariadb.com/Connectors/odbc/connector-odbc-3.1.0/mariadb-connector-odbc-3.1.0-win64.msi ]
- [ https://mariadb.com/kb/en/library/installing-mariadb-msi-packages-on-windows/ ]
Note: From 12.1 FP 2 onwards, use 3.1.6 MariaDB ODBC Connector version for Windows and linux.
URL : https://downloads.mariadb.com/Connectors/odbc/connector-odbc-3.1.6/

Steps to install MariaDB Connector/ODBC 3.1.0 for RHEL

Download the link : [ https://downloads.mariadb.com/Connectors/odbc/connector-odbc-3.1.0/mariadb-connector-odbc-3.1.0-rc-rhel7-x86_64.tar.gz ]
mkdir odbc_package
cd odbc_package
wget [ https://downloads.mariadb.com/Connectors/odbc/connector-odbc-3.1.0/mariadb-connector-odbc-3.1.0-rc-rhel7-x86_64.tar.gz ]
tar -xvzf mariadb-connector-odbc-3.1.0-ga-rhel7-x86_64.tar.gz
sudo install lib64/libmaodbc.so /usr/lib64/

Note: V12.1 FP2 onwards we need to use MariaDB ODBC connector version 3.1.6 for Windows and Linux and 3.1.9 for Suse operating system.

Also, we need to add new MariaDB Driver level configurations as mentioned below.

MariaDB ODBC connector versions

Windows

Maria DB ODBC Connector : 3.1.6

Download link : https://downloads.mariadb.com/Connectors/odbc/connector-odbc-3.1.6/mariadb-connector-odbc-3.1.6-win64.msi

RHEL

Maria DB ODBC Connector : 3.1.6

Download link : https://downloads.mariadb.com/Connectors/odbc/connector-odbc-3.1.6/mariadb-connector-odbc-3.1.6-ga-rhel7-x86_64.tar.gz

SUSE

Maria DB ODBC Connector : 3.1.9

Download link : https://downloads.mariadb.com/Connectors/odbc/connector-odbc-3.1.9/mariadb-connector-odbc-3.1.9-sles123-amd64.tar.gz

MariaDB ODBC driver level configurations

Example for windows - need to set in ODBC Driver Manager under statements parameter:

SET SQL_MODE='PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_KEY_OPTIONS';

Example for SUSE/linux - need to set in odbc,ini:
[<CampaignSystem>]
Driver=/usr/local/mariaDBConnector/lib/libmaodbc.so
DATABASE=<DATABASE_NAME>
DESCRIPTION=MariaDB
SERVER=<DATABASE_SERVER>
UID=<USER_NAME>
PASSWORD=<PASSWORD>
PORT=3306
InitStmt=SET SQL_MODE='PIPES_AS_CONCAT,ANSI_QUOTES,IGNORE_SPACE,NO_KEY_OPTIONS';

Note: Use MariaDB database user name. DON'T use root and unica*03 as username and password for UA_SYSTEM_TABLES datasource.

MariaDB JAVA Client (JDBC Client): v2.4.0 -

Download the link:[ https://mariadb.com/kb/en/library/about-mariadb-connector-j/ ]

Note: For AIX, we do not support Maria DB as System and User Database.

The odbctest utility allows you to test connections to an MariaDB server.

From a command prompt on the Unica Campaign server, run the odbctest utility.
Enter the following information exactly at the prompts:
- The name of the MariaDB datasource name that you want to connect to.
- A database user ID.
- The password associated with the database user ID.

The utility does not prompt for confirmation of your selections.If successful, the utility prints a Connection Successful message and exits with a return value of zero (0).