UnixODBC
When attempting to prove ODBC connectivity on a Unix system, you often need an ODBC Driver manager, http://www.unixodbc.org is a useful resource. Once downloaded to install :-
untar /gunzip the downloaded media
As root user
./configure
make
make install
This will create the following files which you can then configure
/usr/local/etc/odbcinst.ini
/usr/local/etc/odbc.ini
A recent test was to configure a unix environment with the ability to connect to a RedShift database via ODBC. Redshift underlying architecture is actually PostGres, so the this test case was used.
Install the ODBC Driver via YUM
Yum install postgresql-odbc.x86_64
This should create the driver library file at location /usr/lib64/psqlodbcw.so
To test an ODBC connection you now need to configure the odbcinst.ini and odbc.ini files located in /usr/local/etc
Odbcinst.ini
[PostgreSQL_Yum]
Description=PostgreSQL ODBC Driver
Driver=/usr/lib64/psqlodbcw.so
Debug=1
CommLog=1
Odbc.ini
[redshift_Yum]
Description = Services Database
Driver = PostgreSQL_Yum
Database = hlc
Servername = hlc-dw-instance.cuuvb6sl3b8y.us-west-2.redshift.amazonaws.com
UserName = hlcadmin
Password = Hlcadmin1
Port = 5439
ReadOnly = No
RowVersioning = No
ShowSystemTables = No
ShowOidColumn = No
FakeOidIndex = No
ConnSettings =
The odbc.ini “driver” parameter refers to the name of the driver defined in odbcinst.ini. The odbc.ini has also been configured with the servername,username,password and port number of an AWS Redshift instance.
To test connectivity, having installed the unixodbc package, there is a command line utility called isql which allows you to connect using the ODBC configuration
isql <dsn> will attempt to connect to the data source configured, see the example below
[root@hlcdb01 etc]# isql redshift_Yum
+—————————————+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+—————————————+
SQL> select count(*) from users
+———————+
| count |
+———————+
| 49990 |
+———————+
SQLRowCount returns 1
1 rows fetched
SQL>quit
[root@hlcdb01 etc]#
Install and Build a Driver
If the ODBC driver you wish to use is not available as a YUM installation, then it is often possible to download the source files, and build the library manually. The steps below demonstrate how you could build a Postgres driver from the source
Download the source binaries from http://www.postgresql.org/ftp/odbc/versions/src/
Unpack the binaries to a location of your choice
./configure –disable-openssl
Make
Make install
Once a successful build has run, you will see the library created at location /usr/local/lib/psqlodbcw.so
During the Make, you may experience some errors, where the definition of the source, may differ to the local definition for a particular function. For example during the above build an error occurred during the “make” execution. There was a mismatch between “odbcapi.c” so the parameter list of SQLExtendedFetch matched that of “/usr/local/include/sqlext.h”.
To Test the newly built ODBC driver, configure the ini files as follows
Odbcinst.ini
[PostgreSQL_Build]
Description=PostgreSQL ODBC Driver
Driver=/usr/local/lib/psqlodbcw.so
Debug=1
CommLog=1
Odbc.ini
[redshift_build]
Description = Services Database
Driver = PostgreSQL_Build
Database = hlc
Servername = hlc-dw-instance.cuuvb6sl3b8y.us-west-2.redshift.amazonaws.com
UserName = hlcadmin
Password = Hlcadmin1
Port = 5439
ReadOnly = No
RowVersioning = No
ShowSystemTables = No
ShowOidColumn = No
FakeOidIndex = No
ConnSettings =
Test using isql
[root@hlcdb01 etc]# isql redshift_build
+—————————————+
| Connected! |
| |
| sql-statement |
| help [tablename] |
| quit |
| |
+—————————————+
SQL> select count(*) from users;
+———————+
| count |
+———————+
| 49990 |
+———————+
SQLRowCount returns 1
1 rows fetched
SQL> quit
[root@hlcdb01 etc]#
That completes the ODBC test. This at least proves the ODBC connection to a source system, even if these native drivers are not used by the application in question.