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.
