Article ID: 195047
Article Last Modified on 8/30/2004
APPLIES TO
- Microsoft Visual FoxPro 6.0 Professional Edition
- Microsoft Data Access Components 2.1 Service Pack 2
- Microsoft Data Access Components 2.5
- Microsoft Data Access Components 2.6
This article was previously published under Q195047
SUMMARY
The main purpose of the ActiveX Data Objects (ADO) Command object is to allow the execution of stored procedures and parameterized commands against a data provider.
MORE INFORMATION
The sample uses the pubs database in SQL Server to demonstrate the use of the ADO command object to call parameterized queries. The pubs database contains a stored procedure called byroyalty that accepts an integer parameter and returns all authors from the Titleauthor table whose royalty amount matches the passed value. Although this stored procedure does not return any values to the caller, the example reserves space for a return value in the Parameters collection for illustrative purposes.
The code demonstrates three methods of calling this parameterized query.
The first method treats the stored procedure as a command to be executed. It does not require a complicated calling syntax, but cannot return values from the stored procedure.
The second method also treats the stored procedure as a command to be executed. However, it uses the Refresh method of the parameters to automatically populate the parameters collection with information about parameter data types. This is undesirable because there is a substantial performance hit associated with returning parameter information from the server. This method does allow return values from the stored procedure.
The third method requires that you manually populate the Parameters collection with parameter information. From a performance standpoint, this method is less expensive than automatically populating the parameters collection, but it does require the programmer to specify the parameter binding information. It uses the CreateParameter method of the Command object to create a parameter of a specific type, and the Append method of the Parameter object to add the parameter to the Parameters collection.
To use this example, you must have Microsoft Data Access Components (MDAC) version 2.x or later installed, which is included in the data components of Visual Studio 6.0 or can be downloaded from the following Web address:
Create and execute the following program. It calls the byroyalty stored procedure in the SQL Server pubs sample database three different ways. It prints the returned recordset on the desktop and displays a wait window between each of the methods. Substitute an appropriate Server, User ID and Password in the definition of the lcConnString variable.
It may be helpful in understanding the Parameters collection to uncomment the call to the ShowParms() function, found immediately before the stored procedure executes. It prints the contents of the Parameters collection on the desktop.
Sample Code
* Begin code.
* Demonstrates three ways to call a stored procedure that accepts
* parameters.
*
* The stored procedure used is BYROYALTY in pubs, which queries the
* titleauthor table for royalty amounts that equal the
* passed value, and returns a recordset.
#DEFINE adInteger 3
#DEFINE adParamOutput 2
#DEFINE adUseClient 3
#DEFINE adModeReadWrite 3
#DEFINE adCmdText 1
#DEFINE adExecuteNoRecords 128
CLEAR
oConnection = CREATEOBJECT("ADODB.Connection")
oCommand = CREATEOBJECT("ADODB.Command")
oRecordSet = CREATEOBJECT("ADODB.Recordset")
oParameters = CREATEOBJECT("ADODB.Parameter")
lcConnString = "driver={SQL Server};" + ;
"Server=CHICKENHAWK;" + ;
"DATABASE=pubs"
lcUID = "sa"
lcPWD = ""
WITH oConnection
.CursorLocation = adUseClient
.ATTRIBUTES = adModeReadWrite
.OPEN(lcConnString,lcUID,lcPWD, )
ENDWITH
************************************************************
* Here's the easiest way to implement:
*
* Tell the command object that the CommandType is
* a regular command, and pass the parameter you want
* in the CommandText. Most providers can interpret
* the default adCmdUnknown, or the common adCmdText correctly.
*
* However, it will not let you return a value.
WITH oCommand
.CommandText = "byroyalty (40)"
.ActiveConnection = oConnection
ENDWITH
oRecordSet.OPEN(oCommand)
* display the recordset on the desktop
=ShowRS()
WAIT WINDOW "Method 1 complete - press a key to continue"
** released rs and command object
release oRecordset
release oCommand
************************************************************
* A second method is to pass parameters with a command,
* automatically populating the parameters collection.
*
* The programmer does not have to know the parameter binding
* information, the Parameters collection refresh method
* gets it for you from the server.
*
* However, this method has to go to the server
* before calling the Stored Procedure, resulting in a likely
* performance hit.
*
* This command text string says:
* Return a parameter (?) from a call to SP byroyalty
* which accepts one input parameter (?).
oCommand = CREATEOBJECT("ADODB.Command")
oRecordSet = CREATEOBJECT("ADODB.Recordset")
WITH oCommand
* This command text string says:
* call SP byroyalty, which accepts one input parameter (?).
* removed comment
* remove ? = here for input parameter
.CommandText = "{call byroyalty (?)}"
.ActiveConnection = oConnection
.PARAMETERS.REFRESH
ENDWITH
* Specify the parameter
oCommand.PARAMETERS(0).VALUE = 40
oRecordSet = oCommand.Execute
=ShowRS()
WAIT WINDOW "Method 2 complete - press a key to continue"
** release rs and cmd
release oCommand
release oRecordset
************************************************************
*
* A third method to implement it.
* Create both parameters manually and append them to the
* parameters collection.
*
* The programmer has to know the binding information,
* but there's no performance hit as with method 2.
oCommand = CREATEOBJECT("ADODB.Command")
oRecordSet = CREATEOBJECT("ADODB.Recordset")
oParameters = CREATEOBJECT("ADODB.Parameter")
WITH oCommand
.commandtype = adCmdText
* removed ?= here for nonexistent input parm
.commandtext = "{call byroyalty (?)}"
* removed definition of input parameter
.PARAMETERS.APPEND (oCommand.CreateParameter("@percentage",;
adInteger, 1, 4, 40))
.ActiveConnection = oConnection
ENDWITH
oRecordSet = oCommand.Execute
=ShowRS()
WAIT WINDOW "Method 3 complete - press a key to continue"
* function ShowRs: Print the returned recordset on the desktop.
FUNCTION ShowRS()
oRecordSet.MoveFirst
? "Records returned: ", oRecordSet.RecordCount
* and print the au_id field values
DO WHILE ! oRecordSet.EOF
? oRecordSet.FIELDS("au_id").VALUE
oRecordSet.MoveNext
ENDDO
?
* End Code
The constants used were defined using the Microsoft Visual Basic 6.0 object browser.
REFERENCES
MSDN Visual Studio Library 6.0; topic: "ADO Detailed Programming Model" topic
Additional query words: Query
Keywords: kbhowto kbsqlprog kbdatabase KB195047
