Difference between revisions of "Documentation/How Tos/Using SQLite With OpenOffice.org"

From Apache OpenOffice Wiki
Jump to: navigation, search
 
(41 intermediate revisions by 8 users not shown)
Line 1: Line 1:
{{Documentation/BeingEdited}}
 
 
 
=Introduction=
 
=Introduction=
 
+
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 [http://www.sqlite.org/ SQLite] database file.
The aim of this guide is to help OpenOffice.org users to take advantage of the SQLite database engine as a data source.
+
  
 
==What is SQLite?==
 
==What is SQLite?==
Line 14: Line 11:
  
 
Data management can be achieved in the following ways:   
 
Data management can be achieved in the following ways:   
# Via a C/C++ Linux library or Windows DLL.
+
# Via a C/C++ Linux library or Windows DLL.
# 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.   
+
# 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.   
 
# Via the SQLite PHP module or, if you have, PHP version 5 internally to a SQLite database.   
 
# Via the SQLite PHP module or, if you have, PHP version 5 internally to a SQLite database.   
 
# Via ODBC (Linux and Windows) which allows any application supporting this standard to reach a SQLite database.
 
# Via ODBC (Linux and Windows) which allows any application supporting this standard to reach a SQLite database.
# Using the [http://dba.openoffice.org/drivers/sqlite/index.htm experimental SDBC SQLite driver]
+
# Using the [http://dba.openoffice.org/drivers/sqlite/index.html experimental SDBC SQLite driver]
  
 
This guide addresses ODBC which OpenOffice.org uses to attach to databases and SQLite, in particular.
 
This guide addresses ODBC which OpenOffice.org uses to attach to databases and SQLite, in particular.
Line 26: Line 23:
 
The use of SQLite has many advantages:   
 
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.
+
* 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.
 
* Java JRE or SDK is not required to support SQLite.
* No server is needed (local or remote) to access a database.
+
* 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.
+
* ODBC drivers exist for *NIX and for Windows, thus enabling users of both environments to access data and exchange them.
 
* A [http://dba.openoffice.org/drivers/sqlite/index.html new SDBC driver] (native to OpenOffice.org) is available for experimentation.
 
* A [http://dba.openoffice.org/drivers/sqlite/index.html 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 separate and can also be exchanged between users under UNIX or Windows.
+
* 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:
 
and some disadvantages:
Line 41: Line 38:
 
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.   
 
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.   
  
{{Documentation/Note|SQLite appeals to anyone frustrated with using MDB files under Linux, or who has had difficulty installing MySQL or other RDBMS server on their workstation.}}
+
{{Note|SQLite appeals to anyone frustrated with using MDB files under Linux, or who has had difficulty installing MySQL or other RDBMS server on their workstation.}}
  
=Installing the ODBC Driver for SQLite=
 
  
For OpenOffice.org to access a SQLlite database in production, it is recommened that you install the ODBC driver.
+
=Installation=
ODBC drivers for SQLite are independent of the SQLite project.  However you can obtain a driver specifically developed by [mailto:chw@ch-werner.de Christian Werner] for OpenOffice.org [http://www.ch-werner.de/sqliteODBC/ from his site]. This ODBC driver wraps the SQLite library and is available for Linux and Windows (for other UNIX, use the sources).
+
  
{{Documentation/Linux}}
+
{{Lin|'''Under Linux'''}}
==Under Linux==
+
  
In order to be able to install the SQLite ODBC driver, unixODBC and SQLite must be installed first.
+
== Unix ODBC Platform ==
 +
Connection to a SQLite database will be established through [http://www.unixodbc.org/ 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.
  
===UnixODBC===
+
You can install <tt>unixodbc</tt> with your distribution's package manager, or download the sources from the [http://www.unixodbc.org/ UnixODBC website]. The <tt>unixodbc-bin</tt> package provides a GUI application for configuration which is will be used below.  Under Ubuntu or similar Linux distributions you can use the command:
You will find most of the needed packages pre-installed in the majority of Linux distributions. For Mandrake, for example, install the following rpm files:
+
  
  libunixODBC2-2.2.x-xmdk.i586.rpm
+
  sudo apt-get install unixodbc unixodbc-bin
libunixODBC2-devel-2.2.x-xmdk.i586.rpm
+
libunixODBC2-qt-2.2.x-xmdk.i586.rpm
+
unixODBC-2.2.x-xmdk.i586.rpm
+
unixODBC-GUI-qt-2.2.x-xmdk.i586.rpm.
+
  
{{Documentation/Caution|Check that your version of unixODBC is at least version 2.2.5.  An older version will cause OpenOffice.org to hang when using the ODBC for SQLite driver. If your version is one of the older versions which is the case for Debian or Mandrake 9.1, it will be necessary for you to download and install a more recent version of unixODBC. If you do not find packages that are sufficiently recent in your distribution, [http://www.unixodbc.org/ download and compile the sources].}}
+
==SQLite==
 +
Obviously you will need to install [http://www.sqlite.org SQLite] if it is not installed already as this will be the database you want to connect to.  
  
{{Documentation/Caution|Compiling from the sources does not install the graphic utility ODBCConfig. Thus you will have to manually configure ODBC.}}
+
You can install SQLite with your distributions package manager. The package should be called <tt>sqlite</tt> or <tt>sqlite3</tt>. Under Ubuntu:
  
===SQLite===
+
sudo apt-get install sqlite3
  
Before starting, users of Mandrake, RedHat and other distributions using rpm distribution, should check
+
If you prefer, you can always [http://www.sqlite.org/download.html download] and install the latest version from source.
that the distribution does not currently have the SQLite rpm already installed.
+
  
SQLite evolves very quickly and the packages present in any Linux distribution often provide a
+
== SQLite ODBC Driver ==
version of SQLite which is dated, (for example Mandrake 10.0 integrates SQLite 2.8.6). At the time of writing, the latest
+
For SQLite to work through the ODBC standards you will also need to install and configure the [http://ch-werner.de/sqliteodbc/ SQLite ODBC Driver].
versions of the ODBC driver may include a more recent version of SQLite (2.8.13 for version 0.6.2 of the driver); however,
+
This driver is available in Ubuntu in the <tt>libsqliteodbc</tt> package.
it is to better to get and install a more recent version than that in your distribution. In theory, the SQLite driver for
+
ODBC can be compiled with SQLite 2.8.6, but the using this version will introduce some limitations , such as no support
+
auto-incremented fields.
+
  
[http://www.sqlite.org/download.html Download] and install the packages <tt>sqlite-x.x.x-x.i386.rpm</tt> and <tt>sqlite-devel-x.x.x-x.i386.rpm</tt>". This will also install the commandline version of "<tt>sqlite</tt>.
+
If you prefer, you can [http://ch-werner.de/sqliteodbc/ download the source] and compile it yourself.  You will need the <tt>unixodbc-dev</tt> and <tt>libsqlite3-dev</tt> packages installed for it to build successfully.
  
  
For Mandrake (9.1, 9.2 or 10.0) users, the installation of these rpm will issue warnings that
+
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:
<tt>libreadline.so.4</tt> is missing even though it is installed. This is because the rpm referred to above does not look
+
for this library where Mandrake installs it. Check initially with <tt>rpmdrake</tt> that the library <tt>libreadline4</tt>
+
is installed (if not install it), then install the rpm <tt>sqlite</tt> from the commandline and force the no dependencies check:
+
  
  $ rpm -ivh sqlite-devel-2.8.x-x.i386.rpm –nodeps<br>
+
  sudo ODBCConfig
$ rpm -ivh sqlite-2.8.x-x.i386.rpm --nodeps
+
  
 +
{{Note|If ODBCConfig is not found check that you also installed the <tt>unixODBC-bin</tt> package while installing the [[#ODBC_drivers|ODBC drivers]].}}
  
If you have Debian or another distribution that will not install the rpm, [download http://www.sqlite.org/download.html http://www.sqlite.org/download.html] <tt>sqlite-x.x.x.tar.gz</tt> and compile the sources.
+
Now the a GUI to manage the configuration shows up.
 +
[[Image:doc_howto_sqlite_odbcadmin.png|390px]]
  
=== sqliteODBC===
+
Click the <tt>Drivers</tt> tab and choose <tt>Add...</tt> Then fill the fields as follows:
  
Since there are no precompiled binary packages (RPM or other) of <tt>sqliteODBC</tt>, [http://www.ch-werner.de/sqliteODBC/ download the source code from the author's site]. Download a version at least equal to the 0.6.1, to avoid limitations with OpenOffice.org. If you have
+
Name: SQLite
downloaded the <tt>.tar.gz</tt>, uncompress and unpack it using <tt><nowiki>tar xvzf <filename></nowiki></tt>, which will create a directory
+
Description: SQLite for ODBC
<tt>sqliteODBC-0.6.x</tt> containing all the files. Change into this directory and type the following commands for compilation and installation:
+
Driver: /usr/lib/libsqlite3odbc.so
 +
Driver64: /usr/lib64/libsqlite3odbc.so
 +
Setup: /usr/lib/libsqlite3odbc.so
 +
Setup64: /usr/lib64/libsqlite3odbc.so
  
$ ./configure –prefix=/usr
+
{{Note|If you are still using a 2.8.x version of libsqlite the driver names will be different. Try browsing the folder by clicking on the arrow at the right and select one that seems suitable.}}
$ make
+
$ su
+
$ make install
+
 
+
<tt>--prefix=/usr</tt> is optional, but is especially useful with Mandrake distributions, to install the driver in <tt>/usr/lib</tt> rather than in the default location, <tt>/usr/local/lib</tt>.
+
 
+
{{Documentation/Note|Certain dependences are necessary to the compilation of the driver. For example, if compilation fails by saying that <tt>ltdl.h</tt> is not found, it will be necessary for you to install the corresponding library/development package (<tt>libltdl3-devel</tt> under Mandrake) before restarting the compilation process.}}
+
 
+
To complete installation, the driver should be integrated into <tt>unixODBC</tt>. To do this, launch the <tt>ODBCConfig</tt>
+
utility as root in an open terminal window or xterm and type what follows:
+
 
+
$ su
+
$ ODBCConfig
+
 
+
This command brings up the following window:
+
 
+
[[Image:doc_howto_sqlite_odbcadmin.png]]
+
 
+
Click the <tt>Drivers</tt> tab and choose <tt>Add...</tt> Then fill the fields in the property window exactly as on the following graphic:
+
 
+
 
+
[[Image:doc_howto_sqlite_driverproperties.png]]
+
  
 
Then confirm using the checkmark icon on the left in top of the window. Your new driver will now appear in the <tt>Drivers</tt> tab:
 
Then confirm using the checkmark icon on the left in top of the window. Your new driver will now appear in the <tt>Drivers</tt> tab:
 
+
[[Image:doc_howto_sqlite_odbcadmin-2.png|450px]]
[[Image:doc_howto_sqlite_odbcadmin-2.png]]
+
  
 
The driver installation under Linux is now finished and you can exit <tt>ODBCConfig</tt>.
 
The driver installation under Linux is now finished and you can exit <tt>ODBCConfig</tt>.
 +
  
 
For those who do not have <tt>ODBCConfig</tt> or who prefer to carry out this configuration manually, it is necessary to edit the file <tt>/etc/odbcinst.ini</tt> as root and add the following lines:
 
For those who do not have <tt>ODBCConfig</tt> or who prefer to carry out this configuration manually, it is necessary to edit the file <tt>/etc/odbcinst.ini</tt> as root and add the following lines:
Line 132: Line 98:
 
  Description<nowiki>= ODBC for SQLite</nowiki>''
 
  Description<nowiki>= ODBC for SQLite</nowiki>''
 
  Driver<nowiki>= /usr/lib/libsqliteODBC.so</nowiki>''
 
  Driver<nowiki>= /usr/lib/libsqliteODBC.so</nowiki>''
 +
Driver64<nowiki>= /usr/lib64/libsqliteODBC.so</nowiki>''
 
  Setup<nowiki>= /usr/lib/libsqliteODBC.so</nowiki>''
 
  Setup<nowiki>= /usr/lib/libsqliteODBC.so</nowiki>''
 +
Setup64<nowiki>= /usr/lib64/libsqliteODBC.so</nowiki>''
 
  FileUsage<nowiki>= 1</nowiki>''
 
  FileUsage<nowiki>= 1</nowiki>''
 
  CPTimeout<nowiki>= </nowiki>''
 
  CPTimeout<nowiki>= </nowiki>''
 
  CPReuse<nowiki>= </nowiki>''
 
  CPReuse<nowiki>= </nowiki>''
  
If you are comfortable compiling, the easy way is to [http://www.chwerner.de/sqliteodbc/ get the source rpm] of <tt>sqliteODBC</tt>. Once you have the srpm, it is easy to create a rpm for your distribution by typing the following instruction:
 
 
$ rpmbuild --rebuild sqliteODBC-0.60-1.src.rpm
 
 
You now should have a pre-compiled binary rpm ready for installation that, according to the author, self-configures the driver in ODBC thus avoiding the above manual procedures.
 
 
{{Documentation/Windows}}
 
 
==Under Windows==
 
==Under Windows==
 +
{{Win|Under Windows the installation and configuration is much easier. Simply [http://ch-werner.de/sqliteodbc/ download the sqliteODBC.exe], the installer will automatically setup the ODBC drivers including the SQLite configurations for it and also provides you with the sqlite command line tool to create and modify SQLite databases.}}
  
Under Windows installation and configuration is easier. Simply download the file <tt>sqliteODBC.exe</tt> and execute it.
+
=Setup of SQLite database=
The driver installs automatically and is also configured automatically in ODBC. You will be able to check it by
+
launching <tt>data Source ODBC</tt> from the control panel:
+
 
+
[[Image:doc_howto_sqlite_odbcadmin-win.png]]
+
 
+
{{Documentation/Note|A second, experimental driver is installed (Utf-8) but you are cautioned to only use standard driver.}}
+
 
+
= Creating a Data File =
+
 
+
  
An initial database file cannot be created directly in OpenOffice.org but, rather, this is accomplished by using either the utility “<tt>sqlite</tt>” on the command line, or by using the tool <tt>sqlitebrowser</tt> which is graphic, very simple and available for both Linux and for Windows. If you wish to use <tt>sqlitebrowser</tt>, you will find it as the third entry in the list at: [http://www.sqlite.org/cvstrac/wiki?p=SqliteTools http://www.sqlite.org/cvstrac/wiki?p=SqliteTools,]
+
==Creating a SQLite database file==
 +
An initial database file cannot be created directly in OpenOffice.org but rather this is accomplished by using either the utility “<tt>sqlite</tt>” on the command line or by using a graphical management tool, a list of GUIs is available at [http://www.sqlite.org/cvstrac/wiki?p=ManagementTools SQLite.org]
  
The utility "<tt>sqlite</tt>" 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 [http://www.sqlite.org/download.html http://www.sqlite.org/download.html] .
+
The utility "<tt>sqlite</tt>" 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 [http://www.sqlite.org/download.html]
  
 
Performance and usage under Linux and Windows is identical.
 
Performance and usage under Linux and Windows is identical.
 
   
 
   
== Using <tt>sqlite</tt> ==
+
===Using sqlite===
 +
{{Lin|Open a terminal window and change to the directory where you want to create your database. Run <tt>sqlite</tt> with the filename of your database as argument (e.g. ''$ sqlite mydatabase''}}
 +
{{Win|Run <tt>sqlite</tt> from the start menu or where it was installed. A standard file open dialog box will open and ask you to select a database file.}}
 +
If this database file does not exist yet it will be created automatically. Using <tt>sqlite</tt> you can also create tables and modify your database with standard SQL-commands:
  
Open a terminal window or xterm and change to the directory where you want to create your database and then enter the following instructions:
+
  <nowiki>$ sqlite <filename of database></nowiki>
 
+
  <nowiki>$ sqlite <name of the database></nowiki>
+
 
  SQLite version 2.8.12
 
  SQLite version 2.8.12
 
  Enter ".help" for instructions
 
  Enter ".help" for instructions
  sqlite> .database
+
  sqlite> CREATE TABLE mytable (id INT, text VARCHAR(100));
 
  sqlite> .exit
 
  sqlite> .exit
  
You can then check that the file was, indeed, created in your filesystem. The name of the file does not require a file extension, but you can give it the extension <tt>db</tt> so that is easy to locate as a database.
+
{{Note|You can then check that the file was, indeed, created in your filesystem. The name of the file does not require a file extension, but you can give it the extension <tt>db</tt> so that is easy to locate as a database.}}
  
Using <tt>sqlite</tt> on the command line program also makes it possible to create tables and indices, to enter and post data, and  
+
Using <tt>sqlite</tt> on the command line program makes it possible to create tables and indices, to enter and post data and  
to make queries providing you know the SQL language ; however, it is more practical for beginners to carry out these operations using
+
to make queries provided you know the SQL language - however, it is more practical for beginners to carry out these operations later on using
OpenOffice.org.
+
OpenOffice.
  
==Using SQLite Database Browser==
+
===Using SQLite Database Browser===
 +
There is also a graphical utility for creating and manipulating SQLite databases called <tt>sqlitebrowser</tt>.
 +
You can install this using your distribution's package manager.
  
 
After unpacking the file, launch <tt>sqlitebrowser</tt> and select <tt>File/New Database</tt> or the corresponding icon:
 
After unpacking the file, launch <tt>sqlitebrowser</tt> and select <tt>File/New Database</tt> or the corresponding icon:
  
 
+
[[Image:doc_howto_sqlite_dbbrowser.png|410px]]
[[Image:doc_howto_sqlite_dbbrowser.png]]
+
  
 
Choose the directory in which to place file and then enter name of the database file and click on <tt>Save</tt>.
 
Choose the directory in which to place file and then enter name of the database file and click on <tt>Save</tt>.
Line 192: Line 148:
 
field creation. In this case, it may be preferable to do it later using OpenOffice.org.
 
field creation. In this case, it may be preferable to do it later using OpenOffice.org.
  
=Configuring ODBC=
+
==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.
  
This step demonstrates how to define your new database in ODBC and to set that the driver to be used should be <tt>SQLite</tt> so
 
that OpenOffice.org can access it. This operation has to be repeated for each new <tt>SQLite</tt> database that must be accessed
 
through ODBC.
 
  
{{Documentation/Linux}}
+
{{Lin|'''Under Linux:'''
==Under Linux==
+
Launch <tt>ODBCConfig</tt> again as normal user and go to the tab <tt>User DSN</tt>. Here add a new entry. A window appears in which you must choose
 +
the <tt>SQLite</tt> driver you have configured earlier. In the next window, as shown below, you enter a name for this connection (for example the name of your database) and the complete access path to the database file which you created previously.
  
Launch <tt>ODBCConfig</tt>, go in the tab <tt>User DSN</tt> and choose <tt>Add...</tt>. A window appears then in which you must choose
+
[[Image:doc_howto_sqlite_driverproperties-2.png|380px]]
the <tt>SQLite</tt> driver. In the next window, as shown below, you enter a name for this connection (for example the name of
+
your database) and enter the complete access path to the file which you created previously.
+
 
+
[[Image:doc_howto_sqlite_driverproperties-2.png]]
+
  
 
Validate the data on the screen, click the checkmark and you should see your new data source in the tab <tt>User DSN</tt>.
 
Validate the data on the screen, click the checkmark and you should see your new data source in the tab <tt>User DSN</tt>.
  
{{Documentation/Caution|: If in the place of this window you obtain an error message ("Could not construct has property list for (SQLite)"), your version of the driver is too old. Install version 0.6.1, or higher, which corrects this bug.}}
 
  
 
If you do not have <tt>ODBCConfig</tt> or simply prefer to configure manually, launch your favorite editor, open (or create)  
 
If you do not have <tt>ODBCConfig</tt> or simply prefer to configure manually, launch your favorite editor, open (or create)  
Line 221: Line 171:
 
  StepAPI<nowiki>= No</nowiki>
 
  StepAPI<nowiki>= No</nowiki>
  
{{Documentation/Note|If this data source must by other users on the system, launch <tt>ODBCConfig</tt> as root and add this source in the tab <tt>System DSN</tt> or <tt>data Source system</tt>, or create a <tt>/etc/odbc.ini</tt> file.}}
+
{{Note|If this data source must by other users on the system, launch <tt>ODBCConfig</tt> as root and add this source in the tab <tt>System DSN</tt> or <tt>data Source system</tt>, or create a <tt>/etc/odbc.ini</tt> file.}}
  
You are now ready to access your data base with OpenOffice.org!
+
You are now ready to access your data base with OpenOffice.org!}}
:
+
{{Documentation/Windows}}
+
== Under Windows ==
+
  
Open <tt>data Source ODBC</tt> and go to the tab <tt>Source data user</tt>. Choose <tt>To add...</tt> and start by selecting the driver
+
{{Win|'''Under Windows:'''
<tt>SQLite ODBC Driver</tt>. In the following window, enter the name of this data source (<tt>Data Source Name</tt>) and the complete
+
You will reach the configuration through the OpenOffice dialog in the next step, just read on.}}
access path to your data file (<tt>Database Name</tt>), or select it by using the <tt>Browse...</tt>. button
+
  
Validate the data and exit ODBC. Nothing more is needed and you may launch OpenOffice.org and be able to reach your database!
 
  
[[Image:doc_howto_sqlite_odbcconfig.png]]
+
=Connecting OpenOffice to a SQLite database=
 +
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.
  
= Configuring the Data Source in OpenOffice.org =
+
Start ''OpenOffice Base'' and in the first step '''1. Select database''' chose the third option, connect to an existing database using ODBC.
  
Now you can finally start using OpenOffice.org with SQLite. In order to make use of your new SQLite database and to add tables,
+
Connect to an existing database
it is necessary to configure the <tt>data source</tt>. To do this, use the menu '''Tools > Data Sources...'''.
+
  ODBC
  
In the window shown below, click on <tt>New data source</tt>:
+
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 [[#ODBC-setup of new database|configured]] in ODBC.
  
[[Image:doc_howto_sqlite_datasourceadmin.png]]
+
If nothing is showing up here you have a problem. Check that you actually [[#ODBC-setup of new database|set up the database in ODBC]] and that the drivers are properly configured as described.
  
* In the <tt>Name</tt> field, replace <tt>Bibliography</tt> with the name of your data source.
+
{{Win|'''Under Windows:'''
* In <tt>Database type</tt>, select <tt>ODBC</tt>.
+
Before you will find your database listed here you need to register it with ODBC as mentioned above. Click on ''Organize...'' in the bottom right of the window.
* For <tt>Data source URL</tt>, select the button with the three dots (on the right-hand side of the field). This opens a window in which you see all the data sources already declared in ODBC. Choose that which you created in the preceding chapter.
+
  
Voilà, your SQLite database is now accessible from OpenOffice.org!
+
[[Image:doc_howto_sqlite_odbcdriver-windows-1.png]]
  
Now click on <tt>Apply</tt> without leaving this window, so that we can create the tables and the fields of the database as described in the next section.
 
  
= Creating Database Tables in OpenOffice.org =
+
In the ''User DSN'' (or the ''System DSN'') tab click on ''Add...'' to setup your database file.
  
In the window, <tt>Data Source Administration</tt>, go in the tab <tt>Tables</tt>. To create your first table, select <tt>New</tt>
+
[[Image:doc_howto_sqlite_odbcdriver-windows-2.png]]
icon in <tt>Table Designer</tt> to get a window in which create the fields for the new table:
+
  
  
[[Image:doc_howto_sqlite_tabledesignbiblio.png]]
+
Give your database a meaningful name as ''Data Source Name'' - this will be the name you find your database listed as. For ''Database Name'' browse to the SQLite database file you have created earlier. Leave the rest on its default values and press ok.  
  
# Enter the name of the new field in the <tt>Field Name</tt> column.
+
[[Image:doc_howto_sqlite_odbcdriver-windows-3.png]]
# Select its field type from the drop-down list.
+
# In the <tt>Field Properties</tt> zone, set the <tt>Length</tt> as necessary, default value of this field and choose the format.
+
# Repeat steps 1-3 for all fields.
+
  
{{Documentation/Caution|The creation of a primary key on a field type of integer is available only with version 0.6.1, or later, of sqliteODBC driver. Primary keys are useful in assuring that OpenOffice.org can add records to your table. In a table, the first field is often a unique identifier (id) that can be defined as the primary key.}}
 
  
Fields defined as type "integer", automatically increment the value of the field at the addition of each new record in this table,
+
The database will now be listed in the ''ODBC Data Source Administrator'' and leaving this window you should now also be able to select this database in the ''OpenOffice Base'' dialog.
 +
}}
  
Once you have created all your fields, checked that all is good and selected your primary key, save your new table by clicking on
 
the Save icon and enter the name of your table
 
  
{{Documentation/Caution|Once you have saved your table, you cannot modify it! I.e. you will not be able to modify the properties of the fields any more, nor add or remove!}}
 
  
This rather awkward constraint is specific to SQLite, which does not handle the SQL command <tt>ALTER COUNTS</tt>, 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
+
=Working on the database with OpenOffice=
transfer the data from the original table to the new table.
+
The rest is ''OpenOffice Base'' just as you know it.
  
Once a table is created, you can create an index to speed up queries and sorting on large volumes of data. To do this, select your
+
Only note a few things:
table from the tab <tt>Tables</tt> in the <tt>Data Source Administration</tt> window and click on the icon "To publish the table".
+
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.
You are again in the window which posts the fields of your table. Select the fourth icon then "Outlines index..." and you will obtain
+
the following window:
+
  
[[Image:doc_howto_sqlite_indexes.png]]
+
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.
  
Click on the first icon "New index" and enter a name for this index. In the column <tt>Index Field</tt>, choose the field(s) to be indexed, the order of sorting, and check the <tt>Single</tt> box. Save this by clicking on the fourth icon <tt>To record the active index</tt>.
+
{{Warn|Once you have saved your table, you cannot modify it! I.e. you will not be able to modify the properties of the fields any more, nor add or remove!}}
  
{{Documentation/Caution|When saving the index, you may see an error message, "<tt>Error connecting to the data source</tt>". To check if the message is valid, exit the index window then the table window. Re-open the index window, and check that your index is created in spite of the error/ You may find that it is but that the "<tt>Single</tt>" box is no longer checked. This is a known incompatibility between the sqliteODBC driver and the way in which OpenOffice.org dispatches a create index with ODBC! This problem has been identified and a patch already proposed that should appear in the next version of OpenOffice.org. Where you absolutely must create an index, use "<tt>sqlite</tt>" or "<tt>sqlitebrowser</tt>" which do so with no problems.}}
+
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.
  
Now your database structure is created and you can use OpenOffice.org to connect, add data et cetera.
+
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.  
  
= Using a Database with OpenOffice.org =
 
  
This chapter is not specific to SQLite databases but its purpose is to succinctly present the various existing possibilities and  
+
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 [[Base|other documentation]].
how thy relates to the limitations and possibilities of SQLite.
+
  
== Accessing databases via Data Sources ==
+
{{Warn|If you want to use subforms you need to activate "parameter substitution" first.
 +
Select "Edit/Database/Advanced Settings..." in the main menu of your OpenOffice Base window. There, in the "Special Settings" tab check the option "Replace named parameters with '?'". You might need to restart OpenOffice but then you should be able to use subforms without problems.}}
  
The simplest method to access your databases in OpenOffice.org is to open Data Sources dialogue using the icon on the Main toolbar
+
=Exchanging databases between users=
which is the vertical bar on the left in Writer and Calc.
+
 
+
[[Image:doc_howto_sqlite_writerwithdb.png]]
+
 
+
You can find the name of your database, named as you declared it in OpenOffice.org, in the left part of the panel by traversing the tree
+
structure while clicking on "<tt>+</tt>". You will find a heading "<tt>Tables</tt>" from which you can select your table and have the
+
contents displayed in the panel to the right. In this panel, you will be able to modify each record directly in the table, or to create
+
new records by adding them in the line with the "<tt><nowiki>*</nowiki></tt>" symbol.
+
 
+
{{Documentation/Note|If the first field of your table is of type <tt>integer</tt><nowiki> and you defined it as the primary key, it will
+
contain <tt><auto-increment></tt> provided you have not selected the complete line (i.e. You pressed "<tt>Enter</tt>"after each field).
+
This means that this field will take the value of the preceding record's auto-increment field plus 1.}}
+
 
+
{{Documentation/Caution|This feature of auto-incrementing primary keys with type "integer" is not possible unless you use a version of the sqliteODBC driver that is, at least, version 0.6.0 or higher.}}
+
 
+
 
+
== Creating Queries ==
+
 
+
Creating queries can also be done through the data sources panel. To post the heading <tt>Queries</tt> of your base in the panel of
+
right-hand side and click above with the right button of the mouse. In the contextual menu, choose <tt>New Query (Design View)</tt>
+
and you will open a new window:
+
 
+
[[Image:doc_howto_sqlite_querydesign.png]]
+
 
+
You must start by selecting all the tables, that are to be included in your query, by choosing them from the list then while
+
clicking <tt>Add</tt>. Then you can create connections by dragging one field of a table on to the corresponding field in the other table.
+
 
+
To define your query, select the fields which are to be part of the query and drag them to the table located in the lower part of the
+
window, one-at-a-time. For each field added to the query, this table will enable you to choose the order of sorting, grouping, criteria,
+
et cetera.
+
 
+
The various icons will then enable you to post the result of your query, to save it, publish it in mode SQL.
+
 
+
Remember creating queries offers so many possibilities that it is not possible to detail them here; however, a good guide to using SQL should help.
+
 
+
{{Documentation/Caution|The possibility of adding several tables during creation of a query on a SQLite database, and thus of querying multi-tables, is not possible with sqliteODBC driver, version 0.6.0.}}
+
 
+
== Creating Forms ==
+
 
+
Creating forms with Writer or Calc is a very powerful feature of OpenOffice.org that makes it possible to create a true graphic
+
presentation from your data base. Form features and applications are so numerous that it is not possible to describe them in this
+
document. However, you will find information on how to make forms in OpenOffice.org Help and on [http://documentation.openoffice.org/ http://documentation.openoffice.org/].
+
 
+
By creating subforms your options are multiplied tenfold.
+
 
+
{{Documentation/Caution|: To create subforms in forms, it is necessary to use "named parameters " in order to establish the link between the various tables. For this you will have to use a macro whose installation and use are described in the following document: [http://dba.openoffice.org/howto/param_subst.html http://dba.openoffice.org/howto/param_subst.html].}}
+
 
+
== Exchanging databases between users ==
+
  
 
Thanks to the availability of driver sqliteODBC driver for both Linux and Windows (just as for OpenOffice.org, of course!), a SQLite
 
Thanks to the availability of driver sqliteODBC driver for both Linux and Windows (just as for OpenOffice.org, of course!), a SQLite
database SQLite can be easily exchanged between users of the two environments. It is necessary, of course, that each has an installed  
+
database 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, Writer and Calc
+
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.
files (sxw and sxc) containing forms and macros can also be exchanged between users of the two environments.
+
  
{{Documentation/Caution|: If forms are to function correctly for each user, it is absolutely necessary that each user applies exactly the same name to the data source during its creation.}}
+
{{Warn|If forms are to function correctly for each user, it is absolutely necessary that each user applies exactly the same name to the data source during its creation.}}
  
 
{{PDL1}}
 
{{PDL1}}
 +
 +
[[Category:Database]]
 +
[[Category:Documentation/How Tos/Database]]

Latest revision as of 11:43, 7 August 2018

Introduction

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 (241 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.

Documentation note.png SQLite appeals to anyone frustrated with using MDB files under Linux, or who has had difficulty installing MySQL or other RDBMS server on their workstation.


Installation

Documentation linux.png Under Linux

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:

sudo apt-get install unixodbc unixodbc-bin

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:

sudo apt-get install sqlite3

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:

sudo ODBCConfig
Documentation note.png If ODBCConfig is not found check that you also installed the unixODBC-bin package while installing the ODBC drivers.

Now the a GUI to manage the configuration shows up. Doc howto sqlite odbcadmin.png

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

Name: SQLite
Description: SQLite for ODBC
Driver: /usr/lib/libsqlite3odbc.so
Driver64: /usr/lib64/libsqlite3odbc.so
Setup: /usr/lib/libsqlite3odbc.so
Setup64: /usr/lib64/libsqlite3odbc.so
Documentation note.png If you are still using a 2.8.x version of libsqlite the driver names will be different. Try browsing the folder by clicking on the arrow at the right and select one that seems suitable.

Then confirm using the checkmark icon on the left in top of the window. Your new driver will now appear in the Drivers tab: Doc howto sqlite odbcadmin-2.png

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:

[SQLite]
Description= ODBC for SQLite
Driver= /usr/lib/libsqliteODBC.so
Driver64= /usr/lib64/libsqliteODBC.so
Setup= /usr/lib/libsqliteODBC.so
Setup64= /usr/lib64/libsqliteODBC.so
FileUsage= 1
CPTimeout= 
CPReuse= 

Under Windows

Documentation windows.png Under Windows the installation and configuration is much easier. Simply download the sqliteODBC.exe, the installer will automatically setup the ODBC drivers including the SQLite configurations for it and also provides you with the sqlite command line tool to create and modify SQLite databases.

Setup of SQLite database

Creating a SQLite database file

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

Documentation linux.png Open a terminal window and change to the directory where you want to create your database. Run sqlite with the filename of your database as argument (e.g. $ sqlite mydatabase
Documentation windows.png Run sqlite from the start menu or where it was installed. A standard file open dialog box will open and ask you to select a database file.

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:

$ sqlite <filename of database>
SQLite version 2.8.12
Enter ".help" for instructions
sqlite> CREATE TABLE mytable (id INT, text VARCHAR(100));
sqlite> .exit
Documentation note.png You can then check that the file was, indeed, created in your filesystem. The name of the file does not require a file extension, but you can give it the extension db so that is easy to locate as a database.

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 using OpenOffice.

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:

Doc howto sqlite dbbrowser.png

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. While sqlitebrowswer is suitable for these actions, there is the disadvantage that it does not show the full list of field types during field 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.


Documentation linux.png Under Linux:

Launch ODBCConfig again as normal user and go to the tab User DSN. Here add a new entry. A window appears in which you must choose the SQLite driver you have configured earlier. In the next window, as shown below, you enter a name for this connection (for example the name of your database) and the complete access path to the database file which you created previously.

Doc howto sqlite driverproperties-2.png

Validate the data on the screen, click the checkmark and you should see your new data source in the tab User DSN.


If you do not have ODBCConfig or simply prefer to configure manually, launch your favorite editor, open (or create) the file odbc.ini in your home directory, and add the following lines to it:

[My Base]
Description= My test database
Driver= SQLite
Database= /home/<user>/basename.db
Timeout= 1000
StepAPI= No
Documentation note.png If this data source must by other users on the system, launch ODBCConfig as root and add this source in the tab System DSN or data Source system, or create a /etc/odbc.ini file.

You are now ready to access your data base with OpenOffice.org!

Documentation windows.png Under Windows:

You will reach the configuration through the OpenOffice dialog in the next step, just read on.


Connecting OpenOffice to a SQLite database

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.

Connect to an existing database
 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.

Documentation windows.png Under Windows:

Before you will find your database listed here you need to register it with ODBC as mentioned above. Click on Organize... in the bottom right of the window.

Doc howto sqlite odbcdriver-windows-1.png


In the User DSN (or the System DSN) tab click on Add... to setup your database file.

Doc howto sqlite odbcdriver-windows-2.png


Give your database a meaningful name as Data Source Name - this will be the name you find your database listed as. For Database Name browse to the SQLite database file you have created earlier. Leave the rest on its default values and press ok.

Doc howto sqlite odbcdriver-windows-3.png


The database will now be listed in the ODBC Data Source Administrator and leaving this window you should now also be able to select this database in the OpenOffice Base dialog.



Working on the database with OpenOffice

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.

Documentation caution.png Once you have saved your table, you cannot modify it! I.e. you will not be able to modify the properties of the fields any more, nor add or remove!

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.

Documentation caution.png If you want to use subforms you need to activate "parameter substitution" first.

Select "Edit/Database/Advanced Settings..." in the main menu of your OpenOffice Base window. There, in the "Special Settings" tab check the option "Replace named parameters with '?'". You might need to restart OpenOffice but then you should be able to use subforms without problems.

Exchanging databases between users

Thanks to the availability of driver sqliteODBC driver for both Linux and Windows (just as for OpenOffice.org, of course!), a SQLite database 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.

Documentation caution.png If forms are to function correctly for each user, it is absolutely necessary that each user applies exactly the same name to the data source during its creation.
Content on this page is licensed under the Public Documentation License (PDL).
Personal tools