Using PHP with ADO

I l @ ve RuBoard

Now that you have an idea of what ADO is, how do you use it with PHP? Chapter 7 looked at how PHP can use COM. Because ADO is a set of COM objects, all you need in order to access ADO is PHP's COM support.

Accessing ADO.NET is trickier, because PHP has no direct support for .NET. However, as discussed in Chapter 7, it is possible to emulate a COM object in .NET. As such, it's certainly possible to write a COM wrapper object for certain ADO.NET functions and thus make them useable by PHP.

For the purposes of this chapter, I'll show you how to use ADO first in ASP and then in PHP. The ASP code is included for reference. If you don't have an ASP background, don't worry. The PHP code is fully explained.

Database Example

For the purposes of this chapter, I have created a simple database called phpbook in SQL Server 2000 with a simple table called Names. ADO can work with any database that supports OLE DB, such as Microsoft Access and Oracle. The Names table contains the fields shown in Table 10.1.

Table 10.1. Names Table Fields

Name	Type	Length
Id	Int	4
fname	Nvarchar	15
lname	Nvarchar	15

I then created a sample record within the database table, as shown in Figure 10.1.

Figure 10.1. A sample record with the Names table of the phpbook sample database.

To finish setting up the database, I created a DSN (see Appendix A, "Creating an ODBC Connection") to the database called phpbook.

Using the ADO Connection and Recordset Objects

The ADO Connection and Recordset objects must be used together, as shown in Figure 10.2.

Figure 10.2. The ADO Connection and Recordset objects, as well as the Recordset Fields collection in the ADO object model.

The ADO Connection object is the first object that can be used in ADO. Its purpose is quite simply to allow you to connect to the database. Using the Connection object, you can connect via ODBC or directly.

The ADO Recordset object is what you use to work with data within a database table. Using the Recordset object, you can display fields within a table, as well as add, edit, and delete fields. Recordset objects hold a mirror copy of a database table that is directly linked to a table on a database. As you work through the fields in the Recordset object, you are also working through the fields in the database table that are linked to that Recordset object. However, ADO provides you with disconnected recordsets. You still use the Recordset object, but rather than staying connected to the database as you work through a table, you hold that table within the Recordset object, work with the table, and then update the table on the database when you are done.

Finally, you have the Fields collection of the Recordset object. It references each field within the database table.

Querying a Database Table Using ADO

Now that you have an idea of what the ADO objects are, let's put them to use by querying a database.

Querying Using ASP

Using ADO with a web scripting language is pretty straightforward. ADO is commonly used with ASP, so Listing 10.1 looks at how you can use it with ASP.

Listing 10.1 ADO with ASP

 <%  'create ADO Connection object  set objcon = Server.CreateObject("ADODB.Connection")  dbcon = "DSN=phpbook;UID=sa;PWD=;"  'open database connection using the ADO Connection object  objcon.Open dbcon       sqlquery = "SELECT * FROM Names"       'run sql query against database using the ADO connection; returns an       'ADO recordset       set objrs = objcon.Execute(sqlquery)            'cycle through database rows using the ADO recordset            while not objrs.EOF                  'display database fields                  Response.write(objrs("fname") & " " & objrs("lname") & "<BR>")                  'move to next record in the ADO recordset                  objrs.MoveNext            Wend       objrs.close       set objrs = nothing  objcon.close  set objcon = nothing  %> 

Querying Using PHP

Now that you have used ADO with ASP, Listing 10.2 looks at how you can use ADO with PHP.

Listing 10.2 ADO with PHP

 <?php  //create ADO Connection object  $objcon = new COM("ADODB.Connection");  $dbcon = "DSN=phpbook;UID=sa;PWD=;";  //open database connection using the ADO Connection object  $objcon->Open($dbcon);       $sqlquery = "SELECT * FROM Names";       //run sql query against database using the ADO connection; returns an       //ADO recordset       $objrs = $objcon->Execute($sqlquery);       //note we have to look up the field directly       $fname = $objrs->Fields(1);       $lname = $objrs->Fields(2);       //cycle through database rows using the ADO recordset       while(!$objrs->EOF) {                //display database fields                 //note we have to show the value directly                 print($fname->value . " " . $lname->value . "<BR>");                 //move to next record in the ADO recordset                 $objrs->MoveNext();       }       $objrs->close;  $objcon->close;  ?> 

