For data retrieval MUIbase 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 section Structure editor) and in the chapter about programming MUIbase (see section Programming MUIbase).
This chapter describes the usage of the query editor, a requester where you can enter queries and view the output in a scrolling list-view.
MUIbase 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 attribute names) or a simple star * matching all attributes 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 attributes 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 attribute contents of all records in the given table.
SELECT attr1 FROM table WHERE (LIKE attr2 "*Madonna*")
lists the value of the attr1 field in all records of table where the contents of field attr2 contain the word `Madonna'.
For more information about the select-from-where query including its full syntax, see section Programming MUIbase, for more example see section Query examples.
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 requester. 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.
After you have run a query you can print the result to a file or printer by clicking on the `Print' button in the query editor. This will open a print requester containing the following items:
After you are done with all settings, click on the `Ok' button to start the print job.
The print requester can also be used for exporting records to an ASCII file. To do this, specify `Tabs' in the `Delimiter' field, set the number of spaces in the `Indent' field to 0, check `Headline', un-check `Escape codes' to suppress the font, size and init sequence settings, optionally check `Quotes' if you want the field contents to be surrounded by double quotes, check `Nothing' in the `After printing' field, and enter the output filename in the `Output' field. Using the query editor together with the print requester for exporting records can be much more powerful than using MUIbase' import/export feature (see section Import and Export) since in the query editor any query can be entered whereas the export requester only uses a fixed query.
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 attribute `Name', an integer attribute `Age', and two reference attributes `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 attribute `Name', a choice attribute `Color' and a reference attribute `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 attributes 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
Go to the first, previous, next, last section, table of contents.