R odbc package connect to libreoffice base odb

Gigiux · November 10, 2021, 8:58am

Hello
how can I connect to a LibreOffice base odb file using the package odbc for R?
I tried with:

> library(odbc)
> library(DBI)
> con <- dbConnect(odbc::odbc(),
									 +                  driver = "PostgreSQL Driver",
									 +                  database = "proof.odb",
									 +                  uid = "",
									 +                  pwd = "",
									 +                  host = "localhost",
									 +                  port = 5432)
Error: nanodbc/nanodbc.cpp:1021: 00000: [unixODBC][Driver Manager]Can't open lib 'PostgreSQL Driver' : file not found

> con <- dbConnect(odbc::odbc(),
									 + 								 driver = "/usr/local/lib/psqlodbcw.so",
									 + 								 database = "proof.odb",
									 + 								 uid = "",
									 + 								 pwd = "",
									 + 								 host = "localhost",
									 + 								 port = 5432)
Error: nanodbc/nanodbc.cpp:1021: 00000: [unixODBC][Driver Manager]Can't open lib '/usr/local/lib/psqlodbcw.so' : file not found

> con <- dbConnect(odbc::odbc(),
									 + 								 driver = "/usr/local/lib/libsqlite3odbc.dylib",
									 + 								 database = "proof.odb",
									 + 								 uid = "",
									 + 								 pwd = "",
									 + 								 host = "localhost",
									 + 								 port = 5432)
Error: nanodbc/nanodbc.cpp:1021: 00000: [unixODBC][Driver Manager]Can't open lib '/usr/local/lib/libsqlite3odbc.dylib' : file not found

What would be the correct driver/syntax?

RobertG · November 10, 2021, 9:17am

What should be “R”?

You are trying to connect to your *.odb-file with ODBC. That doesn’t work. The database must be the database from “R”.

The errors reported from your systems say: You try to connect to PostgreSQL and haven’t installed the ODBC driver for PostgreSQL. You try to connect to SQLite, but you haven’t installed the ODBC driver for SQLite.

When I read something about “R” it seem to be a programming language, connecting to a database. So “R” isn’t a database you could connect to. You could connect, for example, to a database like MySQL/MariaDB, where “R” also could connect to.

Gigiux · November 10, 2021, 10:25am

Ok, but so how do I import a database (made with LibreOffice base) into the R environment? The package odbc (together with DBI) is supposed to do that (and I have installed several linux packages needed for the installation of odbc). Alternatively, I can import with

library(ODB)
db =  odb.open("proof.odb", jarFile = NULL)

without problems. ODB uses a HSQLDB driver (The help says “Extracts embedded HSQLDB files from an .odb archive an sets a DBI connection to it, in order to read or write data from the database.”)
What would be the equivalent for odcb?

PS: I would be more than happy to use ODB, but when I run the command:

print(odb.read(db, "SELECT * FROM demographic"))

I get the error:

> 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"

although the demographic table is present:

> 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)
[...]

RobertG · November 10, 2021, 11:09am

Could it be you have to mask the table name, because it isn’t written in uppercase?

Don’t know anything about the command you try to execute … What kind of shell is it?

Gigiux · November 10, 2021, 12:22pm

The language is simple SQL, and the table’s name is in lowercase as shown by odb.tables. I don’t know why the statement is not recognized, that is why I wanted to switch to odbc, but how do I make the connection?

Villeroy · November 10, 2021, 1:33pm

Base is not a database. It is a frontend to work with databases. In most cases “Base database” means “embedded HSQLDB”. The status bar of your database document indicates the type of connection.
HSQL is a Java database. As a stand-alone product, it is accessible via JDBC and via ODBC too. HSQL embedded in a Base document, is accessible for office documents.
You need HSQL2 to connect via ODBC.
Create a dedicated directory with a copy of your database document.
Create a subdirectory “driver”.
Download https://sourceforge.net/projects/hsqldb/files/hsqldb/hsqldb_2_4/hsqldb-2.4.1.zip and extract lib/hsqldb.jar into the driver directory.
Install my [Python] Macro to extract and reconnect embedded HSQLDB, open your database document with embedded HSQL and run the macro Tools>Macros>Run… pyDBA>ExtractHSQL>Main. You get a subdirectory with the extracted HSQLDB and your document will be connected to it through the database driver.

Alternatively, you may decide to connect HSQL via JDBC. Just install my Python macro, open your document and run the macro. The extracted database will be driven by the HSQL 1.8 driver that is shipped with LibreOffice. The driver is <install_dir>/program/classes/hsqldb.jar.

Villeroy · November 10, 2021, 2:17pm

OOOPS, I read that you try to connect to PostgreSQL. Just connect R to PostgreSQL via ODBC This has nothing to do with LibreOffice. LibreOffice and R are just two different frontends to work with a PostgreSQL database.

Gigiux · November 10, 2021, 2:39pm

Thank you! this looks like a (relatively) lot of work; I understood that these R packages should do the connection effortlessly…

Villeroy · November 10, 2021, 8:34pm

ODBC does the connection effortlessly. You still do not tell us what your “Base database” is supposed to do in that matter. LibreOffice has absolutely nothing to do with a working connection between PostgreSQL and R via ODBC.
If your problem is a connection between embedded HSQL and R, your problem has nothing to do with PostgreSQL and “a lot of work” will be done in less than 5 minutes.