Conceptual Explanation

The power of middleware is that the “making a connection to the DBMS” mentioned in the previous section actually is simple to program. However, you will need to make arrangements with your service provider before you proceed. In this chapter, we will describe the procedures we have designed for students at our institution.

What do you need to specify to set up the mechanisms for the middleware program to pass arguments to DBMS software? When you think about it, you should arrive at the following list:

• The name of the DBMS software. You are specifying the driver to be used by your program.

• The name and location of the specific database.

• Information relating to permissions, if required, such as user and password.

One more item of information you might have thought of concerns the mode of access. This specifies what you are going to do with the database; for example, just read it, or read and write. This might need to be specified explicitly so the system can protect the data from two people or, rather, two programs attempting to write at the same time. It is related to locking the database and will be discussed later in the chapter.

The presence of the first two as distinct items might be puzzling, but it is necessary. The commands need to specify in some way that it is a MySQL database or an Access database, in addition to “being told” the name and location of the specific database. You might think that there is some other program that will go to the database, see what type it is, and then go fetch the right software, but that is not how things work. The two systems, PHP and ASP, divide the specification of this information in different ways and also provide alternatives. This all contributes to the complexity. However, in each case, you need to write only a few lines of code. The standard practice is to put those lines of code in a separate file. Both PHP and ASP have a feature for including one file in another file. All your PHP or ASP scripts for a particular database will have calls to include the connection-making file.

Connecting with PHP

The PHP system has libraries for several DBMS products, including MySQL. For the other DBMSs, PHP has a set of functions making use of the Open Database Connectivity standard (ODBC). Since the text focuses on PHP and MySQL, you will use the MySQL functions. This is telling the system what DBMS you will be using. The command for making the connection is:

    $link=mysql_connect($host,$user,$password);

where $host will indicate the host computer making the connection; $user, the user; and $password, the password. You could use constants in place of the variables. Recall the discussion in the previous chapter on the use of MySQL’s grant statement. These relate to values set up by someone working on the server computer directly using the grant command. The value of $host would be “localhost.” It definitely is not the client computer, because the connection is not made to the client computer but the server computer itself running the PHP program. At our college, our procedure is to use the same user IDs and passwords as the students use to upload their files using a File Transfer Protocol (FTP) program such as ws-ftp.

Notice that no mention has been made of the database. You have two options, and for one of those options, there again are two options. A PHP command exists to name the database:

    $result=mysql_select_db($DBname);

will attempt to select a database for future commands, using the name in the variable $DBname. It will use the last opened link. The command returns and assigns to the variable indicated on the left-hand side of the assignment statement either true or false, depending on the success of the operation. It is a good idea to check these values and take appropriate action when operations fail. The appropriate action would be to return a message to the person on the client computer that the transactions cannot continue. The alternative is to have an ugly error message appear later in the process.

The other way to name the database is for you to specify the link:

    $result=mysql_select_db($DBname,$link);

Assuming the firstdb is the name of your databases, you could also use a constant string:

    $result=mysql_select_db("firstdb");

All the MySQL databases are kept in the same place on the server so there is no need to specify a path.

Alternatively, you can wait to specify the database until later when you make your queries. For example, the following command:

    $result = mysql_db_query($DBname,$query, $link);

would specify the database named in the variable $DBname, pass along the query held in a string in the variable $query, and making use of the connection named in the variable $link.

Connecting with ASP

Let us move on to the ASP system, where, again, there are multiple ways to accomplish the same tasks. The ASP system provides a mechanism similar to what has just been described for PHP in which you specify the information directly in the program. However, it also supports a mechanism using what are called data source names, or DSNs. These DSNs are a way to package the DBMS information and the file and path to the specific database information in one place. You need to contact your Internet service provider (ISP) to find out if you need to use a DSN or use the other mechanism, called DSN-less, that will be described later.

If you are doing development work on your own computer, using Internet Information Server (IIS) or some other software, you can use the DSN method. You can define your own DSN on your computer by clicking on Start/Control Panel/Administrative Tools as shown in Figure 9.1.

Figure 9.1: Control Panel.

Next, click on ODBC Data Sources. You will see something like Figure 9.2.

Figure 9.2: ODBC panel.

