Setting up vRODBC

Moderator: NorbertKrupa

Post Reply
sn0x0r
Newbie
Newbie
Posts: 2
Joined: Mon Feb 15, 2016 3:43 pm

Setting up vRODBC

Post by sn0x0r » Mon Feb 15, 2016 3:55 pm

Dear people,

I'm at wits end, I have absolutely no clue how to proceed from here, and I've scoured the internet for any solutions to my problems. The problem has evolved over the course of several weeks now, so the problem that you see here comes from a streak of other problems.

What I am trying to achieve is to set up an vRODBC connection between R and Vertica. Eventually we want to load data into a Vertica DB and then query on it from R in order to analyse or predict or do stuff in general.

The problem however is quite simple, I just cannot achieve the connection. It has thrown several errors at me for a while now, but at this point we've landed on the following message:

Code: Select all

bbubuntuuser@BBVertica-Master:~$ isql -v VerticaDSN
[08001][unixODBC][Vertica][ODBC] (10380) Unable to establish a connection with data source. Missing settings: {[Database] [ServerName]}
[ISQL]ERROR: Could not SQLConnect
So, I am running Ubuntu 14.04 LTS, and the system variables are as follows:

Code: Select all

echo $VERTICAINI
/etc/vertica.ini

echo $ODBCINI
/etc/odbc.ini

echo $LD_LIBRARY_PATH
/opt/vertica/lib:/usr/lib:/usr/local/lib
Leading us to two files obviously:

First /etc/odbc.ini:

Code: Select all

[ODBC Data Sources]
VerticaDSN = "vmart"

[VerticaDSN]
Description = VerticaDSN
Driver = /opt/vertica/lib64/libverticaodbc.so
Database = vmart
Servername = 10.0.0.4
UserName = dbadmin
Password = <omitted>
Port = 5433
ConnSettings =
Locale = en_US

[Driver]
DriverManagerEncoding = UTF-32
ODBCInstLib = /usr/lib/x86_64-linux-gnu/libodbcinst.so
ErrorMessagesPath = /opt/vertica/lib64
LogLevel = 5
LogPath = /tmp
and then /etc/vertica.ini:

Code: Select all

[Driver]
DriverManagerEncoding = UTF-32
ODBCInstLib = /usr/lib/x86_64-linux-gnu/libodbcinst.so
ErrorMessagesPath = /opt/vertica/lib64
LogLevel = 5
LogPath = /tmp
All the path's resolve to the targeted file or directory, but with installing unixODBC from the website and the ODBC drivers from the Vertica website and all that stuff I cannot possibly tell anymore where it exactly went wrong.

The log is also very descriptive:

Code: Select all

