Tom Moran
Microsoft Corporation
October 23 1998
The following article was originally published in the Site Builder Network Magazine "Servin' It Up" column (now MSDN Online Voices "Servin' It Up" column).
The big news is that Service Pack 4 for Windows NTŪ 4.0 has been released. If you haven't downloaded it yet, take a look at it. You can download it from the Microsoft Support Online site. Then check out all the fixes in Knowledge Base (KB) article Q150734 and new features in KB article Q194303. Service Pack 4 is very comprehensive, and has more than 400 fixes. For developers, several previously released security issues were rolled into this, and several Internet Information Server (IIS) issues were also resolved. Several things were fixed regarding heap and memory management; if you have a poorly behaved app (one that handles freeing memory incorrectly), I've heard you may see some strange behavior. As soon as a KB article is posted describing more of the specifics, I'll let you know.
Don't forget that if you send me a tip or question and I use it, I'll send you a really cool MSDN T-shirt. In this month's question, TeeJ asks about pulling images from a database using ASP.
Anyway, it's time to finish up our small application. For those of you who are just now joining us, we have over the past two months done the following:
What's left? After all, we now have an application that we can use to randomly put quotes on our Web page. Is that it? Hardly! We could go on for months with this, slowly expanding it, finding out where it breaks and where it doesn't scale, making this a public database, adding maintenance and external submission routines, upsizing Microsoft Access to Microsoft SQL Server, and so on. In reality, this is what most Web applications go through. But we'll wrap it up this month by doing two things:
First, we'll get the correct record count. To do this, we'll use a Windows Foundation Classes (WFC) function called getRecordCount, which is a property of the Recordset object. The syntax is basically:
Recordset rs; int i = rs.getRecordCount();
One might think this is all that is necessary. However, if you just added code similar to this, the variable i would invariably become -1. That is because the cursor type matters. By default, our cursor type is forward-only. Great for performance, but not so great if we have to do anything beyond making a single pass through data. To avoid this incorrect result, we open the record set using a static cursor, which allows the getRecordCount property to work correctly. There are other cursor types to be aware of -- keyset and dynamic -- which would work, but the static cursor is great for our purposes. The finished function looks like this:
public int ReturnRecordCount() { Connection c = new Connection(); Command cmd = new Command(); Recordset rs = new Recordset(); c.open("DRIVER={Microsoft Access Driver (*.mdb)};DBQ=e:\\downloads\\quotes.mdb"); cmd.setActiveConnection(c); cmd.setCommandText("SELECT * from quote_detail"); /*set cursor type to be static */ rs.setCursorType(3); rs.open(cmd); int i=rs.getRecordCount(); rs.close(); c.close(); return i; }
We've basically copied the data access code from the getRandomQuote function. If I were going to do any more than this, I would create functions that open the connections and close the connections, since that would make the code much easier to maintain. Note that wherever I am connecting to a database, I am also closing the connection as soon as I am done with it.
Next, we'll add the appropriate error checking. We want to wrap all of the data access in a try/catch block, and if an exception occurs, deal with it in an appropriate manner.
We'll first add a try/catch block around our code. Just above the line that starts with "c.open" in your getRandomQuote function, add
try{
To add the catch, place the following code just below the "return str;" line in the same function:
} catch(com.ms.wfc.data.AdoException e){ str = DumpADOErrors(c,e); } return str;
If an exception occurs, we will branch down to the catch block, format an error string, and return the error string instead of a quote. We'll talk about the DumpADOErrors function in just a moment.
To test this is pretty easy. I've chosen to change the path for the database in the connect string and then recompile. The smarter way, which you've probably already figured out, is to rename the target database file, which would involve no rebuilding, and no shutting down and restarting of IIS.
Before adding the try/catch, we would have received a somewhat cryptic 8000xxxx error. Now we see the following, based on the invalid path:
AdoException -> [Microsoft][ODBC Microsoft Access 97 Driver] '(unknown)' isn't a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides. ADO Error[0]->[Microsoft][ODBC Microsoft Access 97 Driver] '(unknown)' isn't a valid path. Make sure that the path name is spelled correctly and that you are connected to the server on which the file resides. ADO Error[1]->[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed
Not that this isn't somewhat cryptic itself. However, the point is that we are starting to take control of what happens. The DumpADOErrors function is simply a helper function that puts the connection and exception objects into a formatted string for us to display:
public String DumpADOErrors( com.ms.wfc.data.Connection conn, com.ms.wfc.data.AdoException adoEX ) { String str = "AdoException -> " + adoEX.getMessage(); if ( null != conn ) { for ( int i=0; i<conn.getErrors().getCount(); i++ ) { str +=" ADO Error[" + i + "]->" + conn.getErrors().getItem(i).getDescription() ; } } return str; }
Normally, we could use the standard Java System.out.prntln to print out the error messages -- but since this is a COM object on a server, we wouldn't see that on the client. Returning the error message in a string instead of returning the quote assures us that the message is seen on the client.
It is now not only easier to figure out what went wrong, but we can customize the messages as well. We can add instructions for the user, such as an alternate Web site to try, a support phone number, or a message to try again later. We also can dump out information to help us troubleshoot, such as the path and other variables. We can automatically try a backup server. We can even supply a different quote string altogether, choosing not to display the ADO error messages; the user might never know there is an error unless he refreshes several times and notices that the same string keeps coming up.
That's it. The intention of this sample was to get you started on the road toward creating a server object that interacts with a database and can be accessed using ASP. There is a lot more to be done to make it a robust, scalable, maintainable solution. However, I find that often the hardest part is just getting things to compile and work together, and we should be there now. Good luck.
Until next month,
Tom Moran
Tom Moran is a program manager with Microsoft Developer Support and spends a lot of time hanging out with the MSDN Online Web Workshop folks.
This question is answered in several places, and I'll provide pointers in just a moment. In fact, I wasn't even going to address this topic, since so much information is available. However, when I looked, I realized those sources were quite difficult to find. It is an extremely popular question, and is especially relevant in auction and catalog applications, which are becoming so popular. There are a variety of ways to get images from a database using ASP technology, and how you do it really depends on two things:
In general, it is a more accepted practice to store links to your images rather than the entire image. Performance will generally be much better, and the code is much simpler, easier to maintain, and not as subject to changes in versions, back-end storage changes, and so on. However, the articles below will help you regardless of your approach. One of the issues with using Access is that it puts an OLE wrapper around each image, and you have to deal with that.
I've tried to locate all of the relevant articles on this, and have outlined them below:
My personal favorite:
Other good resources:
Older articles, but you might find them useful: