Loading data into R with rsqlserver package

Question

I just installed rsqlserver(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 get 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

Strange, the file c:\projects\R\rsqlserver\src\rsqlserver.net\src\SqlDataHelper.csdoes not actually exist on my computer.

Am I doing something wrong?

Answer 1

rsqlserver. , , , , . ( ). , / data.frame Sql.

data.frame . .

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))

:

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

dat dbWriteTable:   dbWriteTable (Conn, name= 'T_TABLE_WITH_MISSINGS',                DAT, row.names = FALSE, = TRUE), , 2 :

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 DBI , , 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

Answer 2

(EDIT: - ( ). .)

: NULL ? .

, :

DB, , http://www.codeproject.com/Tips/326527/Create-a-Sample-SQL-Database-in-Less-Than-2-Minute

EDIT: :

library(rClr)
library(rsqlserver)
drv <- dbDriver("SqlServer")
conn <- dbConnect(drv, url = "Server=Localhost\\somename;Database=Fabrics;Trusted_Connection=True;")
res <- dbSendQuery(conn, "SELECT TOP 100 * FROM [Fabrics].[dbo].[Client]")
str(res)
## Formal class 'SqlServerResult' [package "rsqlserver"] with 1 slots
  ..@ Id:<externalptr> 
> 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

, , , .

> dbExistsTable(conn, name='Client')
Error in sqlServerExecScalar(conn, statement, ...) : 
  Message: There is already an open DataReader associated with this Command which must be closed first.
> dbClearResult(res)
[1] TRUE
> dbExistsTable(conn, name='Client')
[1] TRUE
> dbExistsTable(conn, name='SomeIncorrectColumn')
[1] FALSE

, MissingMethodException

df <- dbGetQuery(conn, "SELECT TOP 100 * FROM [Fabrics].[dbo].[Client]")
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

, . SqlDataHelper.Fetch

_resultSet[_cnames[i]].SetValue(_reader.GetValue(i), cnt);

:

i   11  int
_cnames[i]  "Street2"   string
_reader.GetValue(i) {}  object {System.DBNull}
_reader.GetValue(i-1)   "806 West Sir Francis Drake St" object {string}
_reader.GetValue(i+1)   "Spokane"   object {string}

Street2 NULL:

ClientId    FirstName   MiddleName  LastName    Gender  DateOfBirth CreditRating    XCode   OccupationId    TelephoneNumber Street1 Street2 City    ZipCode Longitude   Latitude    Notes
1   Nicholas    Pat Kane    M   1975-10-07 00:00:00.000 3   ZU8 5ML 4   (279) 459 - 2707    2870 North Cherry Blvd. NULL    Carlsbad    64906   32.7608137325835    117.112738329071    

, sessionInfo() :

R version 3.0.2 (2013-09-25)
Platform: x86_64-w64-mingw32/x64 (64-bit)
other attached packages:
[1] rsqlserver_1.0 rClr_0.5-2    
loaded via a namespace (and not attached):
[1] DBI_0.2-7   tools_3.0.2

, .