Renjin database connectivity.
Based on renjin-dbi.
Releases are now available on maven central.
Note that you need to add the driver jar to the classpath in addition to R2JDBC e.g:
<dependencies>
<dependency>
<groupId>org.renjin.cran</groupId>
<artifactId>DBI</artifactId>
<version>1.0.0-b9</version>
</dependency>
<dependency>
<groupId>se.alipsa</groupId>
<artifactId>R2JDBC</artifactId>
<version>10.0.25</version>
</dependency>
<!-- the driver, depends on what db you want to use ;) -->
<dependency>
<groupId>com.h2database</groupId>
<artifactId>h2</artifactId>
<version>2.1.212</version>
</dependency>
</dependencies>
library("org.renjin.cran:DBI")
library("se.alipsa:R2JDBC")
drv <- JDBC("org.h2.Driver")
con <- dbConnect(drv, url="jdbc:h2:mem:test")
df <- dbGetQuery(con, "SELECT * from sometable")
dbDisconnect(con)
All the api functions uses a connection to perform tasks. In order to create a connection to the database you need to load the driver first, e.g:
con <- dbConnect(JDBC("org.h2.Driver"), url="jdbc:h2:mem:test")
dbSendUpdate(con, paste('CREATE TABLE MyTable (
"id" INT NOT NULL,
"title" VARCHAR(50) NOT NULL,
"author" VARCHAR(20) NOT NULL,
"submission_date" DATE,
"insert_date" TIMESTAMP,
"price" NUMERIC(20, 2)
)'))
There are two ways to drop a table:
# 1. using dbRemoveTable:
dbRemoveTable(con, "MyTable")
# 2. using dbSendUpdate
dbSendUpdate(con, "drop table MyTable")
dbSendUpdate(con, "ALTER TABLE MyTable DROP COLUMN author")
dbSendUpdate(con, paste("
insert into MyTable values
(1, 'Answer to Job', 'C.G. Jung', CURRENT_DATE, CURRENT_TIMESTAMP, 22),
(2, 'Lord of the Rings', 'J.R.R. Tolkien', '2019-01-20', CURRENT_TIMESTAMP, 14.11),
(3, 'Siddharta', 'Herman Hesse', '2019-01-23', CURRENT_TIMESTAMP, 9.90)
"))
The dbGetQuery returns a data.frame:
df <- dbGetQuery(con, "SELECT * from MyTable")
dbSendUpdate(con, "update MyTable set price = 25 where id = 1")
dbSendUpdate(con, "delete from MyTable where id = 1")
Returns a character vector of all the table names for the connection.
returns a logical (boolean) if the specified table exists
Drops the table specified
Return a list of columns names for the table name specified
Returns the appropriate database type for the R object specified. Note: this method is not very refined and should be used a hint rather than as a recommendation.
Used to insert a dataframe
- Example
con <- dbConnect(drv, url="jdbc:derby:derbyDB;create=true")
dbBatchInsert(con, name=name, df=mtcars, overwrite=TRUE)
Writes the value (a vector, list of data.frame) to a database table. This is functionally equivalent to dbBatchInsert but implemented differently.
Note that the csvdump argument is ignored.
Begins a transaction, sets autocommit to false
Commit the transaction
Rollback the transaction
The microsoft SQL Server driver (and maybe others) gets confused when user and password is specified in the url only. In most other JDBC drivers, supplying and empty string for user and password works where the username/password in the url will then take precedence, but not so for the SQL server driver. Hence, you need to set user and password to NA to get it to work. E.g. this pattern (which works for postgres, derby, h2 etc) will not work:
con <- dbConnect(
JDBC("com.microsoft.sqlserver.jdbc.SQLServerDriver"),
url="jdbc:sqlserver://localhost:1433;databaseName=tempdb;user=test;password=unS3cur3P@55"
)
but this will work fine:
con <- dbConnect(
JDBC("com.microsoft.sqlserver.jdbc.SQLServerDriver"),
url="jdbc:sqlserver://localhost:1433;databaseName=tempdb;user=test;password=unS3cur3P@55",
user=NA,
password=NA
)
and of course so will this:
con <- dbConnect(
JDBC("com.microsoft.sqlserver.jdbc.SQLServerDriver"),
url="jdbc:sqlserver://localhost:1433;databaseName=tempdb",
user="test",
password="unS3cur3P@55"
)
R2JDBC is built using maven and Java 8. To create and install a local package
simply run mvn install
Note: This does not build properly on Windows for some reason (classes are does not end correctly with RData, this is probably a bug in the renjin-maven-plugin or the gcc bridge). Until this is resolved you need to build in Linux (though it probably works on other Unix like distributions as well)
- Upgrade to Java 11
- Removed dependency on Joda Time
- Make dateTime retrieval more robust
- Upgrade jdbc drivers used in test
- Upgrade h2 dependency
- Workaround for RowNamesVector changes in renjin master compared to 0.9.2716 (now works in all versions)
- upgrade slf4j version
- upgrade testcontainers versions
- upgrade dependencies (h2, maven site plugin)
- improve bigint support for postgresql (when detected as int8), add support for bigserial
- Add support for CHARACTER VARYING data type (e.g. H2)
- Version bump of dependencies.
- mysql has a BIGINT UNSIGNED type; treat it as a regular BIGINT for now.
- Version bump of dependencies.
- Add support for the "name" datatype (e.g. in postgresql).
- Fix for Sql server when url contains username/password.
- Add unit tests using TestContainer with Docker.
- Version bump of dependencies.
- Add support for datetimeoffset datatype. Published on maven central.
- Fix for boolean datatype on postgres (announced as "bool" type)
R2JDBC is a renjin extension providing database connectivity. It is Based on the renjin-dbi (https://github.com/bedatadriven/renjin-dbi). Most of the changes consists of support for more data types.