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

[Laurent Pantera]

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.