R help - Jan 2009 - run query using rexcel runrfile and rodbc sql query

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?

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