PowerBuilder Tips, Tricks, and Techniques

Berndt Hamboeck

Subscribe to Berndt Hamboeck: eMailAlertsEmail Alerts
Get Berndt Hamboeck: homepageHomepage mobileMobile rssRSS facebookFacebook twitterTwitter linkedinLinkedIn


Article

Pocket SQL Workbench

Or, what data is in my database?

One of my favorite PocketBuilder projects is an application that I started a long time ago, a TimeSheet application in which I can track the hours I spent on different client sites.

This application stores all its data within a Sybase SQL Anywhere database. I also created some reports (including graphical ones) to get the data out of the DB and present it to the user (in fact, to me), but sometimes I can't see what I need to see. This brought up a new idea or better a new application that needs to be written in PocketBuilder: the Pocket SQL Workbench. This application should help me build queries on the fly and display the ad hoc data I need at a specific time. Okay, agreed, it's a "simple" query tool for the Pocket PC. Hmm, is this really so simple? Let's have a closer look at what such an application has to offer so it will get some attention from the PocketBuilder community.

What Should It Do?

  1. It should work on the desktop and on the Pocket PC (implementing it in PocketBuilder enables us to code it that way).
  2. I want to be able to choose one or more tables and columns without a lot of typing, as it's not too easy to create a select statement on a Pocket PC (this statement may also be very complex).
  3. It should display data in a way that users like (this is not very hard to implement, because PocketBuilder can create DataWindows on the fly) so that they can easily browse through the retrieved data.
  4. The queries need to be saved so that users are able to run them again against the database the next time they run the tool.
  5. Users should be able to filter and sort the data and also export the results as a file on the device or desktop.
  6. It should use only PocketBuilder stuff (no need to buy an additional product or tool) and should be made freely available to the PocketBuilder community.
There are some points that need our attention, which we'll discuss. Let's dive into the implementation problems we might run into when we start building the Pocket SQL Workbench.

The Implementation
We want the application to be able to run on both the desktop and the Pocket PC. That shouldn't be too hard; there are only two problems. First, the different-sized windows on the desktop and on the devices. This is already a well-known problem, which we'll ignore today (a simple resize will do this and I'll have already implemented it when you read this article). The second problem is the different storage locations for the database data sources. The desktop stores them in the registry; the Pocket PC (or better PocketBuilder applications) expects text files to be stored in the root of your device ("") with the extension DSN. As you can see in Listing 1, depending on the environment's OS type, we'll fill a listbox either with the registry keys or simply use the dirlist function of the listbox by specifying the correct filetype (*.DSN) (see Figure 1).

Now the first problem is solved; when the user chooses a value from the populated listbox, we'll connect to the database. Let's look at the second problem, as the users expect some support when defining the query. At least we'll support the user by offering two DataWindows, where one will display all available user tables within the chosen database and the corresponding columns to define the select clause without typing a lot. (If we didn't offer that I think our users would sooner or later throw their devices out the window - if you are writing a lot on the device without a keyboard, only with a stylus, you'll know what I mean.) The DataWindows are built using two different selects:

SELECT table_name, table_id FROM sys.systable WHERE creator = 1
ORDER BY table_name ASC
SELECT column_name FROM sys.syscolumn WHERE table_id = :table_id

The first select displays all user tables within the DataWindow and the second select displays the corresponding columns using the table_id as a retrieval argument. We'll display the DataWindow for the tables when the user clicks the button, and the user will be able to choose a column when he or she clicks the button (where the user has to choose a table name first so that we're able to retrieve the corresponding columns) (see Figure 2). Our users are able to build even complex select statements using this methodology; to test the statement the user uses the run command button. The script behind this button uses the SyntaxFromSQL function of the transaction object and the Create function on the DataWindow. The simple script in the following code, the one implemented in the SQL workbench, also defines some additional settings such as the font and the background colors of the header (see Figure 3).

sPresentation = 'style(type=grid)'
sSyntax = SQLCA.SyntaxFromSQL(
sSqlSelect, sPresentation, sErrmsg)
idw_1.create(sSyntax)

If everything is fine and there is no error in the statement created by the user, the result is displayed on the corresponding tab page. As we know, not everything works as expected and errors occur, so there is an additional tab page implemented that displays the results of the database operation. If everything is correct, the window shows the count of the retrieved rows (see Figure 4); if something went wrong, the error message is displayed and the statement can be easily corrected. The retrieved data can be resorted (ascending and descending) by the user by clicking on the header of any column.

As an additional service for our users, the user-defined statement is saved in a datastore behind the scenes and the rows of this datastore can be saved by the user in a simple text file on the device (or on the desktop if the application is executed on the desktop). This is accomplished by using the save button to be able to select one of the created statements in the future (or maybe to transfer them later to the desktop where it could be used to create a DataWindow for the application that uses this database). The history can also be previewed and edited within the SQL Workbench by using the history button, which brings up the history window (see Figure 5).

It's possible to delete rows here (statements that were previously created by the user), so that the user is able to remove outdated statements or statements that are not of interest anymore, or simply have a look if a previously created select could be used as a starting point for a new (more complex) statement.

If the user would like to filter the result, a criterion should be entered into the "filter" singlelineedit on the bottom and the filter button needs to be pressed. This functionality simply uses the standard filter functions in PocketBuilder. However, by the time you're reading this article I may have implemented a more complex solution to create more complex filter criteria easily. By using the clipboard button our users are able to paste the clipboard into the current singlelineedit. The button for saving the create script allows users to get a complete creation script for the selected table, including insert statements for the data that is returned by the select built by the user. Such a SQL script can be imported by using the load button. This functionality might be useful if there are changes on the database and you'd like to have a script that you distribute to update other Pocket PCs and their databases.

Conclusion
The Pocket SQL Workbench provides users with an inside look at their database directly on the handheld. This might be useful for testing purposes to proof the correctness of the application, to see if the data is stored correctly, or to manipulate the data during the testing phase (right, you are also able to update your data and feel free to call a stored procedure - assuming you have a full-blown version of SQL Anywhere for Windows CE on your device). I also like to use the tool to create reports on the fly when I'm not in the office, since I don't have access to my PocketBuilder installation to build DataWindows. I do it with the Pocket SQL Workbench and save the history file to use it later in my office to enhance my application accordingly.

The full source code is available as usual on Sybase CodeXchange (www.sybase.com/developer/codex change); feel free to enhance it for your own needs. I wish you a happy time coding PocketBuilder and spying around within the SQL Anywhere databases on it.

More Stories By Berndt Hamboeck

Berndt Hamboeck is a senior consultant for BHITCON (www.bhitcon.net). He's a CSI, SCAPC8, EASAC, SCJP2, and started his Sybase development using PB5. You can reach him under [email protected]

Comments (0)

Share your thoughts on this story.

Add your comment
You must be signed in to add a comment. Sign-in | Register

In accordance with our Comment Policy, we encourage comments that are on topic, relevant and to-the-point. We will remove comments that include profanity, personal attacks, racial slurs, threats of violence, or other inappropriate material that violates our Terms and Conditions, and will block users who make repeated violations. We ask all readers to expect diversity of opinion and to treat one another with dignity and respect.