Click on the System DSN tab. You now will see something like Figure 9.3. Yours might have no entries.

Figure 9.3: System Data Sources already set up.

To make a DSN, click the Add… button. What you see depends on what drivers you have loaded on your computer. It will look something like Figure 9.4.

Figure 9.4: Panel to create a new data source.

Select the driver. In the case of Access, it is the one highlighted here. Click Finish. You will now have a chance to name the database, using the window shown in Figure 9.5.

Figure 9.5: Panel to enter data for DSN.

First, however, give the data source name and a description of the database. The first two text boxes already are filled in the screen shot as shown in Figure 9.6.

Figure 9.6: Form for DSN with entries

Now, click Select to see the usual Windows window for locating a file. Notice that you have the option to create a database. The screen shot in Figure 9.7 appears after browsing to a folder.

Figure 9.7: Window to select file.

Select the database you want, and click OK. You will see something like the window shown in Figure 9.8.

Figure 9.8: Form after selecting actual file.

Click OK, and from now on, you can use the DSN name you indicated in your commands. We will use firstdb or test in our examples. Your Internet Service Provider might give you a DSN to use.

The ASP and JavaScript system requires that you define the variables before using them. In this case, this means that you need to use the CreateObject method of the Server object to create an object of type ADODB.Connection. You will see this construction several more times. The code is:

    Conn=Server.CreateObject("ADODB.Connection");

At this point, Conn is a variable holding a connection object. To make the connection for using the database defined by a DSN, you use the following code:

    DSN="DSN=firstdb";     Conn.Open(DSN);

Notice that the variable called DSN is not the string “firstdb,” but the string “DSN=firstdb.” Conn is an object of type ADODB.Connection. All objects of this type have a method named Open. The Open method can take as a parameter a DSN.

Now let us turn to the DSN-less way. Again, you will follow the directions of your service provider. At our institution, we use the DSN-less way. Specifically, the students create their own Access databases, including specifying the tables. They then upload the databases using FTP.

To set up a DSN-less connection, you still need to create an object of type ADODB.Connection, and you do it the same way. However, now, you will prepare a string to use with the Open method. The string will hold information on the DBMS driver and the database itself. It is called a connection string.

    strConnect =      "Driver={Microsoft Access Driver (*.mdb)};"      + "DBQ=" + Server.MapPath("firstdb.mdb") ;       Conn=Server.CreateObject("ADODB.Connection");       Conn.Open (strConnect, "admin", "") ;

The string used for the Open specifies two parameters by name: Driver and DBQ. The Driver parameter is what is indicated between the curly brackets. The DBQ parameter must be the path to the firstdb database. The way we get the path is through use of the MapPath method of the Server object. The second parameter to the Open method names a user; we use "admin." The third parameter could contain a password. For industrial applications, you will probably choose a DBMS other than MS Access, and you will follow the procedures for passwords.

As promised, this is not a large amount of code, but it makes sense to put it in a file by itself to be included by all the files needing access to the database. Using this technique, you have just one place to go to if there are changes.

The PHP system has the include command for bringing one file into another file. When the statement is executed, the named file is brought into the calling file. If you have the statement within an if clause and control never goes to the clause, then the include operation will not happen. The PHP system has an alternative, the require command. This causes the named file to be included even if the section of code with the require statement is not executed. Use of include could conceivably save time by preventing loading of another file when it is not necessary. ASP has an include mechanism also, but it is not a statement but part of a comment. You will see examples of all these commands in the Examples section. In either case, you will need to name the file to be included and give it an extension. One practice is to use the extension .inc to remind you that this is not a file used by itself, but one included in other files. This reminder is beneficial. However, an alternative is to use the extension .php or .asp. This will prevent the file from being examined by someone browsing the Web. Since you might be putting password information here, you would not want others to see the files.

Making Queries

Continuing the explanation of the use of databases, assume that you have written the program to make the connection to an existing database with tables already defined. You now want to write code to insert a record or query the database using a select statement. The items of information you need to specify are:

• The connection already established

• The database name if that is not part of the connection and has not been specified already

• The query

The differences between PHP and ASP should now be familiar to you. The PHP code makes use of one of the commands in the MySQL library, and it has been mentioned already:

    $result = mysql_db_query($DBname,$query, $link);

