The Margarita Story
One of the issues VB programmers often have to deal with is a change from Access and DAO to SQL Server and ADO. OLEDB and ADO do have a different architectural focus than DAO and I came up with what I call "The Margarita Story" to help explain that difference.
The Bars of Microsoft Street
Let's say you're thirsty for a margarita. You go down to the local bar area (for instance, in Austin it's called Sixth Street; in Chicago, it's Rush Street - at least it was when I lived there...). We'll say it's called Microsoft Street here.
First, you see an establishment called "Access Bar" and figure that sounds okay, so you go in. There's a long bar with a bartender and some bar stools. You sit at one of the stools and tell the bartender you'd like a margarita. The bartender gives you a bottle of tequila, a bottle of triple sec, a bottle of lime mix, a glass, the glass salter thingee, and some limes. Since this is the Access bar, you take all these ingredients which have been passed from the back of the bar over the bar to you, and proceed to mix them together with your hands. Your hands then lift the finished drink up to your mouth and your mouth is happy because it has a margarita.
After a few of these drinks made this way you're getting tired of having to mix them yourself. Besides having to focus on processing the drink, you're weary of waiting for the bartender to pass all those raw ingredients across the bar each time. You stumble out of the Access Bar and resolve to seek further.
A few doors down on Microsoft Street you see a sign for "SQL Server Bar". It looks a little more expensive and there's someone at the door checking IDs. Why not? you think, and go in.
The set up is roughly similar: the long bar, the bartender, the bar stools. But, there is a difference: when you sit down and order a margarita, instead of the bartender handing you all those bottles and the glass salter thingee, etc., you get handed a simple margarita. Once again, your mouth is happy - it has a margarita.
Wow! you think, this is cool! Much faster! I didn't have to spin all those cycles worrying about how to process a margarita. And it didn't take as much time! I can drink a bunch more margs!
Back now to database land, where instead of the Access Bar we call that concept "Single Tier" and instead of the SQL Server Bar we call that one "Two Tier". With Access, even if all your tables are kept on a separate machine - say, a central file server - in order to process that data, it all (the bottles, glass salter thingee, etc.) has to travel across the network to get to your client workstation where the query is run and the data formatted for display and/or printing (this is the mouth, incidentally).
With SQL Server, the query is run at the database server, which has its own data engine, and then just the slimmed down result set (the margarita) is returned to the client workstation for display and/or printing. Your monitor and/or printer does not care whether the processing occurred on a server or on the client, BTW - there is no difference to the final output - just like your mouth doesn't care if your hands or the bartender's hands processed the margarita.
(But, wait! you cry! what about DAO's DBEngine???! That's an engine, right??? Well, yes. But it runs only on the client.
It's closing time at the SQL Server Bar and as you leave you notice that a bit further down the street is a different bar - this one is called "Enterprise SQL Server Bar". We'll go check that one out tomorrow...
The Enterprise SQL Server Bar
It was a dark and stormy night; a darkness relieved only by the screaming neon of the "Enterprise SQL Server Bar", the sole establishment open on Microsoft Street in the wee hours of the morning. You've been driven to this pass by your unrelenting desire, no, need for the perfect margarita...
After making it through the ID checking portal, you are whisked away to a cozy room, filled with tables and chairs. "No bar??" you ponder warily as you take a seat, "I'm used to dealing directly with the bartender. Hmmmmm." Just then, as though by magic, a cocktail server approaches your table and asks what you'd like to drink. "Can't I just go up to the bar and get my drink?" you ask. "No, at this bar all your drink requests have to go through me. I pass them along to the bartender and then return with the result," replies the cocktail server, a little annoyed that you wanted to deal directly with the bartender.
You order a plain margarita, blended, with lots of salt, and as the cocktail server disappears behind a heavy door you sit back to wait and see what happens (trying to ignore that pulsey-sounding noise and the word "energize"...)
One the other side of the door, the cocktail server presents your order to the bartender for one plain margarita, blended, with lots of salt. The bartender goes behind the bar, mix, mix, whirr, whirr, pour, and hands the finished margarita to the cocktail server.
You're surprised at how quickly the cocktail server returns with your drink, but perhaps it's not quite quick enough. "I've got it!", you exclaim to yourself as you sip your icy concoction, "I'll order a bunch of drinks and that way I'll have them here and ready." You flag down the cocktail server and say, "I'd like 10 margaritas please, only this time use special expensive tequila."
"I'm sorry, but at this bar we have rules and you can only order one margarita at a time."
"But a friend of mine was here last month - just after you opened - and said she got three at once."
"We used to allow more than one drink to be ordered at a time but then the bar kept sending back panicked messages about not being designed to take it and not knowing how far they could push it."
"Oh, okay, then. If that's the way it has to be. One will be fine." You sit back again and wait.
But not for long! The cocktail server had conferred with some managerial looking employee on this side of the heavy door. "Apparently the bar does not allow customers who haven't paid a cover fee to order margaritas made with special expensive tequila so your request for the margarita made with special expensive tequila cannot be processed," the cocktail server reports without bringing a drink. Slightly bummed, you agree to take another plain margarita.
(Your mouth, BTW, is still quite happy, despite the lack of the special expensive tequila, and has not noticed that a cocktail server, nor the other managerial-looking employee has entered the equation.)
It's now last call. You and everybody else at the bar have placed their drink orders. Many cocktail servers, who all strangely look similar as though they were pressed from the same cookie cutter, disappear through the magic door to the bar carrying messages about many drinks.
You were a bit alarmed watching all those cocktail servers descend on that door at once. In your experience only one bartender functioned at a time. "It's going to take forever to get my drink," you mutter testily.
Back at the bar, the cocktail servers line up to request drinks. Fortunately, there are many bartenders - a cluster, one might say - there who step up to the counter to get the order request and then step back to make the drink. Each cocktail server, laden with drinks, goes back to the seating area and distributes the drinks. It really didn't take that much extra time. Many mouths were made happy.
So, back to Database land again, this is called "3-tier" or "n-tier" (remember the managerial-looking employee? - that could be considered another tier). The important thing you want to get out of this is that you never directly deal with the data (the bartender). If the data (bartender) is only focussing on processing (making drinks), it can churn out a lot more result sets. If the data has to worry about making sure only one request comes in at a time and that it's properly formatted (customer asks for more than one drink at a time or customer asks for drink to be served on a plate instead of a glass), that's processing cycles that cannot be used for just data.
Middle-tier components - the cocktail server and the managerial-looking employee - are the go-betweens of the client and data, enforcing business rules and delivering either data results or error results from the data server to the client.
I'm sure at this point you're wondering how mistakes aren't made like your margarita getting someone else's pina colada mix in it. Tomorrow I'll talk about how something called acid prevents that from happening. (Lest I get our course site blocked by filtering software please let me clarify that we are not talking about illicit chemical substances here... :-)
ACID (well, limes *are* acidic...)
On your way home from Microsoft Street it really starts to perplex you how all those last call drinks were processed without any mistakes.
There was the couple at the next table who was told that since the bar was out of creme de cacao that they couldn't have grasshoppers. It would have been so easy for the bartender to have left out the ingredient and left no one the wiser, but the cocktail server came back and said, sorry, but either the drink as a whole can be made or it's not made at all. (The couple opted for vodka tonics instead.)
Then there was the group of noisy conventioneers at the table in the center. You heard them exclaiming at how marvelous it was that their pina coladas tasted exactly the same each time - the consistency was amazing.
Yourself, you wondered the whole night how it was that such a busy bar never made a mistake and got, say, the conventioneers' pina colada mix into your margaritas. It was as though each drink was made inside some protected bubble where unwelcome ingredients had no chance to interact.
And, lastly, the table in the corner - the one whose cocktail server got sick and had to turn his orders over to another server - they got their drinks just as they ordered them - pretty cool considering the change over.
Atomicity, Consistency, Isolation and Durability. These make up the ACID property of data transactions. If all these conditions are met, the changes may commit. If any of these conditions are not met, the transaction cannot occur and any changes are rolled back.
A classic example of atomicity is software installation (a data transaction, really - it places data on your hard drive): if the installation is aborted because of, say, low disk space, the installer doesn't copy just the files that will fit, it refuses to copy anything and quits the installation. It's an all or nothing proposition. If you want a database example, if one of the fields cannot be updated, then the record as a whole will fail to update. If the creme de cacao is missing, no grasshopper.
Consistency relates to the integrity of the data and is often discussed in terms of field constraints such as "no duplicates". Some of you have first hand experience seeing how well databases like to have duplicate values in primary key fields. You can't change the ingredient measurements on a pina colada and call this modified recipe "pina colada", as well - there can be only one pina colada and it's made to exacting specifications.
Isolation is what keeps coworkers' data entry from being saved into the record you're working on. Each data transaction is done in isolation from all other data transactions. One bartender's margarita transaction cannot affect another bartender's pina colada transaction.
A transaction is durable if it can survive a crisis situation like a power failure - or the cocktail server getting sick and having to turn orders over to a substitute. If the record has been updated, those changes should persist even if something "weird" happens.
This is a simplification (obviously) of enterprise data workings. The Distributed class (and test...) goes a lot more into transactions and Microsoft Transaction Server (MTS). I believe I recommended the Ted Pattison book Programming Distributed Applications with COM and Microsoft Visual Basic 6.0 (Microsoft Press, ISBN 1-57231-961-5) in another post. He explains this stuff far better than I do! :-)
Alrak's Course Resources ©2002-2022 Karla Carter. All rights reserved. This material (including, but not limited to, Mini-lectures and Challenge Labs) may not be reproduced, displayed, modified or distributed without the express prior written permission of the copyright holder. For permission, contact Karla.