 |
|
|
 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
David Berlind's Reality Check
By David Berlind
June 23, 2004
Regular readers may recall that I have a mobility experiment simmering on my backburner. Each week, I give the project a bit of attention--some bug fixes here, some infrastructure preparations there. But, as I learned how much heavy lifting is required to mobilize and webify the data, I started to wonder whether it might be a while before those trade show demos--the ones where your grandmother can program a fully Web-enabled transactional system with a few points and clicks--become a reality for most organizations. Yes, some progress has been made, especially where business process is involved. But before non-IT types can click and drag their way to the sort of IT that delivers serious competitive advantage, they'll need something to point to and click on first.
The challenge: Getting that "something" into place is not itself a click-and-drag operation. How much heavy lifting is required will obviously be different from one case to another. Green-field projects, for example, are unimpeded by legacy issues. Still, getting all the parts in place and tying them together is not for the weak at heart, and you'll definitely need more than a mouse to get the job done. Simply getting some of the components working on their own can be a challenge. Most companies have a legacy in place. Whether they're webifying it, mobilizing it, or modernizing it in any other way, crossing the chasm from their status quo to the bleeding edge (where competitive advantage usually lives) will require changes under the hood that may be harder than starting from scratch. Once the "change monster" rears its inevitable, ugly head, cost and aggravation will escalate. The Web services gurus will tell you, "well, if everything you're connecting or modifying was an XML-based Web service, change wouldn't be so stressful or costly." The "reduced cost of change by way of integration standards" pitch is the supposed promise of Web services. Unfortunately, not only does it take a rocket scientist to sort through the tangle of standards, but it also takes one to figure out just which discreet components of your IT should be a Web service, which shouldn't, and what to do about it. The Promised Land is being dangled before our eyes, but we've been left to our own devices (and not a mouse) to erect the bridge that gets us there. One way around the problem -- impractical for most -- is to buy everything from one vendor. Of course, everything integrates perfectly then. For my own project, I was using Visual Basic for Applications (VBA) to integrate some e-mail processing routines into Outlook 2003. The project started with a very modest goal: After reading an e-mail, I wanted to reply with a canned response that wasn't quite as impersonal as a form letter. Once I discovered the programmability of Outlook, I started to get carried away. Before long, not only was I processing hundreds of e-mails in the same time that I previously processed 20 or 30, but I also was driving a significant amount of data (including the e-mails themselves) into a Microsoft Access-based relational database. Soon, the plethora of data stored in the database had become just as valuable as the productivity gains. For example, I can easily look up the contact data of correspondents who work for a particular company. Most vendors are represented by public relations companies. Now, with my system, I can see which PR companies work for which vendors, and which individual at a PR company does that work for a particular vendor. If I need a reader's input on a particular story, a quick query to the database will show me the name and contact information of every reader with whom I've corresponded that has an interest in, say, Java. Not only does the application do everything I want it to do; it does everything others want it to do, too. When I show it to others, they melt and say "I want it. When can I have it?" Functionally speaking, the application's specification is finished. But, technically speaking, it's time to make it a little more industrial strength before I consider giving others access. There must be a million other projects out there like mine. They start small, but when others see the way they improve one person's productivity, changes are requested. Let's make the data available for query on the corporate intranet or on the Internet. Let's make the database updatable by multiple users-- including those outside the company where appropriate. Let's make it accessible to our people on the road.. And by the way, let's make the distribution of both the data and the software idiot proof. What started as a little script that bridges Microsoft Outlook and Access has to grow up-- not only to support me, but maybe even others. In terms of databases, Access, my only legacy, would have to go. (VBA is another legacy that will have to give way to VB.NET.) Access simply doesn't have enough horsepower for an enterprise application of this sort. With no other corporate standard to worry about, I had one luxury: I could standardize on one company's database technology to keep data in synchronization between a central database server (the one that will also power any Web interfaces or Web services) and any mobile or stationary devices (notebooks, handhelds, or desktops). Most database companies offer some form of database synchronization with foreign databases. iAnywhere's (a division of Sybase) MobiLink database synchronization technology, for example, comes with vendor-specific scripts to keep a desktop or handheld instance of its Adaptive Server Anywhere (ASA) database server in synch with databases from Oracle, IBM, and Microsoft. As a side note, I'm going with iAnyWhere's database technologies on the client-side on the basis of the strength and maturity of its mobility-driven solutions. iAnyWhere's mobile database servers (ASA and the smaller footprint UltraLight) work on both PocketPC and PalmOS, support stored procedures and triggers (ASA only) and, in addition to synchronization in typical client/server style, provide for data synch and mobile access for companies with a proclivity towards Web-based development rather than application development (through iAnyWhere's AvantGo technology). For the central server, I could have picked an IBM, Oracle, or Microsoft database for the central data repository while staying with ASA on the client devices. But, as I plowed through iAnywhere's documentation, it became clear that going with ASA across all three tiers (handheld, workstation, server) was the surest bet to the unattended longevity of a synchronization-dependent solution. Also, I ruled out Microsoft's SQL Server because I did not want the overhead of an entire Windows Server to run a small database. ASA runs on almost any operating system. I have it running on a Red Hat Linux 9-based Pentium II box with 192 MB of RAM and the system isn't even breaking a sweat. Of course, it isn't getting hammered yet. But if and when it does, giving it more juice is the least of my worries. With the database choice made, I dreaded the idea of cutting over from Access to a new database. First, I would have to move the data. Second, I would have to fix the code to point to the new repository. Although both phases are now complete, neither went as smoothly as I had hoped. As do other database vendors, Sybase provides a tool to upsize a database from MS-Access to iAnyWhere's ASA or even ASA's big sister -- Sybase's Adaptive Server Enterprise. The first problem I encountered was that the upsizing tool didn't work with Access 2003. My project stalled. When Sybase upgraded the tool to support Access 2003, I was back in business. Not only did the upsizing tool copy the tables and data from Access to ASA, it also changed the pointers within Access to point to the ASA database rather than the old JET-based tables. (Access uses the JET engine for its database.) The upsizing tool also makes copies of the JET-based databases and stores them under a new name; if the upsizing operation doesn't work, you can easily restore your Access-based setup to its original state. This is where I learned that Access is far more forgiving than a real database server when it comes to designing tables, assigning primary and foreign keys and indices, and creating relationships. The upsizing tool choked on several design inconsistencies, which forced me to go back and clean up not only the design of my tables, but some of the data as well. Although this process was just one cost of the overall change, I was glad to do it because it brought my database up to snuff. Another issue, whicht Sybase is now addressing, is that the tool imports the tables, but not the queries. (In ASA parlance, a query is called a "view.") The beauty of queries is that, to an application, they're treated like tables. Instead of programmatically handling relationships between tables in my code, I was taking advantage of the way queries can insulate the complexity of those relationships to external code or applications. For example, some of my drop-down menus were driven by queries rather than the tables themselves. You can imagine what happened when my VBA code, which uses Microsoft's ADO interface to access databases, looked for a query that didn't exist (post upsizing operation). So, I re-created the queries by hand through Sybase's front-end tool called Sybase Central. With my queries recreated, I crossed my fingers and ran the application again. Boom. VBA's debugger sprang to life and complained about a line in my code that it had never complained about before. It was the Visual Basic method (recordset.update) for permanently committing data that was just loaded into a record. The database didn't like something about the update operation, but I wasn't sure what. To recreate the problem outside of VBA, I went into Sybase Central and used a series of SQL statements to attempt the same sort of update, and it worked perfectly. Stymied, I went back to the application, found a chunk of code where I could run a small test, commented out some of the lines that used Visual Basic's built-in methods for creating, updating, and committing a record, replaced them with SQL statements, and ran the program while watching what happened as each line executed. Boom. A SQL expert would know this, but I didn't: After creating a new record with a SQL statement, you have to run another SQL command ("@@IDENTITY") before you can probe the newly created record for its automatically generated record number. VB doesn't require this extra step. Thanks to Sybase's technical support, I was furnished with some sample code to do the VB equivalent of getting a newly created record's number, and voila, that part of the code started working. But now, I was faced with hunting down every instance of the same issue in my code and fixing it--a task about which I had mixed feelings. On one hand, I really didn't want to rewrite all that code. On the other hand, rewriting the code as SQL made me think about how that might improve the code's portability to some other language. For example, Microsoft's C# (a language that I wouldn't mind trying) or even Java (if I want to port the application to a non-Microsoft platform). ASA's rejection of my VB code haunted me to the point that it didn't matter whether a workaround existed. I checked with Sybase's technical support to see if there was a logical explanation before I deployed the SQL workaround. Based on the information available, Sybase tech support determined that ASA was complaining that my code might not be compatible with the ANSI standard for SQL. Their suggestion was to set a flag that turned ASA's ANSI compatibility off before manipulating any records. The suggestion worked. Even though it did, I'm leaning towards going with the SQL approach anyway. (Let me know if you think this is a mistake.) After all this turbulence simply to swap the database on the machine that's local to the application, I've yet to get to the three tier synchronization part. I haven't even scratched the surface of all the changes that stand between my application's current single-user, monolithic state, and a multi-user, webified, Web-servicized and mobilized solution. Nevertheless, I'll stay the course. Somewhere down the road--supposedly--is the big payoff. Meanwhile, there's a sign hanging on my door now that reads, "This is a no click and drag programmer zone. Grandmothers keep out." You can write to me at david.berlind@cnet.com. If you're looking for my commentaries on other IT topics, check the archives.
What do you think? |
|
|
|
|
|
|||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
|
|
|
|
|
|
|
|
|
 |
|
 |
|
 |
|
 |
|
 |
|
 |
|
|
|
|
|
