Search code examples
roraclerjdbc

RJDBC: R to Oracle cannot DELETE or DROP TABLE

I'm using RJDBC to connect to a local database. This allows me to make SELECT queries easily using dbGetQuery, and CREATE TABLE using dbWriteTable.

However, I cannot figure out a method to DROP TABLE or DELETE or SELECT INTO directly from my R console. These things work when I do it directly in SQL Developer, but not when I pass the query onto the database from R.

How do I perform database record manipulations which are not SELECT statements using R?

Solution

  • I'd try using a different type instead. dbGetQuery bases itself on finding and iterating over the DB rather than manipulating it's records. Similar questions were asked before; I couldn't find a nice R example, but if it helps, A nice java example could be found here:

    EDIT:

    I found the type I was talking about! Took me a while, anyhow - sqlQuery allows you to run pretty much any query, that is - a change in the DB records. Example I modified from this source:

    res <- sqlQuery(con1,"DELETE TABLE TESTDATA", errors=FALSE) 
# res will now hold the result of the query.
# -1 means error, otherwise iteration is sucessful, and it will hold the number of rows affected.
if (res == -1){ #if something messed up
 cat ("An error has occurred.\n")
 msg <- odbcGetErrMsg(con1) #Use your connection for this.
 print (msg)
} else {
  cat ("Table was deleted successfully.\n")
}

    EDIT 2:

    I got it confused with RODBC, however there's no reason to worry, since I found the RJDBC alternative as well! It's called, dbSendUpdate. Example:

    # Assuming you have the connection saved as conn; these example shows how to use dbSendUpdate to create tables and insert values.
# You could use it with every non-selective query, that is, which manipulates the record (update,delete,insert,drop etc.)
# create table, with dbSendUpdate:
dbSendUpdate(conn, "CREATE TABLE foo(a INT,b VARCHAR(100))")
# insert value, bind parameters to placeholders in statement:
dbSendUpdate(conn, "INSERT INTO foo VALUES(?,?)", 42, "bar")
# feel free to modify the query itself, these are just example values.