Message-ID: <CAMk+s2Q+udXcNYKWpx5g2STd3A=YRoa90_7nu_4gXX6Phb+pvQ@mail.gmail.com>
Date: 2021-11-05T15:25:27Z
From: Luigi Marongiu
Subject: How to select single table with ODB package
Hello,
I am connecting R to a libreoffice-generated database (.odb) using the
ODB package.
I would like to link a dataframe to a specific table, but I cannot
manage to extract a given table:
```
> db = odb.open(".../LOdatabase.odb", jarFile = NULL)
> odb.tables(db)
$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
[...]
> sqlQuery = "FROM TABLE demographic SELECT ALL"
> odb.read(db, sqlQuery, stringsAsFactors = FALSE, check.names = FALSE,
+ encode = TRUE, autoLogical = TRUE)
Error: Error while executing SQL query : "Unable to retrieve JDBC result set
JDBC ERROR: Unexpected token: FROM in statement [FROM]
Statement: FROM TABLE demographic SELECT ALL"
> sqlQuery = "SELECT * FROM demographic"
> odb.read(db, sqlQuery, stringsAsFactors = FALSE, check.names = FALSE,
+ encode = TRUE, autoLogical = TRUE)
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"
> sqlQuery = "SELECT * FROM db$demographic"
> odb.read(db, sqlQuery, stringsAsFactors = FALSE, check.names = FALSE,
+ encode = TRUE, autoLogical = TRUE)
Error: Error while executing SQL query : "Unable to retrieve JDBC result set
JDBC ERROR: Table not found in statement [SELECT * FROM db$demographic]
Statement: SELECT * FROM db$demographic"
```
What is the correct syntax?
Thank you