First you create the ADO connection object using PHP's COM function:

 $objcon = new COM("ADODB.Connection"); 

Next you establish a connection that uses the DSN you set up previously:

 $dbcon = "DSN=phpbook;UID=sa;PWD=;";  $objcon->Open($dbcon); 

Then you set a connection string:

 $dbcon = "DSN=phpbook;UID=sa;PWD=;"; 

The connection string always follows a fixed format of DSN name (such as phpbook), database username, and database password. If your database does not require a username or password, you should just leave these fields blank (such as DSN=phpbook;UID=;PWD=; ).

Next you open a connection to the database using your connection string:

 $objcon->Open($dbcon); 

Then you define a SQL query that you will run against your database and use in the Execute function of the ADO Command object. This returns an ADO Recordset object, in which the SQL query's results are placed:

 $sqlquery = "SELECT * FROM Names";  $objrs = $objcon->Execute($sqlquery); 

To complete the query, you cycle through each record with the Recordset object and display each field within the record:

 while(!$objrs->EOF) { print($fname->value . " " . $lname->value . "<BR>");  $objrs->MoveNext();  } 

Note that PHP does not support anonymous object dereferencing. In other words, you cannot reference an object by an object; you must first load the object and then reference it. Therefore, to display the value of the database field, you use this:

 $fname = $objrs->Fields(1);  $fname->value 

You first load the database field object into a variable:

 $fname = $objrs->Fields(1); 

You then reference the object:

 $fname->value 

In ASP, this equates to the following:

 objrs.Fields("fname"). Value 

In PHP, you must reference the fields and then display their values. PHP cannot use named references in fields; it must reference them by their values. If you run the script, you can see the result of the ADO query, as shown in Figure 10.3.

Figure 10.3. The result of running the ADO query in PHP.

Using Cursors and Locking

Cursors let you control how your Recordset object and the database table communicate with one another. You control how you use cursors using the CursorType (see Table 10.2) and CursorLocation (see Table 10.3) properties of the Recordset object.

Table 10.2. CursorTypes

Constant	Value	Description
adForwardOnly		Known as a forward-only cursor, this type of cursor lets you move through a database table one row at a time. If you intend to query the database table only once, this type of cursor offers the best type of performance.
adOpenKeySet	1	Known as a keyset cursor, this type of cursor is a fixed cursor, in which the data within the Recordset object stays current with the database table but does not show deletions or additions to the database table.
adOpenDynamic	2	Known as a dynamic cursor, this type of cursor always stays concurrent with the data in the database table. Dynamic cursors are not always supported by the database vendor's OLE DB implementation.
adOpenStatic	3	Known as a static cursor, this type of cursor is a fixed cursor that holds a copy of the database table. It does not stay current with the database table.

Table 10.3. CursorLocations

Constant	Value	Description
adUseNone	1	No cursor location
adUseServer	2	Server-side location
adUseClient	3	Client-side location

When you use disconnected recordsets, you set a client-side location, because the Recordset object holds an offline copy of the database table and thus must be held on the client side.

Locking lets you control how a database is accessed when you change it by updating, deleting, or adding. ADO supports the use of locking using the LockType property of the Recordset object. The LockType properties are listed in Table 10.4.

Table 10.4. LockTypes

Constant	Value	Description
adLockReadOnly	1	The database table is read-only and cannot be altered .
adLockPessimistic	2	The database table is locked by the database as soon as editing of the database starts and is unlocked when editing is finished.
adLockOptimistic	3	The database table is locked by the database during ADO Update actions and is unlocked when editing is finished.
adLockBatchOptimistic	4	The database table is locked by the database during ADO UpdateBatch actions and is unlocked when editing is finished.

Setting these properties requires a change in syntax. When you queried the database using ADO earlier in this chapter, you returned the Recordset object from the Execute method of the Connection object. This is known as an implicit recordset. To make use of cursors and locking, you must create an explicit recordset using the following in PHP:

 $objrs = new COM("ADODB.Recordset"); 

