[R] RODBC and RDCOM
guox at ucalgary.ca
guox at ucalgary.ca
Thu Oct 23 19:22:21 CEST 2008
I got a table on SQL Server, which has columns EquationId (int) and
Formula (varch(900)).
I need to select Formula with certain EquationId, for example,
Select Formula from OGA_DEV.dbo.Equation where EquationId = 3
I tried to work within RGui using RODBC:
> library(RODBC)
> conn = odbcConnect("SQL Server")
> odbcQuery(conn, "Select Formula from OGA_DEV.dbo.Equation where
EquationId = 3")
[1] 1
> results = sqlGetResults(conn)
> close(conn)
> results$Formula
[1]
((-0.19*({EthanePropaneMolFracTotal}*100)^2)+(12.3*({EthanePropaneMolFracTotal}*100))-13)*0.8
Levels:
((-0.19*({EthanePropaneMolFracTotal}*100)^2)+(12.3*({EthanePropaneMolFracTotal}*100))-13)*0.8
> typeof(results$Formula)
[1] "integer"
> typeof(results$Formula[1])
[1] "integer"
Formula was there. Notice that results$Formula has Levels. To get the
formula, I tried
> formulaLevels = levels(results$Formula)
> typeof(formulaLevels)
[1] "character"
> formulaLevels[1]
[1]
"((-0.19*({EthanePropaneMolFracTotal}*100)^2)+(12.3*({EthanePropaneMolFracTotal}*100))-13)*0.8"
These works well within RGui. Then I tried to use RDCOM to communicate
with .net:
StatConnector curve = new STATCONNECTORSRVLib.StatConnectorClass();
curve.Init("R");
curve.EvaluateNoReturn("library(RODBC)");
curve.EvaluateNoReturn("conn = odbcConnect("+@"""" + "SQL Server" +@""""
+")");
curve.EvaluateNoReturn(odbcSqlQuery);//Select Formula from
OGA_DEV.dbo.Equation where EquationId = 3
curve.EvaluateNoReturn("results = sqlGetResults(conn)");
curve.EvaluateNoReturn("close(conn)");
curve.EvaluateNoReturn("formulaLevel =
levels(results$Formula)");//Exception throwed here
curve.Evaluate("formula = formulaLevel[1]");
string[] equationStrings = (string[])curve.GetSymbol("formula");
It throwed exception: SafeArrayTypeMismatchException
at formulaLevel = levels(results$Formula)
Any comments/suggestions where were wrong with the abobe code and how to
get formula from
SQL Server table to .net? Thanks.
-james
More information about the R-help
mailing list