DA/Delphi calling StoredProcedure with result set and return value

Data Abstract
1

Hi!

I have a MSSQL database running here and I have a stored procedure (better: a lots of them…) that pass back a return value as indicator for success and a result set.

e.g.

CREATE PROCEDURE [dbo].[MyStoredProc] AS
begin
SELECT a, b, c FROM MyTable
Return (1)
end

Now I try to bring that into DA but I can find no way to get both, the return value plus the result set. All my tries just give me either the return value or the result set.

Is this possible with DA/Delphi?

2

Hello,

It could be done in such a way: define new service method that return Binary. Inside it execute SQL that call stored procedure and select result with the help of Connection.NewDataset method. Thus you will get dataset with two result sets inside. Write these two result sets in returned Binary with the help of TADOQuery.NextRecordset method:

// uses uDADatasetWrapper, ADODB;

function TDataService.DatasetAndResult: Binary;
var lDataSet: IDADataSet;
RecordsAffected:Integer;
ds:IDADataset;
begin
result:=Binary.Create;
try
ServiceDataStreamer.Initialize(result, aiWrite);
try
lDataSet := Connection.NewDataSet ('declare @ret int '+
‘EXEC @ret = StoredProcedureName ‘+
’ select @ret’);

  lDataSet.Open;
  ds := TDatasetWrapper.Create(lDataSet.Dataset);

  //........................................

  ds.LogicalName := 'Dataset1';
  ServiceDataStreamer.WriteDataSet (ds, lOptions, lMaxRecords);

  //switch to next recordset
  TADOQuery(lDataSet.Dataset).Recordset := TADOQuery(lDataSet.Dataset).NextRecordset(RecordsAffected);
  ds.LogicalName := 'Dataset2';

  ServiceDataStreamer.WriteDataSet (ds, lOptions, lMaxRecords);
finally
  lDataSet:=nil;
end;

finally
ServiceDataStreamer.Finalize;
end;

// …
end;

Now you could read datasets (one with result set and second with result value) from client.

Hope this helps

3

Sorry for my late reply (was on holiday),

thank you for your answer. I’m just wondering why this does not work out of the box with the tables defined in DASchema? Since for the tables I can create SQL based tables and such call stored procedures I don’t see the reason why this is not working just out of the box?
Anything I’m missing in here why this wouldn’t work (e.g. not available with a lot of databases) or is it just that it’s not done?

Thank you,

Thomas

4
Toemmsn said: I'm just wondering why this does not work out of the box with the tables defined in DASchema?

Sorry, I didin’t understand your question. Out of box for table is created simple “SELECT FROM …” query, in your case need to use stored procedure and solution proposed by Elena is ADO based, for another DB driver it can be different.