How can all Haiku users benefit from the inclusion of ODBC capabilities–a most excellent question. I would also promote JDBC, since I believe that would be an easier work to achieve since JDBC is not OS-centric but Java-based. But, that is another separate topic related to this.
First how can we benefit. If we could open and use external Relational Database Management Systems via database connectors, we could leverage the capabilities of Base to create database applications with forms and reports, perform SQL data processing, and so much more (in similar manner as one would MS Access). That is the power of LibreOffice Base.
However, let’s look at present condition of LibreOffice Base. When you first open LO Base, the database wizard opens and there are three options:
A. LO Database Creation:
We cannot create a LO native embedded database. I can understand the main issue here is that LO maintainers would have to port the entire modified HyperSQL database system within LO Base. And its primary database connector for LibreOffice Base’s HSQLDB, which is the LO native ‘Star Database Connector’ (sdbc) for creating and connecting to its native HSQLDBs: ‘sdbc:embedded:hssqldb’.
This is true for the other native built-in database, Firebird: ‘sdbc:embedded:firebird’.
B. LO Open an Existing Database File:
This section of the database wizard is dependent on the first and/or third option. We can ignore this for now–but look at it after the next option.
C. LO Connect to an Existing Database:
In the picture below, we are provided a short list of available database connectors to select from (depending what type of database file we wish to open and use). The four available options: Spreadsheet, dBASE, Text and Writer Document have extreme limitations database-wise, hence we cannot fully leverage the database power and flexibility of Base and using SQL. It is not very usable unless it is a very simple project restricted to one table.
The fifth option: ODBC is not viable in Haiku’s current state. In the next window, LO is looking to the Haiku (at the OS level) for the registered system or user data sources.
What is missing in the dropdown are other database connectors that LibreOffice usually comes with: JDBC, Oracle JDBC, ADO, MS Access, MS ACCESS 2007, Firebird External, MySQL/MariaDB, and PostgreSQL. These were not ported at this time. However, at least ODBC was ported–not presently testable.
Hence my request for an ODBC Admin Preference applet to manage data sources and connector drivers derives from this oint of LO development.
How does ODBC usually work in other OS’es:
1) Download ODBC driver from the database maintainer web site [in Haiku's situation our developer's will have to produce these]
2) Install/Setup ODBC driver using its executable, which performs the necessary loading steps to assign files and OS registration
3) Open the ODBC Administration applet to set-up and assign the database connector (i.e. ODBC driver) to the data source (e.g. usually a database)--once configuration is accomplished you can connect to the database and test the connection
4) [Returning to the LO Database Wizard: Choose a data source] the configured data source would then be listed in this window
Return to B. LO Open an Existing Database File:
So if we cannot create a native database, we should at least be able to use a previously created database using database connectors: SDBC, ODBC or JDBC connectors.
Here is an example of a LibreOffice Base database using a SQLite database back-end created in a different OS. I selected ‘Open an existing database file’ and click-on the ‘Open’ button:
Then the Base opens, but once I click-on ‘Tables’:
Currently, in the defined unixODBC connectors within LO does not recognize the SQLite database. This is due to the missing SQLite ODBC driver in the Haiku system.
The source code for the SQLite ODBC driver is located at http://www.ch-werner.de/sqliteodbc/sqliteodbc-0.9998.tar.gz
Now, this represents how LibreOffice can use ODBC drivers to further LO’s utility. Other situations for ODBC drivers can possibly be used by YAB, C++ or other programming scenarios where you need database services.
Now, I am not in no way criticizing the LO developers–it’s an immense project to maintain. I really appreciate that we have LibreOffice to use (since GoBe Productive was not ported).