RODBC returns truncated errors

[minh5]

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

[jim89]

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

[minh5]

Yup the truncation occurs in both the view and the console

[jim89]

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?

[minh5]

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

[jim89]

Dead weird.

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

[konradino]

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?

[konradino]

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!

[konradino]

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.

[mara]

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.

FAQ: How do I mark a solution?

If your question has been answered, don't forget to mark the solution! How do I mark a solution? Find the reply you want to mark as the solution and look for the row of small gray icons at the bottom of that reply. Click the one that looks like a box with a checkmark in it: [image] Hovering over the mark solution button shows the label, "Select if this reply solves the problem". If you don't see the mark solution button, try clicking the three dots button ( ••• ) to expand the full set of options. When a solution is chosen, the icon turns green and the hover label changes to: "Unselect if this reply no longer solves the problem". Success! [solution_reply_author] …

Thanks

[konradino]

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

[mara]

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

[konradino]

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

[mara]

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

[konradino]

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

[jcblum]

@konradino

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

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: