dmkaplan2000/rh2

R package containing DBI/RJDBC interface to h2 database

rh2's Issues

dbIsValid method for H2 connection

Would be nice to handle such basic method as dbIsValid

library(RH2)
dbIsValid(dbConnect(H2(), "jdbc:h2:mem:"))
# Error in (function (classes, fdef, mtable)  :
#   unable to find an inherited method for function 'dbIsValid' for signature '"H2Connection"'

while

library(RSQLite)
dbIsValid(dbConnect(SQLite(), "sqlite.db"))
# [1] TRUE

use dbSendQuery instead of dbSendUpdate for DBI consistency

DBI defines dbGetQuery as submit query+retrieve results and dbSendQuery just to submit query. RH2 seems to break that rule and here dbSendQuery expects to retrieve results anyway - which results error.
Can we get a consistency with DBI and be able to use dbSendQuery for any kind of submit queries? The one mentioned below (drop table) is just an example, the behavior is the same when using other submit queries, e.g. create sequence.

library(RH2)
conn <- dbConnect(H2(), "jdbc:h2:mem:")
dbWriteTable(conn, "mytab", data.frame(a = 1:3, b = letters[1:3]))
# [1] TRUE
nrow(dbReadTable(conn, "mytab"))==3L
# [1] TRUE
dbSendQuery(conn, "drop table mytab;")
# Error in .verify.JDBC.result(r, "Unable to retrieve JDBC result set for ",  : 
# Unable to retrieve JDBC result set for drop table mytab; (Method is only allowed for a query. Use execute or executeUpdate instead of executeQuery; SQL statement: drop table mytab; [90002-175])

library(RSQLite)
conn <- dbConnect(RSQLite::SQLite(), dbname = "sqlite.db")
dbWriteTable(conn, "mytab", data.frame(a = 1:3, b = letters[1:3]))
# [1] TRUE
nrow(dbReadTable(conn, "mytab"))==3L
# [1] TRUE
dbSendQuery(conn, "drop table mytab;")
# <SQLiteResult>

Boolean type support

Hi,
Any chance for support boolean types? currently they are queried as character, see below.

library(RJDBC)
library(RH2)
con <- dbConnect(H2(), "jdbc:h2:~/test", "sa", "")
s <- 'create table tt("id" int primary key, "logical" BOOLEAN)'
dbSendUpdate(con, s)
dbSendUpdate(con, "insert into tt values(1, TRUE)")
str(dbGetQuery(con, "select * from tt"))
# 'data.frame': 1 obs. of  2 variables:
#  $ id     : num 1
#  $ logical: chr "TRUE"
dbGetQuery(con, "select COLUMN_NAME, TYPE_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME = 'tt'")
#   COLUMN_NAME TYPE_NAME
#1          id   INTEGER
#2     logical   BOOLEAN
packageVersion("RH2")
# [1] ‘0.2.3’