Several years ago, Mike M’s company decided that they needed to build an in-house Content Management System portal application. Well, actually, the lead developer made the decision… and the recommendation… largely because writing applications are boring. But writing platforms upon which you can write applications… now that’s exciting!
Two years and several failed “proof of concepts” later, and the company had portal driven by a VB6 application with a SQL Server database behind it. If one were to create a list of adjectives to describe this monstrosity, “stable,” “reliable,” and “accurate” would be strikingly absent. However “stillborn” would probably make the top of the list. When Mike took over, the application had 200 forms, one auxiliary code module, and zero classes.
As for the SQL Server database, it had nearly 200 tables (mostly imported from Microsoft Access) and not a single stored procedure. Boolean-type values were represented as a CHAR(5) field, holding possible values of 'true', 'false', or NULL. Let me repeat that, because it bears repeating. Boolean-type values in the database were rendered as a CHAR(5) field holding possible values of 'true', 'false', or NULL.
There were also no indexes. Nor any primary key columns. Or even any auto-incrementing IDENTITY columns for that matter. For inserts, an ID was assigned via in-line SQL by requesting the Max(ID) field of any particular table and adding 1. "Duplications?" you ask. Why yes. Yes, there were.
The code module contained such gems as this function:
Function FileExists(f As String) As Boolean Dim ff As Long On Error GoTo BadFile ff = FreeFile Open f For Input As #ff FileExists = True Close #ff On Error GoTo 0 Exit Function BadFile: FileExists = False On Error GoTo 0 End Function
There were also several dozen Crystal Reports, each pointing to a specific table in the database. The database table for any particular report was erased and repopulated via in-line SQL statements each time the report was run. As for how that was actually accomplished, following is the basic report code. It was essentially repeated, line-for-line, on each report in the application.
Function GenerateSomeReport(sdate As String, EDate As String) As Boolean Dim st As String, lt As String Dim fn As String, ln As String Dim counter As Long Dim itmx As ListItem, Started As Boolean Dim dt As New Recordset, dt2 As New Recordset GenerateSomeReport= True Started = False Load frmProcess frmProcess.Caption = "Generate Some Report" frmProcess.Show frmProcess.Imsg = "Deleting old Report..." frmProcess.pMin = 0 frmProcess.cCancel.Enabled = False DoEvents dt.Open "SOME_REPORT_TABLE", cn, adOpenKeyset, adLockOptimistic If Not dt.EOF Then dt.MoveLast frmProcess.pMax = dt.RecordCount dt.MoveFirst Do While Not dt.EOF dt.Delete dt.MoveNext frmProcess.pVal = frmProcess.pVal + 1 DoEvents Loop End If frmProcess.cCancel.Enabled = True frmProcess.Imsg = "Generating..." frmProcess.pMin = 0 DoEvents st = "SELECT * FROM TARGET_TABLE WHERE " st = st & "SOMEDATE >= ('" & sdate & " 00:00:00') AND SOMEDATE <= ('" & EDate & " 23:59:59') " st = st & "ORDER BY ID;" dt2.Open st, cn, adOpenKeyset, adLockOptimistic If dt2.EOF Then msg = "No records match the report criteria!" MsgBox msg, 48, frmProcess.Caption GenerateSomeReport = False GoTo GetOut End If Started = True frmProcess.pMax = dt2.RecordCount ContGen: Do While Not dt2.EOF And frmProcess.tag <> "C" dt.AddNew For X = 0 To dt.Fields.COUNT - 1 dt(X) = dt2(dt(X).Name) Next dt.Update dt2.MoveNext frmProcess.pVal = frmProcess.pVal + 1 DoEvents Loop GetOut: If frmProcess.tag = "C" Then msg = "Are you sure you want to cancel generating the report?" rsp = MsgBox(msg, vbYesNo + vbDefaultButton2 + vbQuestion, frmProcess.Caption) If rsp = vbNo Then frmProcess.tag = "" GoTo ContGen End If GenerateSomeReport = False GoTo CancelGen End If CancelGen: frmProcess.Hide Unload frmProcess dt.Close If Started Then dt2.Close End If End Function
Few interesting things to note...
- frmProcess was a simple form with a progress/status bar and a cancel button. Pressing the button set its tag value to "C," which canceled the report.
- Each leftover row in the table (from the last time the report was run) is deleted one at a time, and repopulated one at a time. 'DELETE|TRUNCATE TABLENAME' anyone?
- Each row in the selection recordset was added to the report table not just row by row, but field by field. Apparently 'SELECT ... INTO' was too new-fangled to be trusted.
- Imagine what happens should two people run the same report at the same time.
Amazingly enough, Mike lasted several years there, rewriting the application as best he could. He added a few classes to the application, pointed the reports to stored procedures (using gasp! "parameters"), deleted in-line SQL where ever he found it, and generally kept the whole thing limping along. All the while, Mike maintained a constant plea for the resources to rewrite the whole thing from scratch. Obviously, his pleas were never granted, and he eventually left the monstrosity to some unfortunate new developer.