This assumes that the variables listed as the parameters have all been set to the database name, the query string, and the link, respectively. The $result variable will be assigned the result code indicating success or failure for an insert query. For a select query, the $result variable will hold what the query returns in the special data type called a recordset.

The ASP code is different depending on whether the query is a select or something else. For an insert, the command is:

    result = Conn.Execute(query)

where Conn is the ADODB.Connection object already created and opened with the database specified. However, if the query is going to return a recordset, you will need to write the code to create an object of ADODB.Recordset. The code for this is:

    rs=Server.CreateObject("ADODB.RecordSet");     rs.Open (query, Conn);

where query is a string holding the select statement, and Conn is, as before, the connection object. The Open method of the recordset can have two more parameters. The third parameter, which is optional, specifies the type of cursor, the internal pointer, to be used for the recordset. The significance of this is that you cannot use the default of zero if you want to find out the number of rows returned by the query. The fourth parameter specifies the locking mechanism.

The PHP and ASP systems handle locking in distinct ways. Locking is when you indicate that other programs are not to make use of the database or to make only limited (read-only) use. The PHP/MySQL system provides the SQL commands Lock and Unlock to lock a table from any other access and then restore access. The ASP system lets you specify a mode of access for the connection. For example:

    Conn.Mode = 3;

specifies read and write access, without locking out any other access. Other modes include read only, write only, and modes that lock out other programs from reading or from writing. The ASP system also allows you to set up locking conditions at the time of opening the recordset. The statement:

    rs.Open(query, Conn, 2, 3);

will set the cursor condition to provide going back and forth through the rows of the recordset and not lock out other users except at the moment that you update the recordset. For your information, much of the online ASP documentation makes use of a set of constants called ADOVBS. For example, adOpenDynamic is 2, and adLockOptimistic is 3. If you go to an online source, you might see the following in place of the previous line:

    rs.Open(query, Conn, adOpenDynamic, adLockOptimistic);

If you looked in the code surrounding this line, you probably would not find the definitions of adOpenDynamic and adLockOptimistic. Instead, the example is making use of the ADOVBS constants. You can download a copy of the file from the Web and include it (or, more exactly, put in an include command to bring in the file). Since these are the only two we use, we use the actual values. An argument can be made that the named constants make the code more readable.

The recordset object requires special commands to get at the individual rows. In PHP, these will be commands from the library for MySQL. For ASP/JavaScript, you will use methods of the recordset object.

The last thing you need to do is close the connection to the database. The PHP and ASP systems both have commands to do this. Closing the connection frees up systems resources so it is a good thing to do.

Before closing this section, it is time to mention that you can and most probably will make mistakes when you develop applications! Programming problems, named bugs by Grace Murray Hopper, one of the pioneers of computing, can be sorted into two categories: problems that the system detects, and problems that it does not detect. In the first case, processing will stop and you will see some message produced by the PHP or ASP interpreter saying that you have an error. The message often contains a line number, although the problem might have occurred prior to that point in the code. Although seeing these messages can be annoying, this actually is the good case. In the second case, the system “finds no fault” with the coding in your scripts. However, the results are not what you intended. This situation is complicated by the basic nature of middleware in which processing is occurring in three different places: interpreting the PHP or ASP script, invoking the DBMS driver programs, and displaying the HTML. These situations require you to examine the results and, if they are not what you want, study all your scripts to discover the location of the problem.

It is possible for you to write code that will test the return values given by the DBMS software. You need to devise the appropriate action if such an event occurs, but it is generally better for you to generate a suitable display for the people using your application than for them to see a systems error message. This is sometimes referred to as error catching or error trapping, although those terms sometimes are reserved for more elaborate mechanisms than what is shown here.

One general recommendation is to proceed incrementally in your development: divide tasks into smaller tasks, and get the smaller ones working. You also need to make sure you test your application adequately. This means you need to take on the role of your customers and imagine what data they might enter into forms. Many programming systems have elaborate tools for debugging. With middleware, you are more on your own, but this might change over time. One technique you might need to use when a problem occurs is to put in statements that display the state of variables at different stages in the process. These strategies will be demonstrates in the examples that follow.