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 compel-

ling 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

/

the new record the current record - just

•! 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 alterna-

tive.

Command Object

ProgiD:

ADODB.Command

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 data-

base:

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