RODBC returns truncated errors

dplyr
shiny
rstudio

#1

I'm trying to query a Redshift database using the odbc package, however when I get the query, it is truncated. I've asked this in StackOverflow: https://stackoverflow.com/questions/50380575/rodbc-returning-truncated-results

If anyone can help, that would be great. I've also tried this tip


#2

When you say the data are truncated, do you mean in RStudio's viewer, or at the console, or both?


#3

Yup the truncation occurs in both the view and the console


#4

That's very odd. Does the truncation persist if you select a single column as vector, e.g. (based on your SO code) data$gender?


#5

Yea it's really odd. It persists inside the dataframe while in the databse the string is complete. I've never encountered this before


#6

Dead weird.

What's the data type in the column (both in the database and in R)?


#7

Up. I'm getting exactly the same error using the latest Amazon Redshift driver on mac. Different string columns get truncated randomly across the data frame. The solution I found is that you should apply the following in the SELECT statement for each column individually:

::VARCHAR(500) AS

but that's very inefficient if you have a lot of columns to download. Has anyone came across a solution to this problem?


#8

I also tried to follow the solutions here:

  1. https://stackoverflow.com/questions/48392622/odbc-truncating-varchar-fields
  2. https://stackoverflow.com/questions/4623460/rodbc-string-getting-truncated

but none of them worked for the Redshift driver. I also added explicitly these parameters in the odbc.ini file (https://docs.aws.amazon.com/redshift/latest/mgmt/configure-odbc-options.html), but none of them worked either. Looking forward to more ideas how to solve this one!