Libreoffice Base Sqlite

• Libreoffice Base Sqlite Odbc
• Libreoffice Base Jdbc Sqlite

• 1Introduction
• 2Installation
• 3Setup of SQLite database
  • 3.1Creating a SQLite database file

How to export data from a LibreOffice (libre office) or open office base database table using sql and text tables to create a csv file on the Apple Mac file. Open LibreOffice main app (with white icon), go to Tools-Options-Libre Office-Advanced and on the right-hand side, click the Class Path button on the right-side of the dialog: In the dialog that.

OpenOffice Base provides functions to connect to other databases than the default HSQL database. This Tutorial should guide you through the steps to connect OpenOffice to a SQLite database file.

What is SQLite?

SQLite is a basic database engine that implements most of the features of SQL92. Unlike PostgreSQL and MySQL, SQLite stores a whole data base with all its tables a single file. Other benefits are: database access requires no database server, database files can be freely shared between machines with different byte orders and databases can be up to 2 terabytes (2⁴¹ bytes) in size. Plus it is fast (twice as much as PostgreSQL and MySQL for most operations) and has a small memory footprint.

Data management can be achieved in the following ways:

1. Via a C/C++ Linux library or Windows DLL.
2. Via an in-line program (sqlite: available under Linux and Windows) that makes it possible to create and to manage the files of data bases.
3. Via the SQLite PHP module or, if you have, PHP version 5 internally to a SQLite database.
4. Via ODBC (Linux and Windows) which allows any application supporting this standard to reach a SQLite database.
5. Using the experimental SDBC SQLite driver

This guide addresses ODBC which OpenOffice.org uses to attach to databases and SQLite, in particular.

Why use SQLite with OpenOffice.org?

The use of SQLite has many advantages:

• The data base is in only one file, in contrast to dBASE which creates a file per table, making it easy to exchange databases between various users.
• Java JRE or SDK is not required to support SQLite.
• No server is needed (local or remote) to access a database.
• ODBC drivers exist for *NIX and for Windows, thus enabling users of both environments to access data and exchange them.
• A new SDBC driver (native to OpenOffice.org) is available for experimentation.
• Sophisticated graphic interfaces, such as those of msaccess, can be easily developed using the forms, queries, states and macros of OpenOffice.org. These interfaces are stored separately in OpenOffice .odb files and can be exchanged independent from the data between users under UNIX or Windows.

and some disadvantages:

• Modifying the structure of a SQLite table after its creation is not directly possible. This means that you cannot easily add fields nor to modify their properties once that the table is created. Therefore you must prepare the structure of your tables before creating them.
• OpenOffice.org requires the use of the ODBC driver for accessing a SQLite database thus making this operation somewhat complicated. First it must be declared in ODBC and then in OpenOffice.org. The native SDBC driver should correct this situation when it is production ready.
• The initial database file cannot be created with OpenOffice.org but by using other tools.

In short, the principal advantages of the use of SQLite with OpenOffice.org are the format of data storage and the portability from one environment to another. In the list of disadvantages, the two last are not specific to SQLite, but are requirements of using ODBC. However, these are not as awkward as they do not take place during the initial creation of the database.

Unix ODBC Platform

Connection to a SQLite database will be established through UnixODBC. ODBC is an open specification to provide applications with a consistent interface to a variety of data sources. UnixODBC is an implementation of this specification for non-Windows platforms.

You can install unixodbc with your distribution's package manager, or download the sources from the UnixODBC website. The unixodbc-bin package provides a GUI application for configuration which is will be used below. Under Ubuntu or similar Linux distributions you can use the command:

SQLite

Obviously you will need to install SQLite if it is not installed already as this will be the database you want to connect to.

You can install SQLite with your distributions package manager. The package should be called sqlite or sqlite3. Under Ubuntu:

If you prefer, you can always download and install the latest version from source.

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 libsqliteodbc package.

If you prefer, you can download the source and compile it yourself. You will need the unixodbc-dev and libsqlite3-dev packages installed for it to build successfully.

To complete the installation the driver has to be integrated into unixODBC. To do this, launch the ODBCConfig utility as root in an terminal window:

Now the a GUI to manage the configuration shows up.

Libreoffice Base Sqlite Odbc

