R help - Oct 2010 - trouble with RODBC -- chopping off part of column names

Hello all,

    I have a strange / interesting problem that might be 'R' settings
themselves, or it might be something with the OS.

    I am using the RODBC library.  I have a script that goes out and, before
making a query for a big data set, will first query for the column names of
the data set.  The column names could sometimes be quite long (e.g., "Time
Background Estimation (seconds)" ).  If I make this query for the column
names from my Windows laptop or from a Windows server, using odbcConnect() &
sqlQuery(), I get the column names properly.  However, if I run this via
unix, it will chop off part of the column name.  (E.g., with "Time
Background Estimation (seconds)", it becomes "Time Background
Estimation
(se", which is 30 characters long.)

    Does anyone have a clue what might be causing this (settings in 'R',
something within unix, etc.)?  I am not even sure how to debug, and I can't
really get around this because I cannot simply query all of the columns, the
data set would become too large.

                                                  Thanks!
                                                          Mike




"Telescopes and bathyscaphes and sonar probes of Scottish lakes,
Tacoma Narrows bridge collapse explained with abstract phase-space maps,
Some x-ray slides, a music score, Minard's Napoleanic war:
The most exciting frontier is charting what's already here."
  -- xkcd

--
Help protect Wikipedia. Donate now:
http://wikimediafoundation.org/wiki/Support_Wikipedia/en

	[[alternative HTML version deleted]]

On Oct 1, 2010, at 6:26 PM, Mike Williamson wrote:
> Hello all,
> 
>    I have a strange / interesting problem that might be 'R'
settings
> themselves, or it might be something with the OS.
> 
>    I am using the RODBC library.  I have a script that goes out and, before
> making a query for a big data set, will first query for the column names of
> the data set.  The column names could sometimes be quite long (e.g.,
"Time
> Background Estimation (seconds)" ).  If I make this query for the
column
> names from my Windows laptop or from a Windows server, using odbcConnect()
&
> sqlQuery(), I get the column names properly.  However, if I run this via
> unix, it will chop off part of the column name.  (E.g., with "Time
> Background Estimation (seconds)", it becomes "Time Background
Estimation
> (se", which is 30 characters long.)
> 
>    Does anyone have a clue what might be causing this (settings in
'R',
> something within unix, etc.)?  I am not even sure how to debug, and I
can't
> really get around this because I cannot simply query all of the columns,
the
> data set would become too large.
> 
>                                                  Thanks!
>                                                          Mike

Mike,

You indicated 'unix' above. Is that Solaris or are you being generic in
a reference to a Linux platform?

We need the details of your OS, the version of R you are running and whether it
is 32 or 64 bit, as well as the database that you are connecting to (eg.
Oracle).

You can use:

  vignette("RODBC")

from the R command line to bring up a PDF carefully written by Prof. Ripley,
that contains additional details the use of RODBC and some OS/DB specific
documentation for the package.

For further details on how we can better help you, see the R Posting Guide:

  http://www.R-project.org/posting-guide.html

Lastly, for future reference, there is an R-SIG-DB list:

  https://stat.ethz.ch/mailman/listinfo/r-sig-db

Marc Schwartz

>If I make this query for the column
>names from my Windows laptop or from a Windows server, using odbcConnect()
&
>sqlQuery(), I get the column names properly.  However, if I run this via
>unix, it will chop off part of the column name.  (E.g., with "Time
>Background Estimation (seconds)", it becomes "Time Background
Estimation
>(se", which is 30 characters long.)
With an ODBC connection, between R and the database there sit: R > RODBC >
(OSodbc) > DBMSodbc > DBMS. Your problem must be with the unixodbc driver.
Have you tried the RODBC setting to query one row at the time (rows_at_time
argument in odbcConnect command)? Prof. Ripley warns about the connection
retrieving incomplete data when rat > 1, precisely because of driver issues. 
See pages 5-6 of the pdf. 
http://cran.r-project.org/web/packages/RODBC/RODBC.pdf

Plan B: reduce the abstraction layers between R and the dbms. Can you connect
directly (i.e. not via odbc)?

Leo A. Salas, Ph.D. Senoir Scientist - Informatics Division
PRBO Conservation Science
3820 Cypress Drive, Suite 11, Petaluma, CA 94954
Ph: (707) 781-2555 x334
www.prbo.org<http://www.prbo.org/cms/index.php>  |  Please follow PRBO on
Facebook<http://www.facebook.com/home.php?#!/PRBOConservationScience?ref=search>!
PRBO conserves birds, other wildlife, and their ecosystems through innovative
scientific research and outreach.


	[[alternative HTML version deleted]]