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 | How a query looks like. | |
| 14.2 Query editor | How to enter and manage your queries. | |
| 14.3 Exporting queries as text | Export the result of a query as a text file. | |
| 14.4 Exporting queries as PDF | Export the result of a query as a PDF file. | |
| 14.5 Printing queries | Print the result of a query. | |
| 14.6 Query examples | Some examples. |
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:
- a string input field with an attached pop-up button.
The edit-able string field displays the name of the current query.
By pressing the pop-up button, a list with further query names
together with several buttons appear.
You can select one of the listed queries to make it the current one,
press the `New' button to create a new query,
press the `Duplicate' button to get a copy of the selected query,
click on the `Sort' button to sort the list of queries,
or press the `Delete' button to delete the selected query.
For leaving the pop-up window without changing anything,
click on the pop-up button again.
- a choice field that allows to assign the query to a table.
When assigning a table, BeeBase runs this query
when the user selects menu item `Table - View all records' in the
corresponding table.
- a `Run' button that compiles and runs the query program
and displays the output in the output list-view.
- an `Export' button that opens a dialog
(see Exporting queries as text)
for exporting the query results to a text file.
- a `PDF' button (on most systems) that opens a dialog
(see Exporting queries as PDF)
for exporting the query results to a PDF file.
- a `Print' button that opens a dialog (see Printing queries)
for printing the results.
- an editor field for entering the query program.
Here you usually enter a select-from-where query.
However, it is also possible to enter any expression
of BeeBase' programming language.
This can be useful if you want to do simple computations
or update some fields of a table by using a simple program.
Please note that BeeBase automatically surrounds your program expression with
a pair of parenthesis, thus you can omit the outermost ones.
- a list-view that displays the output after running the current query. The output is formatted into rows and columns. The title row holds the field names of the select-from-where query (usually the field names). The other rows hold the contents of the query result, one set of records per row. Each field is displayed in its own column. By clicking on a column title you can sort the list with respect to this column. A second click onto the same column title reverses the order. On the Amiga you can set a secondary sort column by holding down the Shift key when clicking on a title. If you double click on entry in the list and the entry was generated from a record then this record is displayed in the corresponding table mask. This offers an easy way to jump to a certain record in its table mask.
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
- a string field for entering the export filename. Right to
this field you find a pop-up button to open a file dialog
for choosing the filename.
- two string fields for entering the record and field delimiters.
You can enter a single character or an escape code by typing
\n, \t, \f, \??? (octal code), or
\x?? (hex code).
- a field `Double quotes' that can be checked to specify
that the fields should be surrounded by double quotes.
- two buttons `Export' and `Cancel' for leaving the window.
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:
- a string field for entering the export filename. Right to
this field you find a pop-up button to open a file dialog
for choosing the filename.
- a choice field for selecting the paper size.
- a choice field for specifying the orientation
(`Portrait' or `Landscape').
- a field for entering the font name and size.
On Windows, Mac OS X and Linux, you can press the pop-up button to the right
for selecting a font in a font dialog.
On MorphOS you select the font in a choice field and enter the font size
in the text field to the right.
It is possible to enter fractions, e.g. `10.5'.
- a string field for entering an optional header text.
The header is printed at the top of each page.
Leave empty if no header is desired.
- a string field for entering an optional footer text.
The footer is printed at the bottom of each page together with the page number.
- a status field that shows the number of pages and whether the contents
fit into the page width.
- two buttons `Create PDF' and `Cancel' for leaving the window.
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:
- a choice field for selecting the paper size.
- a choice field for specifying the orientation
(`Portrait' or `Landscape').
- a choice field for choosing the font with a text field for entering
the font size. It is possible to enter fractions, e.g. `10.5'.
- a string field for entering an optional header text.
The header is printed at the top of each page.
Leave empty if no header is desired.
- a string field for entering an optional footer text.
The footer is printed at the bottom of each page together with the page number.
- a status field that shows the number of pages and whether the contents
fit into the page width.
- two buttons `Create PDF' and `Cancel' for leaving the window.
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:
- a field `Delimiter' where you specify
how the columns should be separated.
`Spaces' pads the fields with space characters. Padding is done
on the left or on the right side depending on the type
of the field (numbers are padded on the left, text on the right side).
`Tabs' inserts exactly one tab character between the columns.
This can be useful if you want to use the print dialog for
exporting records (see below).
`Custom' allows to specify a custom delimiter string to be
printed between fields.
- a field `Font' where you specify which font should be used for
printing the output. `NLQ' stands for near letter quality which
should print the output in better quality than `Draft'.
- a field `Size' where you define the character size.
`Pica' prints in a large font (10 CPI), `Elite' in a medium
font (12 CPI) and `Condensed' in a small font (17 CPI).
- a string field `Init sequence' where you can enter a string
for initializing your printer. The contents of this field are written directly
after opening the printer.
For example you can use `\33c' as init sequence which resets your printer.
- a field `Indent' where you can enter a number of spaces that
are used for indenting each output line.
- a field `Headline' that, if checked, prints the field names
in the first line.
- a field `Escape codes'.
If not checked the output of all escape codes
is suppressed which means that the settings of the fields `Font'
and `Size' are ignored and the contents of `Init sequence'
are not printed. Suppressing the output of all escape codes is
useful if you want to generate an ASCII file, e.g. for exporting records.
- a field `Quotes' that, if checked, surrounds all
fields with double quotes.
- a field `After printing' where you can specify how the
output should be finished. `Form feed' prints a form feed
character \f. `Line feeds' prints a number of line feed
characters \n. The number of line feeds can be entered
in the string field to the right of the `Line feeds' button.
`Nothing' doesn't write anything to the printer.
- a string field `Output' with an attached pop-up button.
You can use the pop-up button to open a file dialog for
choosing a filename or directly enter the filename into the string field.
For writing the output to your printer enter `|lpr' (Linux)
respectively `PRT:' (Amiga).
For other special filenames, see Program output file.
- two buttons `Ok' and `Cancel' for leaving the print dialog.
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 August, 22 2022 using texi2html