Click the Drivers tab and choose Add... Then fill the fields as follows:

Then confirm using the checkmark icon on the left in top of the window. Your new driver will now appear in the Drivers tab:

The driver installation under Linux is now finished and you can exit ODBCConfig.

For those who do not have ODBCConfig or who prefer to carry out this configuration manually, it is necessary to edit the file /etc/odbcinst.ini as root and add the following lines:

Under Windows

Creating a SQLite database file

Libreoffice Base Jdbc Sqlite

An initial database file cannot be created directly in OpenOffice.org but rather this is accomplished by using either the utility 'sqlite' on the command line or by using a graphical management tool, a list of GUIs is available at SQLite.org

The utility 'sqlite' should be already installed if you use Linux and have installed SQLite from rpm. For other *NIX systems, you must build SQLite from source which you can find at [1]

Performance and usage under Linux and Windows is identical.

Using sqlite

If this database file does not exist yet it will be created automatically. Using sqlite you can also create tables and modify your database with standard SQL-commands:

OpenOffice Base provides functions to connect to other databases than the default HSQL database. This Tutorial should guide you through the steps to connect OpenOffice to a SQLite database file.

What is SQLite?

SQLite is a basic database engine that implements most of the features of SQL92. Unlike PostgreSQL and MySQL, SQLite stores a whole data base with all its tables a single file. Other benefits are: database access requires no database server, database files can be freely shared between machines with different byte orders and databases can be up to 2 terabytes (2⁴¹ bytes) in size. Plus it is fast (twice as much as PostgreSQL and MySQL for most operations) and has a small memory footprint.

Data management can be achieved in the following ways:

1. Via a C/C++ Linux library or Windows DLL.
2. Via an in-line program (sqlite: available under Linux and Windows) that makes it possible to create and to manage the files of data bases.
3. Via the SQLite PHP module or, if you have, PHP version 5 internally to a SQLite database.
4. Via ODBC (Linux and Windows) which allows any application supporting this standard to reach a SQLite database.
5. Using the experimental SDBC SQLite driver

This guide addresses ODBC which OpenOffice.org uses to attach to databases and SQLite, in particular.

Why use SQLite with OpenOffice.org?

The use of SQLite has many advantages:

• The data base is in only one file, in contrast to dBASE which creates a file per table, making it easy to exchange databases between various users.
• Java JRE or SDK is not required to support SQLite.
• No server is needed (local or remote) to access a database.
• ODBC drivers exist for *NIX and for Windows, thus enabling users of both environments to access data and exchange them.
• A new SDBC driver (native to OpenOffice.org) is available for experimentation.
• Sophisticated graphic interfaces, such as those of msaccess, can be easily developed using the forms, queries, states and macros of OpenOffice.org. These interfaces are stored separately in OpenOffice .odb files and can be exchanged independent from the data between users under UNIX or Windows.

and some disadvantages:

• Modifying the structure of a SQLite table after its creation is not directly possible. This means that you cannot easily add fields nor to modify their properties once that the table is created. Therefore you must prepare the structure of your tables before creating them.
• OpenOffice.org requires the use of the ODBC driver for accessing a SQLite database thus making this operation somewhat complicated. First it must be declared in ODBC and then in OpenOffice.org. The native SDBC driver should correct this situation when it is production ready.
• The initial database file cannot be created with OpenOffice.org but by using other tools.

In short, the principal advantages of the use of SQLite with OpenOffice.org are the format of data storage and the portability from one environment to another. In the list of disadvantages, the two last are not specific to SQLite, but are requirements of using ODBC. However, these are not as awkward as they do not take place during the initial creation of the database.

Unix ODBC Platform

Connection to a SQLite database will be established through UnixODBC. ODBC is an open specification to provide applications with a consistent interface to a variety of data sources. UnixODBC is an implementation of this specification for non-Windows platforms.

You can install unixodbc with your distribution's package manager, or download the sources from the UnixODBC website. The unixodbc-bin package provides a GUI application for configuration which is will be used below. Under Ubuntu or similar Linux distributions you can use the command:

SQLite

Obviously you will need to install SQLite if it is not installed already as this will be the database you want to connect to.

