Re: [R] RODBC : reading binary data from a TXT field belonging to a PostgeSQL table
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 1 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:1],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@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 __ R-help@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.
[R] RODBC : reading binary data from a TXT field belonging to a PostgeSQL table
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 1 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:1],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@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.
[R] RODBC : reading binary data from a TXT field belonging to a PostgeSQL table
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 1 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:1],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@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.