[R-pkgs] ODB : connecting OpenOffice Base with R

From: Sylvain Mareschal <maressyl_at_gmail.com>
Date: Sun, 19 Jun 2011 13:25:11 +0200

The recently released "ODB" package was developped to manage HSQL databases embedded in .odb files (the default when creating a database with OpenOffice Base) via R.

BRIEFLY The goal of this package is to access OpenOffice databases via R, to process data stored in it or to automatize their building from scratch or updating.

The package provides 5 main functions :
- odb.create, to create a new .odb file from a template.

A few other functions are also provided to manage .odb specificties such as comments on table fields and stored queries. Some wrappers are also provided to insert directly a data.frame in a database table without writing the SQL query, list the table names ands fields or export the database in a .sql file.

Other wrappers may be added in future versions to help users not familiar with the SQL language.

TYPICAL USE connection <- odb.open("file.odb")
data <- odb.read(connection, "SELECT * FROM table WHERE id < 15") odb.write(connection, "UPDATE table SET field='peach' WHERE id = 5") odb.close(connection)

TECHNICAL CONSIDERATIONS .odb files, as any other OpenDocument files, are ZIP archives containing the HSQL files. To establish the connection, the .odb file is unzipped via the "zip" bash command if available, and the connection is made via the RJDBC interface. The "odb" object produced inherits from the "DBIConnection" class, thus all functions provided in the DBI packages may be used directly on it to manage the database. The odb.read and odb.write functions are only wrappers to such DBI functions, handling frequent issues such as charset or factors considerations.

Notice the database files are copied in a temporary directory, thus any update made to the database is not written in the .odb file untill the odb.close call, so simultaneous access to a same database (via R and OpenOffice) should not be considered.

Any suggestion or comment may be sent back to this email adress.

Sylvain Mareschal

R-packages mailing list
https://stat.ethz.ch/mailman/listinfo/r-packages Received on Mon 20 Jun 2011 - 17:05:37 EST

This archive was generated by hypermail 2.2.0 : Mon 20 Jun 2011 - 17:10:01 EST