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

0 Likes

#2

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

0 Likes

#3

Yup the truncation occurs in both the view and the console

0 Likes

#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?

0 Likes

#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

0 Likes

#6

Dead weird.

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

0 Likes

#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?

0 Likes

#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!

0 Likes

#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.

1 Like

#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

0 Likes

#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.

0 Likes

#12

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

0 Likes

#13

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

0 Likes

#14

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

0 Likes

#15

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

0 Likes

#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:

0 Likes