I started using RemObjects products 20 years ago (RemObjects SDK).
Eventually, I used RemObjects Data Abstract (I was doing something similar with SDK creating my own servers and using TClientDataSet).
I’m not sure now, but after using AnyDAC, FireDAC, and UniDAC for many years, in addition to RO DA, I’m not sure of the features I had at my disposal.
The reality is that I got used to create stored procedures in my data modules capable of extracting all the information from the database (parameters, name, types).
I found strange that now DA only creates datasets (tables and datasources), missing the “TDARemoteCommand” with all the properties already captured by Relativity Server, or easily received from the database itself.
Are you leaving Delphi behind, and that feature is available on other platforms?
type
TDABaseCommand_helper = class helper for TDABaseCommand
public
function GenerateInputParameters(aSchema: TDAClientSchema; aCommandName: string): DataParameterArray;
end;
{ TDABaseCommand_helper }
function TDABaseCommand_helper.GenerateInputParameters(aSchema: TDAClientSchema; aCommandName: string): DataParameterArray;
var
l_command: TDASQLCommand;
i: Integer;
begin
Result := nil;
l_command:= aSchema.Commands.SQLCommandByName(aCommandName);
for i := 0 to l_command.Params.Count - 1 do begin
if l_command.Params[i].ParamType in [daptInput, daptInputOutput] then begin
if Result = nil then Result := DataParameterArray.Create;
Result.Add.Name := l_command.Params[i].Name;
end;
end;
end;
If that same code was used in a similar way to “Get Design Data”, it could make things even easier because saving the component will leave it available at design-time.
The latest DA build 10.0.0.1571 broke the previous behavior for executing TDARemoteCommand. My previous code executed correctly as:
var outParam : DataParameterArray := nil;
MyRemoteCommand.Execute(‘MyCommand’, nil, outputParam);
Now, I had to create an inputParam:
var inputParam : DataParameterArray := DataParameterArray.Create;
I don’t have any input parameter, and the output parameters could be inferred by using the suggested helper (which I did).
If you look at the conversation, I was having some issues with TDARemoteCommand because I was used to the very old behavior of receiving it with all the design-time data imported from the schema. The code you suggested worked for me, executing it like
Execute(‘MyCommand’, nil, opa).
I have been using it for the past two weeks, but now I got an access violation.
It was triggered in this method:
procedure TDARemoteCommand.FillCommandParams(aDataParameterArray: DataParameterArray; aCommandParam: TRORequestParam);
var
lArray: DataParameterArray;
lParam: DataParameter;
i: Integer;
begin
if (aCommandParam.DataType <> rtUserDefined) or (aCommandParam.TypeName <> 'DataParameterArray') then Exit;
lArray := DataParameterArray.Create();
for i := 0 to aDataParameterArray.Count - 1 do begin <== Here aDataParameterArray is nil
lParam := lArray.Add();
lParam.Name := aDataParameterArray[i].Name;
lParam.Value := aDataParameterArray[i].Value;
end;
aCommandParam.AsComplexType := lArray;
aCommandParam.OwnsComplexType := True;
end;
What is interesting about this error is that it happens because of something strange:
function TDARemoteCommand.Execute(aCommandName: string;
aInputParameters: DataParameterArray;
out aOutputParameters: DataParameterArray): Integer;
var
lParam: TRORequestParam;
begin
Result := 0;
aOutputParameters := nil;
DAError(not Assigned(fExecuteCall), err_ExecuteCallIsUnassigned);
if (Length(fExecuteCall.OutgoingCommandNameParameter) > 0) then
fExecuteCall.ParamByName(fExecuteCall.OutgoingCommandNameParameter).Value := aCommandName;
if (Length(fExecuteCall.OutgoingParametersParameter) > 0) then begin
lParam := fExecuteCall.Params.FindParam(fExecuteCall.OutgoingParametersParameter); <== This is the issue
if Assigned(lParam) then FillCommandParams(aInputParameters, lparam);
end;
I don’t have any input parameters, just output parameters. That call shouldn’t assume there is an input parameter at all, as they are not required by all stored procedures.
In any case, I didn’t change my code until it failed. It is now working.
procedure TDARemoteCommand.FillCommandParams(aDataParameterArray: DataParameterArray; aCommandParam: TRORequestParam);
...
lArray := DataParameterArray.Create();
if Assigned(aDataParameterArray) then //<<<<<<<<< added
for i := 0 to aDataParameterArray.Count - 1 do begin
After migrating to build 1575, my code started failing again with error: An exception was raised on the server: Procedure or function ‘GetLastMessageID’ expects parameter ‘@MessageID’, which was not supplied.
My previous implementation, working with DA 1571, followed the latest suggestions:
function TCrashDB.GetLastMessageID : string;
begin
Enter('TCrashDB.GetLastMessageID');
try
var cmdGetLastMessageID : TDARemoteCommand := TDARemoteCommand.Create(nil);
cmdGetLastMessageID.RemoteService := RemoteService;
var ipa : DataParameterArray := DataParameterArray.Create;
var opa : DataParameterArray := cmdGetLastMessageID.GenerateOutputParameters(RemoteDataAdapter.Schema, 'GetLastMessageID');
try
cmdGetLastMessageID.Execute('GetLastMessageID', ipa, opa);
if VarIsNull(opa[1].Value) then
Result := ''
else
Result := opa[1].Value
finally
opa.Free
end
finally
Leave('TCrashDB.GetLastMessageID')
end
end;
The method GenerateOutputParameters is part of a helper class defined as:
type
TDABaseCommand_helper = class helper for TDABaseCommand
public
function GenerateInputParameters (aSchema: TDAClientSchema; aCommandName: string): DataParameterArray;
function GenerateOutputParameters(aSchema: TDAClientSchema; aCommandName: string): DataParameterArray;
end;
{ TDABaseCommand_helper }
function TDABaseCommand_helper.GenerateInputParameters(aSchema: TDAClientSchema; aCommandName: string): DataParameterArray;
begin
Result := nil;
var l_command:= aSchema.Commands.SQLCommandByName(aCommandName);
for var i := 0 to l_command.Params.Count - 1 do
if l_command.Params[i].ParamType in [daptInput, daptInputOutput] then
begin
if Result = nil then
Result := DataParameterArray.Create;
Result.Add.Name := l_command.Params[i].Name
end
end;
function TDABaseCommand_helper.GenerateOutputParameters(aSchema: TDAClientSchema; aCommandName: string): DataParameterArray;
begin
Result := nil;
var l_command:= aSchema.Commands.SQLCommandByName(aCommandName);
for var i := 0 to l_command.Params.Count - 1 do
if l_command.Params[i].ParamType = daptOutput then
begin
if Result = nil then
Result := DataParameterArray.Create;
Result.Add.Name := l_command.Params[i].Name
end
end;
In MSSQL, the output parameters is @MessageID, and Schema Modeler was defining it as MessageID. I wonder if that is the issue.
I believe I found the issue.
Relativity Server imports the stored procedure definition with the output parameter MessageID as dapInputOutput, despite its declaration in MSSQL:
CREATE PROCEDURE [dbo].[GetLastMessageID](@MessageID NVARCHAR(128) OUTPUT)
AS
BEGIN
select @MessageID = MessageID
from crashes
where ID = (select max(ID) from crashes)
END
I’m creating the input parameter DataParameterArray empty, and trying to create the output parameter with the extracted MessageID.
I modified the helper method GetOutputParameters by adding the possibility of dapInputOutput as ParamType:
function TDABaseCommand_helper.GenerateOutputParameters(aSchema: TDAClientSchema; aCommandName: string): DataParameterArray;
begin
Result := nil;
var l_command:= aSchema.Commands.SQLCommandByName(aCommandName);
for var i := 0 to l_command.Params.Count - 1 do
if l_command.Params[i].ParamType in [daptInputOutput, daptOutput] then
begin
if Result = nil then
Result := DataParameterArray.Create;
Result.Add.Name := l_command.Params[i].Name
end
end;
I’m trying the change to see if it works.
If that is the case, I will need to know where I should define a IN/OUT parameter, input or output?
I can do that.
I didn’t do it because the first time it worked as expected after dragging it from the other window.
This build has other issues, like it is trying to load all the data from each table when opening it.
It takes forever to open some tables or views, even from the Relativity Server Administrator application.
Did something huge changed in this build?
I found something in the schema that could be creating the issue.
The parameter types are prefixed with dapt like daptInput, daptOutput, daptInputOutput, and daptResult.
That was the same value used in the schema (.daschema).
In the latest build, when I declare the parameter as Out (instead of InOut), it saves dapOuput in the schema.
It looks like the enumerated param type TParamType daptOutput is read/written from/to a stream as datOutput, but it is consistent.
I changed the param type of my MessageID parameter to Out and everything worked.
What is still strange is that when doing a preview of a table or view in Relativity Server Administrator, it always tries to load all the records. It should load a few records, enough to test the connection and mapping.