The LA Fox Developer Newsletter

July 2000

ADO Jumpstart (Con’t fmm page 1)

To illustrate this capability, consider the need to fetch a list of files from a specified directory. In Visual FoxPro, a handy function, ADIR() performs this sort of task. However, what if you need to pass the data to another application? Or, perhaps you need to persist the list to a file on disk. While Visual FoxPro arrays are powerful, ADO recordsets provide a compelling alternative. The following code fetches a list of files from a specified directory, fabricates a recordset, and copies the values from the array into the newly created recordset:

*/GetFiles.prg #INCLUDE “adovfp.h” Local Array aFlles[1] Local nFiles,nFIeld,nFlle,oRS nFiles = Adir(aFlles,Getdir( )+"*.*") 0RS=Createobject(”adodb.recordset”)

With 0RS

•CursorLocationADUSECLIENT

.LockType=ADLOCKOPTIMISTIc

•I Adding new fields is ~. matter of appending 1 new field objects to th~ Fields Collection. .Fields.Append(”File”,ADCHAR,20)

.Fields.Append(”Size”,ADDOUBLE,lO)

.Fields.Append(”DateTime”,ADDBTIME,8)

.Flelds.Append(”Attributes”,ADcHAR,l 0) .Open

EndWith

For nFile = 1 To nFiles

! Add a new record. This automatically makes

•! like VFP.

oRS.AddNew

With ore .Fields(”File”).Value = aFIles[nFile,1] .Fields(”Slze”).Value = aFiles(nFlle,21 .Fields(”DateTime”).Value = Ctot(Dtoc(aFiles(nFile,3]) + “ “+ aFiles[nFile,4])

.Flelds(”Attributes”).Value = aFiles[nFile,5]

EndWith

Next nltem

Return oRS

With the new recordset created and populated, it can be navigated like any other recordset:

oFiles = GetFiles

Do While loFiles.Eof

?oFlles.Fields(”File”).Value

oFiles.movenext

EndDo

ADO Recordsets Instead of Arrays

Referring to the previous example, let’s say that the list needs to be sorted by file size, descending. Arrays in Visual FoxPro can be sorted, when all columns in the array are of the same data type. In this case, there are three data types: Character, Numeric, and DateTime. With a client-side ADO recordset, the process becomes simple. The following code does the trick:

oRS.Sort = “Size Desc”

Sorts are not limited to just one column. Perhaps you need to sort by size, descending, and then by file, ascending:

oRS.Sort = “Size Desc,FIle”

And, when it comes to sorting, such properties as Bookmark and AbsolutePosition that have already been demonstrated are available here as well.

Perhaps you need to find a specific value. The ASCAN() function in Visual FoxPro enables you to do this. However, it does not allow you to specify a particular column to search. Rather, once the first occurrence of a specified value is found, regardless of the column, the search is stopped. With ADO recordsets, more granular control is provided. The following code checks to see if a file called VFP6.EXE is in the recordset:

oRS.Flnd(”FIle Like ‘VFP6.EXE”)

If IoRS.Eof

•1 Found It

Else

•l Not found

Endif

Finally, you may wish to filter the list based on the file size being greater than a specified value:

oRS.Fllter = “size> 50000”

When evaluating the tools at your disposal for local data handling, be sure to consider fabricated ADO recordsets. Also, if you find yourself running into obstacles with Visual FoxPro arrays, fabricated ADO recordsets may provide a sound alternative.

Command Object

The purpose of the Command object is just as the its name implies, to run commands. For example, you may need to run a SQL update against a SQL Server table. To illustrate, the following code applies a 10 percent increase in the UnitPrice field in the Products table of the SQL Server Northwind database:

oCommand = CreateObject(”adodb.command”) With oCommand

.ActiveConnection = '

.CommandText = “Update Products Set unitprice = unitprice * 1.1”

.Execute

EndWith

The ActiveConnection Property

To review, both the Command object and Recordset object have the ActiveConnection property. A Command object needs to know what data source it is to execute commands against. A Recordset object needs to know what data source contains the data it is to retrieve. The way you accomplish this is by setting the ActiveConnection property.

The ActiveConnection property presents a great opportunity to talk about the flexible nature of the ADO object model. The ADO object model is very flat, in that you do not have to create a

(Con’t, page 7)

Page 6