Libreoffice Sqlite

Posted onby admin

Let LibreOffice Base be a SQLite frontend. Slideshare uses cookies to improve functionality and performance, and to provide you with relevant advertising. If you continue browsing the site, you agree to the use of cookies on this website. LibreOffice Base natively supports some flat file database formats, such as the dBASE format. You can also use LibreOffice Base to connect to external relational databases, such as databases from MySQL or Oracle. The following database types are read-only types in LibreOffice Base. SQLite ODBC Driver For SQLite to work through the ODBC standards you will also need to install and configure the SQLite ODBC Driver. This driver is available in Ubuntu in the libsqliteodbcpackage. If you prefer, you can download the sourceand compile it yourself.

The problem

Open a Base file of the database type that you want. Either create a new Base file using the Database Wizard, or open any existing Base file that is not read-only. Open the Calc file that contains the data to be imported to Base.

As I conduct interviews for my dissertation research, I’ve been trying to figure out an open source database for storing interview notes and keeping track of the people and organizations I’m talking to. My ideal requirements are simple:

Libreoffice slide master
  1. The format should be open source.
  2. The format should be portable and not require an underlying server (sorry MongoDB and MySQL)—this way I can save the file in an encrypted file container for IRB data protection purposes.
  3. The format should be easy to access with multiple languages (especially R and Python), ideally without external dependencies like Java.
  4. The format should be compatible with some sort of Microsoft Access-esque form GUI to allow for easy data insertion.
Sqlite

However, finding the right combination of programs and formats has been slightly more difficult. SQLite is the best format, given that it’s the most widely deployed and used database engineand is open source and has native support in both R1 and Python. The only thing it lacks is a nice form-based GUI front end.

Treiber

There are plenty of SQLiteviewers, but I haven’t found any that let you create Access-like forms. I could use Python to program my own GUI (or even get fancy and learn Swift and make a native Cocoa app), but that seems like an excessive amount of work.

LibreOffice Base has excellent support for database-backed forms, but under the hood, LibreOffice uses the Java-based HSQLDB, which does not have native R and Python support and requires older Java runtime environments.

The solution

Fortunately there’s a way to use an SQLite database as the backend for LibreOffice Base using an ODBC driver, giving the best of both worlds: an open, universal, Java-free database behind a customizable form-based GUI.

There are official instructions for doing this on Linux and Windows, but there’s nothing about doing it in OS X. So here’s that missing tutorial.

Libreoffice sqlite linux
  1. SQLite is already installed on OS X. Create a new SQLite database using sqlite3 in Terminal (or even easier, use a GUIprogram). Add some tables to it, or don’t—it doesn’t matter. You just some sort of database file.
  2. Download the SQLite ODBC driver for OS X. The page includes a link to a precompiled version (currently it says “Steve Palm kindly provided a build of version 0.9993 for MacOSX 10.{6,7,8,9,10,11} on Intel as installer package (sqliteodbc-0.9993.dmg)'). Install the driver by opening sqliteodbc-0.9993.pkg.
  3. Download an ODBC manager app. Prior to OS X 10.5, Apple included one of these, but for whatever reason they stopped with Snow Leopard. There are two that work equally well: ODBC Manager and iODBC Administrator.
  4. Open the ODBC manager/administrator app. Add a new driver using these settings:
  5. Add a new User DSN (Data Source Name). Create a new key named “database” and use the full absolute path to the SQLite database file as the value:
  6. Quit the ODBC manager. The SQLite file is now accessible in any program that uses ODBC.
  7. Open LibreOffice and create a new Base database. In the wizard, select “Connect to an existing database” and choose “ODBC”:
  8. Click next to select which ODBC database to load. If you click on “Browse…”, you should see the name of the SQLite database you set up as a DSN earlier.
  9. Click on “Finish.” LibreOffice will prompt you to save an .odf database. This is fine—it’s not actually saving the database, just the accompanying form data.2
  10. Create new tables and forms using LibreOffice:

Any time you save, all edits will occur on the SQLite file. Create a table, insert some records, and open the SQLite file in a GUI program to see all the changes. Magic!

Even though there are 10 steps, it’s not too difficult. tl;dr version: (1) install an SQLite ODBC driver, (2) install an ODBC manager, (3) use the manager to configure the SQLite ODBC driver and connect to an existing SQLite database, and (4) connect to the SQLite database through ODBC with LibreOffice Base.

Perfect!

Libreoffice sqlite extension
  1. Technically RSQLite is a separate package, but it’s a dependency of dplyr, which is as important as base R in my book. ↩︎

  2. I think… I haven’t actually checked or tested this. ↩︎

Connecting to SQLite from OpenOffice and LibreOffice using ODBC Driver for SQLite

Libreoffice Base Handbuch Pdf

The article describes how to use Apache OpenOffice and LibreOffice to access ODBC data sources using the respective driver. You can access SQLite data from Open Office Base or LibreOffice Base — desktop database management systems. Note that the Windows version of OpenOffice is 32-bit, and you may get the error “The specified DSN contains an architecture mismatch between the Driver and Application” when trying to access a data source through a 64-bit ODBC driver. To get rid of the error message, set up the 32-bit version of the driver.

To connect to an ODBC data source from OpenOffice or LibreOffice using our driver for SQLite, perform the steps below:

Libreoffice Slide

  1. Start OpenOffice or LibreOffice, click Database to open the Database Wizard.
  2. Alternatively, you can launch the Database Wizard from OpenOffice or LibreOffice Calc, Writer or any other tool by choosing File > New > Database.

  3. In the Database Wizard dialog box, click Connect to an existing database, select ODBC from the drop-down list, and click Next.
  4. Specify the name of the data source you want to connect to. You can either type the name of your data source into the field, e.g. ODBC Driver for SQLite, or you can click Browse, double-click the data source you need, and then click Next.
  5. If your database requires a user name, type it into the User name field. If you are connecting to a password protected database, check the Password required field. Alternatively, you can specify these parameters in the data source settings of your ODBC Driver for SQLite and leave these fields empty in Database Wizard.
  6. To test the connection to your data source, click Test Connection, input your credentials and click OK.

    If you have entered valid credentials, you will see a success message. Click Next to proceed to the final step.

  7. You can keep the default selection in this dialog box and click Finish.
  8. You will be prompted to give a name to your new database and select the directory where you want to store it.

  9. When the database opens, you will see the list of tables from your data source diplayed in OpenOffice or LibreOffice Base workspace. To view the data from a specific table, double-click the table name.
  10. To create an SQL query, click Queries in the Database pane, then click Create Query in SQL View…
  11. Enter your query in the query text box and click Run Query (F5). The date will be fetched from the database and displayed in Open Office or LibreOffice, respectively.

Libreoffice Slideshow Loop

© 2015-2021 Devart. All Rights Reserved.Request SupportODBC ForumProvide Feedback