QweryBuilder is a database development tool designed and developed in PowerBuilder. Its purpose is to use innovative ideas and techniques to make database development and data retrieval easy and intuitive. It currently works against Sybase ASE, iAnywhere SQL Anywhere, Microsoft SQL Server and Oracle. An evaluation version of the application can be downloaded from www.Werysoft.com.

The purpose of this article will be to take you on a tour of QweryBuilder. I would like to show you what PowerBuilder is capable of. My hope is that some of the functionality you see can be used in your own application. Where I can, I will describe in detail how a specific piece of the application was coded, providing as much insight as possible so you can do the same if you have a similar requirement.

The User Interface
The first thing you'll notice when you run the application is that the UI looks modern. Creating a UI in PowerBuilder like this is actually quite easy and should take you the least amount of time. Most of the controls that you see can be downloaded from www.PowerToTheBuilder.com. QweryBuilder uses an IDE-style interface. By this I mean that it includes a tabbed interface for a window presentation and dockable panels for displaying frequently used functionality. All of this is accomplished by using the PowerDock control (see Figure 1).

When QweryBuilder was first developed it was designed as an MDI application. Once tabbed interfaces became more popular, it was decided that the best thing to do was to change the look and behavior of the application so that it would remain in tune with current UI standards. Because I knew my code, objects and application intimately, I was able to update the look of the application using the controls from PowerToTheBuilder.com in about one day.

I trust that you can do the same to your application whether you're updating something that's been developed already or starting new development.

Criteria Query Window
The Criteria Query window is one of the cornerstone features of QweryBuilder. It allows you to select a table from your database, enter criteria for that table, then retrieve the results. Selecting an item from the table list will display all the columns in a freeform view for that table. You are then able to enter the data you want to retrieve (i.e., criteria) (see Figure 2).

Data will be retrieved in a new tab. These results are fully updateable. You can change the data, delete rows, insert rows or import data from a file. Once you're done manipulating the data you simply click the update button to commit your changes.

How is this done? This is actually quite simple to code. As you guessed, the results are displayed in a DataWindow. We use the built-in DataWindow functions and update properties to provide users with dynamic database update functionality. See Listing 1 for an example of how to accomplish this.

Graphic Query Window
The Graphic Query window allows you to query your database graphically. This means that you can generate a SELECT statement with just your mouse; no key strokes are necessary. Describing how to create this graphic query builder would require an article of its own. It really wasn't hard to do. The main "client" area holds the tables that are included in your query. These tables are actually DataWindow controls with titles. The lower area is just a tab control containing all the different option to create a SELECT statement. The only real complex part of the control is the lines that you can add to visually join, or tether, two tables together (see Figure 3).

The graphic query building control can be downloaded from Sybase's CodeXchange.

Editor Implementation
The editor used in the SQL Query window is the Scintilla editor control. There are several examples of how you can use it in PowerBuilder. A version I've created can be found on CodeXchange. For something more complete, check out the PBEditor control at www.TopWizProgramming.com.

Autocomplete and MyAutocomplete
One of the great features of QweryBuilder is the intellisense found in the SQL Editor window. It assists you as you type your ad-hoc queries. You can display a list of tables by typing in the database owner (e.g., dbo) followed by a period (.). Or you can simply enter a period. To bring up a list of columns for a table, type the name of the table followed by a period, or type the table alias you entered in the FROM clause then enter a period. For example:

SELECT A. <-- Autocomplete window will display here
FROM MyTable A
SELECT *
FROM dbo. <-- Autocomplete window will display here

Another feature in QweryBuilder, known as My Autocomplete, allows you to get quick access to data within your database via the intellisense window. The My Autocomplete feature (Tool/My Autocomplete) can display a list of data retrieved from the database or data that has been manually typed in. Why would a feature like this be handy? I know in a support role you are constantly querying the database looking for information based on "key" data. Let me provide an example. Let's say a popular query is to find all employees by department. Your database will contain an Employee table and a Department table. The Employee Table will contain a foreign key back to the Department table called DeptId. If you need to find all employees in the HR department, you will have to first look up the key for the HR department. Once you have this key, you will then use it to query the Employee table. So really what you've done is create a SELECT statement, executed it, copied the results from the results window or memorized it, created another query for the Employee's table, then entered your department. I know this isn't too many steps but it should be easier.

