ODB R package

The R package ODB provides functions to create, connect, update and query HSQL databases embedded in Open Document Databases (.odb) files, as OpenOffice and LibreOffice do, from within R software.

Following are the main commands I use from ODB to access my databases, they are simple and self-explanatory:

# Connection to the database
ODB <- odb.open("~/path/file_name.odb")

# Creation of a new table
SQL <- "CREATE TABLE name_table (id INTEGER PRIMARY KEY, date_field DATE, numeric_field DOUBLE, character_field VARCHAR(10))"
odb.write(ODB, SQL)

# Creation of a new view
SQL <- "CREATE VIEW view_name AS SELECT * FROM name_table WHERE character_field='AAA'"
odb.write(ODB, SQL)

# Insert data on your database
data = data.frame(id = 1:10, date_field = some_dates, numeric_field = as.numeric(1:10), character_field = "some_name")
odb.insert(ODB, tableName="name_table", data=data, execute=TRUE)

# Read data from database
odb.read(ODB, "SELECT * FROM name_table WHERE date_field >= '2011-01-01' AND date_field <= '2011-03-15'")

# You can use any SQL command
SQL <- "UPDATE name_table SET character_field = 'AAA'"
odb.write(ODB, SQL)

# Close connection
odb.close(ODB, write = TRUE)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s