This project is read-only.

Trouble creating a database connection with RODBC library

Mar 21, 2012 at 12:28 AM

Hello all,

Thanks to the developer for this project!

I'm building an web-based application in ASP.NET.  I'm hoping to use R.NET to access the r library to create the Holt Winters model.  I'm able to get much of my original r script parsed by the R engine, but it throws an exception when I try to create a database connection.  (The original script passes a query to the database, grabs the results as a data frame, converts to a time series object, and creates a model.)

Can anyone tell why the line that creates used the odbcDriverConnect method fails?

Can you suggest a work around that will let me extract data from the database and turn it into a time series object in r?

Thanks in advance for any help! My code is below.

 protected void btnNewSimulation_Click(object sender, EventArgs e)
        {
             REngine engine = RSetup.GetREngine();

            engine.EagerEvaluate("library(RODBC)");
            engine.EagerEvaluate("library(forecast)");
            engine.EagerEvaluate("cn <- odbcDriverConnect('driver={SQL Server};server=THINKCENTRE1\\SQLDEV;database=CAI;trusted_connection=true')");
            engine.EagerEvaluate("x <- c(1:38)"); //test data
            engine.EagerEvaluate("y<-ts(x,start=2010,freq=12)"); //create ts
            engine.EagerEvaluate("fit.hw<-HoltWinters(y)"); // create model
            engine.EagerEvaluate("OUTPUT_AGG_FCST_HIRE<-data.frame(forecast(fit.hw))"); // create forecast

        }
Mar 21, 2012 at 1:08 AM

I'll answer my own question--it turned out to be a minor omission.  I needed an "@" in the line that was parsing the connection string.  Below is the working version of the code.  It pulls data from a database, creates a model, then a forecast, and stores the forecast in the database.



REngine engine = RSetup.GetREngine();

            engine.EagerEvaluate("library(RODBC)");
            engine.EagerEvaluate("library(forecast)");
            engine.EagerEvaluate(@"cn <- odbcDriverConnect('driver={SQL Server};server=THINKCENTRE1\\SQLDEV;database=CAI;trusted_connection=true')");
            engine.EagerEvaluate("sql<-\"SELECT HIRE_MONTH, SUM(HIRE_COUNT) FROM dbo.AGG_TS_MONTHLY WHERE HIRE_MONTH BETWEEN '2010-01-01' AND '2011-12-31' GROUP BY HIRE_MONTH\"");
            engine.EagerEvaluate("hire.total <- sqlQuery(cn,sql)");
            engine.EagerEvaluate("hire<-ts(hire.total[,2],start=2010,freq=12)");
            engine.EagerEvaluate("fit.hw<-HoltWinters(hire)"); // create model
            engine.EagerEvaluate("OUTPUT_AGG_FCST_HIRE<-data.frame(forecast(fit.hw))"); // create forecast
            engine.EagerEvaluate("sqlSave(cn, OUTPUT_AGG_FCST_HIRE, append = TRUE, safer = FALSE, fast = TRUE)"); //store it in the database
Jun 19, 2012 at 5:08 PM

Which library uses RSetup?

my set up doesn't have access to it:

using System;

using System.IO;

using System.Linq;

using System.Windows.Forms;

using Microsoft.Win32;

using RDotNet;

 

Visual Studio 10.0, .NET 4.0, R 2.15

Jul 11, 2012 at 8:48 PM
I can't find RSetup anywhere, and I am learning how to use R.net in asp.net to do a simple regression. Can you provide a bit of light on the steps? a simple example can explains a lot.
Thanks.

rcram15 wrote:

I'll answer my own question--it turned out to be a minor omission.  I needed an "@" in the line that was parsing the connection string.  Below is the working version of the code.  It pulls data from a database, creates a model, then a forecast, and stores the forecast in the database.



REngine engine = RSetup.GetREngine();

            engine.EagerEvaluate("library(RODBC)");
            engine.EagerEvaluate("library(forecast)");
            engine.EagerEvaluate(@"cn <- odbcDriverConnect('driver={SQL Server};server=THINKCENTRE1\\SQLDEV;database=CAI;trusted_connection=true')");
            engine.EagerEvaluate("sql<-\"SELECT HIRE_MONTH, SUM(HIRE_COUNT) FROM dbo.AGG_TS_MONTHLY WHERE HIRE_MONTH BETWEEN '2010-01-01' AND '2011-12-31' GROUP BY HIRE_MONTH\"");
            engine.EagerEvaluate("hire.total <- sqlQuery(cn,sql)");
            engine.EagerEvaluate("hire<-ts(hire.total[,2],start=2010,freq=12)");
            engine.EagerEvaluate("fit.hw<-HoltWinters(hire)"); // create model
            engine.EagerEvaluate("OUTPUT_AGG_FCST_HIRE<-data.frame(forecast(fit.hw))"); // create forecast
            engine.EagerEvaluate("sqlSave(cn, OUTPUT_AGG_FCST_HIRE, append = TRUE, safer = FALSE, fast = TRUE)"); //store it in the database