Base: how to connect to an SQLite database?

[ajlittoz]

Config: LO 5.3.7.2, Linux 4.13, Fedora 26 (I have another “experimental” config under Fedora 27, but problem is the same)

This is a duplicate/refresh of this old question because its answer is not satisfactory or outdated.

I searched through this site and googled without finding the appropriate fix.

I want to generate summary report from an existing active SQLite DB. In order to connect to the database, I installed packages unixODBC and sqliteODBC, but when configuring access to said base with Base wizard:

1. Select DB: chose “existing DB” with ODBC

2. ODBC Data Source: SQLite3 (but same behaviour wit SQLite – for SQLite v2)

3. Authentication: none (as there is none in SQLite)

4. Save and continue: checked “open DB”

5. Clicked on Finish

File selection dialog opens but only choice is ODF data base which will not display SQLite DB files.

Under “connector” jODBC, I can select any file, thus making SQLite DBs visible. However, for various reasons, I don’t want to install Java on my machine (anyway not knowing if I would succeed).

I also searched through LO extensions but it appears there’s nothing related to SQLite connector.

In the end, I don(t know if this is a Fedora issue, an SQLite one, A LO one or some other.

Has anybody solved this problem in a simple way? (by simple, I mean I don’t want to compile from source)

All suggestions are welcome, including pointing me to packaged stand-alone report generator (important word here is “packaged”).

[Ratslinger]

Hello,

If one of your conditions is NOT to use Java at all, then I cannot think of a way to generate this report. I can get SqLite to work on Base with tables & forms but not Report Builder unless Java JRE (or even OpenJDK) is installed. Additionally, most other report generators I have used are Java based.

Regardless, here is a set up I use on Xubuntu 16.04 (last test was with LO 5.4.x but others used). Haven’t used in quite a while so again tested how to set up again today. Have not been able to find a JDBC driver which works for me so all is based upon ODBC for the connection.

The are two files needed in /etc folder - ‘odbc.ini’ & ‘odbcinst.ini’. The are just text type files so if they don’t exist already they are easy to create. The odbcinst.ini file contains the information for the actual drivers:

Here I have two set up - [SQLite] is for SqLite2 DB’s and [SQLite3] is for SqLite3 DB’s. The name in brackets is important for selection in next file.

The odbc.ini contains the connection information:

Here the are connections for three different databases - [SQLite], [SQLite3], & [SQLite4]. These are names assigned by you and will be used when creating the Base connection. The ‘Description’ line is just that. The ‘Driver’ line is the name provided in the odbcinst.ini file for the driver to be used. Here you can see I have three DB’s to be accessed but depending upon the DB, use only one of the two drivers set up. The ‘Database’ line points to the location of the SqLite DB. The remaining settings I got from somewhere (and there are more) but don’t remember where. These do work for me.

With these two files set you can create the Base file. During setup you are asked for the ODBC data source. By selecting ‘Browse’ you will get:

All the items you have set in the odbc.ini file (my sample includes one for a MySQL DB I use for testing). Choosing the appropriate one should be all that is needed to get you up and running.

[ajlittoz]

Thanks. From what I see with ssh on my work computer, odbcinst.ini is fully populated for MySQL, PostgreSQL and SQLite*. However, odbc.ini is near to empty. It seems then that Base does not initialise it with user parameters from the wizard. I’ll enter info manually tomorrow (it’s already nighttime here) as soon as I put my hands on my work comp and I’ll report here.

[Ratslinger]

@ajlittoz Thanks to your question I have corrected my way of thinking that Base requires Java to run. Although some functionality is lost (wizards, Report Builder at the least) it still can create forms, be registered and be used as a data source in Calc and Writer (reports). I have just tested MySQL on Mint 18.2 using ODBC and had no problem accessing in Calc.

[ajlittoz]

Fine, it works.

[Morgan1]

Just a brief note that:

$ ls ~/.odbc*

/home/x/.odbc.ini /home/x/.odbcinst.ini

Also exist.