I have a synonym statement:
CREATE OR REPLACE SYNONYM sample-table-name FOR master.sample-table-name
I want to execute this by using R language using RJDBC library. I've tried different functions including dbGetQuery function etc. But it is not able to execute this statement.
Could someone please advise on how to execute these statements in R.
I would expect that using dbExecute() should work. Failing that, dbSendStatement() should almost certainly work, as it seems to be intended for that sort of DDL.
The docs on dbGetQuery say:
This method is for SELECT queries only
To look at the functions that are available to you, it can be helpful to do something like ls('package:DBI'). Per the docs:
dbExecute:
Executes a statement and returns the number of rows affected. dbExecute() comes with a default implementation (which should work with most backends) that calls dbSendStatement(), then dbGetRowsAffected(), ensuring that the result is always free-d by dbClearResult().
dbSendStatement:
The dbSendStatement() method only submits and synchronously executes the SQL data manipulation statement (e.g., UPDATE, DELETE, INSERT INTO, DROP TABLE, ...) to the database engine. To query the number of affected rows, call dbGetRowsAffected() on the returned result object. You must also call dbClearResult() after that. For interactive use, you should almost always prefer dbExecute().
EDIT: Those methods will likely work for other implementations of DBI, and perhaps for RJDBC in the future. In any case, it looks like they have implemented dbSendStatement() using dbSendQuery(), so they will not work for this purpose.
In the RJDBC framework, dbSendUpdate() is what you want.
dbSendQuery and dbSendUpdate submit a SQL query to the database. The difference between the two is only that dbSendUpdate is used with DBML queries and thus doesn't return any result set.
Confirmed using a similar query on another DBMS:
dbSendStatement(conn,'SET search_path=public;')
#Error in .verify.JDBC.result(r, "Unable to retrieve JDBC result set for ", :
#Unable to retrieve JDBC result set for SET search_path=public; (No results were returned by the query.)
traceback()
#7: stop(..., " (", .jcall(x, "S", "getMessage"), ")")
#6: .verify.JDBC.result(r, "Unable to retrieve JDBC result set for ",
# statement)
#5: .local(conn, statement, ...)
#4: dbSendQuery(conn, statement, ...)
#3: dbSendQuery(conn, statement, ...) ## this is the problem
#2: dbSendStatement(conn, "SET search_path=public;")
#1: dbSendStatement(conn, "SET search_path=public;")
dbSendUpdate(conn, 'SET search_path=public;')
# successful
dbCommit(conn)
# persist the change past the current session (RJDBC seems to give you transaction control here)
