I'm working in an R script that uses a long SQL string, and I would like to keep the query relatively free of other markup so as to allow copying and pasting between editors and applications. I'd also like the ability to split the query across lines for better readability. In the RODBC documentation, the paste function is used to build...
I'd like to save a plot image directly to the database. Is the best way in R to do this: Write the plot image (png) to the filesystem Read the file that was written Send the file to the database via query (RODBC) Ideally I'd like to combine steps 1 and 2 above by simply write the png image to a binary connection. Does R support thi...
I am creating some graphs which I want to update into a database table. The procedure I am following is: create the graphs as a png/jpeg file. Read that file as a binary vector sqlUpdate My code for steps 2 & 3: pngfile <- file(<filename>, "rb") N <- 1e6 repeat{ pngfilecontents <- readBin(pngfile, what="raw", n=N) if(l...
I'm writing an R script to get some database data and then do stuff with it, using the RODBC package. Currently all my sqlQuery commands are one long string; stsample<-sqlQuery(odcon, paste"select * from bob.DESIGNSAMPLE T1, bob.DESIGNSUBJECTGROUP T2, bob.DESIGNEVENT T3, bob.CONFIGSAMPLETYPES T4 WHERE T1.SUBJECTGROUPID = T2.SUBJECTGROU...
I've a question about using sqlSave. How does R map RODBC data in the data frame to the database table columns? If I've a table with columns X and Y and a data frame with columns X and Y, RODBC puts X into X and Y into Y (I found out by trail-and-error). But can I explicitly tell R how to map data.frame columns to database table columns...
I am working on saving twitter search results into a database (SQL Server) and am getting an error when I pull the search results from twitteR. If I execute: library(twitteR) puppy <- as.data.frame(searchTwitter("puppy", session=getCurlHandle(),num=100)) I get an error of: Error in as.data.frame.default(x[[i]], optional = TRUE) : ...
I'm trying to create a procedure which extracts data from a MySQL server (using the RODBC package), performs some statistical routines on that data in R, then saves generated plots back to the server such that they can be retrieved in a Web Browser via a little bit of php and web magic. My plan is to save the plot in a MySQL BLOB field ...
I'm trying to understand how RODBC determines the column types of a newly created (Access) table? The R documentation of sqlSave is very cryptic: "types are selected by consulting arguments varTypes and typeInfo". And no examples for this arguments. Where can I find a better explanation? ...
How can I make sqlSave to write only a subset of columns. I've a (MS-Access) table with e.g. columns A, B, C en D and if I send a sqlSave command with a data frame with columns A, B en D (no C) I get the message: Error in odbcUpdate(channel, query, mydata, coldata[m, ], test = test, : missing columns in 'data' Calls: sqlSave -> sqlw...
I'm using R to read in some data from a MySQL database using the RODBC package. The data is then processed and some results are sent back to the database. The problem is that the server closes the connection after about a minute due to inactivity, which is the time needed to process the data locally. Its a shared server, so the host wo...
RODBC error in Revolution R 64bit on winxp64 bit connected to Oracle using a 64bit ODBC driver thru a DSN library(RODBC) db <- odbcConnect("oraclemiso",uid="epicedf",pwd="…") rslts = sqlQuery(db, "select count(*) from FTRAuction") Error in .Call(C_RODBCFetchRows, attr(channel, "handle_ptr"), max, buffsize, : negative length vectors...
Installing the RODBC package on Ubuntu is a bit of a kludge. First I learned to install the following: $ sudo apt-get install r-cran-rodbc That wasn't good enough as the package was still looking for header files. I solved this issue by: $ sudo apt-get install unixodbc-dev Good, RODBC installed properly on the Ubuntu machine. But ...
A question about this R code: library(RODBC) ch <- tryCatch(odbcConnect("RTEST"), warning=function(w){print("FAIL! (warning)");return(NA)}, error=function(e){print(paste("ERROR:",geterrmessage()));return(NA)}) df <- tryCatch(sqlQuery(ch,"SELECT Test from tblTest"), warning=function(w){print("FAIL! (warning)");return(NA)}, erro...
We keep all our laboratory data in a Sybase database. When I want to do data manipulation and analysis I read the data into R with RODBC. library(RODBC) channellab <- odbcConnect("Labdata") indivs <-sqlQuery(channellab,'SELECT * from CGS.Specimen') So far so good, except that CGS.Specimen is a table for our entire lab holdings. There ...