Data Selection

Table of Contents


Prepared Query


GUI Elements


ListSelection Panel





This piece of application allows standard users, not knowing much about databases, to create complex queries against a RDBMS. Also it has to provide flexible queries with very few GUI-elements that can be used by simply clicking onto elements.

The result of such a generated query can be displayed at the screen and if the result fulfills the users requirements, the query can be exported as a csv-file, a xls-file or into a xml-file.

Queries can be stored for later use. The final query can be saved as a sql-file to be reused within the exporter or as a xml-file to be loaded into the GUI.
Dataselections are based onto prepared SQL-statements. The GUI is generated out of this SQL-statement, the embedded macros are interpreted and displayed within tabs. each macro creates its own tab. You can create tabs inside tabs to group functions. By a click onto the binocular the query is interpreted, there is a simple query-checker that eliminiates impossible constructs, e.g. if a AND directly follows a WHEN. This checker is far away from being something like a professional querychecker. This check is linebased, so it's best to have expressions like 'AND' and 'WHERE' onto a single line, dito while using brackets. There are two types of macros SQL-Commands and the 'include' – command to include files (#include fully-qualified-file-name).

Select the prepared query

The queries are prepared by a person having a certain knowledge in creating complex queries. The number of prepared queries is not limited, but it makes sense, not to overload the user with too many queries. The name of the query and its location is defined inside a control file, what then is interpreted by the application as a ComboBox. If this file does not exist, the applications throws an exception. This file is located at the position mentioned within the file admin.selgui.props what is located in the users homedirectory or at the position you defined with the environment-parameter 'admin.home', the defaultlocation is user.home/datadrill/resources.

To overcome this, I've created a little tool, that copies the needed files to your local harddisk from the internet( look here). With this tool, you can also create the demotables I've used to explain how it works. You probably first want to create your own database, because the tools just creates the tables within the actual database and probably messes your actual database up ;-). You find this tool under the menu 'Data operations -> Data Selection -> Create Demo environment'.

To explain the mechanism of this tool, I'm using this SQL-statement containing macros. The goal ist to select all garages in Zurich, Vienna and Dortmund and display the name of the garage, name and directphone of sales and techies:

To go like this, we create a basic query as this:

select distinct
from   garage, person, function, employe
where  employe.garage_id  = and
       employe.person_id  = and
#execute(City;IN; in;select distinct city from garage)
#criteriabox(Function; in;employee-list.function)
order by

Our database looks as follows:



Format: #checkbox(Unique-tab-Title;filename)

The CheckBoxPanel is used to select single elements. In this case it is lets the user select the Attributes to display within the result query.

To create such a panel, you do something like this:

select distinct
from   garage, person, function, employe

Where the macro #checkbox(Attributes;employee-list.display) must be on its own line and means:

the file employee-list.display looks as follows:

afs@kollbrunn:~/datadrill/resources> more employee-list.display;Name of the Garage;Garage
garage.street;Street of the Garage;Street;Postalcode of the Garage;ZIP;City of the garage;City;E-Mail of the Garage;E-Mail;Phone of the Garage;Phone
person.firstname;Firstname of Employee;Firstname
person.lastname;Lastname of Employee;Lastname;Employee direct Phone;Direct

Where each line contains three (3) items:

  1. the columname

  2. the text displayed within the gui

  3. the title displayed within the SQL-resultset

According the selection done, the SQL-Statement will then look as this:

select distinct "Garage ", "City ", "Phone ",
person.firstname "Firstname ",
person.lastname "Lastname ", "Direct "
from   garage, person, function, employe


Format: #execute(Unique-Tab-Title;Type(IN|SIMPLE);(Table.attribute IN|Table.attribute);SQL-Query)
where IN     generates an IN-SQL-Statement (attribute IN ('A','B'...) )
and   SIMPLE only puts spaces in between two selected elements

The ListSelectionPanel lets the user select elements from a list and adds them to the query. The content of the list is generated out of a SQL-query by itself.

In this case, our statement looks as this

#execute(City;IN; in;select distinct city from garage)

Where the elements are as follows:

this selection will create such a SQL-fragment: in ('Zuerich' , 'Vienna' , 'Dortmund')


Format: #between(Unique-tabtitle;table.attribut;number of lines;or|and;text|zahl)

While using the betweenpanel, the user has the possibility to enter values as letters or numbers.

This is from the second example in our demo-DB. We use this to define the mileage range we'd like to have. If we enter between 5000 and 10000 our generated query-fragment would look like this:

(    car.mileage BETWEEN 5000 AND 10000 )

as our macro is this:


where the values mean:

  1. Unique Tabtitle

  2. table-attribute used

  3. number of lines to generate at the beginning

  4. mode (and or or) to generate the between-SQL-fragment

  5. select 'text' if the attribute contains text and 'zahl' if the content is numbers


Format: #criteriabox(unique-tabtitle,tableattrbiut in;filename)

The criteriabox allows to select elements while checking them.

To create such a panel, we add the criteriabox-macro to our sql-statement:

#criteriabox(Function; in;employee-list.function)

where the values are:

  1. unique-tabtitle

  2. table-attribute

  3. filename used to create the items

the file employee-list.function looks as follows:

afs@kollbrunn:~/datadrill/resources> more employee-list.function

Where each line contains two (2) items:

  1. the text displayed onto the screen

  2. the value returned into the sql-statement (make sure to add the ' if the items are in text-format)

According the selection done, the SQL-Statement will then look as this: in  (1,4)


I've created the demo-DB with this script, there is another file with the DB-content

Get the demo-files Unix-Style, Windows-Style

More examples (a little bit more complex) Unix-Style, Windows-Style