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.


Index

About smartDataPump

Brief overview [Shrink]

License and Download

Not freeware, not shareware ... [Shrink]

Prerequisites

Technical stuff as well as philosophy [Shrink]

Installation

Plug-in and customizing [Shrink]

Evaluation

Sample reports and a software demonstration using the sports database [Shrink]

Attribute Filters

How to suppress technical attributes and how to select the attributes used to describe upper levels (parent SDOs) [Shrink]

Internationalization

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]

Related Software

Information and other solutions dealing with generic data output in the Progress® world [Shrink]



About smartDataPump

[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

[Shrink]

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.

Download:
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.

Credits:
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!



Prerequisites

[Shrink]

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 ;)



Installation

[Shrink]

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!

Done.



Evaluation

[Shrink]

Demo Software


Installation
· 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'.

Demo window - click on the image to view it in full size (712*525)

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.



Sample Reports


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.

Static HTML
· Customers.htm
· Customers-printedHTML.pdf shows the repeated headings on every page (printed with IE6.0)
· CustomersUSonly.htm
· Orders.htm
· OrderLines.htm

CSV
· Customers.csv (delimiter=';')
· Orders.csv (delimiter='|')
· OrderLines.csv (delimiter=',')

Excel
· Customers.xls
· Orders.xls
· OrderLines.xls

View screen shots of the smartDataPump's user interface.



Attribute Filters

[Shrink]

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.


Internationalization

[Shrink]

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

[Shrink]

The primary source for attribute properties is the database schema. The secondary source is the running smart data object.


Current SDO


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.

Field Order#:
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.

Width-Characters:
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.


Parent SDOs


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.


Sort Order


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

[Shrink]

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 ...



Related Software

[Shrink]

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
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.



Author: Sebastian
Last Update: May 2005   Web Feed

· Home

· Products

· smartDataPump

· Web Links

· Link to us

· Contact

· What's new

· Site map

· Get Help


Most popular:

· Site Feeds

· Database Design Guide

· Google Sitemaps

· smartDataPump

· Spider Support

· How To Link Properly


Free Tools:

· Sitemap Validator

· Simple Sitemaps

· Spider Spoofer

· Ad & Click Tracking



Search Google
Web Site

Add to My Yahoo!
Syndicate our Content via RSS FeedSyndicate our Content via RSS Feed



To eliminate unwanted email from ALL sources use SpamArrest!






Digg this · Add to del.icio.us · Add to Furl · We Can Help You!




Home · Categories · Articles & Tutorials · Syndicated News, Blogs & Knowledge Bases · Web Log Archives


Top of page

No Ads


Copyright © 2004, 2005 by Smart IT Consulting · Reprinting except quotes along with a link to this site is prohibited · Contact · Privacy