[R] How to save a model in DB and retrieve It

[Daniele Amberti]

I'm wondering how to save an object (models like lm, loess, etc) in a DB to retrieve and use it afterwards, an example:

wind_ms <- abs(rnorm(24*30)*4+8)
air_kgm3 <- rnorm(24*30, 0.1)*0.1 + 1.1
wind_dg <- rnorm(24*30) * 360/7
ms <- c(0:25)
kw_mm92 <- c(0,0,0,20,94,205,391,645,979,1375,1795,2000,2040)
kw_mm92 <- c(kw_mm92, rep(2050, length(ms)-length(kw_mm92)))
modelspline <- splinefun(ms, kw_mm92)
kw <- abs(modelspline(wind_ms) - (wind_dg)*2 + (air_kgm3 - 1.15)*300 + rnorm(length(wind_ms))*10)
#plot(wind_ms, kw)
windDat <- data.frame(kw, wind_ms, air_kgm3, wind_dg)
windDat[windDat$wind_ms < 3, 'kw'] <- 0
model <- loess(kw ~ wind_ms + air_kgm3 + wind_dg, data = windDat, enp.target = 10*5*3) #, span = 0.1)

modX <- serialize(model, connection = NULL, ascii = T)

Channel <- odbcConnect("someSysDSN; UID=aUid; PWD=aPwd")
sqlQuery(Channel,
paste(
"INSERT INTO GRT.GeneratorsModels
           ([cGeneratorID]
           ,[tModel]
   VALUES
           (1,",
           paste("'", gsub("'", "''", rawToChar(modX)), "'", sep = ''),
           ")", sep = "") )
# Up to this it is working correctly,
# in DB I have the "modX" variable
# Problem arise retrieving data and 64kb limit:
  strQ <- "
    SELECT  CONVERT(varchar(max), tModel) AS tModel
    FROM    GRT.GeneratorsModels
    WHERE   (cGeneratorID = 1)
    "
x <- sqlQuery(Channel, strQ, stringsAsFactors = F, believeNRows = FALSE)
x <- sqlQuery(Channel, strQ, stringsAsFactors = F, believeNRows = FALSE) #read error



Above code is working for simplier models that have a shorter representation in variable "modX".
Any advice on how to store and retieve this kind of objects?
Thanks
Daniele


ORS Srl

Via Agostino Morando 1/3 12060 Roddi (Cn) - Italy
Tel. +39 0173 620211
Fax. +39 0173 620299 / +39 0173 433111
Web Site www.ors.it

------------------------------------------------------------------------------------------------------------------------
Qualsiasi utilizzo non autorizzato del presente messaggio e dei suoi allegati è vietato e potrebbe costituire reato.
Se lei avesse ricevuto erroneamente questo messaggio, Le saremmo grati se provvedesse alla distruzione dello stesso
e degli eventuali allegati.
Opinioni, conclusioni o altre informazioni riportate nella e-mail, che non siano relative alle attività e/o
alla missione aziendale di O.R.S. Srl si intendono non  attribuibili alla società stessa, né la impegnano in alcun modo.