In ASP, you use the following:

 set objrs = Server.CreateObject("ADODB.Recordset") 

Note

You can use explicit and implicit recordsets for querying when working with pages of database records, for example.

Because the Recordset object must be tied to the Connection object, you tie them together using the Open property of the Recordset object. Let's explore this further.

Adding to a Database Table Using ADO

We begin our exploration of the explicit Recordset object, cursors, and locking by looking at adding to a database using ADO.

Adding to a Database Using ASP

Like the previous example, let's first look at using ADO to insert data in a database using ASP (see Listing 10.3).

Listing 10.3 Inserting Data Using ASP

 <%  'create ADO Connection object  set objcon = Server.CreateObject("ADODB.Connection")  'open database connection using the ADO Connection object  dbcon = "DSN=phpbook;UID=sa;PWD=emma;"  objcon.Open dbcon       'update code       'create ADO Connection object       set objrs = Server.CreateObject("ADODB.Recordset")       'set cursor and locktype       objrs.CursorLocation = 2       objrs.CursorType = 0       objrs.LockType = 3         'set what range of records the recordset will hold by querying       'the database       sqlquery = "SELECT * FROM Names"       'run query using the Connection object       objrs.Open sqlquery, objcon       'call the AddNew method of the Recordset object to prepare the       'recordset to add new data       objrs.AddNew       'new data to add       objrs("fname") = "Emma"       objrs("lname") = "Stopford"       'call Update method of Recordset object to update database table with       'new data       objrs.Update       'refresh data       objrs.Requery       objrs.Close       set objrs = nothing       'query code       sqlquery = "SELECT * FROM Names"       set objrs = objcon.Execute(sqlquery)            while not objrs.EOF                 Response.write("Record added <BR><BR>")                 Response.write(objrs("fname") & " " & objrs("lname") & "<BR>")            objrs.MoveNext            Wend       objrs.close       set objrs = nothing  objcon.close  set objcon = nothing  %> 

Adding to a Database Using PHP

Now that you know how to use ADO to insert data with ASP, Listing 10.4 shows you how to use ADO to insert data with PHP.

Listing 10.4 Inserting Data Using PHP

 <?php  //create ADO Connection object  $objcon = new COM("ADODB.Connection");  //open database connection using the ADO Connection object  $dbcon = "DSN=phpbook;UID=sa;PWD=emma;";  $objcon->Open($dbcon);       //update code       //create ADO Recordset object       $objrs = new COM("ADODB.Recordset");       //set cursor and locktype       $objrs->CursorLocation = 2;       $objrs->CursorType = 0;       $objrs->LockType = 3;       //set what range of records the recordset will hold by querying       //the database       $sqlquery = "SELECT * FROM Names";       //run query using the Connection object       $objrs->Open($sqlquery, $objcon);       //call the AddNew method of the Recordset object to prepare the       //recordset to add new data       $objrs->AddNew();       //new data to add       $test1 = $objrs->Fields(1);       $test2 = $objrs->Fields(2);       $test1->value  = "Emma";       $test2->value = "Stopford";       //call Update method of Recordset object to update database table with       //new data       $objrs->Update();       //refresh data       $objrs->Requery();        $objrs->Close();       //query code       print("Record added <BR><BR>");       $sqlquery = "SELECT * FROM Names";       $objrs = $objcon->Execute($sqlquery);            while(!$objrs->EOF) {                $test1 = $objrs->Fields(1);                 $test2 = $objrs->Fields(2);                 print($test1->value . " " . $test2->value . "<BR>");                 $objrs->MoveNext();            }       $objrs->close();  $objcon->close();  ?> 

In this listing, you first create a Connection object. Remember that recordsets when implicit or explicit are dependent on an associated Connection object.

 $objcon = new COM("ADODB.Connection"); 

Next you open a connection to the database using your Connection object:

 $dbcon = "DSN=phpbook;UID=sa;PWD=emma;";  $objcon->Open($dbcon); 

