How to use a parameterized query with a Remote Data Service.

Abstract: Using the Remote Data Service (RDS) feature of ADO.

There is a demo that comes with Delphi which uses TRDSConnection to connect to the DBDEMOS database using ADO. We are going to modify the server part of this by adding a Command object, as well as few other objects. We will be using Command.Execute instead of Result.Open. It is possible to execute a query without using a Command object by passing a query string to the Execute method of a Connection object or to the Open method of a Recordset object, but a Command object is required when you want to use query parameters.

Below is the code used to create a parameterized query using an ADO Command object. To test out this code just open the RDS demo: C:\Program Files\Borland\Delphi6\Demos\Ado\Rds. Replace the code for function TRDSAppServer.Get_Employee: _Recordset; with the code below.

function TRDSAppServer.Get_Employee: _Recordset;
var
  ConnStr: WideString;
  CMD : _command;
  vRecsAffected, vParams: OleVariant;
  aConnection: _Connection;
begin
  ConnStr := 'FILE NAME=' + DataLinkDir + 'DBDEMOS.UDL';
  aConnection := CoConnection.Create;
  aConnection.ConnectionString := ConnStr;
  aConnection.Open(ConnStr, '', '',-1);  
  CMD := CoCommand.Create;
  CMD.Set_ActiveConnection(aConnection);
  CMD.CommandType := adCmdText;
  CMD.CommandText := 'SELECT * FROM Employee WHERE EmpNo = :EmpNo';
  Result := CoRecordSet.Create;
  Result.CursorLocation := adUseClient;
  Result.CursorType := adOpenForwardOnly;
  Result.LockType := adLockOptimistic;
  vRecsAffected := -1;
  vParams := CMD.CreateParameter('EmpNo', adInteger, adParamInput, 0, 4);
  Result := CMD.Execute(vRecsAffected,vParams,adCmdText);
end;
You can use code completion in Delphi to find the meaning of each of the parameters - just hold the cursor over the procedure name. The key to using a query with ADO, whether you are using the result set's open method or the Command object, is the command option adCmdText. Stored procedures can also be used to execute queries. In this case calling open with the command option of adCmdStoredProc will work, and will not require the Command object.

When running this program you may get the error "Dataset does not support bookmarks, which are required for multi-record data controls". As far as I can tell, this is a problem with the OLE provider, not with Delphi. To avoid this do not use a DBGrid. Use other data-aware components instead (just drag the fields from the fields editor onto the form).

For more information on ADO Commands see the ADO API Reference on the Microsoft web site.
For more about RDS go here.

  Latest Comments  View All Add New RSS ATOM

Move mouse over comment to see the full text

Server Response from: ETNASC04