Ask Your Question
0

Base: how to connect to an SQLite database? [closed]

asked 2017-11-26 12:37:17 +0200

ajlittoz gravatar image

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

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by ajlittoz
close date 2017-11-27 13:57:22.773833

1 Answer

Sort by » oldest newest most voted
1

answered 2017-11-26 20:54:16 +0200

Ratslinger gravatar image

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:

image description

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:

image description

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:

image description

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.

edit flag offensive delete link more

Comments

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.

ajlittoz gravatar imageajlittoz ( 2017-11-26 21:47:45 +0200 )edit

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

Ratslinger gravatar imageRatslinger ( 2017-11-27 02:20:36 +0200 )edit

Fine, it works.

ajlittoz gravatar imageajlittoz ( 2017-11-27 12:32:03 +0200 )edit

Question Tools

1 follower

Stats

Asked: 2017-11-26 12:37:17 +0200

Seen: 4,419 times

Last updated: Nov 26 '17