The Objects of My Affection, Part 2: Adding Data Access Using WFC/ADO

The following article was originally published in the Site Builder Network Magazine "Servin' It Up" column (now MSDN Online Voices "Servin' It Up" column).

I bought my mom a computer a little while ago so she could play around with the Internet, see pictures of her grandkids, and try out Active Server Pages (ASP) -- although she hasn't gotten to that last one yet. She decided to check out what I do for MSDN Online Web Workshop. Using the new computer I bought her, she did an Internet search for "Tom Moran." She didn't search on the Microsoft site, but rather on one of the big Internet search sites. So what did she get?

"Tom Moran, father of lies"

"Tom Moran, caught with his pants down"

"Born without a backbone"

"Deceit & Misrepresentation"

Needless to say, this was a good opportunity to do some quick explaining about the Internet and how global it is. I was thinking about this because I am putting together a database that shows random pictures whenever someone signs on to our Web page. This month's column gets us halfway there. Just keep in mind that a big part of creating a great Web application is making it easy for customers to find and use it

Based on some of the mail I received, I'm continuing with our Java object, adding in some very basic database functionality. I'll explain how to create a simple Access database that holds some quotes, and how to modify the Java object and ASP page to randomly pull one of those quotes up whenever the page is refreshed. I'll discuss using the new Windows Foundation Classes (WFC), which wrap Active Data Objects (ADO), for the database access. When it comes to working with databases, there are usually many ways to do things, but this is what I chose for this project.

To start, I created a small Access database with a list of quotes. You can download this database if you want to use it instead of making your own. It's very simple, with only three fields -- ID, author, and quote -- and it contains only four records. Note the most important point to this article: I have placed the data access in a component and am not doing it directly from ASP. Generally, your business logic and data access for a system should go into a middle-tier component, and not in your ASP code.

Creating the Database

To create the database, I simply opened up Access 97, selected Blank Database, and called it "quotes." Access automatically had the Table tab selected, so I just pressed the New button, and created a new table using the Design View. If you haven't used Access before, you might want to select the Table Wizard, look at some of the sample tables and fields, and choose from there. I already had an Excel file full of favorite quotes, so I used the Import facility to bring in the data to populate the table. You can easily type directly in the table if you wish, but you probably won't want to do that for a large table. To use the Import facility, select File/Get External Data, and then choose Import. The only trick is to ensure that the columns in your spreadsheet match those in the table. I added a row to my spreadsheet and called the columns "quote" and "author." Now that the database is created, that's it for Access; the rest of the project will be done using Visual J++™.

You might ask why I chose to use Access instead of Microsoft SQL Server. After all, don't real programmers use Microsoft SQL Server? I suppose you'll have to ask a real programmer that question. For me, Access is very simple to prototype in, and it is easy to migrate data from Access to Microsoft SQL Server. Access also offers more power for doing Web sites than most people think. You start to see some performance degradation after getting several concurrent users at a time, but this is actually hard to do on a small site. Consider our example of a quotes database that offers up a quote each time the page is refreshed. Say it takes 1 second to get the quote from the database, and then we close the connection. How often during the day are you going to have 10 or 20 people doing that concurrently over an extended period of time? The other issue is that I would have a hard time putting a tiny Microsoft SQL Server database up on my column's Web page for you to download. Having said all that, I encourage you to check out Microsoft SQL Server 7.0.

Setting up Your DSN

Once the database was ready, I needed to set up a Data Source Name (DSN), so other applications could access it. In this case, it really doesn't matter if you use a file or system DSN. There are two ways to set up a DSN: You can either go to Start/Settings/Control Panel/ODBC and create a new one there, or do it directly in Visual InterDev™ using Project/Add Data Connection. There are really several ways to do it, but these are the most common.

To make the database connection easy, I used what is called a DSN-less connection -- meaning that the information needed to connect to the database is not in a DSN, but rather is explicitly spelled out in my program. There are benefits and drawbacks to this approach, but for our application, it works fine. One drawback is that if you move the database to another location, you have to recompile the code. After I had my DSN, I entered its text into my connection string, which is how I created my DSN-less connection.

Modifying the ASP Page

Because I was adding another function to get the quote, I needed to make a quick modification to the ASP page. Using page from last month, my page ended up looking like this:

<%
Set x=Server.CreateObject("Simple.Simple")
Response.Write(x.GetString())
Response.Write("<BR>")
Response.Write(x.getRandomQuote())

%>

Modifying the Java Code

Using the code from last month, I typed the following two lines above my class declaration:

import com.ms.wfc.data.*;
import java.math.*;

That will bring in both the math library -- used for a random number generator -- and the WFC ADO classes, and will make them available to our application. Note that I brought in the WFC data classes, and not the ADO library itself. If you are already familiar with ADO, you might be tempted to import that library directly, since WFC does redefines all of the constants you are used to. However, the WFC data classes are necessary if you later want to use the WFC data grid, and you can't mix the two.

This is a good place to build the project and see if the import statements work. Now, before you build, be sure you go to Project, select Simple Properties, hit the COM tab, and select the checkbox for Simple under Automatically Generate Type Library". If you don't do this, you'll get a cryptic error of "Cannot create a DLL without COM classes." We didn't get this before because we weren't dealing with any other COM objects. That changed when we brought in WFC.

