Ehrlich's Rule: "The first rule of intelligent tinkering is to save all the parts."

In the Trenches with LAROKE

Konsultant's Log, Cyberdate 03.04.2002 (Database Facelift Part I: Where the Hekawi?)

SITREP: Those who visit this site on occasion know nothing's been happening here for twenty-five months. I've been in the trenches all that time but I haven't been recording it for posterity. What prompted me to put my visor on and start log entries again is that I'm starting to wade chest deep into a massive project that will require a lifeline of documentation before I proceed much farther.

A sleeping dog gets a kick in the ribs

That sleeping dog is me. I administer the various computer databases at the architectural firm where I spend my days, some of which are compatible and some which are not. I use a variety of interfaces. Some are "command line" and downright dangerous. Nobody else in the organization knows how to do this stuff and I take my job title "Merlin" seriously. If something happens to me, however, the company will be up the creek. I know, I know . . . the cemetaries are full of "indispensable" people but, nevertheless, the company would face great hardship for awhile due to my unforseen absence. I managed to hand-code my way through the Y2K transition period on a day-to-day basis, but the fact that a great deal of the system is hopelessly antiquated remains. It's sometimes hard for me to face this fact since I'm the antique who originally programmed it.

The boss's daughter, Christine, knew something had to be done so she took it upon herself to outsource some programming upgrades. I first learned of this after the contract was signed. One of the consequences of not involving me from the gitgo is that the contracting company salesman made a series of assumptions based on our forms and painted his programmers into a corner. The dog of a Beta they came up with just won't hunt. I'll describe the situation more fully below.

This chain of events got me up off my dead butt unto my dying feet. I've been pondering the transformation of our data systems for several years now but have been teetering on the point of indecision as the technologies continue to change at ever faster rates. When it first appeared, I purchased Microsoft Visual FoxPro 3.0 and the FoxExpress RAD development environment for it, but then hesitated as everything started to turn internet-centric towards client-server. I experimented in this direction with Redhat Linux but was somewhat daunted by the learning curve. As of the date of this log entry, various technologies have advanced to the point that I believe I can begin a flexible data systems makeover without massively re-educating myself.

INTREP:

Ya can't figure out how to get where you're goin' til ya know where you're startin' from

First order of business: What is the current configuration of our database systems? OK, this is gonna get ugly. feel free to use the air sickness bag on the seatback in front of you at any time.

The following table represents the various applications currently in place.

* indicates data entry duplication

Some of you may notice that QuattroPro is a spreadsheet application and that WebSTAR is a web server. Architectural billings can be complex due to the "ad hoc" nature of every project. Custom summary sheets are provided to clients who request them based on their own organization's needs. We are still dealing with the basic elements of time and materials, but they are presented in different arrangements. I will still have to prototype the summary sheet at the beginning of each project, but I want each month's summary sheet to be "populated" from the database in lieu of the keyboard entry methods currently in place. In addition, the company intranet is not updated often enough because it is hand-coded from beginning to end by yours truly. It's way past time to upgrade to a data-driven model.

TPO:

