benjaminbruecker.de

How to access SQL-Lite databases using ODBC on recent Debian/Ubuntu

Von Benjamin BrückerBeitrag gepostet am Beitrag gepostet in Computer

Many modern applications are using SQLite databases to store their data. I wanted to access to data of Hamster, a very nice time tracking application for the Gnome desktop, to use that data for further analyses in OpenOffice Calc. My experience from Windows is that ODBC is very easy to access to SQL Server or other databases. Is it possible to work that with Linux? Yes, it is! This article describes how you can use ODBC to access to those databases. Also some issues are discussed.
[toc=“1,2,3″ title=“Table of Contents“]

Concept and basic advantages and disadvantages of ODBC

ODBC offers SQL functionality which is more or less interdependent from the SQL server you can access. The functionality is offered by the driver. It could offer only basic functions or more advanced functions for the server.
So one advantage is to use SQL statements which could work on several servers or versions. The speed is critical. Many describe SQL as very slow.

Installation

Everything which is needed is included in the standard repositories of recent Debian or Ubuntu versions (I guess in other linux distributions either, but I didn’t check it by myself. These packages were useful for me:
unixODBC libsqliteodbc unixodbc-bin
This is the core functionality, a ODBC driver for SQLite, and some tools, some of them are graphical.

Configurating a driver

Open the shell and enter ODBCConfig. This opens a graphical interface, where you can configure the driver:

  1. Click on the User DSN tab.
  2. Click on Add.
  3. Select the driver and click OK.
  4. Enter in the field name a good name and enter in the field for the Database the complete path (including the database name).

That’s all! As an alternative you could create the configuration using a text editor. Name of the file is .odbc.ini and you need to save it in your home directory for User-DSNs. The configuration can look like this:

[hamster]
Description = SQLite3
Driver = SQLite3
Database = /home/username/.local/share/hamster-applet/hamster.db
Timeout = 100000
StepAPI = Yes
ShortNames = No
NoCreat = No
SyncPragma = FULL
LoadExt =

Access with OpenOffice

Create an OpenOffice database with ODBC connection

After searching the internet it seems that the standard way is to create an OpenOffice database using Base (maybe you have to install it). This is also necessary if you like to access to the database with Calc (Maybe there are other ways, but I didn’t find them).

  1. Create a database and use the option Connect to an existing database and select ODBC.
  2. Enter the name of the User DSN or select the data source by clicking on Browse and click on Next.
  3. For SQLite databases no username or passwords are needed. So you could leave the fields blank. You can test the connection. Click on Next.
  4. In the last step you could register the database (in that case it´s accessible in Calc). Click on Finish.

Now we have a database which is connected via ODBC to the  source. If you use a database of an application you should not change tables or views here, if you don’t want to change the application.  You can use the SQL queries to display data and save those queries. You can access to those queries in Calc.

Create linked connection in Calc

As described above, you need an registered OpenOffice database, to access to tables or self created queries in that database. The access is quite easy:

  1. Open or create a File in calc.
  2. Click F4 or select View datasources from the View menu.
  3. Click in the left pan of the functional window on your datasource and browse it.
  4. Drag the query or table into your table (a copy will not be updated).
  5. Select Define range from the Data menu and enter a name or accept the offered name.

Now the table or query is linked. Other methods will just copy the content.

Update the linked connection in Calc

It’s a pity and from my personal point of view not logical, that the linked query or table does not appear as a link in the edit link. Also those links will not be updated automatically when you open the table. You have to do that by hand:

  1. Go to the linked table.
  2. Choose Refresh range from the Data menu.

Maybe you can write some macro to do this automatically.

Change text to number or other values of connected queries on SQLite

Another very poor problem is, that all my Integer, Long and other datatypes are represented as text. (This is not the case if you link to tables.) To solve that just create another table sheet with the function value to your entry. This is not very elegant – I know -, but it is an easy solution.

Nach oben scrollen