2

When I run the insert statement with odbc driver everythings fine.

drv <- odbc::odbc()
conn <- createConn(drv, trusted_connection = T, dsn="mydsn", uid="myuid", pwd="mypwd")
DBI::dbSendQuery("INSERT INTO \"dbo\".\"testjdbc\" (d) values('4')")

When I run the select statement with jdbc everything is fine too: 

   drv <- RJDBC::JDBC(driverClass = "com.microsoft.sqlserver.jdbc.SQLServerDriver", classPath = "C:\\mssql-jdbc-7.0.0.jre8.jar") 
    conn <- DBI::dbConnect(drv, trusted_connection = T, url = "jdbc:sqlserver://myserver\\\\myinstance:1111;databaseName=mydatabasename", user="myuid", password="mypwd") 
    DBI::dbGetQuery(conn, "Select * from dbo.mytable")

and the connection for jdbc is valid: 

drv <- RJDBC::JDBC(driverClass = "com.microsoft.sqlserver.jdbc.SQLServerDriver", classPath = "C:\\mssql-jdbc-7.0.0.jre8.jar") 
        conn <- DBI::dbConnect(drv, trusted_connection = T, url = "jdbc:sqlserver://myserver\\\\myinstance:1111;databaseName=mydatabasename", user="myuid", password="mypwd") 
    DBI::dbIsValid(conn) # TRUE

But when I try insert statement (the same like the first one) with jdbc driver like this:

drv <- RJDBC::JDBC(driverClass = "com.microsoft.sqlserver.jdbc.SQLServerDriver", classPath = "C:\\mssql-jdbc-7.0.0.jre8.jar") 
conn <- DBI::dbConnect(drv, trusted_connection = T, url = "jdbc:sqlserver://myserver\\\\myinstance:1111;databaseName=mydatabasename", user="myuid", password="mypwd") 
DBI::dbSendQuery(conn, "INSERT INTO \"dbo\".\"testjdbc\" (d) values('4')")

then I get the error:

Error in .verify.JDBC.result(r, "Unable to retrieve JDBC result set for ",  : 
  Unable to retrieve JDBC result set for INSERT INTO "dbo"."testjdbc" (d) values('4') (The statement did not return a result set.)

So jdbc select is OK but inserts, updates, deletes gives errors while with odbc I can do everything.

stakowerflol
  • 979
  • 1
  • 11
  • 20
  • 2
    An insert statement is not a query (it does not produce a result set). Use a method to execute non-queries. I don't know RJDBC, so I can't help you identifying the right method: check its API documentation. – Mark Rotteveel Oct 31 '18 at 10:38
  • but with the odbc driver everything works fine... – stakowerflol Oct 31 '18 at 10:51
  • 2
    It all depends on what happens below the surface, and JDBC has a strict separation between executing result-set producing statements and other statements. Given the exception RJDBC calls `executeQuery` on a JDBC statement, which is not allowed for statements that don't produce result sets. Maybe it is different for ODBC. – Mark Rotteveel Oct 31 '18 at 10:53

1 Answers1

1

The solution is to run inserts not with DBI::dbSendQuery but with RJDBC::dbSendUpdate.

Thank you @Mark Rotteveel for your answer. Thanks to you I have found the solution.

Mark Rotteveel
  • 100,966
  • 191
  • 140
  • 197
stakowerflol
  • 979
  • 1
  • 11
  • 20