I remember a time, years ago, when ordinary programmers were morphing into "database" programmers; I applied for a job at a company in Tucson, Arizona. The company responded to my resume by sending me a large packet of papers in a plain manila envelope.
"Odd," I thought, and started rifling through the papers in no particular order. Questions, which had absolutely nothing to do with computers, filled page after page. A letter covered the mess in a cheap, ditto-type ink (I automatically sniffed it to check), asking that I complete the enclosed forms and return them.
"That’s a Sta-9 test," volunteered my husband, blatantly snooping over my shoulder.
"What’s a Sta-9 test?"
"You know, it asks questions like – When did you stop beating your wife? – To which there is, of course, no good answer. A personality test."
"How do you pass it?" This was my standard engineer’s response to the word "test."
"You can’t."
"What do you mean, ‘you can’t’?"
"There are no right answers," he said defensively. Even in our "salad" days he could sense when an argument was starting.
"Do I have to take it?"
"If you want to get into the door for an interview, I would imagine that would be a big ‘yes.’"
So I took it. I got in for an interview, which consisted of another test, which vaguely resembled an IQ exam. The hiring process for this particular company was so long that in between the Sta-9 test and the interview test I had already been offered one or two other positions. So I felt pretty confident in asking the interviewer a few questions regarding their unorthodox potential–employee-weeding process.
"Has anyone ever failed that test you mail out?" I asked.
"Sure."
"How?"
"One guy wrote in big black letters across the test that he wasn’t going to answer any of our mind-controlling questions and that if we didn’t call him for an interview, he would sue us."
"And that was an F?"
"Absolutely."
"And an A would be . . .?"
"There’s no A, just pass and fail. We want to make sure we hire people suitable, emotionally, to handle the kind of programming we do here."
Now I was making some progress, "What kind of programming would that be?"
"SQL – A fairly new language designed to query database files."
The Problem
I can’t remember much of the conversation after that. I accepted a position elsewhere. But, even then, before the phrase "Going Postal" was ever coined, the computer industry obviously sensed that programming for prolonged periods in SQL had the potential to drive certain people over the edge.
Tens of thousands of programmers have managed to avoid SQL and survived many therapy-free years in their careers. Nothing lasts forever, right?
Database programmers are quickly morphing into "Web" programmers. And if you want to design a halfway decent Web site that accesses any kind of database file, you are probably familiar with the concept of using SQL to transform your Active Server Pages (ASP) into a viable database-driven presence on the net.
Perhaps you’ve managed to avoid it by contorting your work into a hybrid of text files and Common Gateway Interface (CGI) scripts. This is not a pretty picture, I must say, and not very secure either. Allow me to elaborate in graphic detail. I give you Exhibit A.
<SCRIPT LANGUAGE=VBScript>
Sub btnSubmit_OnClick()
‘Validate all fields that contain data.
If Len (frmUpdate.txtClassName.value) = 0 Then
Alert "Enter a Class Name"
frmUpdate.txtClassName.focus
Exit Sub
Elseif Len (frmUpdate.txtItemName.value) = 0 Then
Alert "Enter an Item Name"
frmUpdate.txtItemName.focus
Exit Sub
Elseif Len (frmUpdate.txtItemDescription.value) = 0 Then
Alert "Enter an Item Description"
frmUpdate.txtItemDescription.focus
Exit Sub
Elseif Len (frmUpdate.txtItemSku.value) = 0 Then
Alert "Enter an Item SKU"
frmUpdate.txtItemSku.focus
Exit Sub
Elseif Len (frmUpdate.txtItemPrice.value) = 0 Then
Alert "Enter an Item Price"
frmUpdate.txtItemPrice.focus
Exit Sub
Elseif Len (frmUpdate.txtLotsPurchased.Value) = 0 Then
Alert "Enter the Number of Lots Purchased"
frmUpdate.txtLotsPurchased.focus
Exit Sub
Endif
Call frmUpdate.submit ()
End Sub
</SCRIPT>
‘*******************************************************
‘ And now we arrive at the fun part. Build the SQL update string.
‘*******************************************************
<%
strSQL = "Update StockClass Set " & _
"ClassItemName = & _
CLng(Request.Form("TxtItemName")) & ", " & _
"ClassItemDescription = & _
CLng(Request.Form("txtItemDescription")) & ", " & _
"ClassItemSku = " & _
CLng(Request.Form("txtItemSku")) & ", " & _
"ClassItemWholeSalePrice = " & _
CLng(Request.Form("txtItemPrice")) & ", " & _
"ClassLotsPurchased = " & _
CLng(Request.Form("txtLotsPurchased")) & ", " & _
"Where ClassName = ‘ " & _
‘ Create and Open the Database Object
Set objConn = Server.CreateObject ("ADODB.Connection")
ObjConn.Open "DSN=Stock"
‘ Create the Command Object.
Set objCmd = Server.CreateObject ("ADODB.Command")
‘ Set the Command Object properties.
Set objCmd.ActiveConnection = objConn
objCmd.CommandText = strSQL
objCmd.CommandType = adCmdText
‘Execute the command.
obj.Cmd.Execute
‘ Display the update string.
Response.Write "The following Update string was executed and " & _
" values updated in the Stock table. <P>"
Response.Write strSQL
‘ Close and de-reference database objects.Set objCmd = Nothing
ObjConn.Close
Set objConn = Nothing
End If ‘ and pass the peas . . .
%>
If you suffer from the same affliction that I do, you just scanned through that code snippet to establish the gist of what it seeks to accomplish and concluded that it really doesn’t do very much at all. At the risk of stating the obvious, it accepts four fields in text format and puts them into a database record. If you ever actually get a job where that is the only level of detail required to maintain a company’s data files, then call me. Geraldo Rivera and I will be right over for the interview and champagne.
In the real world of the Information Age, that code snippet would be twenty times as long. It would be littered with complex commands designed to validate the data being entered, commands to retrieve fields from one file which are indexed to the form you are filling out, in order to seed it with specific data related to the transaction being implemented. The snippet would also have devices like buttons to allow for (as Microsoft’s Windows 2000Ô so quaintly puts it) Human Interface. You know, <Save>, <Delete>, <Clear Form>, <Next Record>, <Find>, and that sort of thing. Take a breath.
I defy you to hand that off to the Whiz Kids in the eye candy department to jazz it up for the Web. And, once they are done, you get to fix it again so that it actually works.
Better Solutions
"There is always a way to do it better . . . find it!" Thomas A. Edison
I have that quote on a magnet on my refrigerator. (You thought I was going to say computer, right? I may put stereo speakers on top of the TV, but I would never put a magnet on my computer.)
There is a better way to write a functional Active Server Page, which allows meaningful interaction between a database file and the Web. Remember that professor in college who taught functions using the "Black Box" example? Dig back down into your procedural roots. It’ll come to you. What you have here is a tangle that desperately needs organization. And, once organized, it transforms into an efficient, manageable, usable application. Break it into logical layers and arrange them in a design that allows them to work as one tool. At the basic level, the data files, with all the structures, types, and relationships described in their file definitions, establish the rules of the data: key fields, capital letters only, masks, etc. Set up the rules of the interface. Design the document that the audience will see on the Web.
Enough with the esoteric lecture, here is what it would actually look like.
Taking the SQL snippet above and fleshing it out with some basic error checking and formatting, your new Active Server Page would look something like the following program:
<table border="0" Class="EntryTable">
<tr>
<%FieldError=oStock.DDValue("Stock.Itemname",DDFIELDERROR) %>
<td Class="Label">
<% If FieldError<>"" then
response.write("<Font color=""red"">") %>
Itemname
<% If FieldError<>"" then response.write("</Font>") %>
</td>
<td Class="Data">
<input type="text" size="10" title="" name="Stock__Itemname" value="
<%=oStock.DDValue("Stock.Itemname") %>" >
</td>
<% If FieldError<>"" then %>
<td Class="Error"><%=FieldError%></td>
<% end if %>
</tr>
<tr>
<%FieldError=oStock.DDValue("Stock.Itemdescription",DDFIELDERROR) %>
<td Class="Label">
<% If FieldError<>"" then
response.write("<Font color=""red"">") %>
Itemdescription
<% If FieldError<>"" then response.write("</Font>") %>
</td>
<td Class="Data">
<input type="text" size="20" title="" name="Stock__Itemdescription" value="
<%=oStock.DDValue("Stock.Itemdescription") %>" >
</td>
<% If FieldError<>"" then %>
<td Class="Error"><%=FieldError%></td>
<% end if %>
</tr>
<tr>
<%FieldError=oStock.DDValue("Stock.Itemsku",DDFIELDERROR) %>
<td Class="Label">
<% If FieldError<>"" then
response.write("<Font color=""red"">") %>
Itemsku
<% If FieldError<>"" then response.write("</Font>") %>
</td>
<td Class="Data">
<input type="text" size="14" title="" name="Stock__Itemsku" value="
<%=oStock.DDValue("Stock.Itemsku") %>" >
</td>
<% If FieldError<>"" then %>
<td Class="Error"><%=FieldError%></td>
<% end if %>
</tr>
<tr>
<%FieldError=oStock.DDValue("Stock.Itemprice",DDFIELDERROR) %>
<td Class="Label">
<% If FieldError<>"" then
response.write("<Font color=""red"">") %>
Itemprice
<% If FieldError<>"" then response.write("</Font>") %>
</td>
<td Class="Data">
<input type="text" size="10" title="" name="Stock__Itemprice" value="
<%=oStock.DDValue("Stock.Itemprice") %>" >
</td>
<% If FieldError<>"" then %>
<td Class="Error"><%=FieldError%></td>
<% end if %>
</tr>
<tr>
<%FieldError=oStock.DDValue("Stock.Lotspurchased",DDFIELDERROR) %>
<td Class="Label">
<% If FieldError<>"" then
response.write("<Font color=""red"">") %>
Lotspurchased
<% If FieldError<>"" then
response.write("</Font>") %>
</td>
<td Class="Data">
<input type="text" size="10" title="" name="Stock__Lotspurchased" value="
<%=oStock.DDValue("Stock.Lotspurchased") %>" >
</td>
<% If FieldError<>"" then %>
<td Class="Error"><%=FieldError%></td>
<% end if %>
</tr>
</table>
</blockquote>
<hr>
</form>
The field error variable you see peppered through this entry table returns a message to the person typing in the information. It appears in red at the field where there is an error. This form, when submitted, calls on a Web business object (WBO) to validate the action being taken and the information being submitted. A very basic WBO would look like this:
Use cWebBusinessProcess.pkg
//IDE-UseDD-Start
Use Stock.DD
//IDE-UseDD-End
Object oStock is a cWebBusinessProcess
Set psDescription to "Stock"
Set pbClearAfterSave to False
//IDE-DDO-Start
Object oStock_DD is a Stock_DataDictionary
//IDE-DDOsettings-Start
Send DefineAllExtendedFields
//IDE-DDOsettings-End
End_Object // oStock_DD
Set phMainDD to (oStock_DD(self))
//IDE-DDO-End
// Enable/disable WBO operations
Set pbAllowSaveNew to True
Set pbAllowSaveEdit to True
Set pbAllowDelete to True
Set pbAllowClear to True
Set pbAllowFind to True
Set pbAllowDDUpdate to True
// Register WBO interfaces
Send RegisterStandardInterface
// Send RegisterDebugInterface
// Send RegisterFullErrorInterface
// Send RegisterAutoEntryInterface
End_Object // oStock
This in turn, calls up the Data Dictionary in which you have decided the rules for your data.
If there is an error in how your data is being handled, if there is a change required in the data file or structure, or if you want to add new stuff, it is simply a matter of editing the Data file and Data Dictionary. Do you need to mask a field? Make a field read only? There is no need to pick through an avalanche of ASP-SQL code to make sure you’ve hit every possible place you may have affected.
This is not new. This is the way database programmers have been doing it for years. It’s just that now, a database language has actually evolved with the programmers to the Web. (For example, Data Access Worldwide has introduced a product called WebApp Server. See http://www.webappdeveloper.com or the evaluation download page at http://www.webappdeveloper.com/evaluationform.asp .)
Download a demo. Take it for a test drive on your NT test machine. And, never build another SQL Query string for as long as you are sane enough to program.
Proposion N2N connects Microsoft .NET applications to Lotus Notes and Lotus Domino databases. This ADO.NET managed data provider allows you to perform blindingly fast queries and updates of Notes data from ASP.NET pages, .NET web services, Windows, or Mobile applications. An innovative SQL-like query language leverages the unique features of Notes and makes collaborative software accessible to relational database programmers.
Unlike text-based file formats image files aren't made up of words, which makes searching for an image file by keyword difficult. Instead of being able to simply open the file to see what it contains, we're stuck looking at the text around it and other metadata to determine the image's meaning. In this article, Ziran Sun shows you how to build a simple database-based image keyword system that allows you to associate keywords with images and use these keywords to make finding images easier. [Read This Article][Top]
In the second part of of his article on using MySQL with ASP.NET, Ziran Sun covers how to add a new MySQL user to the database server, assign the user the appropriate permissions, connect to the database, and build a simple ASP.NET page to perform a query. [Read This Article][Top]
Back in the days of classic ASP, if you were building a database-driven
web site, your choice was either to invest a lot of money to get a copy of Microsoft SQL Server
(or some other enterprise-ready database) or invest a lot of time finding a way to deal with the
performance and scalability limitations of Microsoft Access. Luckily these days there's
another viable alternative: MySQL. [Read This Article][Top]
Moving or copying a SQL Server database from one machine to another requires a lot of preparation in order to ensure a smooth transfer. In this article, Dina Fleet Berry examines the different methods and highlights the different issues associated with each of them. [Read This Article][Top]
There are many times when using SQL Server 2000 Query Analyzer to debug SQL statements is a better choice than debugging in Visual Studio .NET. In this article, Dina Fleet Berry explains why and walks you through the debugging process step-by step. [Read This Article][Top]
As a follow up to his article on retrieving objects from SQL Server using SQLXML and serialization, Gianluca Nuzzo discusses saving objects back to SQL Server using a schema definition file and updategrams. [Read This Article][Top]
One area that stands out when comparing ADO.NET 1.x to ADO.NET 2.0 is transaction processing. Bill Ryan shows just how easy transaction processing has become with the TransactionScope object in ADO.NET 2.0. [Read This Article][Top]
Developers often use brute force coding to marshal data between the GUI and application objects. In this article, Luther Stanton explains how to use .NET's out-of-the box data-binding functionality to make this job much easier. [Read This Article][Top]
Learn how to create a console application to queue a message in Microsoft Message Queuing (MSMQ) and then use an extended stored procedure to call the console application from a SQL Server trigger. [Read This Article][Top]
Connection pooling increases the performance of Web applications by reusing active database connections instead of creating a new connection with every request. This article shows how to monitor the connection pool, diagnose a potential problem, and apply the appropriate fix. [Read This Article][Top]
Mailing List
Want to receive email when the next article is published? Just Click Here to sign up.
