The alstonlabs.com HTMRPT tool is a scriptable HTML reporter that allows a simple query or stored procedure to generate the raw data. The following code is very small and can remain so because there are no issues with page formatting, different paper sizes, etc. The idea is to have a small and fast tool that allows reports to be generated that can then be viewed online, emailed, and so on. Users can print copies as they see fit directly from their browsers.
As you study the code, note that there are numerous design goodies, but the most notable part is the lack of using ODBC or other driver. Instead, the program speaks directly to whatever client DLL is the target. (In the code shown here, the target is MSSQL 6 or 7.) To change targets, all you have to do is replace the GetSQLData() and PutSQLData() procedures with your target specific implementations.
Interestingly the target DLL syntax is fairly standard; the calls to MSSQL (in the example) look a lot like the MySQL implementation.
But wait, isn't it faster/better/more reliable to use ODBC?
The short answer is NO. What the design cookbooks and all that seem to assume is that you are creating a TWO-TIER application; i.e. your app talks directly to the database table such that on-screen fields have a one to one correlation to target tables. ODBC requires that you have a database friendly type assigned to each. While this type checking is a reliable way for apps using two-tier I/O, it is not necessarily a good thing for general purpose data extraction for reports. When you design a report using a traditional report generator, you're assigning fields and the data type that the field will accept. The upshot is that changing fields around or returning a different number of rows/columns requires reprogramming. Moreover, although the promise of ODBC is that an app can talk to different back ends, in reality this is untrue due to the need to have strict control over table/column names, etc. In HTMRPT we studiously avoid all typing and naming.
The HTMRPT tool does not require reprogramming to generate reports with different returned types or column counts. It accepts any arbitrary number of columns in a report. How this is accomplished is simple: the query or stored proc used converts the data to CHAR format as it returns it. HTMRPT accepts this as ASCII data and the control script tells it that column N, although ASCII, should be treated as (name your data type.)
For HTML reporting this also makes sense to do it this way. To show numeric data on a web page it needs to be converted to ASCII anyway, so why not use this requirement to our advantage?
About The Code
The basic premise is the use of three TStringList class instances and the interface to the database target. In this case the target is MSSQL so the GetSQLData() and PutSQLData() are target-specific. These can be easily replaced for whatever the target is.
The TStringList classes (for all practical purposes, an array of char[][]) are used to hold all data. The querylist instance reads the query file from the disk and contains the query to generate the data as well as the formatting instructions (e.g. column 1 is text, column 2 is numeric, that sort of thing.) The reportlist instance holds the raw returned data from the query results. Each column returned (an arbitrary number) is seprated in a single string from the rest of the columns by use of a delimiter. We use a semicolon as a default; if your data contains these then use a pipe or some other legal char value. Each string in the reportlist is one returned row.
The htmllist instance holds the output. Essentially, the output is constructed in a loop reading the reportlist using the instructions from the querylist. The column by column definitions are manipulated in the columnplan[] array. All it does is tell you "position X is really this data type." Tags are attached as required. The ASCII text output is written to disk using the built-in 'SaveToFile' method with an html friendly extension. The default browser in this example is then invoked and handed the filename.
Regarding talking to MSSQL directly, some of you may wonder if this is a legal or proper thing. The answer is YES. All of the interface is available in the MSSQL "books online" documentation. Of all companies Microsoft would probably be the LAST to publish a great deal of information on an interface that it doesn't want you to use. Interestingly all of the calls are found in NTWDBLIB.DLL and you can verify this with a utility such as TDUMP.EXE (found in the C++ Builder \bin\ directory.) All you need to do to use the DLL is create a *.LIB file using IMPLIB or similar utility (which is in the same \bin\ directory.)
About HTML
A number of you have worked with HTML and yet still perceive it as the domain of generator programs such as Front Page. However, HTML is little more than ASCII text with display instructions inserted directly and surrounded by angle bracket characters. The browser interprets these embedded instructions. The "runbuttonClick" event shows the process of looping through the reportlist instance and creating the htmllist elements that will become the HTML file.
HTML GENERATOR C++ BUILDER CODE