With several years of research (tech site web surfin') under my belt, I decided on the following choice of weapons:

1. Apache Web Server for Windows
2. MySQL Client/Server Database Management System
3. PHP Scripting Language
4. Visual FoxPro 3 Professional

I chose Apache for Windows to get a fast start in a operating system environment that I'm familar with. This is a small office intranet. Later, if I need a more robust and secure Apache environment, I can switch to the Linux version. The Apache environment is required for the the MySQL database to run in.

MySQL is one of the most popular open source implmentations of the Structured Query Language database management systems on the Internet.

PHP, in turn, is also a very popular open source scripting language for embedding in HTML to interface with MySQL databases.

Visual FoxPro 3 Professional is a powerful Object-oriented flat file DBMS in its own right based on the famous dBase (Vulcan) language. It can also be used, in conjunction with ODBC, to build sophisicated clients to interact with MySQL

In another seeming manifestation of Carl Jung's "Synchronicity" I received a Microsoft offer in the mail to upgrade my Visual FoxPro 3 Professional to Visual FoxPro 7.0 Professional for about $300. Synchronicity describes a series of seemingly unconnected, but related, events or, as I like to say, "Timing is everything". I upgraded to Visual FoxPro 7.0 and am now in the process of wading thru the documentation.

When asked why he always kissed his leading lady's hand, Douglas Fairbanks replied "You have to start somewhere."

I too, have to start somewhere. To paraphrase Howard Roark, "An architect has clients in order to build. S/he does not build in order to have clients." Everything in an architect's office revolves around and relates back to "The Project". Therefor, the first area of the database that I will convert to the new system will be the project list (or jobs list, or commission list, if you prefer).

TACMO:

9:45 AM 11/09/2001 Grumpy was mothballed about 15 months ago when I couldn't resolve the occasional lockup problem to Christine's satisfaction. She's been forlornly gathering dust since then in a corner of the Krash Lab. I need a database server platform for redevelopment of the vpa database and Grumpy will serve (no fun intended) my purpose, so I connected her to the Cybex Commander switch using the also mothballed Old Blue connections and turned her on with my fingers crossed hoping the CMOS battery had not died and lost all the BIOS settings . . . She came back from the dead with flying colors. I set about uninstalling programs and users except for the admin user, me.

After adjusting the BlackICE Defender firewall settings on Pentagon to allow Grumpy to get thru, Microsoft Windows Critical Updates were downloaded/installed. (at least some of them - it was a painful process fraught with lockups and at one point the firewall quit letting Grumpy have access again). I turned off the advanced video card features and the lockups have gone away (for the time being anyway).

Downloaded and installed PHPTriad for Windows v2.11 - this includes the Apache for Windows web server v1.3.14, MySQL relational database v3.23.32, PHP scripting language v4.0.5, PHPMyAdmin v2.1.0 and Perl - nsPerl scripting language 5.005_03 . . . This is a lot on my plate to learn about. Could not bring up the documentation webpages thru the Apache server until I told the MSIE Browser to bypass the WinGate Proxy Server/Firewall for requests to http://localhost/ which is what Apache is initally configured for. I guess my first task is to configure Apache and Grumpy so the other vpa machines can see it.

OK, Dazed and confused but by the end of the day I had other machines seeing the Apache server and could run a PHP script as well as administer the MySQL database server. I also downloaded and installed MySQL ODBC driver so I will be able to access the MySQL databases with MS Access or maybe even FoxPro once I figure out how to configure it.

2:41 PM 11/10/2001 Installed MS Visual Foxpro v3.0b database development system on "Buc Nekid", my development workstation, in hopes the ODBC would be reinstalled and show up in the Control Panel . . . No Joy, Oh well I wanted to reinstall VFP anyway. Tried same thing with Windows 98 setup CD and Office 2000 setup CD - still in the dark here. Then, I started looking for it on the hard drive - finally located the ODBC configuration module and put a shortcut to it on the Desktop. Next, I installed the ODBC drivers for MySQL. Finally figured out that these drivers only have to be installed on the Client machine, not the database server. This was not readily apparent to a ODBC novice like myself. Setup a test datasource entry for the MySQL server on Grumpy. Opened MS Access 2K and setup a ODBC link to the datasource - Eureka, Dynamic connection to the MySQL database via Access!

MISREP:

OK. We're off to the races. This particular log entry was started a few months after the events it covers, so it isn't very detailed. There will be more detail in upcoming log entries on this subject. For awhile, I was scrambling around in the experimentation phase just trying to discover if what I wanted to accomplish was doable with the chosen tools. I've had my nose stuck in a few books the last two months 'an I've convinced myself that I have a workable solution. Next, we'll git down to it.

LAROKE Microcomputer Consultants
155 East Boca Raton Road
Boca Raton, Florida 33432
(954)857-5830 (Tel)

Issued Monday March 4, 2002

copyright © 1996-2002 LAROKE Microcomputer Consultants all rights reserved

Search www.laroke.com the Web for