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

Documentation

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


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

14. Data Retrieval

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 Structure editor) and in the chapter about programming MUIbase (see Programming MUIbase).

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

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 Programming MUIbase, 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, MUIbase 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 MUIbase' 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 MUIbase 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 attribute 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, MUIbase 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.

On Windows, Mac OS X and Linux this opens the standard print dialog where the `Print to file' entry with a `PDF' format is preset. 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 written to the specified file.

On MorphOS 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 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 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, MUIbase opens the specified file and writes 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.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. 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 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, MUIbase 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 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


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

This document was generated on Oktober, 10 2017 using texi2html