Feb 15 15:54:56 INFO  2752448320 VConnection::UpdateConnectionSettings: in: DSN:VerticaDSN, type 0
Feb 15 15:54:56 INFO  2752448320 VConnection::UpdateConnectionSettings: out BackupServerNode
Feb 15 15:54:56 INFO  2752448320 VConnection::UpdateConnectionSettings: type 0, value: ?
Feb 15 15:54:56 INFO  2752448320 VConnection::UpdateConnectionSettings: out ColumnsAsChar
Feb 15 15:54:56 INFO  2752448320 VConnection::UpdateConnectionSettings: type 0, value: ?
Feb 15 15:54:56 INFO  2752448320 VConnection::UpdateConnectionSettings: out ConnectionLoadBalance
Feb 15 15:54:56 INFO  2752448320 VConnection::UpdateConnectionSettings: type 0, value: ?
Feb 15 15:54:56 INFO  2752448320 VConnection::UpdateConnectionSettings: out ConnSettings
Feb 15 15:54:56 INFO  2752448320 VConnection::UpdateConnectionSettings: type 0, value: ?
Feb 15 15:54:56 INFO  2752448320 VConnection::UpdateConnectionSettings: out Database
Feb 15 15:54:56 INFO  2752448320 VConnection::UpdateConnectionSettings: type 0, value: ?
Feb 15 15:54:56 INFO  2752448320 VConnection::UpdateConnectionSettings: out DirectBatchInsert
Feb 15 15:54:56 INFO  2752448320 VConnection::UpdateConnectionSettings: type 0, value: ?
Feb 15 15:54:56 INFO  2752448320 VConnection::UpdateConnectionSettings: out DriverStringConversions
Feb 15 15:54:56 INFO  2752448320 VConnection::UpdateConnectionSettings: type 0, value: ?
Feb 15 15:54:56 INFO  2752448320 VConnection::UpdateConnectionSettings: out Label
Feb 15 15:54:56 INFO  2752448320 VConnection::UpdateConnectionSettings: type 0, value: ?
Feb 15 15:54:56 INFO  2752448320 VConnection::UpdateConnectionSettings: out Locale
Feb 15 15:54:56 INFO  2752448320 VConnection::UpdateConnectionSettings: type 0, value: ?
Feb 15 15:54:56 INFO  2752448320 VConnection::UpdateConnectionSettings: out Port
Feb 15 15:54:56 INFO  2752448320 VConnection::UpdateConnectionSettings: type 0, value: ?
Feb 15 15:54:56 INFO  2752448320 VConnection::UpdateConnectionSettings: out PreferredAddressFamily
Feb 15 15:54:56 INFO  2752448320 VConnection::UpdateConnectionSettings: type 0, value: ?
Feb 15 15:54:56 INFO  2752448320 VConnection::UpdateConnectionSettings: out PWD
Feb 15 15:54:56 INFO  2752448320 VConnection::UpdateConnectionSettings: type 0, value: ?
Feb 15 15:54:56 INFO  2752448320 VConnection::UpdateConnectionSettings: out ResultBufferSize
Feb 15 15:54:56 INFO  2752448320 VConnection::UpdateConnectionSettings: type 0, value: ?
Feb 15 15:54:56 INFO  2752448320 VConnection::UpdateConnectionSettings: out ServerName
Feb 15 15:54:56 INFO  2752448320 VConnection::UpdateConnectionSettings: type 0, value: ?
Feb 15 15:54:56 INFO  2752448320 VConnection::UpdateConnectionSettings: out SSLCertFile
Feb 15 15:54:56 INFO  2752448320 VConnection::UpdateConnectionSettings: type 0, value: ?
Feb 15 15:54:56 INFO  2752448320 VConnection::UpdateConnectionSettings: out SSLKeyFile
Feb 15 15:54:56 INFO  2752448320 VConnection::UpdateConnectionSettings: type 0, value: ?
Feb 15 15:54:56 INFO  2752448320 VConnection::UpdateConnectionSettings: out SSLMode
Feb 15 15:54:56 INFO  2752448320 VConnection::UpdateConnectionSettings: type 0, value: ?
Feb 15 15:54:56 INFO  2752448320 VConnection::UpdateConnectionSettings: out TransactionIsolation
Feb 15 15:54:56 INFO  2752448320 VConnection::UpdateConnectionSettings: type 0, value: ?
Feb 15 15:54:56 INFO  2752448320 VConnection::UpdateConnectionSettings: out UID
Feb 15 15:54:56 INFO  2752448320 VConnection::UpdateConnectionSettings: type 0, value: ?
Feb 15 15:54:56 ERROR 2752448320 Connection::SQLConnectW: [Vertica][ODBC] (10380) Unable to establish a connection with data source. Missing settings: {[Database] [ServerName]}
Is there anybody who can make sense of this or have any ideas what I could try in order to get this to work? I would be so grateful. Thanks in advance!

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: Setting up vRODBC

Post by JimKnicely » Mon Feb 15, 2016 4:37 pm

Hi,

Can you check if the solution offered in this thread helps?

viewtopic.php?f=21&t=2154&p=7257&hilit=servername#p7257
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

sn0x0r
Newbie
Newbie
Posts: 2
Joined: Mon Feb 15, 2016 3:43 pm

Re: Setting up vRODBC

Post by sn0x0r » Mon Feb 22, 2016 10:26 am

JimKnicely wrote:Hi,

Can you check if the solution offered in this thread helps?

viewtopic.php?f=21&t=2154&p=7257&hilit=servername#p7257
Thanks for the suggestion.

First off, if I change libverticaodbc.so to libodbc.so the connection doesn't immediately trigger an error, but nothing happens either, the database is running though since in admintools I can enter the VSQL tool and query things. So at least there's a change in behaviour.

Secondly, I tried moving all the files from /etc/* to /usr/local/etc/* and change the system variables accordingly, but this didn't change the error in any kind of way.

A last thought on the first option, isn't using the other libodbc.so file actually besides the point since using the libverticaodbc.so file is optimized for a verticaODBC connection? So how come using this file could be a solution? (Even though it isn't working for me regardless)

Thanks in advance!

Post Reply

Return to “ODBC”