3

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?

Richard Neish
  • 8,414
  • 4
  • 39
  • 69
Anton
  • 1,458
  • 1
  • 14
  • 28

2 Answers2

2

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.
Community
  • 1
  • 1
A. Abramov
  • 1,823
  • 17
  • 45
  • sqlQuery is a function in the RODBC package, which unfortunately is no longer available for present versions of R. I'm using version 3.1 and it won't allow me to install RODBC. Do you know a similar function in RJDBC? – Anton Aug 04 '15 at 10:16
  • @tony Pretty sure I got it this time, take a look at my edit :) – A. Abramov Aug 04 '15 at 10:23
1

this is similar to another answered question here

basically dbGetQuery() as it name implies is used to send queries and recive their result.

if you want to send a general statement to the db like 'drop table' etc. you can use:

dbSendUpdate(connection_object, "drop table table_name")
doron
  • 454
  • 6
  • 10