[R-sig-DB] [R] trouble with RODBC -- chopping off part of column names

Marc Schwartz m@rc_@chw@rtz @end|ng |rom me@com
Tue Oct 5 15:25:14 CEST 2010


On Oct 4, 2010, at 5:15 PM, Mike Williamson wrote:

> Marc, et. al,
> 
>    Below are all of the pertinent version info.  However, I think versions,
> etc., are somewhat irrelevant.  Instead, somewhere there must be an
> environment variable or something that 'R' is talking to that is forcing the
> column name to fit within a set column width, and I need to either blow away
> that variable or make it much larger.
>    Again, to recap:  if I make SQL queries outside of 'R', I am able to
> grab column names properly.  If I make SQL queries *within 'R' and through a
> Windows server*, I grab all column names properly.  However, if I make SQL
> queries *within 'R' and through the linux/unix server described below*, the
> column names are cut off at a fixed length of 30 characters.
>    Any advice as to where to look for this environment variable or whatever
> setting would help greatly!
> 
>                                 Thanks!
>                                    Mike

[Moving to R-SIG-DB alone]

Mike,

I don't have hands on experience with MS SQL Server, so my comments here are based upon my experience with Oracle and RODBC on both Fedora Linux and OSX.

With Oracle, the setting of 'rows_at_time = 1' in the calls to both odbcConnect() and sqlQuery() are required to preclude the corruption of data returned from a query. See ?odbcConnect and ?sqlQuery for more information. This is also covered in the RODBC vignette.

There are also some references in the archives to setting 'believeNRows = FALSE' in odbcConnect() and sqlQuery(). This value defaults to TRUE. I do not need this with Oracle, but you may with SQL Server.

So you may wish to set all four arguments in your code and then re-run your connection and queries from a clean R session, to see if they have any impact on the returned results. If that helps, then backout each of the settings to see if you can isolate which one seems to be of value.

You may also wish to use sqlColumns() to query the table in question to see what it returns for the column names, if different than the results from the above queries. 

I am not aware of any environment (shell) settings that affect this. At least with Oracle, such settings are solely to define the $PATH for the relevant libraries and config files.

HTH,

Marc




More information about the R-sig-DB mailing list