[R] Workaround for RODBC asymmetric numeric data treatment
Rui Barradas
ruipbarradas at sapo.pt
Fri Oct 3 21:18:54 CEST 2014
Hello,
Inline
Em 03-10-2014 19:04, Bos, Roger escreveu:
> Andrew,
>
> I ran your code using my SQL Server database and it seems like it worked okay for me, in that I end up with "num" data types when I read the data back in. So it may be a setting on your database. I don't claim to know which one.
>
> BTW, I had to install 5 or 6 separate packages to get fPortfolio to load. Anyone know why install.packages("fPortfolio",repos="http://R-Forge.R-project.org") can't install all the dependencies automatically?
Try
install.packages(..., dependencies = TRUE)
Hope this helps,
Rui Barradas
>
> Thanks,
>
> Roger
>
>
>> library(RODBC)
>
>> library(fPortfolio)
> Loading required package: timeSeries
>
> Attaching package: ‘timeSeries’
>
> The following object is masked from ‘package:zoo’:
>
> time<-
>
> Loading required package: fBasics
>
>
> Rmetrics Package fBasics
> Analysing Markets and calculating Basic Statistics
> Copyright (C) 2005-2014 Rmetrics Association Zurich
> Educational Software for Financial Engineering and Computational Science
> Rmetrics is free software and comes with ABSOLUTELY NO WARRANTY.
> https://www.rmetrics.org --- Mail to: info at rmetrics.org
>
> Attaching package: ‘fBasics’
>
> The following object is masked from ‘package:TTR’:
>
> volatility
>
> Loading required package: fAssets
> Error in loadNamespace(j <- i[[1L]], c(lib.loc, .libPaths()), versionCheck = vI[[j]]) :
> there is no package called ‘DEoptimR’
> Error: package ‘fAssets’ could not be loaded
>> source(.trPaths[5], echo=TRUE, max.deparse.length=150)
>
>> library(RODBC)
>
>> library(fPortfolio)
> Loading required package: fAssets
> Error in loadNamespace(i, c(lib.loc, .libPaths()), versionCheck = vI[[i]]) :
> there is no package called ‘sn’
> Error: package ‘fAssets’ could not be loaded
>> library(RODBC)
>> library(fPortfolio)
> Loading required package: fAssets
> Error in loadNamespace(i, c(lib.loc, .libPaths()), versionCheck = vI[[i]]) :
> there is no package called ‘sn’
> Error: package ‘fAssets’ could not be loaded
>> library(timeSeries)
>> head(SWX.RET$SBI)
> Error in head(SWX.RET$SBI) :
> error in evaluating the argument 'x' in selecting a method for function 'head': Error: object 'SWX.RET' not found
>> library(fPortfolio)
> Loading required package: fAssets
> Error in loadNamespace(i, c(lib.loc, .libPaths()), versionCheck = vI[[i]]) :
> there is no package called ‘sn’
> Error: package ‘fAssets’ could not be loaded
>> library(fPortfolio)
> Loading required package: fAssets
>
>
> Rmetrics Package fAssets
> Analysing and Modeling Financial Assets
> Copyright (C) 2005-2014 Rmetrics Association Zurich
> Educational Software for Financial Engineering and Computational Science
> Rmetrics is free software and comes with ABSOLUTELY NO WARRANTY.
> https://www.rmetrics.org --- Mail to: info at rmetrics.org
> Error in loadNamespace(i, c(lib.loc, .libPaths()), versionCheck = vI[[i]]) :
> there is no package called ‘slam’
> Error: package or namespace load failed for ‘fPortfolio’
>> library(fPortfolio)
> Package Rsolnp (1.14) loaded. To cite, see citation("Rsolnp")
>
> Error in loadNamespace(i, c(lib.loc, .libPaths()), versionCheck = vI[[i]]) :
> there is no package called ‘kernlab’
> Error: package or namespace load failed for ‘fPortfolio’
>> library(fPortfolio)
> Error in loadNamespace(i, c(lib.loc, .libPaths()), versionCheck = vI[[i]]) :
> there is no package called ‘rneos’
> Error: package or namespace load failed for ‘fPortfolio’
>> library(fPortfolio)
>
>
> Rmetrics Package fPortfolio
> Portfolio Optimization
> Copyright (C) 2005-2014 Rmetrics Association Zurich
> Educational Software for Financial Engineering and Computational Science
> Rmetrics is free software and comes with ABSOLUTELY NO WARRANTY.
> https://www.rmetrics.org --- Mail to: info at rmetrics.org
>> head(SWX.RET$SBI)
> [1] -0.0020881194 -0.0001045205 -0.0013597617 0.0004185852 0.0000000000 -0.0010467917
>> str(SWX.RET$SBI)
> num [1:1916] -0.002088 -0.000105 -0.00136 0.000419 0 ...
>> source(.trPaths[5], echo=TRUE, max.deparse.length=150)
>
>> mydata<-as.timeSeries(SWX.RET)
>
>> head(mydata)
> GMT
> SBI SPI SII LP25 LP40 LP60
> 2000-01-04 -0.0020881194 -0.034390059 0.00001367381 -0.011994298 -0.018013035 -0.026155259
> 2000-01-05 -0.0001045205 -0.010408271 -0.00495530624 -0.003657054 -0.005837489 -0.009011403
> 2000-01-06 -0.0013597617 0.012119128 0.00381289851 -0.001323897 -0.001644737 -0.002395959
> 2000-01-07 0.0004185852 0.022461656 -0.00061621046 0.007714991 0.011660151 0.017062613
> 2000-01-10 0.0000000000 0.002107677 0.00238057889 0.003029081 0.004565523 0.006948020
> 2000-01-11 -0.0010467917 -0.002773654 -0.00029384531 -0.002422531 -0.003142903 -0.004183466
>> source(.trPaths[5], echo=TRUE, max.deparse.length=150)
>
>> df2beSavedByRODBC =as.data.frame(mydata)
>
>> str(df2beSavedByRODBC)
> 'data.frame': 1916 obs. of 6 variables:
> $ SBI : num -0.002088 -0.000105 -0.00136 0.000419 0 ...
> $ SPI : num -0.03439 -0.01041 0.01212 0.02246 0.00211 ...
> $ SII : num 0.0000137 -0.0049553 0.0038129 -0.0006162 0.0023806 ...
> $ LP25: num -0.01199 -0.00366 -0.00132 0.00771 0.00303 ...
> $ LP40: num -0.01801 -0.00584 -0.00164 0.01166 0.00457 ...
> $ LP60: num -0.02616 -0.00901 -0.0024 0.01706 0.00695 ...
>> sqlSave(xf, dat=df2beSavedByRODBC,tablename="testTable",rownames=TRUE,append=FALSE,addPK=FALSE,verbose=FALSE)
>> sqlString = "select * from testTable"
>> dataFrameFromDB = sqlQuery(xf, sqlString,errors=TRUE);
>> str(dataFrameFromDB)
> 'data.frame': 1916 obs. of 7 variables:
> $ rownames: chr "2000-01-04" "2000-01-05" "2000-01-06" "2000-01-07" ...
> $ SBI : num -0.002088 -0.000105 -0.00136 0.000419 0 ...
> $ SPI : num -0.03439 -0.01041 0.01212 0.02246 0.00211 ...
> $ SII : num 0.0000137 -0.0049553 0.0038129 -0.0006162 0.0023806 ...
> $ LP25 : num -0.01199 -0.00366 -0.00132 0.00771 0.00303 ...
> $ LP40 : num -0.01801 -0.00584 -0.00164 0.01166 0.00457 ...
> $ LP60 : num -0.02616 -0.00901 -0.0024 0.01706 0.00695 ...
>
>
>
>
> ***************************************************************
> This message and any attachments are for the named person's use only.
> This message may contain confidential, proprietary or legally privileged
> information. No right to confidential or privileged treatment
> of this message is waived or lost by an error in transmission.
> If you have received this message in error, please immediately
> notify the sender by e-mail, delete the message, any attachments and all
> copies from your system and destroy any hard copies. You must
> not, directly or indirectly, use, disclose, distribute,
> print or copy any part of this message or any attachments if you are not
> the intended recipient.
>
>
> -----Original Message-----
> From: r-help-bounces at r-project.org [mailto:r-help-bounces at r-project.org] On Behalf Of Andrew
> Sent: Friday, October 03, 2014 1:40 PM
> To: r-help at r-project.org
> Subject: [R] Workaround for RODBC asymmetric numeric data treatment
>
> Note: I did raise report the issue below to r-sig-db at r-project.org, but didn't see any reply.
> I'm hoping somebody on r-help can help me devise a workaround for a problem I'm having with RODB:
>
>
> I use RODBC to read and write a good deal of data to SQL Server and I'd be extremely grateful
> if anyone has found a workaround in order to be able to write dataframes to SQL Server
> using RODBC dynamically created SQL tables and read the data from those tables, or indeed any
> arbitrary SQL Server table with "float" datatypes and end up with numeric columns instead of "factor" columns
> in a dataframe in R.
>
>
> I have found that when RODBC creates a Microsoft SQL Server data table from a dataFrame using sqlSave(....append=FALSE),
> RODBC uses the SQL "float" datatype to store R numeric data in a dynamically-created table on the server.
>
> However, when RODBC reads any SQL Server "float" datatype from SQL Server via sqlQuery it interprets float columns as "factor" data.
>
>
> I created a standalone sample below to demonstrate the odd behavior of RODBC that I hope to overcome:
>
> # Assuming the reader has access to SQL Server the code below is self-contained and repeatable
>
> # I believe it demonstrates unexpected and undesirable behavior in RODBC
>
>
> library(RODBC)
> library(fPortfolio)
> library(timeSeries)
> head(SWX.RET$SBI)
> str(SWX.RET$SBI)
> mydata<-as.timeSeries(SWX.RET)
> head(mydata)
>
> df2beSavedByRODBC =as.data.frame(mydata)
>
> str(df2beSavedByRODBC)
>
> # shows the numeric data in the dataframe
> #
> # data.frame': 1916 obs. of 6 variables:
> # $ SBI : num -0.002088 -0.000105 -0.00136 0.000419 0 ...
> # $ SPI : num -0.03439 -0.01041 0.01212 0.02246 0.00211 ...
> ...
>
>
> # Let's save the dataframe to SQL Server:
>
> dbconn<-odbcDriverConnect(connection="Driver={SQL Server};server=_YOURSERVERNAMEHER_;database=_YOURDBNAME_;Trusted_Connection=True;");
> sqlSave(channel=dbconn,dat=df2beSavedByRODBC,tablename="testTable",rownames=TRUE,append=FALSE,addPK=FALSE,verbose=FALSE)
>
> # The sqlSave above works very well. The new table is create in the Microsoft SQL database and the ddl for the table is:
> #
> # [dbo].[testTable](
> # [rownames] [varchar](255) NULL,
> # [SBI] [float] NULL,
> # [SPI] [float] NULL,
> # [SII] [float] NULL,
> # [LP25] [float] NULL,
> # [LP40] [float] NULL,
> # [LP60] [float] NULL
> # )
>
>
> # The numeric values from the dataframe are stored as float (i.e. numeric) in SQL server -- good!
>
> ## now let's read back the data RODBC stored in SQL server from a SQL table RODBC created:
>
>
> sqlString = "select * from testTable"
>
>
> dataFrameFromDB = sqlQuery(dbconn,sqlString,errors=TRUE);
>
> str(dataFrameFromDB)
>
> #
> # 'data.frame': 1916 obs. of 7 variables:
> # $ rownames: Factor w/ 1916 levels "2000-01-04","2000-01-05",..: 1 2 3 4 5 6 7 8 9 10 ...
> # $ SBI : Factor w/ 1742 levels "-0.00041080415489958",..: 349 42 161 1418 828 48 49 1419 1024 135 ...
> # $ SPI : Factor w/ 1848 levels "-0.0020169904194276",..: 445 48 970 883 1187 377 1157 1065 951 1840 ...
> ...
>
> #********* RODBC wrote numeric data to SQL Server as float, but read the same data back as Factor ! ********
>
>
> I could use some help to create a robust and flexible workaround for RODBC's asymmetric treatment of numeric data.
> If there were some way to force RODBC sqlQuery to interpret all SQL Server float datatypes as numeric my problem would be solved.
> FWIW: RODBC does interpret the SQL Server "real" datatype as numeric.
>
>
> Thank you,
>
> Andrew
>
> [[alternative HTML version deleted]]
>
> ______________________________________________
> 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.
> ______________________________________________
> 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.
>
More information about the R-help
mailing list