R package: DBI

DBI Backends

The actual database dependent implementation is implemented by so called DBI backends, some of which start with R (for example RMySQL or ROracle).

A backend needs to define three classes which are subclasses of

DBIDriver
DBIConnection
DBIResult

Some methods

DBI::dbDataType(dbiCon, rObj) returns a proposed SQL (database) data type with which an R objects should be stored on a specific database backend.

dbExistsTable

dbExistsTable(con, tab) checks if a table exists.

dbColumnInfo

dbColumnInfo describes a result set (that is: a DBIResult).

dbListFields

dbListFields(con, 'tab_name') returns a vector that contains the names of the columns of the table named `*tab_name*.

Notably, it does not return the columns' data types and I didn't find a straight forward method in DBI that would to that. However, the column types of a tables can be queried a bit combersomly with select * from table combined with a SQL limit clause that truncates the returned record set to 0 records:

db          <- dbConnect(…)

empty_res   <- dbSendQuery(db_omis_lab, 'select top 0 * from data')
col_info    <- dbColumnInfo(empty_res)
dbClearResult(empty_res)

Notes:

It seems that dbSendQuery cannot be used here.
col_info is a data frame with two columns, named name and type.

dbWriteTable

dbWriteTable can be used to write a data frame into a database.

TODO

dbExecute can be used for DML statements, create table, drop table etc.

For a reason I don't really understand, and unlike other functions such as dbSendQuery(…), dbExecute needs to be prefixed with DBI:::

DBI::dbExecute(con, 'create table t (v varchar(20))');