On Friday 21 June 2002 06:09 pm,somebody wrote:

> Could you write down some of your thoughts, steps, and procedures for using
> OOorg with databases. The main thing missing are How-To's.
>

Okay, I'm working on a tutorial sort of overview for my webpage, but here is
the quick overview.

OpenOffice database engine works on the "server-client" model.  That means
before you can use a database, you have to connect to it.  This is useful if
you need to access remote/multi-user databases.  But, it implements its own
internal server for any local databases you may have.

Thus, a local directory may represent a "database" and in it you will put
your tables, queries and reports.  If you don't have a database, you can
create one by using the "administrate database" tool.

A database, as you know, is a collection of tables, queries and reports.
Generally, you would collect tables in the same "database" that have some
relevance to each other -- because you might want to link them and query them
simultaneously.

 This may seem "obvious" to anyone familiar with SQL, but I started
programming in dbase years ago and a "database" back then was pretty much an
individual table or a couple of them linked together.  I later moved to the
Borland C++ database engine, which basically follows the pattern I outlined
above, as does OpenOffice.

A "query" is simply a view of the table that limits itself to those records
that meet a criteria you establish.

A "report" is a convenient output of the data in a query.

Keep in mind, these are not official definitions and "real" database
programmers may quarrel with my explanation. (So please don't spam me, I'm
just trying to help.)

OpenOffice comes with a "bibliography" database pre-installed and has a
wizard (autopilot) to connect to your existing addressbook database, since
that is a database you are likely to use alot.  The bibliography would be
useful if you were writing a book or thesis that required an extensive
bibliography, and then you could easily query the database for those
citations you were particularly interested in for any particular part of the
project.

But the great thing is that you may connect to >any< existing database using
the "administrate database" tool and even create your own new ones.  There
are several formats it will recognize.  I am most familiar with dbase and csv
files.  Dbase files are pervasive and usually end in ".dbf"  If you've got
some of these, you are in luck, OpenOffice will access them.  (But don't name
them starting with a zero, they won't open and I don't know why).

CSV files are "comma separated value" text files.  Many databases (including
Outlook) will export their data to these files, making them a sort of
"universal" exchange medium.  The "administrate database" tool will recognize
several different CSV formats, so there are some options you will have to
select.  OpenOffice will read, but not write to CSV files.  However, the
"workaround" is that you can open the file in the spreadsheet, make your
changes and save it.

So, you're going to use a dbase (or xbase) file and save yourself the
aggravation, right?

Accessing data tools

Access these tools by one of the following:  Datasource icon on the main
toolbar at the left of the Writer screen (also in Calc, I think).  Or,
View>Datasource.  Or, F4.

Datasource opens the data "explorer" and the current table in the upper 1/3
of the screen.  The explorer can be closed or hidden for a wider view of the
table.  The "contact" table is opened by default.

The data "explorer" window will navigate all your databases.  Right click to
get the "administrate database" option or to disconnect a database.

The tree will open up to display reports/queries/tables.  You may build a new
query in the query directory of the database tree using a "query designer"
gui or by sql text.  (There are a couple of sql quirks that will crash the
database connection, such as an sql request that contains no records.)

You can open a table or a query in this upper 1/3 of the screen (which is
adjustable) and you can scroll through it to find the record you want.  You
can add further filters to find the records, including a number of variables,
such as "LIKE *xxx*" which is very useful to find every record with an
instance of a text string.

You can drag and drop the info onto the text screen below or you can insert
variables and do a "form" merge.  Also, if the columns in the table seem all
scrunched up, double-click the title bar at the top of the column and that
column will expand to its full size so you can read the data in it.

OpenOffice will also allow you to manually create a printout by setting up
your variables for a single record and then inserting the "next record"
indicator.  You then copy and paste this for as many records as you have in
your database.  However, that is rather time consuming and inelegant.  So, I
assume OpenOffice has the "catalog" mode somewhere and I just haven't found
it.

Inserting data as a list into a text document

Then, if you are needing to insert your database into the text as a "list,"
you can select the entries that interest you in the data browse window that
you open in the upper 1/3 of the screen.  Then click on the "data to text" icon.
This will provide a dialogue that allows you to select whether to insert as
a table, as text or as fields.  You can also select a "paragraph format."

Form tool for the display of individual records (and additional tasks)

Lastly (at least lastly that comes immediately to mind) you can build a form.
 There are two form tools.  One is on the main toolbar, which will generate a
default style form in several different useful styles.  The form will display
the current record of the query and can be used for input and view of the
record info.  Thus, you get a "browse view" in the top 1/3 of the screen and
a "record view" in the bottom 2/3ds.  You can lock the upper screen in place,
or it can overlay the lower screen and be hidden with the standard "hide"
bars.

The second form tool is found in the menu system (File>Autopilot>Form) and 
allows you to build an extensive form that includes menu buttons,
file dialogues and the like.

If you are familiar with RAD programming tools, the form is a RAD form to
which you can link starbasic routines, so one could arguably build highly
complex database tools.

There are more features that I cannot remember or recite off the top of my
head, but this seems like a particularly robust database server/client
connection for an office suite.  When you start using databases and realize
how easy it is to query them, you will suddenly have this collection of
databases for ... everything.  MS Outlook has some similar tools, but they
are more cumbersome and difficult to implement because they are not as well
integrated.

My computer is just this cluttered mess of dbf files.  I keep track of
wordprocessing documents, music files, CDs, videos, billing data, client
files, junk in my garage, people who spam me, money wasted on programs from
that Redmond software company, you name it.  The OpenOffice database engine
makes it way easier for me to query these files and "find stuff" (which of
course is the whole point of inputting the data in the first place).

Back