CODEWITHSUNDEEP
rdb2rodbc
Nolan
Nolan

Reputation: 75

Maximum Length of Value in R Data Frame, RODBC

I am trying to do a simple query of a DB2 database using the RODBC package in R (myQuery<-sqlQuery(channel,paste0("..."))) One of the columns is a Varchar of length 3000. The resulting data frame shows a "NA" in that column when there should be text. Exporting it to csv also only shows "NA". A query in Access shows an odd character encoding (only after clicking on the cell). Is there a maximum length of a value in a R data frame or a maximum length of a field that can be pulled using RODBC? Or is it the encoding of the field that causes the "NA" to appear?

Upvotes: 1

Views: 381

Answers (1)

user1919238
user1919238

Reputation:

I did an end to end test on DB2 (LUW 9.7) and R (3.2.2 Windows) and it worked fine for me.

SQL code:

create table test (foo varchar(3000));

--actual insert is 3000 chars
insert into test values ('aaaaaa .... a'); 

--this select worked fine in my normal SQL client
select * from test

R code:

long = sqlQuery(connection, "select * from test");

#Displays the 3000 character value.
long;

My guess is the problem is for some other reason than simply the size of the field:

  • Character encoding issues. If you are seeing something funny in Access, perhaps the content of the field is something not acceptable in the character encoding R is using, so it is being discarded. (I'm not familiar with character encoding in R in particular, but it is in general a thorny issue for software development).
  • Overall size of the results. Maybe the problem is due to the overall length of a row rather than the length of a single field. Is the query also returning lots of other stuff? Have you tried a simple test of just this field?
  • Problem in another version. Maybe you are using a different version than I was, and there is indeed a problem with your version. If you think so, update your question with more information.

Upvotes: 1

Related Questions