Creating a database query

Contents

Overview

When you want to see the contents of database tables you may show the table contents for each table that holds your data and search by scrolling through the displayed data rows. It it more efficient to select the data that you want to see with a SQL select statement. If you do not know how to create a SQL select statement the query designer will help you with this.

If you have connected your HBasic project to a database you can open the query designer by creating a new database query in the new form dialog which appears when you select New in the menubar or click on the new icon in the project tree toolbar or HBasic global toolbar.

new_doc  

Next a popup dialog asks you for the name of the new query. Insert a unique name for your query and press the OK button. You should now see the query editor dialog which looks like the following image.

query_designer

In this image the following steps to define a query have already been executed:
Editing database queries

This document should explain how you can set up your own query definitions. The querys you have created will be stored with your HBasic project. You can see a list of all defined queries in the project tree. When you want to change or execute your query later click on the name of the query in the project tree with the right mouse button and select Edit or Execute in the popup menu that will be displayed.


Prerequisites

Before you may create and start a database query you have to prepare the following steps:
db_in_project


Select database and connect to project

Since a query description will be stored in the project files of your current HBasic project you have to select a database  and connect it to the project first.  You can select a database in the database window. Open the database window with the menu entry view / database window or click on the small database icon in the toolbar.

HBasic will open a new dialog where you can edit the list of known databases or connect to one database. Remember to compile the database plugin for your favourite database backend before trying to use the database connection in HBasic. (See setting up database access with HBasic).

To connect a database to your project add the database description to the list of known databases on top of the database window and click on the button to project to connect the current selected database to your project description 


Insert tables into your query

For the following description we assume that you have already connected your project to a database and opened the query designer by creating a new database query.

The first thing to do is select the list of tables that will be used within your query. Click on the Add table button in the query designer. You will now see a new dialog that lists all the tables from the database that is connected to your project. Mark the tables that you want to use with a mouseclick and click on the Add button. If you cannot see any table in this dialog check if you correctly connected to a database (with correct user and password) and the database is online.

You should now see the tables that you have selected in the upper half of the query designer with the names of the columns in each table.

Info: The query designer will create a SQL select statement from the information that has been set up in the designer. The list of tables will be used in the FROM part of this select statement (SELECT columns FROM tablellist).


Connect table columns

If you have selected more than one table for your query you can only get combined information from this tables if you connect them on some columns. Otherwise HBasic doesn't know how to display all the columns in one result. In the SQL select statement HBasic will create a join like "SELECT ... WHERE table1.column_x = table2.column_y".

If you have already created a database design for your project with the database design dialog these prepared connections will already displayed in the query designer.

To connect a column_x of a table table1 with a column_y of table table2 click on the column_x in the tableview of table 1 and drag it with the mouse to the tableview of table2. Drop it on the name of column_y in the destination table. HBasic will now show this connection with a line between the two connected columns.

Select columns that should be displayed

Select the columns that you want to use in your query by clicking on the columns in the tableview of the matching database table. Drag this columns to the bottom of the query designer and drop them into the column display. You can select the columns in this columndisplay and move their position in the query left or rigth by clicking on the direction buttons. You can also delete the selected query column by clicking on the remove button.

Execute query

After you set up all this parts of a query you can start execution by clicking on the Execute button. A new dialog pops up and shows the result data of your query.

After leaving the query designer you can see a list of all defined queries in the project tree. When you want to change or execute your query later click on the name of the query in the project tree with the right mouse button and select Edit or Execute in the popup menu that will be displayed. You may also select the query with the left mouse button and click on the Start button in the project tree window.