[R] how to connect libreoffice base odb using R package odbc?

Luigi Marongiu m@rong|u@|u|g| @end|ng |rom gm@||@com
Wed Nov 10 16:03:46 CET 2021

Thank you.
I have used ODB, it looks easier to use (and install since it needs
way fewer dependencies than odbc) but I can't run the SQL statement:
> library(ODB)
Loading required package: DBI
Loading required package: RJDBC
Loading required package: rJava
> db =  odb.open("proof.odb", jarFile = NULL)
> odb.tables(db)
field.name field.type data.type   name comment
1     day_ID    INTEGER   numeric day_ID      NA
2        Day    INTEGER   numeric    Day      NA

field.name field.type data.type       name                  comment
1          ID    INTEGER   numeric         ID Unique identifier (auto)
2    Study_ID    INTEGER   numeric   Study_ID            link to STUDY
> print(odb.read(db, "SELECT * FROM demographic"))
Error: Error while executing SQL query  : "Unable to retrieve JDBC result set
  JDBC ERROR: Table not found in statement [SELECT * FROM demographic]
  Statement: SELECT * FROM demographic"

the database contains a table `demographic` but I can't select it.
Would you know what is the correct syntax?

On Wed, Nov 10, 2021 at 3:43 PM Rasmus Liland <jral using posteo.no> wrote:
> Dear Luigi,
> I found the package ODB
>         https://cran.r-project.org/web/packages/ODB/ODB.pdf
> and was able to install it after
> reconfiguring rJava ...
> The help page ?ODB::odb.open has this
> example:
>         odbFile <- tempfile(fileext=".odb")
>         odb.create(odbFile, overwrite="do")
>         odb <- odb.open(odbFile)
>         # New table
>         odb.write(odb, "CREATE TABLE fruits (name VARCHAR(6) PRIMARY KEY)")
>         odb.insert(odb, "fruits", c("banana", "pear", "peach"))
>         # Writes to the file and closes the connection
>         odb.close(odb, write=TRUE)
> Best,
> Rasmus

Best regards,

More information about the R-help mailing list