ODBC truncating varchar fields

Question

Using the package ODBC to connect to vertica (MAC OSX). I have connected to vertica database using ODBC driver and DBI package. I was able to connect to the database. The varchar fields coming out of the query results are truncated whereas other fields getting retrieved properly.

library(RODBC)
library(DBI)

default_vertica_database         <- 'dsciasda01'
default_vertica_user             <- 'sadasdy'
default_vertica_pass             <- 'sadasds'
default_vertica_port             <- 5433
default_vertica_hosts            <- 'vsadasdz-vip.adas.sadasda.com'

vertica_driver      <- default_vertica_driver
vertica_database    <- default_vertica_database
vertica_port        <- default_vertica_port
vertica_host        <- default_vertica_host
vertica_user        <- default_vertica_user
vertica_pass        <- default_vertica_pass

vertica_driver      <- '/Library/Vertica/ODBC/lib/libverticaodbc.dylib'

#connect to vertica
vth <- dbConnect(odbc::odbc(),
                 driver   = vertica_driver,
                 database = vertica_database,
                 uid      = vertica_user,
                 pwd      = vertica_pass,
                 host     = vertica_host,
                 port     = vertica_port
)

pool_list<<-dbGetQuery(vth, "Select * from table0.table_current limit 10")

fields that are varchar are getting truncated. If there is a field with a length of 64 only the first char in the string is getting retrieved.

Can you post ur `vertica.ini` and `odbc.ini` files? Need more info about your environment. BTW: ODBC drivers are not recommended (from my experience) — sKwa, Jan 23 '18 at 01:40
Running it on macOS High Sierra. vertica.ini --[Driver] ErrorMessagesPath=/Library/Vertica/ODBC/messages/ ODBCInstLib=/usr/lib/libiodbcinst.dylib DriverManagerEncoding=UTF-32 my odbc.ini file is empty. — Aditya, Jan 23 '18 at 02:52
Is it `iODBC`? Looks like a problem with encoding or driver manager chars conversation. — sKwa, Jan 29 '18 at 22:42
we need to be sure that a problem with `iODBC`. Test your query with `isql`. If `isql` truncates chars - so problem in `iODBC`; if not - so problem in driver configuration. — sKwa, Feb 02 '18 at 22:29
sorry, test it with [`iodbctest`](https://my.vertica.com/docs/7.2.x/HTML/index.htm#Authoring/ConnectingToHPVertica/InstallingDrivers/MacOSX/TestingADSNUsingIODBCTest.htm). — sKwa, Feb 05 '18 at 21:52

score 0 · Answer 1 · answered Jun 07 '18 at 18:42

Possibly related - Using ODBC to connect to Amazon's Redshift, I found that I needed to set the MaxLongVarcharSize parameter in the connection:

con <- dbConnect(odbc::odbc(),
                 driver = "Amazon Redshift Driver",
                 database = "xx",
                 uid = "yy",
                 pwd = "zz",
                 locale="en-US",
                 host = "aa",
                 port = 5439,
                 MaxLongVarcharSize = 8190)

h/t RODBC string getting truncated

ODBC truncating varchar fields

1 Answers1