dbSendQuery expects result set, returns error although statement succeeds

[hongooi73]

It looks like dbSendQuery is expecting a result set, although per the DBI docs, it isn't supposed to. While the SQL executes on the database correctly, the function returns an error.

Here's a minimal example where I create a schema and a table, insert values into it, and then read the table back into R.

con <- dbConnect(SQLServer(), ....)
dbSendQuery(con, "create schema sc")
dbSendQuery(con, "create table sc.table1 (
    n int,
    x real
)")
dbSendQuery(con, "insert into sc.table1 values
    (1, 2.5),
    (2, 0.0),
    (3, 3.14159)
")
dbReadTable(con, "sc.table1")

All the dbSendQuery calls return errors like

Error in .verify.JDBC.result(r, "Unable to retrieve JDBC result set for ", :
Unable to retrieve JDBC result set for create table sc.table1 (
n int,
x real
) (The executeQuery method must return a result set.)

but the final dbReadTable shows that the table was created anyway:

  n       x
1 1 2.50000
2 2 0.00000
3 3 3.14159

[imanuelcostigan]

dbSendQuery is basically a copy of the RJDBC S4 method for JDBCConnection which returns a JDBCResult object.

Which DBI doc are you referring to?

It could be that this error is coming from a print or show method that gets called when printing the return value of dbSendQuery. What happens when you assign the dbSendQuery to a variable?

[hongooi73]

What I mean is that dbSendQuery isn't supposed to want a result set, if I understand correctly. This is from ?dbSendQuery:

The function dbSendQuery only submits and synchronously executes the SQL statement to the database engine. It does not extracts any records — for that you need to use the function dbFetch, and then you must call dbClearResult when you finish fetching the records you need.

Assigning the return value from dbSendQuery doesn't produce anything, which is as expected since the function errors out.

[imanuelcostigan]

Take a look at the "Value" section to ?dbSendQuery which documents the return value as follows:

An object that inherits from DBIResult. If the statement generates output (e.g., a SELECT statement) the result set can be used with fetch to extract records.

Obligatory sessionInfo():

R version 3.2.0 (2015-04-16)
Platform: x86_64-w64-mingw32/x64 (64-bit)
Running under: Windows 7 x64 (build 7601) Service Pack 1

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

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

other attached packages:
[1] DBI_0.3.1      devtools_1.8.0

loaded via a namespace (and not attached):
[1] rversions_1.0.1 tools_3.2.0     curl_0.8        Rcpp_0.11.6     memoise_0.2.1   xml2_0.1.1      git2r_0.10.1    digest_0.6.8   

[hongooi73]

Yep, but none of the dbSendQuery calls in my example should generate output. Do you get the same errors as me?

[imanuelcostigan]

If I'm not mistaken (not a SQL guru), none of those are queries, so shouldn't be using dbSendQuery:

The most common operation in SQL is the query, which is performed with the declarative SELECT statement. SELECT retrieves data from one or more tables, or expressions. Standard SELECT statements have no persistent effects on the database.

If you are looking to create a table, you could use dbWriteTable().

[hongooi73]

No, it's not a query as such, but there are SQL statements that do more than query tables and return a set of rows. For example, CREATE SCHEMA to create a schema, or CREATE INDEX to index a table. In general, you'd use dbSendQuery in such cases to send the statement to the database without needing to retrieve a result set. As you can see though, this is throwing up a bunch of errors for me.

It's possible I've misconfigured the database on my end (it's a local instance on my laptop). Do you get the same errors?

[imanuelcostigan]

Ok, so looked at how RSQLite handles these sort of queries (ignoring schemas which it doesn't support) and it returns SQLiteResult objects.

Running your statements on my server results in same error. I'll take a look.

PS - Can I ask how you connected to a local instance of SQL Server? I'd like to be able to do that.

[imanuelcostigan]

Looks like dbSendQuery as implemented by RJDBC is not meant for non SELECT statements (or more precisely, statements that do not generate a ResultSet).

Indeed, RJDBC has implemented a pretty bespoke method for creating tables which makes use of the non-DBI method dbSendUpdate.

I don't want to be hacking away at the RJDBC layer any more than is necessary. So for now, the error message that is returned when send CREATE TABLE statements via dbSendQuery is the "right" behaviour. Should you wish to create a table, would recommend doing so via dbWriteTable.

[hongooi73]

Hi Imanuel,

I just downloaded SQL Server Enterprise Edition from MSDN and installed it locally. I’m not a database guru either so it took a bit longer than your usual Windows software install, but everything seems to be working fine.

If you don’t have an MSDN subscription, you can get SQL Server Express which is a free download: http://www.microsoft.com/en-au/server-cloud/products/sql-server-editions/sql-server-express.aspx

No probs with leaving the RJDBC layer alone for now. The statements get executed on the backend anyway, so I can just tell R to ignore the errors.

From: Imanuel Costigan [mailto:notifications@github.com]
Sent: Thursday, 18 June 2015 2:15 PM
To: imanuelcostigan/RSQLServer
Cc: Hong Ooi
Subject: Re: [RSQLServer] dbSendQuery expects result set, returns error although statement succeeds (#26)

Ok, so looked at how RSQLite handles these sort of queries (ignoring schemas which it doesn't support) and it returns SQLiteResult objects.

Running your statements on my server results in same error. I'll take a look.

PS - Can I ask how you connected to a local instance of SQL Server? I'd like to be able to do that.

—
Reply to this email directly or view it on GitHubhttps://github.com//issues/26#issuecomment-113029327.