Uses an HTML template file containing embedded SQL statements and optional CGI parameters to run and format a report. Supports nested queries, so you can do breaks and rollups.
It works with mod_perl and Perl versions 5.5.3 and later. It has been used extensively with MySQL, but should work with any SQL database supporting the DBI:: interface.
FWM 11-Jun-2005 format(), subclassable methods; No functional changes.
Refactored format() and created subclassable methods,
for easier.. subclassing.
properties; The template is now stored as a property.
FWM 17-Aug-2003 format() $cgi and $dbi became non-optional with 2.0.
Shame on me...
FWM 12-May-2003 query() If a field is undefined, set it to an empty string.
This gets rid of unitialized value errors in the logs.
FWM 08-Mar-2003 ... Implement nested/iterative queries.
format() opt (a hashref containing additional
substitutions) as an optional parameter
was removed. This is easy for the
caller to do for themselves, and they get more
control that way anyway.
Licensed under the same terms as Perl itself, with no warranty or claims of fitness for any particular use.
The following properties may exist for the object:
The methods here are called by format(). They have been structured to make it easy to subclass and extend SqlHtmlRpt. All routines are expected to return undef on success or an error message on failure unless otherwise noted.
Called prior to CGI parameter processing. The init flag is provided for extensions. This method does nothing here.
Processes CGI parameters into field_hash to be used as substitutions.
init indicates that the report is being called in initialization mode and that valid parameters cannot be expected. Extensions may want to do something special with this.
One global check is performed, which is that a bare quotation mark is
disallowed since this breaks most INPUT tags in HTML forms.
The following parameters are supported. If an error is found they should return undef and store a description in the errstr property.
Handles numeric parameters.
Handles string parameters. Basically nothing to do here, escaping is performed later during the actual substitutions.
Handles unchecked, raw parameters. Unsafe for substitution into SQL statements unless you do some additional checking yourself before calling SqlHtmlRpt.
Prepares a report from template, using the substitutions in field_hash.
This routine should return the formatted report. If an error is encountered it should return undef and store a description in the errstr property.
Instantiates a SqlHtmlRpt Object.
disconnect()Calls disconnect() on the passed dbh.
Uses a SQL statement embedded in an HTML template to query the database
and perform substitutions into the template to format the returned query
set. $dbh->selectall_arrayref() is used to retrieve the query set.
Certain CGI parameters can be used in the SQL statement; see the
CGI Parameters section and the SQL Statement section. Substitutions
performed into the template are described in the Templates section.
Also, see the Usage Notes section for an overview of how this widget
should be used and how it works.
If an error is encountered, this routine should return undef and leave an error message in errstr.
<SQL>SELECT * From Foo</SQL>
<!--SQL SELECT * From Foo -->
Certain substitutions will be performed on the SQL statement based on CGI parameters.
See the SQL Statement section as well. format() looks for CGI parameters starting
with sqlx1 and continuing until it finds one which is undefined, where x is n,
s, or p. Only one parameter with a given number is processed, for example
sqln1, sqln2, sqls3, sqln4. It substitutes the corresponding values for instances
of %%sqlxn%%.
The SQL statement is presumed to be a SELECT statement. As noted, substitutions can be performed into it with a one-to-one correspondence between CGI parameters of the form sqlx1..n and cliches within the SQL statement of the form %%sqlx1..n%%.
Insertions occur into the template according to the following syntax. The syntax of a substitution tag is %%tag%%.
liststartn where
n starts at 1 for the first level of subqueries.
The following parameters are repeated for each record.
sqlfx_n, where x is 1 for the first level
of subqueries, and so on.
Fields from outer queries are available for substitution into subqueries.
Typical usage is to create an HTML template with several elements.
This object is generally called from some other enclosing CGI handler. This handler is designed to be called initially, and when it sees that this is the case it calls SqlHtmlRpt->format( ..., ..., 1 ), that is with init set to true. What ends up happening is that the user sees a blank (or defaulted) form and an empty result set.
At this point the user fills in query information and clicks the submit button. This time the enclosing handler sees that this is not an initial call. It validates the user (and perhaps the CGI parameters), and allocates a database handle. It then calls SqlHtmlRpt->format( ... ).
format() now scans the template to locate (and strip) the SQL statement. It then
iterates over sqlx1..n until it finds an undef and performs the appropriate substitutions
into the SQL statement. It also goes back to the template and substitutes the values
back in as desired, so that the user will see the values that she entered when
the result set is returned.
It then calls dbh->selectall_arrayref() to query the database.
Provided the result status indicates success, clones everything between the liststart
and listend tags and substitutes columns for the appropriate sqlf1..n tags, and repeats
this for every row in the array.
You should find a sample report template accompanying this module.
Want help? Fred Morris has been a licensed business in Seattle Washington since 1984. I have extensive experience writing and using reporting tools. I helped write Park Software's XENTIS report writer for OpenVMS (and the mother of all report wizards), working on its development off and on for about 8 years. Not only have I used XENTIS in production environments, I have extensive experience with a variety of SQL (and other) databases as well as with ACI's 4th Dimension and Microsoft's Access.
We can provide training, consulting and even do it all for you: contact us!