To do this example the QweryBuilder way, open the My Autocomplete window. Enter a new item by clicking the "new" button. Provide a name (e.g., dept); this name will be used in the query window so keep it short. From the type drop down select Command. Enter the following syntax:

SELECT DeptId, DeptName
FROM Department

The column order in the above SELECT statement is important. You want the key column first (the column value that will be inserted into the editor), then the descriptor column second. In your SQL Editor window you can then type the following:

SELECT * FROM EMPLOYEE A
WHERE A.DeptId = dept. <-- dept is the name we specified in the My Autocomplete window.

After entering the period you will see that the word dept is removed (it's not valid for our query) and a list of data is retrieved and displayed (the results of the query we entered in the My Autocomplete window). Find your department and select it. Once you have your department selected you can click enter or tab to insert the key value into your editor window (see Figure 4 and Figure 5).

It may be no surprise that all this functionality is provided via the DataWindow. The only catch is strategically displaying the autocomplete window in the right spot in the editor control.

Database Access
PowerBuilder is all about data access; retrieving and manipulating data with little effort. This is why people like the DataWindow. QweryBuilder uses the DataWindow quite a bit for this very reason. One type of data access that PowerBuilder isn't so great with is executing and retrieving results of ad-hoc queries. Fortunately, there are ways to do this. A little more work is required but it can be done. Some databases come with APIs. For the databases that don't come with their own APIs, you can use the ODBC API.

I've written an article on how to access and manipulate data with ad-hoc SQL using the ASE Open Client API. Jim O'Neil wrote an article on using the ODBC API.

Other Features
Form View Results
You can view your query results in a form view next to your regular result set (see Figure 6). The idea behind this is that you can scroll vertically through your results while easily seeing all the data in the row without having to scroll horizontally. Programming something like this in PowerBuilder is quite trivial. Look at the DataWindows share data functions for more information.

Create a Table from Results
After you select results from the database you can turn those results into a new table (see Figure 7). The results can be from a single table or it can be data from multiple tables. The generated script will also provide you with all the INSERT statements for the data you retrieved. Coding this is yet another trivial process thanks to PowerBuilder. You can simply loop through the columns in a DataWindow to get the column names and data types. Using this information you can generate whatever SQL scripts you like.

Create Insert Statements from Results
The idea of creating insert statements is the exact same as what was described in the previous paragraph. This is a good feature because it allows you to copy small amounts of data from one database to a similar table in another database.

Sort Columns
I can't count the number of times I've retrieved information from a really long table (i.e., a table with many columns), then get lost while scrolling through the results looking for one particular column. With QweryBuilder you can sort the columns in your result set so you can easily find the column you're looking for. It's a bit of a time-saver sometimes (see Figure 8). Coding this is a multi-step process. First you have to loop through all the columns in your DataWindow, turning each column invisible (assuming you're using a grid style DataWindow). You can then place all your columns in a datastore and sort. Loop through this sorted datastore, turning your column visible attribute on as you go. With the grid DataWindow, each column will appear as you make them visible.

Filter Results
After you retrieve your results, it may be necessary to further limit those results (see Figure 9). Clicking the button seen in Figure 6 will open the DataWindow filter window. Enter your filter criteria and close the window. I'm not sure if an explanation is necessary for this one. Use the DataWindows SetFilter and Filter methods to open this window.

Database Search
QweryBuilder comes with a database search window (see Figure 10). You can enter any number of search strings and select the type(s) of database objects you want to search. The application will extract the DDL for each type of database object you selected and search for your strings. If the string is found, it will display it in a treeview. Clicking on the name of the found item will display the object in an editor window so you can easily see where the string was found. The search results can then be printed or saved. This allows you to access your results in another program like Excel.

Conclusion
QweryBuilder uses a lot of built-in PowerBuilder features and functions to do what it does. It's safe for me to say that PowerBuilder made developing this database application easy.

I encourage you to try QweryBuilder for your own database development. I'm always looking for feedback and I always welcome ideas for enhancements.