ActiveX Data Objects (ADO) has been mentioned in previous chapters as a possible alternative to communicating with SQL Server via ODBC. This chapter introduces you to ADO, presents the pros and cons of using ADO, and explains the mechanics of using ADO inVisual FoxPro applications.
Why ADO?
What purpose does ADO serve in Visual FoxPro development? Why use ADO when views and
SQL pass through seem to provide all the necessary functionality?
ADO benefits
ADO provides several benefits over native Visual FoxPro data access:
• ADO is the best technology for passing data between tiers of an n-tier application.
• ADO can access non-relational data sources such as text files and CSV files.
According to Microsoft’s Web site, “OLE DB provides high-performance access to any data source, including relational and non-relational databases, email and file systems, text and graphics, custom business objects, and more.”
• ADO permits return values to be retrieved from stored procedures.
• ADO can be used as a workaround for several bugs or deficiencies in VFP.
Passing data between tiers of an n-tier application
In n-tier applications, data must be passed between the tiers. Consider a three-tier architecture consisting of a back end (database), a middle tier (business objects) and a front-end tier (user interface). (Since Visual FoxPro is often used for the middle tier, a practice recommended byMicrosoft, assume VFP-based middle tiers for this discussion.) In a three-tier architecture, passing data between the business object tier and the front end can be problematic if the two tiers are constructed using different technologies. For example, if the front end is constructed using Visual Basic and the middle tier is constructed using Visual FoxPro, sending data back and forth will be complicated because Visual Basic cannot understand the cursor or DBF that Visual FoxPro understands. Since one of the main benefits of a three-tier system is the ability to use multiple technologies for the front end, the middle tier must be capable of passing data to and from front-end tiers built from different technologies. Using ADO to send data back and forth solves this problem.
ADO is an excellent choice for a communications or data access technology because it is universally understood (at least within the world of Microsoft products). It can be used with
any Visual Studio product as well as within Active Server Pages. Other non-Microsoft products, such as Java, can also use ADO with varying levels of compatibility.
Alternatives to ADO are not satisfactory. These include:
• Accept the limitation that all front ends will be constructed in the same technology as the middle tier. However, this limitation eliminates one of the main benefits of
n-tier architecture.
• Pass an array to the front end. This seems like a good idea until you realize that different products handle arrays differently, forcing you to write custom array- handling code for each client. While Visual FoxPro does provide some help in this arena, there are issues withhandling the different data types, validation of the data, and ensuring that the clients can return any necessary information as a compatible array. In addition, passing data back from the front end to the middle tier is more complicated and requires extensive customcoding.
We agree with Microsoft’s suggestion that ADO is the best choice for passing data between the front and middle tiers of a multi-tiered application.
The ability to access non-relational data (OLE DB vs. ODBC)
ADO uses OLE DB rather than ODBC. OLE DB provides one major capability that ODBC
lacks: the ability to access non-relational data.
ODBC can access only relational databases that understand basic SQL commands. OLE DB, on the other hand, can access relational as well as non-relational data sources such as text and CSV files. Therefore, ADO permits Visual FoxPro (or other host technologies) to
access data sources that were previously unavailable or were available but required importing and converting.
Stored procedures
In Chapter 6, “Extending Remote Views with SQL Pass Through,” you learned how to call
SQL Server stored procedures with the SQLExec() function. Through SQLExec(), you can pass parameters as inputs and accept return values through OUTPUT parameters. However, there is no mechanism for capturing the return value from a SQL Server stored procedure (i.e., when
the T-SQL RETURN command is used to return an integer value).
ADO provides the ability to invoke stored procedures, and to capture any type of returned value.
VFP deficiencies—ADO to the rescue
Since ADO is a completely different data access technology than a remote view or SQL pass through statement, it can be an alternative tool that you can use to work around any bugs or problems with the native Visual FoxPro data access technologies.
For example, SQL Server 7 introduced a few data types to support Unicode character sets, but VFP does not handle these new data types correctly in some situations. If you create a remote view or a SQL pass through statement to retrieve data from an nText column (one of thenew SQL Server data types), Visual FoxPro does not place the data into a Memo field, as
it should. Instead, it incorrectly places the data into a character field of 255 characters, which
can result in truncation. (See article Q234070 in the Microsoft Knowledge Base for more details on this topic.) Since this is a bug in Visual FoxPro, you need a workaround. One approach is to use ADO instead of a view or SQL pass through. ADO properly retrieves data fromnText columns.
Note: The new data types that support Unicode are nChar, nVarchar and nText. These work similarly to their non-Unicode counterparts, except that they consume two bytes per displayed character. These data types are important when creating a database that must store characters from other languages, since a language like Japanese has well over 255 distinct characters. With Unicode, more than 65,000 distinct characters are available, allowing the full Japanese character set to be stored in a Unicode field.
ADO disadvantages
There are some disadvantages to using ADO with Visual FoxPro:
• ADO data cannot be handled in the same way as data retrieved through Visual FoxPro remote views or SQL pass through. Instead, you must access the data through the properties and methods of the ADO object.
• Native Visual FoxPro form controls cannot be bound to ADO data. (However, ActiveX controls exist that can be bound to ADO data sources.)
• ADO data cannot be manipulated using powerful Visual FoxPro technology. (However, ADO data can be converted to cursors, which can be manipulated directly by native Visual FoxPro.)
As you can see, there are advantages and disadvantages to using ADO within a Visual FoxPro application. Many of the disadvantages could be reduced or eliminated by changes to Visual FoxPro. It is widely hoped that future versions of Visual FoxPro will provide better support for ADO.
Installing and distributing ADO
Before you can use ADO, you will need to install it. If you are using Windows 2000, you need not do anything, as all of the components of ADO 2.5 are installed with the operating system and updated through Windows Update. However, if you are running an earlier operatingsystem, you will need to download the latest and greatest version of ADO.
This can be downloaded for free from Microsoft’s Web site at http://www.microsoft.com/data/download.htm. Follow the instructions to download ADO, which is included in the self-extracting archive file MDAC_TYP.EXE. This file contains all of the components of ADO,several OLE DB providers, several ODBC drivers, and the core ODBC components. Therefore, to install the latest version of these components, simply execute this file from Explorer.
This process also explains how you can distribute ADO to your client machines for deployment purposes. Simply copy the MDAC_TYP.EXE file to a directory accessible to the client workstations and execute it to install the necessary files.
Using ADO within Visual FoxPro
Using ADO within Visual FoxPro is straightforward, requiring only the use of the CREATEOBJECT() function and knowledge of the properties and methods of the ADO object model.
The CREATEOBJECT() function is used to instantiate the objects of the ADO object model: Connection, RecordSet and Command. Each object has its own set of properties, events, methods and collections, which provide all the features necessary to access, manipulate and update data from any accessible data source.
You can find extensive help for ADO in the MSDN library that ships with Visual FoxPro. A good place to look after reading this chapter would be the topics “ADO, Basics” and “ADO Jumpstart for Microsoft Visual FoxPro Developers.”
The Connection object
The Connection object is used to connect to a data source, and it also handles transactions and reporting errors. ADO uses OLE DB to connect to data sources, but since there is an OLE DB provider for ODBC drivers, OLE DB can connect to a wide variety of databases including Visual FoxPro or SQL Server.
To create a Connection object, you must instantiate a COM server with the ProgID of
ADODB.Connection. This is done with the CREATEOBJECT() function as follows:
loConn = CREATEOBJECT("ADODB.Connection")
Note that creating a Connection object does not connect to any data source. To connect
to a data source, you must specify values for one or more properties of the Connection object, and then invoke a method of the object to initialize the connection. The following code
shows how to connect to a SQL Server called MySQLSvr by invoking the Open method of a
Connection object:
loConn = CREATEOBJECT("ADODB.Connection") IF VARTYPE(loConn) = "O"
lcConnStr = "Driver=SQL Server;Server=MySQLSvr;Database=pubs;" + ; "uid=User;pwd=Password"
loConn.Open(lcConnStr) ENDIF
On the other hand, you can populate the ConnectionString property before invoking the
Open method, like this:
loConn = CREATEOBJECT("ADODB.Connection") IF VARTYPE(loConn) = "O"
loConn.ConnectionString = "Driver=SQL Server;Server=MySQLSvr;" + ; "Database=pubs;uid=User;pwd=Password"
loConn.Open() ENDIF
To test whether the connection was successful, query the value of the State property on the
Connection object. If the State property is one, the connection is open; otherwise, if it is zero,
the connection failed and is closed. An unsuccessful connection attempt triggers a Visual
FoxPro error, which can be trapped by an object’s Error event or any ON ERROR routine.
The preceding examples used an ODBC driver to connect to SQL Server. However, an OLE DB provider also exists for SQL Server. Using the OLE DB provider improves performance, since ODBC is bypassed.
To use the SQL Server OLE DB provider, use a different connection string as follows:
loConn = CREATEOBJECT("ADODB.Connection") IF VARTYPE(loConn) = "O"
lcConnStr = "Provider=SQLOLEDB;User ID=User;Password=Password;" + ; "Initial Catalog=Pubs;Data Source=MySQLSvr"
loConn.Open(lcConnStr) ENDIF
This code connects to the pubs database on a SQL Server called MySQLSvr using the SQL Server OLE DB provider called SQLOLEDB. Since a connection string is rather cryptic, you might prefer to create this connection string with a Microsoft Data Link file. To start, simply create a new, empty file with a UDL extension. Then, through Windows Explorer, double-click the file, which will open the dialog shown in Figure 1.
Figure 1. The Microsoft Data Link Properties dialog.
Once the dialog is open, you can use the Provider and Connection pages to provide the details of the desired connection (such as the provider, server name, login credentials, and the initial database to select). Use the Test Connection button on the Connection page to verifyyour selections, and then press the OK button.
Next, open the UDL file with Notepad. The UDL file should appear like the example shown in Figure 2. It contains the full connection string that corresponds to the options you selected in the UDL dialog. Simply copy the connection string into your Visual FoxPro code foruse with an ADO Connection object.
Figure 2. A UDL file that has been opened with Notepad.
After you connect to a data source, you will probably want to retrieve data from that source. Retrieving data requires another ADO object called the RecordSet object.
The RecordSet object
When data is downloaded to an ADO object, it is held in a RecordSet object. To create a
RecordSet, use the ADODB.RecordSet ProgID with the CREATEOBJECT() function, as follows:
loRS = CREATEOBJECT("ADODB.RecordSet")
As with the Connection object, creating the object does not populate the object with data. Retrieving data requires a call to the Open method of the RecordSet object. The following code example retrieves all of the records from the authors table in the pubs database on SQLServer and places the records into a RecordSet object:
loRS = CREATEOBJECT("ADODB.RecordSet") IF VARTYPE(loRS) = "O"
lcSQL = "SELECT * FROM Authors"
lcConnStr = "Provider=SQLOLEDB;User ID=User;Password=Password;" + ;
"Initial Catalog=Pubs;Data Source=MySQLSvr"
loRS.Open(lcSQL, lcConnStr)
ENDIF
Note that behind the scenes, the Open method created its own Connection object with attributes specified in the connection string, which was passed as the second parameter. You can confirm that the RecordSet object stored the Connection object specification with the following code:
loCn = loRS.ActiveConnection
Activate Screen
?loCn.ConnectionString
?loCn.State
The ActiveConnection property is an object reference to the Connection object that the RecordSet object uses to connect to a data source. By checking the connection’s ConnectionString property, you can see which data source has been opened by the RecordSet.
It is not common to allow the Connection object to be created implicitly, since it is harder to share an implicitly created connection with other ADO objects. A better practice is to create a Connection object explicitly, and then use that connection for one or more RecordSetobjects as follows:
loCn = CREATEOBJECT("ADODB.Connection")
loRS = CREATEOBJECT("ADODB.RecordSet")
IF VARTYPE(loCn)="O" AND VARTYPE(loRS) = "O"
loCn.ConnectionString = "Provider=SQLOLEDB;User ID=sa;Password=;" + ;
"Initial Catalog=Pubs;Data Source=MySQLSvr"
loCn.Open()
IF loCn.State = 1
WITH loRS
.ActiveConnection = loCn
.Open("SELECT * FROM Authors")
ENDWITH
ENDIF
ENDIF
In this example, the Connection and RecordSet objects are created with the CREATEOBJECT() function. The Connection object’s ConnectionString property is specified, and then the Open method is invoked. If the connection is successfully opened, an object reference to theConnection object is stored in the ActiveConnection property of the RecordSet object. This property tells the Open method where to send the SQL SELECT statement (which is specified as a parameter to the Open method). The Open method then executes the SQL SELECT statement that, in this example, retrieves all records from the authors table.
Displaying RecordSets with code
The next step is to figure out how to view the data within the RecordSet. The following block of code will print on the Visual FoxPro “desktop” all of the columns of each record in the RecordSet referenced by loRS:
ACTIVATE SCREEN CLEAR
DO WHILE NOT loRS.EOF
FOR EACH loField IN loRs.Fields
??TRANSFORM(loField.Value)+CHR(9)
ENDFOR
? && Move to next line
loRS.MoveNext()
ENDDO
The RecordSet’s EOF property will be True if you have moved past the last record of
the RecordSet, similar to the way a Visual FoxPro cursor works. In addition, only one record can be “seen” at any time—the RecordSet will initially position the record pointer on the
first record after retrieving the data. Inside of each record, you can access each field with the
Fields collection.
Each field has numerous properties, such as the Value property, which was referenced in the preceding code. You can also get each field’s Name, DefinedSize, NumericScale or Precision through properties of the same names.
The MoveNext method works just like the SKIP command in Visual FoxPro, moving the record pointer to the next record. You can also MoveFirst, MovePrevious or MoveLast, corresponding to the GO TOP, SKIP –1 and GO BOTTOM Visual FoxPro commands.
It is interesting to note that the similarity between a RecordSet and a Visual FoxPro cursor is not an accident: The RecordSet is based on the Visual FoxPro cursor engine. This similarity will become more apparent as you explore other methods and properties of the RecordSet object.
Displaying RecordSets with ActiveX controls
One limitation of the ADO RecordSet in a Visual FoxPro environment is the inability to bind the fields of a RecordSet to native VFP form controls. However, you can view the data of a RecordSet in a Visual FoxPro form, but it requires ActiveX controls.
By using the same ActiveX controls used by a Visual Basic developer, you can display the contents of a RecordSet in a grid on a Visual FoxPro form. To try this, start by creating a Visual FoxPro form that contains code for retrieving a RecordSet in its Load method. Make surethat you use form-level properties for the object references; otherwise, you won’t be able to “see” the RecordSet from the form’s controls:
WITH ThisForm
.oCn = CREATEOBJECT("ADODB.Connection")
.oRS = CREATEOBJECT("ADODB.RecordSet")
*--Other code goes here to open record set
ENDWITH
The default cursor type for an ADO RecordSet is known as a forward-only static cursor. This means that you can only use the MoveNext method of the RecordSet (i.e., forward-only), and that any changes on the data source are not reflected in the cursor (i.e., static). Beforeyou can display a RecordSet in an ActiveX control on a Visual FoxPro form, you must change the cursor type of the RecordSet to allow movement in any direction. However, a static cursor is preferred for performance reasons, as it will not maintain communication with the server to detect changes made by other users. The CursorType property is used to specify the type of cursor used by the RecordSet, and must be specified before opening it. To create the static cursor required by the ActiveX grid control, use 3 for the CursorType, as in the following code:
.oRS.ActiveConnction = .oCn
.oRS.CursorType = 3 && adOpenStatic
.oRS.Open("SELECT * FROM Authors")
The next step is to place an instance of the Microsoft ADO Data control onto your form and give it a name like oleDataCtrl. This control is needed to provide the proper interface so the ActiveX grid can bind to the RecordSet. You can place this control anywhere within the form, as the control is invisible at run time.
Now place a Microsoft DataGrid control on your form and set its Name property to oleGrid. Once these controls are on your form, your form will look similar to Figure 3. To make it all work, write the following code in the Init method of the form—this will cause the DataGrid todisplay the contents of the RecordSet you created earlier:
WITH ThisForm
.oleDataCtrl.Object.RecordSet = .oRS
.oleDataCtrl.Object.Refresh()
.oleGrid.Object.DataSource = .oleDataCtrl.Object
.oleGrid.Object.Refresh()
ENDWITH
Figure 3. The ADO Test form loaded in the Form Designer.
When you execute the form, the data will appear in the grid control as shown in Figure 4. However, the data will be read-only, as the RecordSet also defaults to a read-only cursor
type. To modify this, change the LockType property of the RecordSet object so it will use optimistic locking:
.oRS.LockType = 3 && adLockOptimistic
.oRS.Open("SELECT * FROM Authors")
Figure 4. Viewing an ADO RecordSet at run time in a Visual FoxPro form with
ActiveX controls.
ADO constants
If you check the Help system on the ADO RecordSet and Connection objects, you will see many references to constants that begin with the letters “ad.” You saw some of these constants referenced in the previous code snippets, such as adLockOptimistic and adOpenStatic. While a Visual Basic program intrinsically recognizes these constants, Visual FoxPro does not; therefore, you must either explicitly reference their values or create “constants” with the
#DEFINE preprocessor directive:
#DEFINE adLockOptimistic 3
#DEFINE adOpenStatic 3
...
.oRS.CursorType = adOpenStatic
.oRS.LockTpe = adLockOptimistic
To make this easier, Microsoft now distributes an include file, adovfp.h, that you can use in your applications. This file contains all the constants recognized by ADO, including those mentioned in the previous code snippets. To get this file, visit the Visual FoxPro home pageat http://msdn.microsoft.com/vfoxpro and search for a utility called VFPCOM. This file is self- extracting and expands into several files, including the adovfp.h file.
Displaying RecordSets with the VFPCOM utility
The VFPCOM download includes other files, all of which comprise the VFPCOM utility, a tool created by the Visual FoxPro team at Microsoft primarily for use with ADO. (However, VFPCOM could be used with any COM server, not just ADO.)
Another limitation of ADO in a Visual FoxPro development environment is the inability to use native Visual FoxPro commands and functions against the data in the ADO RecordSet. Instead, you have to work with the Fields collection to manipulate the data, which requires a lotmore code than if you were working with a Visual FoxPro cursor.
VFPCOM alleviates this limitation by providing methods for converting ADO RecordSet objects into Visual FoxPro cursors and vice versa. The following code shows how to convert a RecordSet to a Visual FoxPro cursor called cAuthors with the VFPCOM utility:
loCn = CREATEOBJECT("ADODB.Connection") loRS = CREATEOBJECT("ADODB.RecordSet") loVFPCOM = CREATEOBJECT("VFPCOM.COMUtil")
IF VARTYPE(loCn)="O" AND VARTYPE(loRS) = "O"
loCn.ConnectionString = "Provider=SQLOLEDB;User ID=sa;Password=;" + ;
"Initial Catalog=Pubs;Data Source=MARS2000"
loCn.Open()
IF loCn.State = 1
loRS.ActiveConnection = loCn
loRS.Open("SELECT * FROM Authors")
lnError = loVFPCOM.RSToCursor(loRS,"cAuthors")
IF lnError <> 0
MESSAGEBOX("Unable to create Visual FoxPro cursor")
ELSE
SELECT cAuthors
BROWSE NOWAIT
ENDIF
ENDIF
ENDIF
Unfortunately, the cAuthors cursor created in the preceding code is never updatable, so you must create code that writes any changes back to the data source, either through the RecordSet or with native Visual FoxPro techniques.
More on VFPCOM
One exciting feature of the VFPCOM utility is the ability to bind Visual FoxPro code to the events of any COM server, including any object from ADO. For example, the RecordSet object has events that occur when data is changed in the current record. These events areWillChangeRecord, WillChangeField, FieldChangeComplete and RecordChangeComplete.
Native Visual FoxPro cannot handle these events, as it does not contain functionality to receive and process events from a COM server like ADO. Armed with the VFPCOM utility, you can have Visual FoxPro code respond to these events as they happen.
To handle the events triggered from an ADO RecordSet, you first create a Visual FoxPro class that “hooks” to the events of the RecordSet. This is easy to do using VFPCOM’s ExportEvents method as follows:
loVFPCOM = CREATEOBJECT("VFPCOM.COMUtil")
loRS = CREATEOBJECT("ADODB.RecordSet")
lnStat = loVFPCOM.ExportEvents(loRS,"sample.prg")
IF lnStat = 0
MESSAGEBOX("program created successfully")
ENDIF
The previous code creates a program called sample.prg, which contains the definition of a custom class with methods for each possible event of the RecordSet:
DEFINE CLASS RecordsetEvents AS custom
PROCEDURE EndOfRecordset(fMoreData,adStatus,pRecordset)
* Add user code here
ENDPROC
PROCEDURE FetchComplete(pError,adStatus,pRecordset)
* Add user code here
ENDPROC
PROCEDURE FetchProgress(Progress,MaxProgress,adStatus,pRecordset)
* Add user code here
ENDPROC
PROCEDURE FieldChangeComplete(cFields,Fields,pError,adStatus,pRecordset)
* Add user code here
ENDPROC
PROCEDURE MoveComplete(adReason,pError,adStatus,pRecordset)
* Add user code here
ENDPROC
PROCEDURE RecordChangeComplete(adReason,cRecords,pError,adStatus,pRecordset)
* Add user code here
ENDPROC
PROCEDURE RecordsetChangeComplete(adReason,pError,adStatus,pRecordset)
* Add user code here
ENDPROC
PROCEDURE WillChangeField(cFields,Fields,adStatus,pRecordset)
* Add user code here
ENDPROC
PROCEDURE WillChangeRecord(adReason,cRecords,adStatus,pRecordset)
* Add user code here
ENDPROC
PROCEDURE WillChangeRecordset(adReason,adStatus,pRecordset)
* Add user code here
ENDPROC
PROCEDURE WillMove(adReason,adStatus,pRecordset)
* Add user code here
ENDPROC
ENDDEFINE
Of course, you will want to add user code in these methods to make them respond appropriately. Once you make your modifications, you can bind a RecordSetEvents object to a RecordSet with the BindEvents method of the VFPCOM utility, as follows:
*--Create object that will hook to ADORS events
SET PROCEDURE TO Sample.prg
loADORSHook = CREATEOBJECT("RecordsetEvents")
*--Bind to events of ADO RecordSet object
loVFPCOM.BindEvents(loRS,loADORSHook)
*--Do stuff in the RS to trigger ADO events loRS.MoveFirst && GO TOP
loRS.MoveNext && SKIP
loRS.Fields(0).Value = "999-99-9999" && REPLACE…
Now you can detect an event that occurs when a user modifies data in a RecordSet, moves a record pointer, or performs other operations that trigger events.
Note that the Connection object and the yet-to-be-introduced Command object also have events that may be of interest. Be sure to check the ADO documentation in the MSDN library for more details.
The Command object
You have seen how to connect to data, read and manipulate data, and respond to events. Next, you will see how ADO executes stored procedures or other commands on a data source. These features are encapsulated within the ADO Command object.
Like the RecordSet object, a Command object requires the services of a Connection object. ADO will create a connection implicitly if one is not specified. As previously mentioned, it is usually better to create a Connection object explicitly that can be shared among multiple RecordSet and Command objects.
The main method of a Connection object is its Execute method, which executes the code that exists in the object’s CommandText property. This command can be a stored procedure call or any other SQL statement that the data source understands.
In other words, the Command object is used for everything that SQL pass through can do from within Visual FoxPro.
The following code shows how to create a Command object that connects to a SQL Server and executes the byroyalty stored procedure from the pubs database:
#INCLUDE adovfp.h
LOCAL loCn, loCmd, loRS, loFld
loCn = CREATEOBJECT("ADODB.Connection")
loCn.ConnectionString="Driver=SQL Server;"+ ;
"Server=MySQLSvr;uid=sa;pwd=;Database=Pubs"
loCn.Open()
IF loCn.State = 1
loCmd = CREATEOBJECT("ADODB.Command")
loRS = CREATEOBJECT("ADODB.RecordSet")
loCmd.ActiveConnection = loCn
loCmd.CommandText = "EXECUTE byroyalty 40" loCmd.CommandType = adCmdText
loRS = loCmd.Execute() ACTIVATE SCREEN
DO WHILE NOT loRS.EOF
FOR EACH loFld IN loRS.Fields
??TRANSFORM(loFld.Value)+" "
ENDFOR
loRS.MoveNext
?
ENDDO
ENDIF
You can see that the Command object has an ActiveConnection property, just like the RecordSet object. The actual command is specified by the CommandText property, and the CommandType property is used to signify that the contents of CommandText are literal text for theserver. In this example, since the command includes the EXECUTE keyword as well as a parameter, it was necessary to use adCmdText instead of the expected adCmdStoredProc. Finally, since the byroyalty procedure returns a result set, a RecordSet object is used to capture the result.
You will not be able to call a stored procedure with output parameters or a return value with the preceding code. Instead, you must take advantage of the Command object’s Parameters collection. This collection is designed to handle the variations in the number and type ofparameters used by the range of available stored procedures.
To use the Parameters collection, you must add Parameter objects to the collection. When adding parameters, you also specify the attributes of each parameter, such as whether it is an input or output parameter, the parameter’s data type and, for input parameters, the input value. The following code modifies the previous example to use the Parameters collection:
loCmd.ActiveConnection = loCn loCmd.CommandText = "byroyalty" loCmd.CommandType = adCmdStoredProc
loParam = loCmd.CreateParameter("Percentage",adInteger,adParamInput,0,40)
loCmd.Parameters.Append(loParam)
loRS = loCmd.Execute()
As you can see, the CreateParameter method builds the actual Parameter object separately from the Command object. Once the Parameter object is created, you use the Append method to add it to the Parameters collection of the Command object. This parameter object is then automatically passed to SQL Server when the stored procedure is invoked.
As you read in Chapter 6, “Extending Remote Views with SQL Pass Through,” SQL pass through can receive output parameters from SQL Server but cannot handle return values from stored procedures. Fortunately, the ADO Command object can handle return values by adding the appropriate Parameter object to the Parameters collection. To demonstrate, first consider the following sample SQL Server stored procedure that accepts an input parameter and returns a RecordSet, an output parameter and a return value:
CREATE PROCEDURE myProc
@inparm int,
@outparm int OUTPUT
AS
SELECT name FROM sysusers WHERE uid < @inparm
SELECT @outparm = 88
RETURN 99
To invoke this procedure properly, it must be called with an input parameter as well as an output parameter. Further, it returns a RecordSet (from the SELECT statement) and an integer value (from the RETURN statement). The following code illustrates how to invoke this procedure properly with a Command object so you can display the contents of the returned RecordSet, return value and output parameter:
loCmd.CommandText = "myproc" loCmd.CommandType = adCmdStoredProc
* Set up parameters
loParam = loCmd.CreateParameter("Return", adInteger, adParamReturnValue,0, 0)
loCmd.Parameters.Append(loParam)
loParam = loCmd.CreateParameter("InParm", adInteger, adParamInput,0, 2)
loCmd.Parameters.Append(loParam)
loParam = loCmd.CreateParameter("OutParm", adInteger, adParamOutput,0, 0)
loCmd.Parameters.Append(loParam)
loRS = loCmd.Execute()
* Print contents of RecordSet
ACTI SCRE
DO WHILE NOT loRS.EOF
FOR EACH loFld IN loRS.FIELDS
??TRANS(loFld.VALUE)+" "
ENDFOR
loRS.MoveNext
?
ENDDO
* Must close RecordSet before you can get return values
loRS.Close()
* All collections are zero based
?"Return value: " + TRANSFORM(loCmd.Parameters(0).Value)
?"Output Parameter: " + TRANSFORM(loCmd.Parameters(2).Value)
Notice how the parameters are created and appended to the Command object. The order of the parameters is significant: You must declare the return value first, followed by each parameter in the order required by the stored procedure. Rearranging the parameter causes the Execute method to fail.
Also note that you must close the returned RecordSet object before you can query the returned values; otherwise, the parameters will contain empty values. This requirement exists because of the way that ADO retrieves the results from a data source—first the RecordSet is passed, then the Output parameters and return values. Therefore, you must
always close any RecordSets before you are able to retrieve the actual values returned from a stored procedure call.
Summary
This chapter has shown you the basics of using ADO within a Visual FoxPro application. The advantages and disadvantages of ADO were described and compared to using the native Visual FoxPro tools for accessing data. You then saw examples of the Connection, RecordSet and Command objects, which showed the purpose of each type of object. Hopefully, you now have enough information to incorporate ADO technology into your client/server applications.
Posts
No comments:
Post a Comment