[R] run query using rexcel runrfile and rodbc sql query

[Erich Neuwirth]

1. Please subscribe to the rcom mailing list,
and pose your question there.
RExcel questions should not be posed on r-help.

2. You have a trivial typo in your code,
you define inSet and then you use inSET.
When code does not run in RExcel, please test the same code in R.
You code will not run in R.



BrianCBolt wrote:
> Hi,
> I'm using R-2.8.1 for windows.  I wrote a script calls the RODBC package
> to run sql queries against an oracle database.  It runs fine when run
> from the rconsole and returns a data.frame with a summary for a set of
> experiments.  I want to run this script using the rinterface.RunRFile
> command in an excel plugin, using the rexcel package.   When I run the
> code from my excel plugin I get an error: "Error -2147220502 in Module
> RExcel.RServer   Error running expression
> eval(parse(text="source(.rexcel..hzpouai)"))"
> 
> 
> Here is my excel plugin code:
>     rinterface.StartRServer
>     rinterface.RRun "setwd(""C:/Documents and Settings/bbolt/Desktop"")"
>     ChDir "C:/Documents and Settings/bbolt/Desktop"
>     rinterface.RunRFile "Script.r"
> 
> It works fine if Script.r is something simple like:
> Text <- data.frame(Hello="Hello")
> 
> But will give me the error when Script.R is something like:
> inSet <- "TEST0000853"
> library("RODBC")
> query <- function(qu){
>     con <-
> odbcConnect("Development",uid="username",pwd="password",believeNRows=FALSE,rows_at_time=50)
> 
>     sqlQuery(con,qu)
>     }
> qu <- paste("m.parentid as SET from setmap m where m.childid in (select
> t.experiment from  experimentsets t where  t.setname='",inSET,"')",sep="")
> ExperimentSet <- as.character(query(qu)[,1])
> 
> The error only occurs when I call my "query" function.  Does anyone know
> if there is a way to run sql queries in this manner?
> 
> ______________________________________________
> 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.
> 
> 
> ------------------------------------------------------------------------
> 
> 
> No virus found in this incoming message.
> Checked by AVG - http://www.avg.com 
> Version: 8.0.176 / Virus Database: 270.10.15/1924 - Release Date: 1/29/2009 5:57 PM
> 

-- 
Erich Neuwirth, University of Vienna
Faculty of Computer Science
Computer Supported Didactics Working Group
Visit our SunSITE at http://sunsite.univie.ac.at
Phone: +43-1-4277-39464 Fax: +43-1-4277-39459