Hi,

Reproduction information can be found at http://stackoverflow.com/a/21456750/2752565

I'll fork and may see if I can figure out a fix, but I am not very knowledgeable with DBs, so don't count on it.

Nice package; I have some things to learn from it. Keep up the good work,

Cheers

One big reason to mix sql server and R is that sql server can be used to store, normalise and query larger amounts of data. I am trying to download several million rows of 80 column data from a single table into R for processing. Unfortunately, even with a pretty fast ssd based array this always times out after the first few hundred thousand rows. Does a proxy for DataContext.CommandTimeout exist in RSqlServer? If not could it be added?

Imports a data file into a database table or view in a user-specified forma using BULK INSERT

a signature can be

writeFile(con,fiel.path,table_name,...)

Hi! Do you have any idea whether this will work on MacOS? You mentioned a possible linux version using mono in the readme, has this been done?

Otherwise, any advice connecting R to SQL Server on a mac?

Update benchamarking to include:

• the cran package RSQLServer.
• odbc package

Connection strings such as

• server\instance
• (localdb)\v11.0
• domain\username

are all valid for SQL Server however R requires that \ are escaped and .NET does not.

eg.

dbConnect("SqlServer", url = "Server=(local)\\SQL2014;...")

is a valid connection string in R.

This breaks when connections are passed as raw strings to .NET through SqlBulkWrite and SqlBulkCopy instead of connection objects.

Possible fix is to simply strip escaped backslashes prior to passing the connection string to .NET in dbBulkCopy and dbBulkWrite.

I see that the Travis-CI builds are failing because the .travis.yml file is missing, and I'm guessing you removed it because Travis wasn't accommodating the rClr installation is missing?

If that's in the ballpark, consider if Wercker is a better fit than Travis. I know very little beyond the basics of Docker, but I'm hoping that you could build a container that has rClr already in it, and therefore test you package.
http://www.jimhester.com/2015/04/28/wercker/

In your docker doc you mention a docker containers:

• ruaridhw/rsqlserver

Please add a documentation for this docker container at hub.docker.com e. g. by linking to your documentation at github.

I'm working on Ubuntu 12.04 with Rstudio 0.98.501 running R 3.0.2 with devtools and rclr installed with no errors.

I then try to install rsqlserver with the following command and error:

install_github('rsqlserver', 'agstudy',args='--no-multiarch')
Installing github repo(s) rsqlserver/master from agstudy
Installing rsqlserver.zip from https://github.com/agstudy/rsqlserver/archive/master.zip
Installing rsqlserver
'/usr/lib/R/bin/R' --vanilla CMD INSTALL '/tmp/Rtmp97xXAX/rsqlserver-master' --library='/home/killick/R/x86_64-pc-linux-gnu-library/3.0'
--with-keep.source --no-multiarch

• installing source package 'rsqlserver' ...
  ** R
  Error in .install_package_code_files(".", instdir) :
  files in 'Collate' field missing from '/tmp/Rtmp97xXAX/rsqlserver-master/R':
  DBIMetaInfo.R
  ERROR: unable to collate and parse R files for package 'rsqlserver'
• removing '/home/killick/R/x86_64-pc-linux-gnu-library/3.0/rsqlserver'
  Error: Command failed (1)

It looks at though that is a file called "DBIMetaInfo.R" missing from the R directory. I check in the repository and can't see it there either. Any ideas?

Is it possible to use the user Windows credentials to log on to the server? By specifiyng the connection string directly it should be possible theoretically

My data table is recorded in SQL Server 2008 database and it contains only the types listed below:

• INT
• DECIMAL(10,5)
• VARCHAR(150)

