Loading data into R with rsqlserver package

r, rsqlserver, sql-server

Solution

I am agstudy the creator of `rsqlserver` package. Sorry for the late but I finally I get some time to fix this bug. ( actually it was a not yet implemented feature). I demonstrate here how you can read/write data.frame with missing values in Sql server.

First I create a data.frame with missing values. It is important to distinguish the difference between numeric and character variables.

library(rsqlserver)
url = "Server=localhost;Database=TEST_RSQLSERVER;Trusted_Connection=True;"
conn <- dbConnect('SqlServer',url=url)
## create a table with some missing value
dat <- data.frame(txt=c('a',NA,'b',NA),
                  value =c(1L,NA,NA,2))

My input looks like this :

# txt value
# 1    a     1
# 2 <NA>    NA
# 3    b    NA
# 4 <NA>     2

I insert dat in my data base with the handy function `dbWriteTable`: dbWriteTable(conn,name='T_TABLE_WITH_MISSINGS', dat,row.names=FALSE,overwrite=TRUE) Then I will read it using 2 methods:

dbSendQuery

res = dbSendQuery(conn,'SELECT * 
                  FROM T_TABLE_WITH_MISSINGS')
fetch(res,n=-1)
dbDisconnect(conn)
   txt value
1    a     1
2 <NA>   NaN
3    b   NaN
4 <NA>     2

dbReadTable:

rsqlserver is DBI compliant and implement many convenient functions to deal at least at possible with SQL.

conn <- dbConnect('SqlServer',url=url)
dbReadTable(conn,name='T_TABLE_WITH_MISSINGS')
dbDisconnect(conn)
   txt value
1    a     1
2 <NA>   NaN
3    b   NaN
4 <NA>     2

Problem

I've just installed `rsqlserver` like so (no errors) ``` install_github('rsqlserver', 'agstudy',args = '--no-multiarch') ``` And created a connection to my database: ``` > library(rClr) > library(rsqlserver) Warning message: multiple methods tables found for ‘dbCallProc’ > drv <- dbDriver("SqlServer") > conn <- dbConnect(drv, url = "Server=MyServer;Database=MyDB;Trusted_Connection=True;") > ``` Now when I try to get data using `dbGetQuery`, I get this error: ``` > df <- dbGetQuery(conn, "select top 100 * from public2013.dim_Date") Error in clrCall(sqlDataHelper, "GetConnectionProperty", conn, prop) : Type: System.MissingMethodException Message: Method not found: 'System.Object System.Reflection.PropertyInfo.GetValue(System.Object)'. Method: System.Object GetConnectionProperty(System.Data.SqlClient.SqlConnection, System.String) Stack trace: at rsqlserver.net.SqlDataHelper.GetConnectionProperty(SqlConnection _conn, String prop) > ``` When I try to fetch results using `dbSendQuery`, I also get an error. ``` > res <- dbSendQuery(conn, "select top 100 * from public2013.dim_Date") > df <- fetch(res, n = -1) Error in clrCall(sqlDataHelper, "Fetch", stride) : Type: System.InvalidCastException Message: Object cannot be stored in an array of this type. Method: Void InternalSetValue(Void*, System.Object) Stack trace: at System.Array.InternalSetValue(Void* target, Object value) at System.Array.SetValue(Object value, Int32 index) at rsqlserver.net.SqlDataHelper.Fetch(Int32 capacity) in c:\projects\R\rsqlserver\src\rsqlserver.net\src\SqlDataHelper.cs:line 116 ``` Strangely, the file `c:\projects\R\rsqlserver\src\rsqlserver.net\src\SqlDataHelper.cs` doesn't actually exist on my computer. Am I doing something wrong?

Original source