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

[Luigi Marongiu]

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)
$day
field.name field.type data.type   name comment
1     day_ID    INTEGER   numeric day_ID      NA
2        Day    INTEGER   numeric    Day      NA

$demographic
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,
Luigi