However, when trying to run the script below an error occurs with the type of data (numerical). I solved the problem inefficiently turning all the table fields in char type, which does not allow the best performance rsqlserver package (see about the performance, according to the test here for the numerical data rsqlserver package is much faster, https://github.com/agstudy/rsqlserver/wiki/benchmarking).

Although I have researched a lot about, I found nothing about how to resolve this problem, do not even exist comments for the issue #22, and I believe it is a similar problem to mine.

So I would like to correct this error and gain in performance.

My code:

Pacakge

library(rsqlserver)     

Driver

drv  <- dbDriver("SqlServer")   

My connection

conn <- dbConnect(drv, 
                  host     = "my_ip",
                  dbname   = "mydb", 
                  user     = "my_user", 
                  password = "my_pass") 

Data reading attempt

df <- dbGetQuery(conn, "select * FROM my_table")

Error message

Error in (function ()  : 
  clr_obj_ms_convert_to_SEXP: COM variant type code 14 unsupported. Returning NULL
Error in clrCall(res.Dict, "get_Item", Cnames[i]) : 
  Type:    RDotNet.EvaluationException
Message: Error in (function ()  : 
  clr_obj_ms_convert_to_SEXP: COM variant type code 14 unsupported. Returning NULL

Method:  RDotNet.ProtectedPointer evaluateCall(IntPtr)
Stack trace:
   at RDotNet.Function.evaluateCall(IntPtr call)
   at RDotNet.Function.createCallAndEvaluate(IntPtr argument)
   at RDotNet.Function.InvokeOrderedArguments(SymbolicExpression[] args)
   at Rclr.RDotNetDataConverter.ConvertObject(Object obj) in c:\Users\per202\AppData\Local\Temp\Rtmp8UJrfL\R.INSTALL23944e8d7aab\rClr\src\ClrFacade\RDotNetDataConverter.cs:line 558
   at Rclr.RDotNetDataConverter.ConvertToSexp(Object obj) in c:\Users\per202\AppData\Local\Temp\Rtmp8UJrfL\R.INSTALL23944e8d7aab\rClr\src\ClrFacade\RDotNetDataConverter.cs:line 368
   at Rclr.RDotNetDataConverter.ConvertAll(Object[] objects, Func`2 converter) in c:\Users\per202\AppData\Local\Temp\Rtmp8UJrfL\R.INSTALL23944e8d7aab\rClr\src\ClrFacade\RDotNetDataConverter.cs:line 387
   at Rclr.RDotNetD 

I've tried to set "encoding/Encoding/DBMSencoding" = "GBK" in url/dbConnect()/dbSendQuery()/fetch() but failed. Is there any parameters to specify character encoding?

-- code --

library("rsqlserver")
url = "Server=192.168.1.101;user=sa;password=**;Database=mydata;Trusted_Connection=True"
conn <- dbConnect("SqlServer", url = url)
a = dbSendQuery(conn, "SELECT TOP 5 *  FROM [mydata].[dbo].[data]")
fetch(a)

dbListFields returns character(0) for temporary table input.

Build a Docker image that can be used to run the package on macOS, Linux and Windows in a self-contained environment.

Relates to #24 #16 #13

• Build Docker image that inherits from rocker/r-ver + devtools + rClr
• Write setup script docker.sh for easy installation and use of images
• Add RStudio build instructions to docker.sh
• Retune docker.sh using compose to avoid manually starting multiple containers

@agstudy, I've tried reproducing your example of NULL type handling and found two regressions:

1. After the dbWriteTable call, the uploaded table contains NULLs in the value column but not in the txt column of the database's table which instead contain blank cells. This can be verified by inspecting the table using means other than rsqlserver.
2. Using the dbSendQuery and dbReadTable functions, the results are that the txt column returns NAs and the value column returns 0s

Essentially the problems here are that

1. DB Integer nulls are converted to 0s in R
2. DB text blanks are converted to NAs in R
3. R text NAs are converted to blanks in DB

Are you able to reproduce this error on Windows using the current master branch or is this just a mono-specific error?

Transaction scope Makes a code block transactional. Very useful when we execute many commands.

See for example this SO question

I have a table ("test") with a field numeric(8,2) or decimal(8,2) without any NULL data.

-( using windows 8; r-3.2 x64; rClr 07.4; .net framework 4.5; SQL Server 2014)

(

library("rClr")
Loading the dynamic library for Microsoft .NET runtime...
Loaded Common Language Runtime version 4.0.30319.34209
)

With sql server 2014 this is what I get

dd <- dbSendQuery(kk,"select c001 from test")
ww <- fetch(dd,2)
Error in (function () :
clr_obj_ms_convert_to_SEXP: COM variant type code 14 unsupported. Returning NULL
Error in clrCall(res.Dict, "get_Item", Cnames[i]) :
Type: RDotNet.EvaluationException
Message: Error in (function () :
clr_obj_ms_convert_to_SEXP: COM variant type code 14 unsupported. Returning NULL

Method: RDotNet.ProtectedPointer evaluateCall(IntPtr)
Stack trace:
en RDotNet.Function.evaluateCall(IntPtr call)
en RDotNet.Function.createCallAndEvaluate(IntPtr argument)
en RDotNet.Function.InvokeOrderedArguments(SymbolicExpression[] args)
en Rclr.RDotNetDataConverter.ConvertObject(Object obj) en c:\Users\per202\AppData\Local\Temp\RtmpuMBMYA\R.INSTALL2164365a3840\rClr\src\ClrFacade\RDotNetDataConverter.cs:línea 559
en Rclr.RDotNetDataConverter.ConvertToSexp(Object obj) en c:\Users\per202\AppData\Local\Temp\RtmpuMBMYA\R.INSTALL2164365a3840\rClr\src\ClrFacade\RDotNetDataConverter.cs:línea 369
en Rclr.RDotNetDataConverter.ConvertAll(Object[] objects, Func`2 converter) en c:\Users\per202\AppData\Local\Temp\RtmpuMBMYA\R.INSTALL2164365a3840\rClr\src\ClrFacade\RDotNetDataConverter.cs:línea 388
en Rclr.RDot

I was wrong about that, I sorted through the columns in my query and the error I am getting happens when I have a column of bigint or bit data types.

Now I get this error:

Error in clrCall(res.Dict, "get_Item", Cnames[i]) : 
  clr_obj_ms_convert_to_SEXP: COM variant type code 8212 unsupported. Returning NULL

Here is the script:

drv <- dbDriver("SqlServer")
conn <- dbConnect(drv, url = url")
query <- "SELECT [name]
      ,[species]
      ,[sample_buffer]
      ,[volume_ul]
      ,[comment]
      ,[object_id]
      ,[tissue_type]
      ,[gender]
      ,[od_value]
      ,[concentration]
      ,[extraction_method]
      ,[ethnicity]
      ,[date_received]
  FROM [Sample]"

df1 <- dbGetQuery(conn, query)

Here is a link to a csv version of the DB: https://www.dropbox.com/s/7pdo8ojpbe119kc/testDB.csv
But that probably won't help reproduce since it will be in a different format...
Its very likely I just don't know what I'm doing.

Hi, just find your package and personally I think it's very useful and interesting.

After reading your introduction, I realized that you've used .Net framework to read and write, and provide a function to use BCP to write table.

So, I'm just wondering why not just use BCP to both read and write at all and remove the sqlClient's part.

I have to admit that I'm not very familiar with the sqlServer stuff in detail, however, I've built two small function in my working environment through BCP to both read and write. For example about Query, it will first use BCP to write a csv file in my temp folder, then it will use data.table::freadto read the csv file as fast as possible. In usually circumstances it seems work ok and very fast, except that if there's too many text the csv file might be failed to read (like containing the seperator in the data).

So, I just want to figure out the reason why not use BCP for everything.

Thanks.

Any future plans to support linux?

If no, maybe some recommendation for high performance MSSQL connection from R on linux?
doesn't have to be DBI compliant.
Regards

I am trying to test a minor update to rsqlserver locally, but am unsure what files I need to replace.

I compiled the project creating a new version of rsqlserver.net in rsqlserver-master\src.nocompile\rsqlserver.net\bin\Release and then I copied this to C:\Program Files\Microsoft\R Open\R-3.4.2\library\rsqlserver\libs

I am still getting an error message referring to C:\Users\Ruaridh Williamson\Documents\GitHub\rsqlserver\src.nocompile\rsqlserver.net\src\SqlDataHelper.cs,
so I suspect that I need to replace some other file.

Do you have an idea what files I need to replace to release an update locally?

Thank you

If any of the data loaded is of the datatype tinyint and is null an error is thrown. TinyInt's themselves work fine.

BTW: I want to say, keep a the good work. RSqlServer is an fantastic addition to R. I come from a C# and Sql Server background, but because of the nature of my work R is now becoming an integral tool. And RSqlServer is already essential to everything I do.

I also have the following error:

Error in clrCall(sqlDataHelper, "Fetch", stride) : 
  Type:    System.ArgumentException
Message: Cannot widen from source type to target type either because the source type is a not a primitive type or the conversion cannot be accomplished.
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:\Users\Ruaridh Williamson\Documents\GitHub\rsqlserver\src.nocompile\rsqlserver.net\src\SqlDataHelper.cs:line 171

Coming from fields of int data-type. but are NULL.

SqlBulkCopy will only allow for uploading files to a destination table comprised of [n]varchar columns only.

The default CSVReader column type is string and if this does not match the table type then SQLBulkCopy throws an error.

CSV column data types need to be set prior to Bulk Copy such that they match the SQL table data types. (See here)

Ideally this should be done dynamically by determining the SQL table types (SELECT COLUMN_NAME, DATA_TYPE FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'destTable') and then converting to DataReader types

I tried several hours and failed to figure out the reason that I can't use your package. The dbGetInfo(conn)$State is 1, but I just can't execute any SQL. As long as I tried to execute any sql, the error occurs:

Error in sqlServerExecStatement(conn, statement, ...) : NA

my code

library(DBI)
library(rsqlserver)
url <- "Server=xx.xx.xx.xx;Database=abc;User Id=abc;Password=abc;"
conn <- dbConnect("SqlServer", url = url)

dbListFields(conn, "tablename")
Error in sqlServerExecStatement(conn, statement, ...) : NA

I can successfully do this dbGetInfo(conn)

$ClientConnectionId
[1] "85666cab-6210-4bc8-9cd3-e0530127941c"

$ConnectionString
[1] "Server=xxx.xxx.xxx.xx;Database=abc;User Id=abc;"

$ConnectionTimeout
[1] "15"

$Database
[1] "abc"

$DataSource
[1] "xx.xxx.xxx.xx"

$FireInfoMessageEventOnUserErrors
[1] "FALSE"

$PacketSize
[1] "8000"

$ServerVersion
[1] "12.00.2000"

$State
[1] "1"

$StatisticsEnabled
[1] "FALSE"

$WorkstationId
[1] "abcdefg"

If I tried to print the connection, it will show:

<SqlServerConnection>
Error in (function (classes, fdef, mtable)  : 
  unable to find an inherited method for function ‘dbIsValid’ for signature ‘"SqlServerConnection"’

my session info

R version 3.2.1 (2015-06-18)
Platform: i386-w64-mingw32/i386 (32-bit)
Running under: Windows 7 x64 (build 7601) Service Pack 1

locale:
[1] LC_COLLATE=Chinese (Simplified)_People's Republic of China.936 
[2] LC_CTYPE=Chinese (Simplified)_People's Republic of China.936   
[3] LC_MONETARY=Chinese (Simplified)_People's Republic of China.936
[4] LC_NUMERIC=C                                                   
[5] LC_TIME=Chinese (Simplified)_People's Republic of China.936    

attached base packages:
[1] stats     graphics  grDevices utils     datasets  methods  
[7] base     

other attached packages:
[1] rsqlserver_1.0 rClr_0.7-2    

loaded via a namespace (and not attached):
 [1] Rcpp_0.11.6     digest_0.6.8    R6_2.1.0        DBI_0.3.1.9008 
 [5] git2r_0.10.1    magrittr_1.5    httr_1.0.0      stringi_0.5-2  
 [9] curl_0.9        xml2_0.1.1      devtools_1.8.0  tools_3.2.1    
[13] stringr_1.0.0   rversions_1.0.1 memoise_0.2.1   knitr_1.10.5  

Can you please help me figure it out? thanks.

I am living in France, which I believe the author also does, so he may also encounter the same problem. When downloading Date fields (I haven't tried DateTime), the dates get converted to 11:00pm on the previous day. This can easily be resolved by x$date <- as.Date(x$date, tz=Sys.timezone()), but is an issue

Saw PR #18 which helped solve my first issue however as mentioned by @wibeasley those quotes are used as text qualifiers for a field which contains commas. Regardless of whether quote=T or F the data still gets uploaded with the comma in that field being interpreted as a separator.

I've tried playing around with sep = " " however since write.csv is called this gets ignored. Is there any way to fix this by passing a certain argument or forcing write.table to be called instead?

PS Amazing package! Thank you so much for this.

Edit Realised this is a known issue with bcp and bulk insert in sql server. Could any of the suggested solutions end up working with rsqlserver? As a workaround I can edit the fields that contain commas but it would be preferable to change the delimiter rather than tamper with the data

I am assuming that it is trying to convert the fetched fields into another data-type, rather than the data type per the database. In the code below, paidAmt is of data type decimal(19,2).

R Code:

selectQry <- "SELECT TOP 10 claimType, transType, paidAmt FROM tmpRx_01"
res <- dbSendQuery(conn, selectQry)
dsResults <- dbFetch(res)
dbClearResult(res)
dbDisconnect(conn)

Error:

Error in (function ()  : 
  clr_obj_ms_convert_to_SEXP: COM variant type code 14 unsupported. Returning NULL
Error in clrCall(res.Dict, "get_Item", Cnames[i]) : 
  Type:    RDotNet.EvaluationException
Message: Error in (function ()  : 
  clr_obj_ms_convert_to_SEXP: COM variant type code 14 unsupported. Returning NULL

Method:  RDotNet.ProtectedPointer evaluateCall(IntPtr)
Stack trace:
   at RDotNet.Function.evaluateCall(IntPtr call)
   at RDotNet.Function.createCallAndEvaluate(IntPtr argument)
   at Rclr.RDotNetDataConverter.CreateClrObj(Object obj) in c:\Users\per202\AppData\Local\Temp\RtmpuMBMYA\R.INSTALL2164365a3840\rClr\src\ClrFacade\RDotNetDataConverter.cs:line 603
   at Rclr.RDotNetDataConverter.TryConvertToSexp(Object obj) in c:\Users\per202\AppData\Local\Temp\RtmpuMBMYA\R.INSTALL2164365a3840\rClr\src\ClrFacade\RDotNetDataConverter.cs:line 320
   at Rclr.RDotNetDataConverter.ConvertToSexp(Object obj) in c:\Users\per202\AppData\Local\Temp\RtmpuMBMYA\R.INSTALL2164365a3840\rClr\src\ClrFacade\RDotNetDataConverter.cs:line 369
   at Rclr.RDotNetDataConverter.ConvertAll(Object[] objects, Func`2 converter) in c:\Users\per202\AppData\Loca

when i use the function:dbWriteTable to writetable into sqlserver,there is something wrong with it:
1.write the data less than 999 rows,it's ok,not the error :Error in textConnection(message) : invalid 'text' argument
dbWriteTable(conn, 'SIX', data[1001:1999,], row.names = F, append = T)

2.when the data >=1000 rows, the error like this
dbWriteTable(conn, 'SIX', data[1:1000,], row.names = F, append = T)

can you help me? what's wrong with it? thks.

Is there a way to have the bulk copy mechanism interpret the quotes surrounding character fields, and not upload them to the database. (For my current situation, I could just unquote the temp csv, but I'm hoping there's a more general solution in case the quotes are necessary to handle commas inside the actual values.)

In other words, instead of the default bulk copy resulting in something like this

it would look like this:

I see that clrCallStatic("rsqlserver.net.misc","SqlBulkCopy",con.string ,value,name) is called underneath, and you've mentioned the bcp utility in other posts, but I didn't understand how they fit together. Is this a simple configuration change?

Hi , am facing problem in inserting data to sqlserver from R using the dbBulkcopy function .

url = "Server=a4ms00134;Database=Demog_Model;Trusted_Connection=False;user id=_;password=_;"
conn <- dbConnect('SqlServer',url=url)
these lines doesnt throw any error but when I run the below code
dbBulkCopy(conn, "NEW_BP_TABLE", value = id.file)

i get the following error -
Error in clrCallStatic("rsqlserver.net.misc", "SqlBulkCopy", con.string, :
Type: System.Data.SqlClient.SqlException
Message: Login failed for user '******'.
Method: Void OnError(System.Data.SqlClient.SqlException, Boolean, System.Action1[System.Action]) Stack trace: at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose)
at System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK)
at System.Data.SqlClien

Please help me solve this issue.

Thanks
Akil