Next, I needed to set up the database connection. Below is the function I called from the ASP page. Add the following code to your Java source code.

public String getRandomQuote()
{
   Connection c = new Connection();
   Command cmd = new Command();         
   Recordset rs = new Recordset();
   Field fld;
   int j;
   int k;
   
   c.open("DRIVER={Microsoft Access Driver 
     (*.mdb)};DBQ=e:\\downloads\\quotes.mdb");
   cmd.setActiveConnection(c);
}

This sets up a connection string using a DSN-less connection, which basically shows explicitly where to get the database and which driver to use. This is actually a good place to compile and see if everything works. Remember, when you make changes to a Java component, you will have to stop and start Internet Information Server (IIS), which I showed in last month's column. When you bring up your ASP page, this is the most likely place that you will get the 80004005 error. It is probably access permissions. Check out the Knowledge Base article on troubleshooting this error. There are also some great tools on http://www.sysinternals.com/ -- you'll want to look at FileMon for Windows NT.

Next, I had two functions that returned the record count and a random number, since I wanted to show a random quote. I added the following two lines directly above the "}" so they were the last lines in the class file:

j = ReturnRecordCount();
k = CreateRandomNumber(j);

I then needed to create a record set and return the string to the ASP page. I added the following code directly above the "}", below the random number function:

StringBuffer statement = new StringBuffer().append
  ("SELECT * from quote_detail where ID=").append(k);
       cmd.setCommandText(statement.toString());
   rs.open(cmd);
   String str = rs.getField("Quote").getString();
   
   rs.close();
   c.close();
   return str;
}

That is the entire getRandomQuote function. Basically, I returned only one record and used the field property to get the quote value. Very simple. One note: Always be sure you close your connection.

Now you might wonder how the random number was generated. This actually gets a little tricky, and I cheated to ensure we had something working. Here are the two functions, and you'll want to add them to your Java source:

public int ReturnRecordCount()
{
/* I am making an assumption that there are 4 records here.
   This is obviously not how you would do this in a production
   system. */
   return 4;
}

public int CreateRandomNumber(int nRecordCount)
{
   int i,nRandom;
   i = nRecordCount;
   nRandom = (int)(i * Math.random()) + 1;
   return nRandom;
}

Remember, I said I was cheating here. Since I knew there were four records, I used that as the record count, and based the random number on that. The basic idea was to return a number from 1 to the number of records in the table, and select that record. I also knew that the ID of each quote was an integer, and that there were no gaps in numbers from deleted records. You could go about getting a record count and determining a random record in several different ways, but some are definitely less efficient than others.

Once you have done all this, you should be able to bring up your ASP page, and whenever you refresh, you'll see a new quote. With some slight modifications, you could have a quote of the day on your Web page. This is also the basic framework you would need to do just about any simple database query, such as generating a random link from a database, a book of the day recommendation, or whatever. Everything we've done is also very applicable to Visual Basic®, since you can also create a server-side component that uses ADO. The functions and constants are slightly different from what WFC offers, but the operations are fundamentally identical.

Finding the answers

While trying to get this sample to work, I ran into several areas where I needed to read additional information or troubleshoot a problem.

To prepare, I did a little reading, finding two articles in the July 1998 issue of the VJ++ Informant, which is a gold mine. The first is VJ & ASP by Robert Zembowicz, and the second is VJ6 ADO Basics, by Andy Wilson.

The second issue (the first being the compiler error I already talked about) I encountered was the infamous 80004005 error. Since ASP was only returning the error itself and not the additional information I needed, I set a breakpoint in my Java code on the line where I opened the connection to the database. I received an exception on that line and Visual J++ passed me the entire error message, which told me I couldn't access the database because it was either opened exclusively or I didn't have the correct permission. The first thing I tried was to connect to one of the sample Access databases that shipped with Visual Studio. That worked great, so I knew I had everything set up correctly from an ADO perspective. I concluded at that point it was a directory permissions error, so I simply moved the file to another directory with the correct permission. A great Knowledge Base article to look at is Q167294, ACC97: Using a File DSN or DSN-Less Connection with ASP Files. You can also find great information in the MSDN docs by searching on "Creating a File DSN."

The next issue was simple: 80040e37. Looking up the error number in the KB told me that the table name was incorrect. Doh! I had left the table name in from my earlier test on one of the sample tables.

After this, most of the sample was working. However, I couldn't get a simple record count to work. I ended up making a call to our developer support team, where I talked to Rick Anderson, one of our Visual J++/database engineers. He gave me lots of advice about what to do, most of which will appear in the next column. In the meantime, since getting a record count was more complex than I had originally thought, I settled on the approach in the article -- which really isn't too bad since I own the database and know it is not going to be updated. One problem is that there are many combinations of driver, cursor type, and so on that affect the results, and you need to be aware of those.

I also checked a couple times in the Visual J++ subset of the MSDN documentation that comes with the product. If you have time, it would not be a bad idea to look through the tutorial.

Next month, we'll discuss and add the functionality to get a good random number based on record count (with no cheating). We'll also discuss performance tradeoffs and add the necessary error checking.

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.