[R] RODBC : reading binary data from a TXT field belonging to a PostgeSQL table

[Laurent Rhelp]

Le 25/03/2010 20:28, Laurent Rhelp a écrit :
> Dear R-List,
>
>    I am working with binary data that I want to store in a PostgreSQL 
> DataBase. I decided to use a TXT field. I read my binary file with 
> readBin function, I succeed in my data storage in the database but I 
> have some trouble to extract the data : the correct amount of bytes is 
> stored in the TXT field but when I access to the data, the extracted 
> dataframe is truncated !
>
>  Here's the code :
>
> library("RODBC");
> ##
> ## 1. Connection to the database
> ##
> ch <- odbcConnect("PostgreSQL30");
>
> ##
> ##  2. Data Storage. I wrote 10000 bytes in the TXT field of the 
> tabsignal table
> ##
> ##      RQ : Thanks to the "paste" function, I convert bytes data in 
> characters for the storage in the TXT field
> ##               and I put one space between every character (cf. 
> collapse argument) in order to use
> ##               the strsplit function for the extraction.
> ##
> ##      bytes is the result of the readBin function
> ##      bytes <- readBin(con, "raw", n=fileSize)
> ##
> sqlQuery(ch, paste("INSERT INTO tabsignal VALUES 
> ('TEST',1,'M1',1,'",paste(bytes[1:10000],collapse=" "),"')",sep=""))
>
> ##
> ## 3. Data Extraction
> ##
> ##        The problem is here :  sqlQuery command doesn't give the 
> same amount of bytes
> ##
> bytes.out <- sqlQuery(ch, paste("SELECT data FROM tabsignal WHERE 
> id_evenement=1"),stringsAsFactors=FALSE)
> bytes.out <- unlist(strsplit(bytes.out[1,1],split=" +"))
> bytes.out <- as.raw(bytes.out)
>
> length(bytes.out) ## bytes.out is shorter than bytes !!! although data 
> is well-stored in the database.
>
> ## 4. End of the connection
> close(ch)
>
> Thank you very much for any idea.
>
> ______________________________________________
> R-help at r-project.org mailing list
> https://stat.ethz.ch/mailman/listinfo/r-help
> PLEASE do read the posting guide 
> http://www.R-project.org/posting-guide.html
> and provide commented, minimal, self-contained, reproducible code.
>
I am sorry for asking again my question, but I found some help after 
having read the "ODBC connectivity" document by Brian Ripley.
In the data types section, we can read (p. 9) :

"There is a sanity check which will allow
only strings of up to 65535 bytes when reading: this can be removed
by recompiling RODBC."

I think that it is the solution to my problem because when I read the 
database with a JAVA code using JDBC, it works fine. But I would like to 
use RODBC.
If you think that I have to recompile RODBC, can somebody give me some 
advice, or a link for explanations because I never did it (I work with 
windows XP).

Thank you