[R-sig-DB] Add a "dbSendUpdate" function to DBI?

[MacQueen, Don]

As the OP, I don¹t have a name preference, and dbExecuteQuery() seems fine.

What I do hope for, of course, is that maintainers of packages based on
DBI will in due course implement the new function, so that my scripts are
portable between packages. I had mentioned ROracle and RJDBC (from which
my request arose), but I also use RMySQL, and I use all three of these
quite a bit. And of course there are several others.

At the bottom of this email I¹m including a reproducible example
illustrating my situation, in case anyone is interested. I also use
dbSendUpdate() or dbSendQuery() to send 'alter table' and 'grant' commands
to the database. Also including sessionInfo() this time.

Thanks
-Don

-- 
Don MacQueen

Lawrence Livermore National Laboratory
7000 East Ave., L-627
Livermore, CA 94550
925-423-1062





On 9/4/14, 8:06 AM, "Denis Mukhin" <denis.x.mukhin using oracle.com> wrote:

>I like it. dbExecuteQuery() matches well with already existing
>dbGetQuery() and dbSendQuery(). All three are essentially doing the same
>operation (submitting a Query) but have different return types.
>
>Denis
>
>-----Original Message-----
>From: Hadley Wickham [mailto:hadley using rstudio.com]
>Sent: Thursday, September 04, 2014 10:54 AM
>To: Denis Mukhin
>Cc: MacQueen, Don; Simon Urbanek; r-sig-db using r-project.org
>Subject: Re: [R-sig-DB] Add a "dbSendUpdate" function to DBI?
>
>On Thu, Sep 4, 2014 at 9:28 AM, Denis Mukhin <denis.x.mukhin using oracle.com>
>wrote:
>> In ROracle we standardized on the second approach, namely always return
>>TRUE, otherwise throw an error.
>>
>> Also since UPDATE is not the only operation that does not return any
>>results (although it might with a RETURNING clause) would a more generic
>>name be more appropriate here? Besides DMLs there are also DDLs.
>>Something like dbExecute(), dbSubmit(), dbSend() ...
>
>How about dbExecuteQuery() ?
>
>(dbSendQuery() is already taken.)
>
>Hadley
>
>-- 
>http://had.co.nz/
>
>_______________________________________________
>R-sig-DB mailing list -- R Special Interest Group
>R-sig-DB using r-project.org
>https://stat.ethz.ch/mailman/listinfo/r-sig-db


################
###     reproducible example
### (my email software stupidly insists on inserting line breaks after
###  79 or 80 characters when configured to send plain text email,
### so take that into account when viewing the lines below. Sorry!
################

require(RJDBC)

jdbm <- JDBC('oracle.jdbc.OracleDriver', 'username')
jcon <- dbConnect(dbm, dbuser$jdbc.con, 'username', 'password')

if (dbExistsTable(jcon,'JUNK')) dbRemoveTable(jcon,'JUNK')
if (dbExistsTable(jcon,'JUNK2')) dbRemoveTable(jcon,'JUNK2')

dbExistsTable(jcon,'JUNK2')

tsttbl <- data.frame(x=1:7, a=c('AA','BB',letters[1:5]))
dbWriteTable(jcon,'JUNK',tsttbl,row.names=FALSE)
dbGetQuery(jcon,'select * from JUNK')

dbSendUpdate(jcon,"create table JUNK2 as select * from JUNK where a='BB'")
dbExistsTable(jcon,'JUNK2')
dbGetQuery(jcon,'select * from JUNK2')

if (dbExistsTable(jcon,'JUNK2')) dbRemoveTable(jcon,'JUNK2')
dbExistsTable(jcon,'JUNK2')

dbSendQuery(jcon,"create table JUNK2 as select * from JUNK where a='BB'")


####################################################
######### here is what I get when running the above

> if (dbExistsTable(jcon,'JUNK')) dbRemoveTable(jcon,'JUNK')
logical(0)
> if (dbExistsTable(jcon,'JUNK2')) dbRemoveTable(jcon,'JUNK2')
logical(0)
> 
> dbExistsTable(jcon,'JUNK2')
[1] FALSE
> 
> tsttbl <- data.frame(x=1:7, a=c('AA','BB',letters[1:5]))
> dbWriteTable(jcon,'JUNK',tsttbl,row.names=FALSE)
[1] TRUE
> dbGetQuery(jcon,'select * from JUNK')
  X  A
1 1 AA
2 2 BB
3 3  a
4 4  b
5 5  c
6 6  d
7 7  e
> 
> dbSendUpdate(jcon,"create table JUNK2 as select * from JUNK where
>a='BB'")
> dbExistsTable(jcon,'JUNK2')
[1] TRUE
> dbGetQuery(jcon,'select * from JUNK2')
  X  A
1 2 BB
> 
> if (dbExistsTable(jcon,'JUNK2')) dbRemoveTable(jcon,'JUNK2')
logical(0)
> dbExistsTable(jcon,'JUNK2')
[1] FALSE
> 
> dbSendQuery(jcon,"create table JUNK2 as select * from JUNK where a='BB'")
Error in .verify.JDBC.result(md, "Unable to retrieve JDBC result set meta
data for ",  : 
  Unable to retrieve JDBC result set meta data for create table JUNK2 as
select * from JUNK where a='BB' in dbSendQuery (ORA-01003: no statement
parsed
)
> 

##################
> sessionInfo()
R version 3.1.1 (2014-07-10)
Platform: x86_64-apple-darwin13.1.0 (64-bit)

locale:
[1] C

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

other attached packages:
[1] RJDBC_0.2-4 rJava_0.9-6 DBI_0.3.0