ODBC Administration

Quick question: where would I create a new Haiku feature request to add a new Preference applet to create and manage all ODBC data sources and connections from third-party connectors? Possibly named as ‘ODBC Adminstration’.

This would enable LibreOffice Base to be useful where we can create new databases or incorporate non-Base databases (if you have its ODBC connector).

1 Like

If it should be in the core system then : https://dev.haiku-os.org

Thanks! Yes it is core–in MS Windows and various Linux OS’es, it is usually located in the control panel area. In Haiku, that is the Preference section.

This should probably be handled by porting unixODBC to Haiku. If you want to request that, you can create an issue at HaikuPorts: Issues · haikuports/haikuports · GitHub

1 Like

That would be a good start. Where by incorporating unixODBC into Haiku, we gain connectivity to at least two RDBMS’: DB2 & PostgreSQL, which seem to be natively adapted into unixODBC.

All other RDBMS’ require third-party OS specific connectors–either from third party vendors, such as Easysoft and others, or from RDBMS Web sites, which are the best sources.

There are currently no ODBC drivers written for Haiku…yet.

But even if unixODBC was ported to Haiku, we would still need a centralized location to manage the data sources and their respective connectors.

I seems you have knowledge regarding ODBC stuff, so could you explain what kind of features or functionality would that bring for Haiku and how the Haiku users can profit from this?

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


Updated sqlite and odbc to the latest version here locally, trying to build sqliteodbc however still seems to fail finding odbc when configure is running, to check odbc is fine I checked a build for freetds that builds ok with odbc, so not sure (config.log isn’t really helpful) how to get it to pick up at configure time.

I’ve got 2 more options here (32bit):

1 Like

Interesting…seems like they had better success in the 32-bit realm of coding versus 64-bit. They were able to add Thunderbird addressbook & MySQL connectors.