How do I install an ODBC driver in Linux?

[Josh]

Hello,

I am having trouble installing Vertica's ODBC driver on Linux. I am getting the following error:

[01000][unixODBC][Driver Manager]Can't open lib '/opt/vertica/lib64/libverticaodbc_unixodbc.so' : /opt/vertica/lib64/libverticaodbc_unixodbc.so: wrong ELF class: ELFCLASS64

Does anyone know what that means?

Thanks for you time.

[JimKnicely]

It looks like you tried installing the 64 bit ODBC drivers on to a 32 bit OS. Try typing uname -m at the OS prompt. If it says i686 then you are using 32 bit and need to DL the 32 bit ODBC drivers from Vertica's web site.

[Josh]

Thanks. That was part of the issue. Can you please help me set up the odbc ini files? Maybe explain what needs to go where?

[JimKnicely]

Josh,

It's relatively easy to install the Vertica ODBC drivers on Linux. I'll show you a simple example using a 64-bit RedHat Linux 5 server.

• Step 1: Grab the correct RPM from Vertica's web site (I downloaded the vertica_5.1.0_odbc_x86_64_linux.tar.gz file)
  
  Step 2: On your server create a directory called /opt/vertica

  Code: Select all

  # mkdir -p /opt/vertica/

  Step 3: Upload the RPM file to that directory
  
  Step 4: Unarchive (untar) the file

  Code: Select all

  # cd /opt/vertica/
  # ls -lrt
  total 7956
  -rw-r--r-- 1 root root 8132677 Feb 16 08:06 vertica_5.1.0_odbc_x86_64_linux.tar.gz
  # tar vzxf vertica_5.1.0_odbc_x86_64_linux.tar.gz
  include/
  include/verticaodbc.h
  lib64/
  lib64/en-US/
  lib64/en-US/VerticaMessages.xml
  lib64/en-US/ODBCMessages.xml
  lib64/libverticaodbc.so.5.1.0
  lib64/libverticaodbc.so
  lib64/libverticaodbc.so.5
  lib64/libverticaodbc.so.5.1

  Step 5: Create/Edit a file called /etc/odbc.ini. In this file you need to create a DSN (Data Source Name) for your Veritca server.
  
  Here's an example:

  Code: Select all

  # cat /etc/odbc.ini
  [vertica_prod01]
  Driver = /opt/vertica/lib/libverticaodbc.so
  Servername = w.x.y.z
  Database = database_name
  Port = 5433
  UserName = dbadmin
  Password = **********

  Note: In your DSN replace w.x.y.z with your server's IP address or host name, and replace database_name with your Vertica database name.
  
  Step 6: Use the isql utility (included with unixODBC driver manager) to test the connection:

  Code: Select all

  # isql vertica_prod01
  +---------------------------------------+
  | Connected!                            |
  |                                       |
  | sql-statement                         |
  | help [tablename]                      |
  | quit                                  |
  |                                       |
  +---------------------------------------+
  SQL> select count(*) from tables;
  +---------------------+
  | count               |
  +---------------------+
  | 149                 |
  +---------------------+
  SQLRowCount returns 1
  1 rows fetched

All done

[id10t]

Hi Jim.

Can you provide me output of next commands?

Code: Select all

rpm -q unixODBC
rpm -q pyodbc
ldd /opt/vertica/lib/libverticaodbc.so 
ldd /opt/vertica/lib64/libverticaodbc.so 

Thnx.

[JimKnicely]

Hi,

Here is the output:

Code: Select all

[root@devcognosdb ~]# rpm -q unixODBC
unixODBC-2.2.11-10.el5
unixODBC-2.2.11-10.el5
[root@devcognosdb ~]# rpm -q pyodbc
package pyodbc is not installed
[root@devcognosdb ~]# ldd /opt/vertica/lib/libverticaodbc.so
 linux-gate.so.1 =>  (0xffffe000)
 libdl.so.2 => /lib/libdl.so.2 (0xf6bb7000)
 libresolv.so.2 => /lib/libresolv.so.2 (0xf6ba3000)
 libcrypt.so.1 => /lib/libcrypt.so.1 (0xf6b71000)
 libpthread.so.0 => /lib/libpthread.so.0 (0xf6b58000)
 libstdc++.so.6 => /usr/lib/libstdc++.so.6 (0xf6a6c000)
 libm.so.6 => /lib/libm.so.6 (0xf6a43000)
 libgcc_s.so.1 => /lib/libgcc_s.so.1 (0xf6a37000)
 libc.so.6 => /lib/libc.so.6 (0xf68de000)
 /lib/ld-linux.so.2 (0x00a42000)
[root@devcognosdb ~]# ldd /opt/vertica/lib64/libverticaodbc.so
 libdl.so.2 => /lib64/libdl.so.2 (0x00002acbc5110000)
 libresolv.so.2 => /lib64/libresolv.so.2 (0x00002acbc5314000)
 libcrypt.so.1 => /lib64/libcrypt.so.1 (0x00002acbc5529000)
 libpthread.so.0 => /lib64/libpthread.so.0 (0x00002acbc5762000)
 libstdc++.so.6 => /usr/lib64/libstdc++.so.6 (0x00002acbc597d000)
 libm.so.6 => /lib64/libm.so.6 (0x00002acbc5c7d000)
 libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00002acbc5f01000)
 libc.so.6 => /lib64/libc.so.6 (0x00002acbc610f000)
 /lib64/ld-linux-x86-64.so.2 (0x0000003b4c800000)
[root@devcognosdb ~]#

I updated the Vertica drivers on this server to version 5.1.4. Now I can't connect using iSQL unless I pass in the username and password in the command line:

Code: Select all

[root@devcognosdb etc]# cat odbc.ini
[vertica_prod01]
Driver = /opt/vertica/lib64/libverticaodbc.so.5.1.4
Database = intersect
Servername = 10.255.16.126
UID = dbadmin
PWD =
Port = 5433

[root@devcognosdb etc]# isql -v vertica_prod01
[28000][unixODBC][Vertica][VerticaDSII] (160) Connection attempt failed: FATAL:  Invalid username or password

[ISQL]ERROR: Could not SQLConnect

[root@devcognosdb etc]# isql -v vertica_prod01 dbadmin
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL>select version();
+---------------------------------------------------------------------------------------------------------------------------------+
| version                                                                                                                         |
+---------------------------------------------------------------------------------------------------------------------------------+
| Vertica Analytic Database v5.1.1-0                                                                                              |
+---------------------------------------------------------------------------------------------------------------------------------+
SQLRowCount returns 1
1 rows fetched

That's annoying...

[JimKnicely]

Ok, so it wasn't me being stupid There's an issue with the ODBC drivers ... Here is the response from Vertica support:

Your ini file is fine. There is actually a bug ver-22665 regarding this behaviour change in the 5.1 drivers. The old drivers used to use UID and a comparison the the ini file's Username, Username taking precidence over UID. The new driver changed to use UID and ignored the ini file's Username field. So passing the name using iSQL forces the driver to use what's passed vs. UID and overcomes the Username in the ini being ignored.

Workarounds are:
for iSQL type envs where you can pass the username do so for applications which rely on the Username in the DSN use the latest 4.1.X driver.

The fixed driver is tentatively due out in the patch release the end of this month.