Then you explicitly create a Recordset object:

 $objrs = new COM("ADODB.Recordset"); 

Now that you have a Recordset object, you can set its cursor and locktype information:

 $objrs->CursorLocation = 2;  $objrs->CursorType = 0;  $objrs->LockType = 3; 

Using Tables 10.2 and 10.4, this translates to the following:

CursorLocation = Use server-side cursor

CursorType = Forward-only cursor

Locktype = Optimisic locking

Next, you tie the Connection object to the Recordset object and use a SQL query to specify which data within the database (such as which fields) the Recordset object can use. Your Recordset object now has a useable range of data, but remember that you must work within that range. In other words, you can update a record if the field is not specified in the SQL query.

 $sqlquery = "SELECT * FROM Names";  $objrs->Open($sqlquery, $objcon); 

To begin the add process, you must call the AddNew method of the Recordset object. This prepares the Recordset for the add process:

 $objrs->AddNew(); 

Next you specify which database fields you are adding data to. As you saw in the query example, PHP does not support default collections, so you must specify the fields in full.

 $test1 = $objrs->Fields(1);  $test2 = $objrs->Fields(2);  $test1->value = "Emma";  $test2->value = "Stopford";  $objrs->Update(); 

To complete the add process, you call the Update and Requery methods of the Recordset object. The Update method updates the data in the database with that in the Recordset object, and the Requery method then refreshes the data in the Recordset from the new contents of the database table.

 $objrs->Requery();  $objrs->Close(); 

You can now make use of the Recordset object to query the database table. If you run the script shown in Listing 10.4, you should see that the data has been added to the database and is displayed, as shown in Figure 10.4.

Figure 10.4. Data added to the database using ADO and PHP.

Editing a Database Table

You can also use explicit Recordset objects to edit the data within a database. We will explore this using ASP and then follow up with PHP. Because you will change existing data in the database, I will assume that you have the database set up with the necessary data.

Editing Database Data Using ASP

As with the previous example, let's first look at using ADO to update data to a database using ASP (see Listing 10.5).

Listing 10.5 Updating with ASP

 <%  'create ADO Connection object  set objcon = Server.CreateObject("ADODB.Connection")  'open database connection using the ADO Connection object  dbcon = "DSN=phpbook;UID=sa;PWD=emma;"  objcon.Open dbcon       'update code       'create ADO Connection object       set objrs = Server.CreateObject("ADODB.Recordset")       'set cursor and locktype       objrs.CursorLocation = 2       objrs.CursorType = 0       objrs.LockType = 3       'set what range of records the recordset will hold by querying       'the database       sqlquery = "SELECT * FROM Names WHERE fname = 'Andrew'"       'run query using the Connection object       objrs.Open sqlquery, objcon      'new data to add       objrs("fname") = "Elle"       objrs("lname") = "Stopford"       'call Update method of Recordset object to update database table with       'new data       objrs.Update       'refresh data       objrs.Requery       objrs.Close       set objrs = nothing       'query code       sqlquery = "SELECT * FROM Names"       set objrs = objcon.Execute(sqlquery)            while not objrs.EOF                 Response.write("Record edited <BR><BR>")                 Response.write(objrs("fname") & " " & objrs("lname") & "<BR>")            objrs.MoveNext            Wend       objrs.close       set objrs = nothing  objcon.close  set objcon = nothing  %> 

Editing Database Data Using PHP

Now that you have seen how to use ADO to update data with ASP, Listing 10.6 shows you how to use ADO to update data with PHP.

