I have a series of questions of some very basic operations which I’m finding to be implemented in a very complicated way or probably I’m not using them right or I’m using a Legacy way of doing things.
For example:
Commands. Supposedly, if I want to execute a stored procedure the way to do it is to add it as a command on the Schema and then call it from the Client. Ok, the part of adding the stored procedure to the Schema is quite straightforward and easy to do.
Calling it from the Client seems to be a bit more touchy. This is what I expect it, on DataSnap, I just assign a ClientDataSet to the stored procedure represented by a datasetprovider attached to the stored procedure component.
In order to execute it I simply execute the ClientDataSet “OPEN” command, if the stored procedure returns a set of value they will be loaded into the ClientDataSet if it is a function or has output parameters, they will be added as such to the clientdataset. Parameters are loaded automatically, etc, etc. Just like it works with the TDAmemDataTable for normal Table representations on DA.
But, this seems not to be the case, I found only this article:
But that is close to having no article at all. The sample doesnt work with multiple parameters, returning sets of data and based on questions I’ve read on “Connect” it seems overly complex compare to the DataSnap implementation, so it is my opinion that I must be looking at the wrong thing, so I looked for samples on the Demos included on DA but none deals with Commands.
Can someone please point me out on the right and proper use of executing Commands of a Schema (stored procedures) from the client side, including passing/receiving multiple input/output parameters, returning simple values (scalar) and multiple values (Table-value).
Hi, what language are using for the client side? Delphi / C# etc?.. I use commands for params in/output, not tables - I use dataset with stored proc for params in and table return.
Hi there, I use this method for calling commands from Delphi - the result is always in the first position, you can amend this to return an array of output from the innerarray items. Let me know if this was helpful.
function ExecuteCommandWithResult(const ACommandName: string; AParamNames: array of string; AParamValues: array of Variant): integer;
function …ExecuteCommandWithResult(const ACommandName: string; AParamNames: array of string;
AParamValues: array of Variant): integer;
var
lDARemoteCommand: TDARemoteCommand;
begin
lDARemoteCommand := GetRemoteCommand();
try
with lDARemoteCommand.ExecuteCall do
begin
MethodName := ‘ExecuteCommandEx’;
Params.Clear;
OutgoingCommandNameParameter := Params.Add(‘aCommandName’, rtString, fIn).name;
OutgoingParametersParameter := Params.Add(‘aInputParameters’, rtUserDefined, fIn, ‘DataParameterArray’).name;
IncomingAffectedRowsParameter := Params.Add(‘Result’, rtInteger, fResult).name;
IncomingParametersParameter := Params.Add(‘aOutputParameters’, rtUserDefined, fOut, ‘DataParameterArray’).name;
lDARemoteCommand.Execute(ACommandName, AParamNames, AParamValues);
Params.ParamByName(IncomingParametersParameter).OwnsComplexType := True;
Result := DataParameterArray(ParamByName(IncomingParametersParameter).AsComplexType).InnerArray[0].Value;
end;
Sorry DevFlex, it seems your answer was not shown to me by this software the first time i replied to you. I’ll have to try it out, but it seems to go on the same direction I was thinking, executing stored procedures on DA is not as visual as the beauty done with DataSnap and strangely enough as it was done with the Tables and join tables on the rest of DataAbstract.
I’ll try it and let you know soon. Thank you very much.