You can install SQLite with your distributions package manager. The package should be called sqlite or sqlite3. Under Ubuntu:

If you prefer, you can always download and install the latest version from source.

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 libsqliteodbc package.

If you prefer, you can download the source and compile it yourself. You will need the unixodbc-dev and libsqlite3-dev packages installed for it to build successfully.

To complete the installation the driver has to be integrated into unixODBC. To do this, launch the ODBCConfig utility as root in an terminal window:

Now the a GUI to manage the configuration shows up.

Libreoffice Base Sqlite Odbc

Click the Drivers tab and choose Add... Then fill the fields as follows:

Then confirm using the checkmark icon on the left in top of the window. Your new driver will now appear in the Drivers tab:

The driver installation under Linux is now finished and you can exit ODBCConfig.

For those who do not have ODBCConfig or who prefer to carry out this configuration manually, it is necessary to edit the file /etc/odbcinst.ini as root and add the following lines:

Under Windows

Creating a SQLite database file

Libreoffice Base Jdbc Sqlite

An initial database file cannot be created directly in OpenOffice.org but rather this is accomplished by using either the utility 'sqlite' on the command line or by using a graphical management tool, a list of GUIs is available at SQLite.org

The utility 'sqlite' should be already installed if you use Linux and have installed SQLite from rpm. For other *NIX systems, you must build SQLite from source which you can find at [1]

Performance and usage under Linux and Windows is identical.

Using sqlite

If this database file does not exist yet it will be created automatically. Using sqlite you can also create tables and modify your database with standard SQL-commands:

Using sqlite on the command line program makes it possible to create tables and indices, to enter and post data and to make queries provided you know the SQL language - however, it is more practical for beginners to carry out these operations later on usingOpenOffice.

Using SQLite Database Browser

There is also a graphical utility for creating and manipulating SQLite databases called sqlitebrowser.You can install this using your distribution's package manager.

After unpacking the file, launch sqlitebrowser and select File/New Database or the corresponding icon:

Choose the directory in which to place file and then enter name of the database file and click on Save.

Using this program, you can also create the tables, the fields and the indices, to access data and to visualize them. Whilesqlitebrowswer is suitable for these actions, there is the disadvantage that it does not show the full list of field types duringfield creation. In this case, it may be preferable to do it later using OpenOffice.org.

ODBC-setup of new database

After creating a new SQLite database above you now have to configure this database in ODBC so you can connect to it in OpenOffice later on.

You had to spend a lot of time installing and configuring other things but finally we can move on to OpenOffice. Only a few more configuration windows and you will be done.

Start OpenOffice Base and in the first step 1. Select database chose the third option, connect to an existing database using ODBC.

In the second step 2. Set up ODBC connection you can select your SQLite database. Clicking on Browse you should get a list of all databases you have configured in ODBC.

If nothing is showing up here you have a problem. Check that you actually set up the database in ODBC and that the drivers are properly configured as described.

The rest is OpenOffice Base just as you know it.

Only note a few things:Creating new tables using OpenOffice is possible. Those tables - along with all data you enter in them - are saved in the external SQLite database file to which you connected your OpenOffice .odb file.

Fields defined as type 'integer' are auto-increment, that is they automatically increment the value of the field when adding a new record in this table.

This rather awkward constraint is specific to SQLite, which does not handle the SQL command ALTER COUNTS, and not the ODBC driver. This limitation is designed in by the authors in order to make the basic database engine as light as possible. However, this limitation may be eliminated in a future version. Therefore you are advised to check your table structure very carefully before recording it.

If you must modify a table, the only solution is to create a new table and save it under different name. By doing do, you can transfer the data from the original table to the new table.

Creating and using Forms, Queries and Reports are all independent of the datasource you use. They will function just as with the default integrated HSQL-database and will still be saved in your odb-file. For more information on these topics refer to the other documentation.

Thanks to the availability of driver sqliteODBC driver for both Linux and Windows (just as for OpenOffice.org, of course!), a SQLitedatabase SQLite can be easily exchanged between users of the two environments. It is necessary that each has an installed ODBC driver that is aware of the target SQLite file and which is defined as a data source in OpenOffice.org. Likewise OpenOffice Base files (.odb) containing forms and macros can also be exchanged between users of the two environments.