Listing 10.6 Updating with PHP

 <?php  //create ADO Connection object  $objcon = new COM("ADODB.Connection");  //open database connection using the ADO Connection object  $dbcon = "DSN=phpbook;UID=sa;PWD=emma;";  $objcon->Open($dbcon);       //update code       //create ADO Recordset object       $objrs = new COM("ADODB.Recordset");       //set cursor and locktype       $objrs->CursorLocation = 2;       $objrs->CursorType = 0;       $objrs->LockType = 3;       //set what range of records the recordset will hold by querying       //the database       $sqlquery = "SELECT * FROM Names WHERE fname = 'Andrew'";       //run query using the Connection object       $objrs->Open($sqlquery, $objcon);       //new data to add       $test1 = $objrs->Fields(1);       $test2 = $objrs->Fields(2);       $test1->value  = "Elle";       $test2->value = "Stopford";       //call Update method of Recordset object to update database table with       //new data       $objrs->Update();       //refresh data       $objrs->Requery();       $objrs->Close();       //query code       print("Record edited <BR><BR>");       $sqlquery = "SELECT * FROM Names";       $objrs = $objcon->Execute($sqlquery);            while(!$objrs->EOF) {                $test1 = $objrs->Fields(1);                 $test2 = $objrs->Fields(2);                 print($test1->value . " " . $test2->value . "<BR>");                 $objrs->MoveNext();            }       $objrs->close();  $objcon->close();  ?> 

If you think this code looks very similar to the add process code, you're correct. The only difference, in fact, is that you don't call the AddNew method of the Recordset object. If you run the script, you can see that the data in the database has been updated, as shown in Figure 10.5.

Figure 10.5. The results of editing data in the database using ADO and PHP.

Deleting from a Database Table

You can also delete records from a database table using ADO. Again we'll start with ASP and then move on to PHP.

Deleting Using ADO and ASP

Deleting with ASP is demonstrated in Listing 10.7.

Listing 10.7 Deleting with ASP

 <%  'create ADO Connection object  set objcon = Server.CreateObject("ADODB.Connection")  'open database connection using the ADO Connection object  dbcon = "DSN=phpbook;UID=sa;PWD=emma;"  objcon.Open dbcon       'create ADO Recordset object       set objrs = Server.CreateObject("ADODB.Recordset")       'set cursor and locktype       objrs.CursorLocation = 2       objrs.CursorType = 0       objrs.LockType = 3       'set range of data       sqlquery = "SELECT * FROM Names WHERE lname = 'Stopford'"       'run query using the Connection object       objrs.Open sqlquery, objcon       'loop through recordset       while not objrs.EOF            'delete records            objrs.Delete            objrs.MoveNext       Wend       objrs.Close       set objrs = nothing  objcon.close  set objcon = nothing  Response.write("Record deleted")  %> 

Deleting Using ADO and PHP

Deleting data using ADO with PHP is exemplified in Listing 10.8.

Listing 10.8 Deleting Data from a Database

 <?php  //create ADO Connection object  $objcon = new COM("ADODB.Connection");  //open database connection using the ADO Connection object  $dbcon = "DSN=phpbook;UID=sa;PWD=emma;";  $objcon->Open($dbcon);       //create ADO Recordset object       $objrs = new COM("ADODB.Recordset");       //set cursor and locktype       $objrs->CursorLocation = 2;       $objrs->CursorType = 0;       $objrs->LockType = 3;       //set range of data       $sqlquery = "SELECT * FROM Names where lname = 'Stopford'";       //run query using the Connection object       $objrs->Open($sqlquery, $objcon);       //loop through recordset       while(!$objrs->EOF) {                //delete records                 $objrs->Delete();                 $objrs->MoveNext();       }       $objrs->Close();  $objcon->close();  print("Record deleted");  ?> 

