martinkabe/rsqls

Package for interactive work with SQL Server

rsqls's Issues

Add source code for benchmark

I really appreciate new DB access solutions and benchmarks like yours but I would like see the source code for the benchmark too to play around and reproduce the results.

For example, your are mentioning using DBI::dbWriteTable but DBI is just an interface and the performance depends on DBI driver you are using (e. g. dbConnect(odbc::odbc()... or RODBCDBI::ODBC())...

Disconnect Function?

First off, thank you for creating this R package. I've been looking for an alternative of inserting data into SQLServer instead having to constantly loop through 999 chunk sizes of data because I do not have BulkAdmin privileges.
I hate keeping connections open while not in use and R stays open, so I looked through the documentation and didn't see anything for a disconnect function. Is there any command to delete the connection session (disconnect the session)?
Thanks!

Add .Net source code to build csv_to_sql_loader.exe

This R packages has a GPL license but is using a non-open binary-only exe file which is not only a security issue but also an unwanted "feature" in (free) open source software.

Even though it is your decision to make the source code for the exe file open source too I would appreciate this step...

This would also allow to fix errors and find weaknesses...

Thank!

Package for AutoDetectCsvSeparator missing?

I'm trying to build the csv loader .exe locally to test a modification. I'm getting the following output:

S:\Forks\RSQLS\inst\Loader>csc -define:DEBUG -optimize -out:csv_to_sql_loader2.exe *.cs
Microsoft (R) Visual C# Compiler version 2.7.0.62707 (75dfc9b3)
Copyright (C) Microsoft Corporation. All rights reserved.

Functions.cs(370,38): error CS0103: The name 'AutoDetectCsvSeparator' does not exist in the current context

Googling tells me that AutoDetectCsvSeparator might come from a GemBox package named ExcelFile?

Only SELECT statement is allowed

Just started getting the following error: "Only SELECT statement is allowed".

*Note: Running the same queries in SMSS work fine.

Query:
SELECT DISTINCT CAST(LTRIM(RTRIM(b.Cust_ID)) AS nvarchar(MAX)) AS Cust_ID, ISNULL(CAST(s.Transact_Num AS int), 0) AS Transact_Num, CAST(LTRIM(RTRIM(e.Cust_Email)) AS nvarchar(MAX)) AS Cust_Email FROM Cust_Static b INNER JOIN Cust_Dynamic e ON (e.Cust_ID = b.Cust_ID) INNER JOIN Transactions s ON (s.Cust_ID = b.Cust_ID) WHERE (s.tKey >= 201801 AND s.tKey <= 201804) AND b.Cust_Cat = '5';

Data Pull:
ds.data <- as.data.frame(dpull_data(connURL.x, ds.qry, showprogress = TRUE))
*Also tried with :
ds.data<- as.data.frame(pull_data(connURL.x, ds.qry, showprogress = TRUE))

---

Also tried placing in global temp table:
Query:
SELECT * CAST(LTRIM(RTRIM(jf.Cust_ID)) AS nvarchar(MAX)) AS Cust_ID, ISNULL(CAST(jf.Transact_Num AS int), 0) AS Transact_Num, CAST(LTRIM(RTRIM(jf.Cust_Email)) AS nvarchar(MAX)) AS Cust_Email FROM ##jf_tmp jf;

Data Pull:
ds.data <- as.data.frame(dpull_data(connURL.x, ds.qry, showprogress = TRUE))
*Also tried with :
ds.data<- as.data.frame(pull_data(connURL.x, ds.qry, showprogress = TRUE))

---

This could possibly be due to a \n being inserted somewhere in the backend when submitting the query and the server is expecting a query in that new line.

Error: There is no row at position 25?

A lot of times, I do not understand what certain error mean during the push_data():

push_data(cs, Table,"[db].[Table]", append = FALSE, showprogress = TRUE)
[1] "Data Frame has 27600 rows and 25 columns."

Pushing data into [db].[Table] table with showing progress
Pipe is used as separator.
Cleaning table [db].[Table]
Table [db].[Table] has been cleaned
There is no row at position 25.
This operation took
Minutes: 0
Seconds: 0
Milliseconds: 815.38

The table has 25 columns. I can't figure out what this error means. Please help.

To CRAN?

Do you plan to submit this to CRAN? Looks like a useful package for us, but we'd rather only depend on CRAN packages.

supress messages from push_data

Is there a way to suppress the output messages from push_data (i.e. pushing data into xxx without showing progress...)? I have the function as part of a loop and the messages are filling up the screen. I have tried using capture.output and sink but so far I have not been able to hide the messages.

Timeout Expiration

Anyway to customize the login timeout?

I've been getting this error quite frequently:
"SqlException message: Connection Timeout Expired. The timeout period elapsed while attempting to consume the pre-login handshake acknowledgement. This could be because the pre-login handshake failed or the server was unable to respond back in time. The duration spent while attempting to connect to this server was - [Pre-Login] initialization=17365; handshake=9391;"

I can usually re-run the command and the issue is resolved. Typically with other R SQL packages there is an option to customize this parameter.

Thanks!

send_SQL_task

Issue template
Would send_SQL_task be the optimal way to run a query and have results inserted into a table and/or temp table?

i.e.:
SELECT *
INTO AIRPLANECARS
FROM AIRPLANES

SELECT *
INTO ##AIRPLANECARS
FROM AIRPLANES

error: The system cannot find the path specified.

After installing the library and call the function got error like following:

> library(RSQLS)
> cs <- set_connString("SERVER", "DBNAME")
> str(cs)
 chr "\"Data Source=SERVER;Initial Catalog=DBNAME;Integrated Security=True;MultipleActiveResultSets=True;\""
> foo <- get_DB_info(cs)
The system cannot find the path specified.
Error in get_DB_info(cs) : See the previous messages for more details.

R environment:

> sessionInfo()
R version 3.5.0 (2018-04-23)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows >= 8 x64 (build 9200)

Matrix products: default

locale:
[1] LC_COLLATE=English_United States.1252  LC_CTYPE=English_United States.1252    LC_MONETARY=English_United States.1252
[4] LC_NUMERIC=C                           LC_TIME=English_United States.1252    

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

other attached packages:
[1] RSQLS_1.9

loaded via a namespace (and not attached):
[1] compiler_3.5.0    tools_3.5.0       data.table_1.11.2

push_data() does not work with non-default SQL schemas

I was testing RSQLS package in one of my projects and I think the push_data() function is having problems writing into SQL tables that do not start with 'dbo' but go under other schema. I want to write into a table that is called [mod].[2.Demand]. [mod] is the schema and [2.Demand] is the name of the table. I get the following error from the push_data():

Call:
push_data(cs, dataframe,"[mod].[2.Demand]", append = FALSE, showprogress = TRUE)

Error:
Pushing data into mod.2.Demand table with showing progress
CSV file has different count of columns than table mod.2.Demand!!!
Data Frame has 24 columns and table on SQL Server has 0 columns!
Tip: Try also check ' ' somewhere in the text in your DataFrame or DataTable you are trying to push to SQL Server, because tabulator is used as a separator!

This is happening even though the get_DB_info() function clearly shows that the table is there and has in fact 24 columns.

The function has no problems with pushing data onto SQL tables that are a part of [dbo] schema.

how to set the encoding?

Issue template
there are some messy codes when pull data from local sqlserver? eg. chinese character.

CRLFs in data

I've tried to use this with columns containing xml data that contained carriage returns and line feeds, when doing so the data into R gets corrupted on the line breaks. I am working around this currently using replace(...) statements in my tsql.

Cannot find data type bool

When creating a new table with push_data, data frame bool column is not created and the statement fails.

Table ITM_SD_SLA_Details doesn't exit in database. Creating it...
Column, parameter, or variable #3: Cannot find data type bool.
[1] "Data Frame has 1 rows and 5 columns."