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!


#9

I eventually managed to find a solution: RPostgres::Postgres(), works really well and it should replace the odbc part when specifying the connection with DBI. No additional setup with this package is required.


#10

If your question's been answered (even if by you), would you mind choosing a solution? (See FAQ below for how).

Having questions checked as resolved makes it a bit easier to navigate the site visually and see which threads still need help.

Thanks


#11

Maybe I'm missing something here but I actually can't see that little solution box to any of the replies in this thread.


#12

Oh, you might have to click on the three dots next to the Reply button (I think that's actually changed) :grimacing:


#13

I wish I could but unfortunately it doesn't seem to be an option here :frowning:


#14

Super weird. Sorry for the goose chase. I marked it for you!


#15

No worries! Anyhow, I'll mark the solutions in the future :wink:


#16

Just FYI, that’s because you did not originate this topic (it was started by @minh5). Only the person who started the topic can mark a solution — which makes sense, since the solution to your apparently similar question might not have actually worked for the topic originator.

This is one reason why in general, it’s probably a better idea to post a new linked topic, rather than to jump in to an old thread with a similar problem. The forum software explicitly supports topics derived from other topics (though I wish it were more obvious!). You start by clicking the little link button at the bottom of the original post: