The Smart Data Pump is an add-on for the Progress® ADM2 framework. Feed it with the handle of any smart data object and it produces user defined reports in various output formats. The plug-in process is painless for developers: download and unzip the source code, add a CTRL-O trigger to your browser class, run the install procedure, compile your application, done. The Smart Data Pump analyzes the smart data object at runtime, extracts all data definitions, asks the user for optional low and high values per attribute and sends the selected data to Excel®, a web browser, or creates a CSV file (XML or PDF output and other formats can be added with ease). The user can define stored reports by saving the current selection including field orders and suppressed attributes in the application database.
Brief overview [Shrink]
License and Download
Not freeware, not shareware ... [Shrink]
Technical stuff as well as philosophy [Shrink]
Plug-in and customizing [Shrink]
Sample reports and a software demonstration using the sports database [Shrink]
How to suppress technical attributes and how to select the attributes used to describe upper levels (parent SDOs) [Shrink]
How to translate the user interface and output constants [Shrink]
Field Sequence, Labels and Output Formats
Describes where these properties are collected and whether the user or the developer can change them or not [Shrink]
Weaknesses and Omissions
Read this before you implement the smartDataPump [Shrink]
Information and other solutions dealing with generic data output in the Progress® world [Shrink]
The smartDataPump was developed in a client/server project, where the users were moaning because the data delivered by a few thousands of smart data browsers weren't printable. Producing individual reports was no option, since the development team shrunk to a maintenance team without an expandable budget, and, from a technical point of view, this approach (although demanded) smells fishy.
I offered the concept exporting the browser data to a generic 'output device', got a go and developed my plan. While coding the output part, Steven Lichtenberg's and Geoff Crawford's article The Data Pump Process (local PDF) and Jeff Pilant's Temp Table Expositor were a great help. It was not possible to include their code, thus I hope I didn't violate their copyrights.
This said, what does the darn thing do?
Once plugged in into an application making use of the Progress® ADM2 framework, the smartDataPump can be called with the handle of a running smart data object (SDO) and creates a report from the current query. Users can define stored reports based on the data the SDO could deliver. That means, users get access to all instances of persistent attributes represented by the RowObject temp table, as long as the developer didn't filter them out. 'Could deliver' means, that the smartDataPump fetches all values from the database, regardless how small the bunch of records currently hold by the RowObject temp table is. The pointer to the current row in the SDO/browser/viewer is not touched, returning from the smartDataPump the user finds his screen unchanged. The user can suppress attributes, change the field order, enter low and high values per attribute to limit the output, and choose the output format.
How does the smartDataPump work?
During the install process, the smartDataPump has added a sequence and two tables to the first connected database (respective DICTDB) to store it's default parameters, strings by language (messages, widget labels/tooltips/screen-values, output constants) and more. These tables are used to store user defined reports, identified by the SDO's relative file name and report titles.
The smartDataPump is started by the client, usually launched by a CTRL-O trigger added to the smart data browser class, a button or whatever. It has one input parameter, the handle of the browser's or viewer's SDO (SdoHandle = DYNAMIC-FUNCTION("GetDataSource":U)).
If the SDO is running on an application server, the smartDataPump creates an instance of itself on the SDO's AppServer. This ensures all startup parameters like connected databases etc. remain the same on the server side. Once running in 'shared C/S mode', database accesses get handled by the AppServer instance of the smartDataPump. If the SDO is running locally, the client needs access to all databases involved.
Now the smartDataPump extracts all buffers (database tables) and the current (even filtered) query from the SDO and offers all attributes defined in the SDO (RowObject temp table) to the user. Depending on the user's input, the smartDataPump defines a dynamic temp table with the selected fields, which is (usually) populated by the AppServer and then copied to the client. This dynamic temp table contains only records matching the user's selection per attribute (low value and/or high value, Null (unknown) allowed yes/no) and only the selected attributes in the field order the user has defined (defaulted by the RowObject's field order).
Once the dynamic temp table is received from the AppServer, the output part is done on the client side. The user can select the application launched to open the output, depending on the output format (from a maximum of 15 applications per format). After launching the external application with the outputted data, the smartDataPump deletes itself on the client and the AppServer as well, freeing all allocated resources.
The default output format is HTML, because usually a web browser is installed on every client machine. The smartDataPump creates printable HTML. That means, headings are repeated on each printed page (works at least with IE5+ and Mozilla, more info here: The World Wide Web Consortium (W3C) Wish List on Printing HTML Documents).
License and Download
You can download the smartDataPump for free, but please read further:
If you think it saves you a lot of work, please consider a reasonable donation.
If you use it with a product you sell, I'm asking for a fair license fee.
In both cases please click the 'Contact' link on the left to get in touch with me (as always, PEG members get better prices).
Removing, translating or changing the copyright notices is not allowed. Removing, translating or changing the 'This report was created by the smartDataPump' string (this includes the embedded link to this article) is not allowed. If you pay for my software, the second point is open for discussion.
In any case, if you find bugs please keep me informed. If you need changes, please ask me before you develop them yourself. Chances are good that I or one of my pals can provide a ready-to-use solution. If you plan to translate the user interface and/or the output constants, please contact me.
Please don't publish the source code. Link to this article instead, because I'll update the source files every now and then.
Preview the source code:
By saving any part of the source code you accept the license agreement above.
· DataPumpVersion.i Version string, release notes.
· config-DataPump.i Configuration, attribute filters ...
· config-Applications.i Ordered list of applications used on client systems to open the reports.
· DataPumpSequence.df Adds a sequence to the application database.
· DataPumpTables.df Adds two tables to the application database.
· p-install-DataPump.p Installation procedure.
· p-DataPump.p Calls the smartDataPump on the client side, passing a SDO handle to the super procedure.
· sp-DataPump.p The super procedure running on both the client and the server machine.
· w-DataPump.w The user interface.
· help-DataPump.i Include used to call the application's help system.
· p-DataPumpInitLang.p Stores default settings and translated strings in the application database.
· sample-call.i Explains one approach to make the smartDataPump callable from every smart data browser within the application.
By downloading the software you accept the license agreement above.
· sportsDemo.zip Demo software using the sports database.
· smartDataPump.zip The smartDataPump.
· getUniqueFileName.p, getOsPath.p, getUUID.p Free utilities used by the smartDataPump.
Partly the smartDataPump code (sp-DataPump.p -> Excel® output part) draws on the free utility tt-file.p by Jeff Pilant and Patrick Tingen. The article The Data Pump Process by Steven Lichtenberg and Geoff Crawford influenced the architecture of the output part. As always, the PEG Archives were a great help.
I consider your feedback valuable. Please invest a few minutes and drop me a message. Thank you!
The smartDataPump was developed with Progress® 9.1D under Win2k, fat clients with connections to a couple of enterprise databases and AppServers on a Win2k machine. The ADM2 framework was a modified and patched 9.1C version. It should run fine with higher versions, because I've used only ADM2 functionality documented in 9.1D/F, especially no pseudo syntax relying on include files which may disappear or change in later versions.
To make use of the smartDataPump, the clients need a current version of a web browser and optional MS-Excel®. So far the technical stuff.
The implementation of generic solutions can become a PITA, when the business application was designed with blurred standards and/or the underlying model lacks a strong naming convention. The smartDataPump partly relies on consistent naming rules, since all information it uses internally as well as on the surface is extracted from the database schema and SDO definitions. Read my article Do's and Don'ts of Progress® Database Design to understand the importance of strong naming and design rules.
With a legacy database you'll get mad on filter definitions and similar stuff, or you'll live with crappy reports ;)
The installation process should be simple and painless, if you can work with the default settings.
Create a subdirectory 'tools/datapump' under your application directory, download the smartDataPump and extract the zip file to the root directory (files are extracted to /tools and /tools/datapump then). If you really need to change the path, read the instructions in p-install-DataPump.p. The smartDataPump makes use of 3 freebies. If you have them already installed in your 'tools' directory, save getUniqueFileName.p, getOsPath.p and getUUID.p before you extract the zip file.
Review DataPumpSequence.df and DataPumpTables.df, most probably you want to replace 'Schema Area' by a storage area containing application tables.
Connect to your application database and open p-install-DataPump.p with the procedure editor, then press [F2].
· p-install-DataPump.p adds a sequence and two tables to your database. You can load the DF files with the DataAdministration tool too, in this case this step is skipped.
· All procedures get compiled.
· p-DataPumpInitLang.p is called to initialize the database.
Open sample-call.i with the procedure editor. It explains one approach to make the smartDataPump callable from every smart data browser within your application. The smartDataPump can be launched from every non-modal user interface component where you have a valid handle of a smart data object. Implement keyboard event triggers, (popup) menu items or buttons in your smart data viewers, smart data browsers and/or smart windows, then compile your application.
Check out the generated reports with the default settings. Adjust the attribute filters in config-DataPump.i according to your naming conventions. Customize the list of applications launched to open output files in config-Applications.i. Run p-DataPumpInitLang.p afterwards to update the database!
· Create a copy of the sample database 'sports' and a (new) working directory.
· Download the sportsDemo.zip and unzip it into the working directory.
· Start a progress session with current directory = working directory and connect to the sports database (-ldb sports).
· Open tools/datapump/p-install-DataPump.p with the ProcedureEditor and press [F2] to initialize the smartDataPump (see 'Installation' for more information on the install process).
· Compile tools/* and salesOrders/*.
Run the demo
Open salesOrders/w-SalesOrders.w with the ApplicationBuilder, then choose 'Run'.
The three SDOs run locally, so you should see populated browsers for Customers, SalesOrders and OrderLines. Don't forget to change the SDO instance properties when you install an AppServer later on to run the demo in client-server mode.
Choose the 'Call...' buttons to evaluate the smartDataPump.
Customers: All records ordered by customer#
US-Customers: Only records with 'USA' or nothing in the country field
Orders: All sales orders of one selected customer
OrderLines: Kinda draft order printout, calculating totals on the quantity field
All reports were created with european settings (Day.Month.Year, comma as decimal point and dot as thousands separator). I didn't apply fancy formatting or filtering, the samples were created to demonstate the basic functionality.
· Customers-printedHTML.pdf shows the repeated headings on every page (printed with IE6.0)
· Customers.csv (delimiter=';')
· Orders.csv (delimiter='|')
· OrderLines.csv (delimiter=',')
View screen shots of the smartDataPump's user interface.
There are two settings in the Attribute Filter procedure used to filter particular attributes.
The exclude filter defines hidden attributes, for example technical stuff like timestamps or attributes used for audit trails. Primary and foreign keys ending in 'ID' or 'OID' (case sensitive!) are suppressed programatically. Example:
'*isDeleted,*Purge*' will suppress CustIsDeleted, InvIsDeleted ... InvLineToPurge, SalesOrderPurgedOn ... but not InvLineIsDeletedByMistake.
The include filter defines attributes which are used to describe parent levels when the report is started from the child level. Example:
'*Name*,*Number*' will print out the upper level attributes CustNumber, CustName and SalOrdOrderNumber from the SalesOrders-SDO (when the report was launched from the SalesOrderLines-SDO's level), but will not show CustCreditLimit or CustSalesRep, even when these attributes are available in the SalesOrders-SDO.
Note: the exclude filter overrules the include filter.
Filters are not case sensitive. They apply to all tables in all databases. The maximum length of a filter is 30,000 bytes. Edit the filters in config-DataPump.i.
All strings visible to users are outputted using the getString() function, which fetches the string in the user's language from the database. If CURRENT-LANGUAGE is not set, the smartDataPump looks for the current PROMSG file and tries to determine the user's language from the file name.
Currently, the smartDataPump 'speaks' English and German. Translating the smartDataPump is easy. Just edit p-DataPumpInitLang.p. Add your translations of labels, titles, screen-values, messages and output constants in the set* procedures. Run p-DataPumpInitLang.p to update the database. Then customize the language detection in sp-DataPump.p -> getUserLanguage().
While you add your translations to the assignments in the setProcedures, keep your session limits in mind. Either increase the -inp startup parameter from 4096 to 8192 or even higher, or split the ASSIGN statements into smaller pieces.
Before you translate the beast, please contact me.
Field Sequence, Labels and Output Formats
The primary source for attribute properties is the database schema. The secondary source is the running smart data object.
Labels and Column-Labels, Help-Text, Display-Format:
Changes in the SDO overwrite the schema property. The developer should supply reasonable properties in the SDO if the schema definitions are not suitable for output. Users cannot change these properties.
Changes in the SDO overwrite the schema property. The developer should sort the attributes in the SDO. The smartDataPump uses the SDO's sort order and incremets it decimal-walked. The user can change and save this field order number.
Changes in the SDO overwrite the schema property. The developer can define a reasonable width for large text fields in the SDO. The smartDataPump limits the column width of character fields to 50 characters, if the length of the column-label does not exceed this limit. Users can change the column width.
If the current SDO is a child of another SDO, the smartDataPump uses particular attributes supplied by the parent SDO to describe the parent level (and the parent's parent ...).
The parent level itself is designated by the label of the first buffer (database table) of the parent SDO.
Properties of attributes taken from parent SDOs can't be changed. The developer defines which attributes are fetched from parent SDOs (see attribute filters). The user can suppress attributes.
The report's sort order is determined through the SDO's current sorting sequence, thus users must sort the browser before they start the output.
Weaknesses and Omissions
Since the smartDataPump was developed in a client/server environment, it needs a few tweaks to make it usable with Progress WebSpeed®. However, the static HTML output could be of interest for WebSpeed programmers.
Because I believe that persistent arrays are evil, extended attributes are not supported. If you have to deal with old data models and legacy systems, this feature can be added. Otherwise go get a book on normalization and state of the art software and database design.
Currently, calculated fields in the SDO's RowObject are ignored.
Calculations of cell width and height are done with constants corresponding to 8 to 12 pt. standard fonts. I've no clue what happens when you change fonts and style sheets or when you run the smartDataPump with double-byte strings.
Depending on Excel® version and language, printed headings may look weird (e.g. path instead of page number).
There is no UI-help ...
The smartDataPump's approach is unique and doesn't reinvent the wheel. However, there are lots of related solutions out there, and some of them even have influenced the smartDataPump to a great degree. Carefully rethink your requirements before you implement a foreign application or utility. Check out the Progress® V9.1E ADM2 source, these SDOs talk to Excel® and Co. Evaluate all candidates:
Temp Table Expositor
Download the free utility tt-file.p developed by Jeff Pilant and Patrick Tingen. The temp-table expositor produces various outputs (CSV, Excel®, HTML, Progress® data browsers) for temp-tables and database buffers (tables) on demand. The latest release adds support for Sylk and DF output formats.
The Data Pump Process
The article The Data Pump Process by Steven Lichtenberg and Geoff Crawford explains a well thought out software architecture on generic data output. Get DataPump.p from FreeFrameWork.org or from Innov8.
GenericAUD is a set of routines to maximise the utility of Progress Dynamic Query Capability to provide a single set of routines for all reference file maintenance. GenericAUD takes as web parameters the file name and key field information and displays the records in the database for that file. It allows add/update delete capability as well as display functionality.
Dynamic Data Creation and Display
These are generic templates that are designed to manage data from a web form, create a new record and then display in table format the matching records. This structure would apply to order line type data entry where you are adding records and redisplaying as you go. The HTML form piece would need to be written for each form used and the field definitions are done via a preprocessor define. The creation and display of temp table records is based on dynamic processing. These routines are an extension of the work Geoff Crawford and Steven Lichtenberg did in creating the DataPump.p set of routines. Download
If you've developed a related software or if you find one please drop me a message. I'll put in a link here if it's not crap ;)
To avoid misunderstandings, the smartDataPump shares no similarities with Oracle's Data Pump Utility, see their white paper.
Last Update: May 2005 Web Feed