Switching from RJDBC and RODBC to odbc

General
, ,
1

I am updating a couple of shiny apps to use the odbc library instead of RJDBC and/or RODBC.

Overall this is working well and faster. However, I have run into one problem that I can not seem to figure out.

When I query a column with fields that hold long strings of text, my query is only returning 255 characters. For example, the following query returns the expected results on my local machine, but on my shiny server fields in the NotesNotes column are truncated to 255 characters. RODBC returns the expected results.

notes <- dbGetQuery(con,
                                   "SELECT NoteDate, 
                                           NoteNotes = CONVERT(VARCHAR(4000), NoteNotes)
                                    FROM Notes")

I have tried a number of variations on the query with no luck.

I am running R 3.4.3. The versions of the R packages that this leverages are the same on my machine and the server. I have tried several variations of the query and continue to hit the same issue.

I am running this on an open source shiny server (v1.4.2.786) with R 3.4.3 and odbc (v1.1.3) and dbi (v0.7).

The database is SQL Server 2012.

Any ideas for next steps in troubleshooting this would be much appreciated. Thanks.

2

Hi @Rich, can you share with us what driver you are using in the Linux box, the version of Linux and the version of unixodbc?

3

Thanks for the quick response, Edgar.

CentOS Linux release 7.4.1708

unixODBC Version: 2.3.1

Driver: libtdsodbc.so.0

4

Great, thank you

@jimhester pointed out that this may be an issue with the Free TDS driver, and pointed towards this SO post: https://stackoverflow.com/questions/2409851/odbc-query-on-ms-sql-server-returning-first-255-characters-only-in-php-pdo-free/2410020#2410020

If this is a commercial application, you may wish to consider using a paid-for driver, or maybe even upgrading to the commercial version of Shiny, it includes pro-ODBC drivers for Linux.

5

Thanks, @edgararuiz and @jimhester. I will take a shot at this and let you know how it goes.

This is not a commercial app, but I am considering upgrading to pro or connect.