how to connect libreoffice base odb using R package odbc?

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

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 833 bytes
Desc: not available
URL: <https://stat.ethz.ch/pipermail/r-help/attachments/20211110/8bea907b/attachment.sig>

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?

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

Are you willing to send me a part of the 
the proof.odb so I can run the query?  R

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 833 bytes
Desc: not available
URL: <https://stat.ethz.ch/pipermail/r-help/attachments/20211110/dd500fb3/attachment.sig>

.... also Note in ?ODB::odb.read says:

	To query databases built with OpenOffice 
	or LibreOffice, it may be necessary to 
	quote table and/or column names in 
	?sqlQuery?, as the default behavior of 
	the HSQL engine is to convert unquoted 
	table and column names to uppercases.

:)

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 833 bytes
Desc: not available
URL: <https://stat.ethz.ch/pipermail/r-help/attachments/20211110/c0c659a1/attachment.sig>

Sure! I am attaching a simplified version of it. I did not see the
quote text; it might be difficult to call all table's names since
there are hundreds of columns..

Are you willing to send me a part of the
the proof.odb so I can run the query?  R

Best regards,
Luigi

Dear Luigi,

I tried quoting ...  This:

	odb = ODB::odb.open("proof.odb", jarFile = NULL)
	
	dput(sapply(names(ODB::odb.tables(odb)), function(tbl) {
	sqlQuery <- paste0('SELECT * FROM "', tbl, '"')
	ODB::odb.read(odb, sqlQuery)
	}, simplify=F))
	
	ODB::odb.close(odb, write=FALSE)

Yields this:

	list(day = structure(list(day_ID = c(0, 1, 2, 3, 4, 5, 6, 7,
	8, 9, 10, 11, 12, 13, 14, 15, 16, 17), Day = c(1, 2, 3, 5, 7,
	9, 11, 13, 14, 16, 18, 20, 22, 24, 26, 28, 30, 31)), row.names = c(NA,
	18L), class = "data.frame"), demographic = structure(list(ID = logical(0),
	    Study_ID = logical(0), Hosp_ID = logical(0), Hospital = logical(0),
	    Address = logical(0), Occ_ID = logical(0), Age = logical(0),
	    Sex = logical(0)), row.names = c(NA, 0L), class = "data.frame"),
	    hospitals = structure(list(Hosp_ID = c(0, 1, 2, 3), Hospital = c("Fatebenefratelli",
	    "Royal Free", "Santa Croce in Gerusalemme", "Sagrada Familia"
	    ), Address = c("Via Tiberina", "Tottenham Court road", "Via Latina",
	    "Barcelona")), row.names = c(NA, 4L), class = "data.frame"))

The demographics table is empty, but has 
headers ....  Maybe you selected the 
wrong file ...

Best,
Rasmus

-------------- next part --------------
A non-text attachment was scrubbed...
Name: signature.asc
Type: application/pgp-signature
Size: 833 bytes
Desc: not available
URL: <https://stat.ethz.ch/pipermail/r-help/attachments/20211110/25874b15/attachment.sig>