BeeBase

Programmable relational database
with graphical user interface Support This Project
This page is also available in Deutsch Français

Documentation

Below documentation is part of the BeeBase dictribution and is also available in PDF.


[ << ] [ >> ]           [Top] [Contents] [Index] [ ? ]

14. Data Retrieval

For data retrieval BeeBase offers two ways: the programming feature and the query editor.

The programming feature allows you to install buttons in table masks which, when pressed, call program functions. The usage of this feature is described in the structure editor chapter (see Structure editor) and in the chapter about programming BeeBase (see Programming BeeBase).

This chapter describes the usage of the query editor, a dialog where you can enter queries and view the output in a scrolling list-view.


14.1 Select-From-Where Queries

BeeBase offers a select-from-where query similar to the one in SQL database systems. The query allows you to list the record contents from one or more tables. Only records matching certain criteria are included in the output. The (incomplete) syntax of an select-from-where query is

 
SELECT exprlist FROM tablelist [WHERE test-expr]
[ORDER BY orderlist]

where exprlist is a comma separated list of expressions to be printed (usually the field names) or a simple star * matching all fields of the specified tables, tablelist is a comma separated list of tables whose records are examined, test-expr is the expression that is tested for each set of records to be included in the output, and orderlist is a comma separated list of fields that defines the order for listing the output. Please note that the WHERE and ORDER BY fields are optional, denoted by the brackets [].

For example, the query

 
SELECT * FROM table

lists the field contents of all records in the given table.

 
SELECT field1 FROM table WHERE (LIKE field2 "*Madonna*")

lists the value of the field1 field in all records of table where the contents of field field2 contain the word `Madonna'.

For more information about the select-from-where query including its full syntax, see Programming BeeBase, for more example see Query examples.


14.2 Query Editor

For entering and running queries, open the query editor by choosing menu item `Program - Queries'. The query editor is able to manage several queries, however only one query is displayed at a time. The query editor window contains the following items:

The query editor is a non-modal dialog. This means that you can leave the query editor open and still work with the rest of the application. You can close the query editor at any time by clicking on the close button in the window title bar.


14.3 Exporting Queries as Text

You can export the results of a select-from-where query to a text file by pressing the `Export' button. This will open a window containing

After you pressed the `Export' button, BeeBase will open the specified file and write out the query result including a header line containing the list header. The fields are written in the order of the columns in the list.


14.4 Exporting Queries as PDF

On Windows, Mac OS X, Linux and MorphOS you can export the query results to a PDF file by pressing the `PDF' button.

A window is opened that contains the following elements:

After pressing the `Create PDF' button, BeeBase opens the specified file and writes out the query result including a header line containing the query field names. The fields are written in the order of the columns in the list.


14.5 Printing Queries

After you have run a query you can print the result by clicking on the `Print' button in the query editor.

On Windows, Mac OS X and Linux this opens the standard print dialog.

If you are running the GTK version of BeeBase, the dialog contains a custom page `Font' where you can specify a font and enable the shrinking of all contents such that they fit into the page width of the selected paper and orientation. After pressing the `Print' button the query results are sent to the selected printer.

On MorphOS the `Print' button opens a similar dialog when exporting queries as PDF (see Exporting queries as PDF).

The window contains the following elements:

After pressing the `Create PDF' button, BeeBase generates a temporary PDF file and opens it with the external viewer (see External viewer). You can then use the printing facilities in the external viewer for sending the query results to your printer.

On other Amiga systems, a print dialog containing the following items is shown:

After you are done with all settings, click on the `Ok' button to start the print job.


14.6 Query Examples

To give you an impression of the power of the select-from-where queries this section gives you some sample queries.

Suppose we have two tables `Person' and `Dog'. `Person' has a string field `Name', an integer field `Age', and two reference fields `Father' and `Mother' that refer to the father and mother records in table `Person'. The table contains the following records:

 
      Name      Age     Father  Mother
      --------------------------------
p1:   Steffen    26     p2      p3
p2:   Dieter     58     NIL     NIL
p3:   Marlies    56     NIL     NIL
p4:   Henning    57     NIL     NIL

`Dog' has a string field `Name', a choice field `Color' and a reference field `Owner' that refers to the owner in the `Person' table. The table contains the following records:

 
      Name      Color   Owner
      -----------------------
d1:   Boy       white   p3
d2:   Streuner  grey    NIL

Given these data the following sample select-from-where queries can be run:

 
SELECT * FROM Person

results to:

 
Name    Age Father Mother
--------------------------
Steffen  26 Dieter Marlies
Dieter   58
Marlies  56
Henning  57

(For the reference fields the `Name' field of the referenced record is printed.)

 
SELECT Name "Child", Age,
       Father.Name "Father", Father.Age "Age",
       Mother.Name "Mother", Mother.Age "Age"
FROM Person WHERE (AND Father Mother)

results to:

 
Child   Age Father Age Mother  Age
----------------------------------
Steffen  26 Dieter  58 Marlies  56

 
SELECT Name, Color,
       (IF Owner Owner.Name "No owner") "Owner"
FROM Dogs

results to:

 
Name     Color  Owner
------------------------
Boy      white  Marlies
Streuner grey   No owner

 
SELECT a.Name, a.Age, b.Name, b.Age FROM Person a, Person b
WHERE (> a.Age b.Age)

results to:

 
a.Name  a.Age b.Name  b.Age
---------------------------
Dieter     58 Steffen    26
Marlies    56 Steffen    26
Henning    57 Steffen    26
Dieter     58 Marlies    56
Henning    57 Marlies    56
Dieter     58 Henning    57


[ << ] [ >> ]           [Top] [Contents] [Index] [ ? ]

This document was generated on September, 30 2024 using texi2html