The roll-your-own approach requires the most effort, but it yields the big advantage that it can be tailored specifically for your users’ needs. You can analyze exactly what your users need and build a query system that perfectly matches their requirements.
This article describes a simple, single-program query management tool. The tool makes it easy to establish a set of SQL database queries that users can select from a menu system that runs on their own personal computer. The system is easy to set up, can be tailored to manage any desired query set, and is flexible in its back-end database support.
The Design
Figure 1 shows how a typical query panel appears to the user.
Figure 1
It lists however many queries have been defined in the system’s query control file (in this example, four). The user selects a query and then sees results displayed in a Microsoft Excel spreadsheet.
Figure 2 shows a partial listing of results as they might appear in the Excel spreadsheet.
Figure 2
This example output displays the results of the fourth query in figure 1, “EPoS Sales by Profile.”
Figure 3 diagrams how the system works.
Figure 3
A single program running on the local PC obtains query information from a local file text named SQLmenu.txt. This program displays the query selection panel of figure 1 to the user, reads his selection and sends the relevant database query to the back-end database server. In our case, the database server is a Windows machine running Microsoft SQL Server. But this same design could easily be used, with very minor code changes, to process against other databases such as MySQL, Oracle, or DB2.
When the query results are returned, the program loads them into an Excel spreadsheet and displays them to the user. After viewing query results the user leaves the program.
A Solution in ooRexx
We implemented a solution for this design in the Open Object Rexx programming language. Often called “ooRexx” for short, Open Object Rexx is a full implementation of the Rexx scripting language standards, plus all the features of a complete object-oriented programming language. ooRexx has objects, classes, messaging, single- and multiple inheritance, data hiding and encapsulation, overloading, and a large built-in class library. The big advantage to ooRexx is that it is very easy to learn and to use, yet it packs the same power as other, harder-to-use scripting languages.
OoRexx is an open source programming language supported by the Rexx Language Assocation. The product and all its manuals and documentation can be freely downloaded from the project home page www.ooRexx.org. You can also download ooRexx from SourceForge at http://sourceforge.net/projects/oorexx/. The download Rank you’ll see displayed at SourceForge indicates that ooRexx is a very popular product.
To run our program you’ll also need to download the free Rexx/SQL and ORexx/SQL database interfaces from SourceForge at http://rexxsql.sourceforge.net/. These products interface between the ooRexx program we’ll discuss below and the SQL database with which it interacts.
The Menu Control File
Two files are required on the user’s PC for the coding solution —
1. The query definition or “control file” named SQLmenu.txt.
2. The ooRexx program
The query definition file contents and format look similar to this example —
* Here is a query without any replacable parms, but which returns nulls
List of EPoS Shops {SELECT * FROM WTEP_Debtors order by AccountNumber ;}
* This one accepts an account and a relative date as parms
DelNotes {select deliverynote, deliverydate from DebDlvJour where deliverydate > ^1^
and InvoiceAccount like '%^2^ ;'} (Startdate,DATE,-30) (Account,TEXT)
Comment lines begin with an asterisk (*). Each of the non-comment lines represent a query the user can select from the query menu. Each has this format —
Menu Text {Query} (replaceable parm 1) (replaceable parm 2)…(replaceable parm n)
The Menu Text is the query description the user sees on the query menu. Query is the actual SQL query itself. It can contain replaceable parameters marked up as ^1^, ^2^ …^n^. The ooRexx program prompts the user to enter values for these replaceable parameters. Figure 4 provides an example.
Figure 4
The definition for each replaceable param is —
(prompt text, datatype[, predefined value])
The prompt text is what the user sees, the datatype is the data type of the replaceable parameter, and the predefined value is the default value given the user when they are prompted. Note that, if the datatype is DATE, then the predefined value may be expressed as a number of days relative to the day that the program is run. For example, –1 means yesterday.
Figure 5 shows how sample lines in the control file correspond to the prompts a user enters values for.
Figure 5
Remember that the ooRexx program will place these values into the SQL query that it sends to the database.
Once you’ve created the query control file, save it in the root directory as c:\SQLmenu.txt. This is where the ooRexx program expects to find it.
The ooRexx Program
Download the source code archive containing the ooRexx program that implements our solution.
We’ll walk you through this program now. We’ll keep it simple and at a high level due to space limitations. For detailed coding information, please refer to the books and manuals freely available at the ooRexx web site and that download with the product.
Following the comments at the top of the script, this code sets up an error handler for any SQL errors that might occur in the program. Like many scripting languages, ooRexx allows you to trap “exceptions” or errors through the CALL ON condition statement
call on USER SQLError NAME SQLException /* Set up SQL Error trapping */
.
.
.
SQLException: /* SQL Error Handler */
/* ------------------------------------------------------------------------- */
call SQLError Condition("O")
This code starts program execution by creating a menu dialog object and running it. When the user ends his interaction with the program, the code de-installs the object and terminates with an exit statement --
MyDialog = .SQLMenuDialog~new /* Create the menu dialog */
MyDialog~Execute('ShowTop') /* Run the menu dialog */
MyDialog~DeInstall /* Destroy the menu dialog */
exit /* The end */
The two requires statements establish addressability to the class libraries for the Windows graphical user interface and the SQL interface —
::requires 'oodwin32.cls' /* Windows GUI */
::REQUIRES 'SQLObj.frm' /* Orexx/SQL from http://rexxsql.sourceforge.net/ */
With the preliminaries out of the way, script defines a class named SQLMenuDialog that is composed of these four methods –
| Method: | Purpose: |
|---|
| Init |
Reads in the query control file, SQLMenu.txt, into an array named ControlArray. |
| DefineDialog |
Creates the basic query menu presented to the user. |
| Run |
Handles the user’s interaction with the basic query menu. |
| Unknown |
Prompts for and reads any required replaceable parameters for the query the user selects and validates those user-input parameters. Then, connects to the SQL database, issues the SQL query on the database to retrieve results rows, activates an Excel spreadsheet as an OLE object, places the retrieved data into the spreadsheet for the user to view, and drops the database connection. |
In ooRexx, the Init method automatically runs whenever an object of its associated type is created. In this case, Init reads the query control file on the user’s PC, called SQLMenu.txt, into an array named ControlArray. ControlArray information is accessible to all the other methods through the expose statement encoded at the start of each.
The DefineDialog method creates the basic query menu presented to the user, while method Run animates the menu panel for user interaction. These two methods together create the basic menu panel, display it to the user, and handle user interaction with it through the selection of a SQL query.
The method Unknown does the bulk of the work in this program. In ooRexx, the method named Unknown accepts any messages that do not otherwise have methods assigned to them. In our program it’s a convenient way to handle the dynamically allocated buttons in the main menu panel.
The Unknown method first locks up the basic menu panel to prevent further user interaction with it, since the user has already selected a database query.
Next, the Unknown method parses out the replaceable SQL query parameters from the query control file information (now in the ControlArray). The method prompts the user to input any required replaceable parameters by invoking the multiInputBox object with this code—
do until valid?
if parmNo > 0
then pValArr = multiInputBox(defTag,'SQL Query',pTagArr,pValArr)
After the user inputs the data, a large block of code validates it.
The next task is to connect to the SQL database, so that the program can issue the SQL query. This code connects to Microsoft’s SQL Server —
/* Get a connection to SQL database ----------------------------------- */
host = 'LOCALHOST'
pwd = 'xxxxxx'
database = 'Accounts'
MyCon = .SQLConnection~new(host, pwd, database)
If you wanted to run the program against some other database, such as Oracle or MySQL, you typically only have to change this connection code. The remainder of the program will work without changes, as long as you issue standard, portable SQL queries.
This code issues the SQL query and retrieves the result set—
ResultSet = .SQLResultTable~new(MyCon,query)~~Open
ResArr = ResultSet~FetchRowSet
With results in hand, the program gets an Excel spreadsheet object to hold the report. The Excel spreadsheet is merely an OLE (or ActiveX) object to the program —
/* Get a spreadsheet object to contain the report --------------------- */
xl=.OleObject~new("Excel.Application") /* create Excel Spreadsheet*/
xl~Visible = .True /* change to .false to do in background */
xl~Application~DisplayAlerts = .true /* Show save dialog etc. */
xl~Application~SheetsInNewWorkbook = 1
xl~WorkBooks~Add
One of the complexities it faces is that the program does not know in advance the data types for the columns that will be retrieved by the SQL query. A substantial chunk of code handles this problem so that data will appear appropriately in the Excel spreadsheet cells.
Finally, the Unknown method drops the database connection to SQL Server –
drop MyCon ResultSet
Then, the script clears the dialog and exits the method.
Summary
We’ve shown how you can write a single, simple program to manage users’ SQL queries.
You can easily adapt this system to any set of SQL queries and their required input prompts simply by editing the textual query control file.
You can also render management of this system as simple as possible by pointing all users to a single query control file on a shared local area network server.
Along the way we’ve introduced the Open Object Rexx scripting language, a free, open source programming language that’s become quite popular. OoRexx is easy to learn and use yet it fully supports object-oriented programming and provides the kind of power needed in today’s programming.
Jon Wolfers is a software developer for the trading arm of a charity. He uses Rexx to tie retail systems to accounting systems as well as tools for stock control, ordering and merchandising.
Howard Fosdick is the author of the Rexx Programmer’s Reference, a tutorial and reference that covers everything about Rexx and introduces Open Object Rexx. He manages the www.RexxInfo.org web site.
Add
to del.icio.us