Note that, as with the add and edit process examples, you create a Connection object to connect to the database and then create an explicit Recordset object that you tie to the Connection object. As before, you set the range of data within your recordset using a SQL query. To complete the delete process, you must first loop through each record in the table:

 while(!$objrs->EOF) { 

You can then delete each record as you cycle through using the Delete method of your Recordset object:

 $objrs->Delete(); 

Querying a Database Table Using Stored Procedures

Stored procedures are very commonly found in DBMSs such as Microsoft SQL Server and Oracle. Not only do they allow you to store and reuse SQL code in a central place, but they also make use of the DBMS's own language (a kind of extended SQL) for working with databases (for example, Microsoft SQL Servers use TSQL). Here we will look at using PHP with a simple query-based stored procedure.

Creating a Stored Procedure in Microsoft SQL Server

I created a simple procedure called sp_LookUpNames to query the Names table, as shown in Figure 10.6. This stored procedure uses the parameter @lname to look up the lname column in your table.

Figure 10.6. A simple stored procedure to query the database.

If you are interested in learning more about stored procedures, I recommend Writing Stored Procedures for Microsoft SQL Server by Matthew Shepker (Sams Publishing, 2000).

SQL Method

You can make use of your stored procedure using standard SQL, ADO, and PHP as shown in Listing 10.9.

Listing 10.9 Making Use of Stored Procedures

 <?php  $objcon = new COM("ADODB.Connection");  $dbcon = "DSN=phpbook;UID=sa;PWD=emma;";  $objcon->Open($dbcon);       //use the SQL EXEC statement to make use of a stored procedure       $sqlquery = "EXEC sp_LookUpNames Stopford";       //all other code standard for query       $objrs = $objcon->Execute($sqlquery);       $fname = $objrs->Fields(1);       $lname = $objrs->Fields(2);       while(!$objrs->EOF) {                print($fname->value . " " . $lname->value . "<BR>");                 $objrs->MoveNext();       }       $objrs->close;  $objcon->close;  ?> 

This code is similar to the query code used at the beginning of this chapter. You can use implicit or explicit Recordset objects. You must reference database fields in full. The major difference is that rather than type the SQL code directly into the script, you call on the SQL code within your stored procedure using the SQL EXEC command:

 $sqlquery = "EXEC sp_LookUpNames Stopford"; 

The EXEC command has the following syntax:

 EXEC "  stored procedure name  " ["  parameters  "] 

Using parameters with the EXEC command is optional. However, should you need parameters, you can include as many as the stored procedure needs.

ADO Command Method

ADO gives you another method of using stored procedures in the form of the ADO Command object (see Figure 10.7). The Command object is not solely for use with stored procedures; it's for issuing commands against a database. As such, it can be used to query a database using standard SQL or stored procedures. This example, however, uses the Command object to query using stored procedures.

Figure 10.7. The ADO Command object and its parameter collection in the ADO object model.

When you use the Command object, things differ slightly from what you have used before. Specifically, the Command object is used directly with the Connection object. From the Connection object, you explicitly take the Recordset object. Before, you took the Recordset object explicitly from the Connection object. As the preceding EXEC SQL example shows, you can still use this method and stored procedures together. Using the Command object, however, gives you greater control over the stored procedure.

To summarize, using the Command object, you use the Command and Connection objects together to connect to and query the database. The Command object uses the Connection object for its database connection (via its ActiveConnection property). The Command object has a parameters collection that you link to the Command object. When you call the Execute method of the Command object, a Recordset object is implicitly returned, and you can navigate the database table as normal. Let's now look at the code for using the Command object.

Using the ADO Command Object and ASP

Listing 10.10 illustrates the use of the ADO Command object with ASP.

Listing 10.10 ADO Command Object with ASP

 <%  set objcon = Server.CreateObject("ADODB.Connection")  dbcon = "DSN=phpbook;Database=phpbook;UID=sa;PWD=emma;"  objcon.Open dbcon       'create ADO Command object       set objcoman = Server.CreateObject("ADODB.Command")       objcoman.CommandText = "sp_LookUpNames"       objcoman.CommandType = 4       'set connection to Connection object       set objcoman.ActiveConnection = objcon            'create parameter            set objSpParam = objcoman.CreateParameter ("@lname", 130, 1, 10, "Stopford")            'add parameter to Connection object            objcoman.Parameters.Append objSpParam            'run Execute method to get Recordset object            set objrs = objcoman.Execute()                 while not objrs.EOF                      Response.write(objrs("fname") & " " & objrs("lname") & "<BR>")                      objrs.MoveNext                 Wend            objrs.close            set objrs = nothing       set objSpParam = nothing  set objcoman = nothing  objcon.close  set objcon = nothing  %> 

Using the ADO Command Object and PHP

Next let's look at how you can use ADO directly with PHP to use the stored procedure (see Listing 10.11).

Listing 10.11 ADO Command Object with PHP

 <?php  $objcon = new COM("ADODB.Connection");  $dbcon = "DSN=phpbook;Database=phpbook;UID=sa;PWD=emma;";  $objcon->Open($dbcon);       //create ADO Command Object       $objcoman = new COM("ADODB.Command");       $objcoman->CommandText = "sp_LookUpNames";       $objcoman->CommandType = 4;       //set connection to Connection object       $objcoman->ActiveConnection = $objcon;            //create parameter            $objSpParam = $objcoman->CreateParameter ("@lname", 130, 1, 10,"Stopford");            //add parameter to Connection object            $objcoman->Parameters->Append = $objSpParam;            //run Execute method to get Recordset object            $objrs = $objcoman->Execute();            $fname = $objrs->Fields(1);            $lname = $objrs->Fields(2);                 while(!$objrs->EOF) {                //note we have to show the value directly                 print($fname->value . " " . $lname->value . "<BR>");                 $objrs->MoveNext();                 }            $objrs->close();  $objcon->close();  ?> 

First you create the Connection object and open the database using a DSN connection:

 $objcon = new COM("ADODB.Connection");  $dbcon = "DSN=phpbook;Database=phpbook;UID=sa;PWD=emma;";  $objcon->Open($dbcon); 

Next you create the Command object:

 $objcoman = new COM("ADODB.Command"); 

Next you set some properties of the Command object ”namely, the CommandText and CommandType properties:

 $objcoman->CommandText = "sp_LookUpNames";  $objcoman->CommandType = 4; 

The CommandText property is the query you will run against the database (in this case, the stored procedure sp_LookUpNames ). The CommandType property is the type of command you will run against the database (see Table 10.5).

Table 10.5. CommandTypes

Constant	Value	Description
adCmdText	1	Executes a SQL statement against a database table.
adCmdTable	2	Opens a database table.
adCmdTableDirect	512	Directly opens a database table. Some databases don't support this, so it's best to use the adCmdTable type instead.
adCmdStoredProcedure	4	Uses a stored procedure.
adCmdFile	256	Opens a recordset using data saved with the Recordset object's SAVE method. This type lets you use disconnected recordsets.
adCmdUnknown	8	The command type is unknown.

The CommandText and CommandType properties are directly linked. For example, if you wanted to open your database using standard SQL, you would use the following:

 $objcoman->CommandText = "SELECT * FROM Names";  $objcoman->CommandType = 1; 

You can shorten this to

 $objcoman->CommandText = "Names";  $objcoman->CommandType = 2; 

Next you set the ActiveConnection property of the Command object to that of the Connection object:

 $objcoman->ActiveConnection = $objcon; 

Next you create a parameter using the CreateParameter method of the Command object. Parameters are for use with stored procedures, so the CommandText and CommandType properties must be set accordingly .

 $objSpParam = $objcoman->CreateParameter ("@lname", 130, 1, 10, "Stopford"); 

The CreateParameter property breaks down as follows:

 CreateParameter(  Parameter Name  ,  Parameter Type  ,  Parameter Direction  ,  Parameter Size,  Parameter Value)  

The Parameter Name is the name of the stored procedure parameter. In the case of our stored procedure, it's @lname . The Parameter Type is the type of data that parameter will hold. This property is indicated by an ADO DataTypeEnum value (see Table 10.6).

Table 10.6. ADO DataTypeEnum Values

Constant	Value
adChar	129
adCurrency	6
adDate	7
adDouble	5
adNumeric	131
adWChar	130

Note

Table 10.6 is incomplete. It lists only the common types you're likely to use. A full list is available in the ADO documentation.

The Parameter Direction indicates how the type of the parameter is assigned: directly in your code or automatically by the database. The Parameter Size indicates the size of the value you are passing in the parameter. Make sure that this value is large enough to reflect the size of the value you are passing, or you will receive an error message. The Parameter Value is the value you will pass into the stored procedure to complete the SQL query within the stored procedure.

As soon as you have a completed parameter, you tie it to the Command object:

 $objcoman->Parameters->Append = $objSpParam; 

Finally, you call the Execute method of the Command object to implicitly receive the Recordset object:

 $objrs = $objcoman->Execute(); 

I l @ ve RuBoard