Is it possible create customize SP in DA Schema Modeler

Hi,
If SP has 3 datasets, then your client-side sample code need to call 3 times “lStreamer.ReadDataset(ltable1.LogicalName, ltable1, True);”. The time consume will be 3 times and performance is drop. Is it possible call 1 time and return all datasets on client-side?

Hi,

You are incorrect.

for example, how much will be performance drop in case

stream.Read(word1, 2);
stream.Read(word2, 2);

vs

stream.Read(int1, 4);

?

the same here - difference will be present but it will be no significant

Hi,
Yes. I overlook that it is return the stream and then read the stream data on client-side.

On the other hands, is it possible to using generic method that keep the aDataset.LogicalName from SP’s temp table on DA server method?

Hi,

can you give some example, pls?

Hi,
For example, sp_Customer will return “select * from #Customers” and “select * from #CustomerOrder”. Then return #Customers aDataset.LogicalName and #CustomerOrder aDataset.LogicalName.

sp_Employee will return “select * from #EmployeeProfile”, “select * from #Family” and “select * from #Education”.
Then return #EmployeeProfile aDataset.LogicalName, #Family aDataset.LogicalName and #Education aDataset.LogicalName.

Is it possible to using generic function “TDataService.MyMethod(SP_Name : string) : Binary;” and return the SP aDataset.LogicalName. That need not to hard-code the LogicalName table in method.

Hi,

so you want to replace

     aDataset.LogicalName := 'table1';
     aDataset.LogicalName := 'table2';

with

     aDataset.LogicalName := 'Customers';
     aDataset.LogicalName := 'CustomerOrder';

ofc you can do it, but where you take these names if SP doesn’t return them?

btw, you can return SP_Name_1, SP_Name_2, etc.
Here is main condition - logical names should be unique in one stream

Hi,
I guess it may have a method to get the SP temporary table name. If it can’t, then can consider the setup the table to keep SP and temporary table name with ordering. After that, pass the temporary table name records when call the method.

About your mention of main condition, I think I need to capture how many SP dataset will return from SDAC. Then create the unique logical names.

Thanks.

Hi,
I prepared the following code in DA server. But I don’t know how to implement on client side that can call the method. Any advise or procedure for me. Because I don’t know how to implement your above sample code on client side. Thanks.

function TDataService.MultiDataSets(var sp_name : string): Binary;
var
aDataset: IDAServerDataset;
NoRecSet : integer;
begin
NoRecSet := 1;
result := Binary.Create;
DataStreamer.Initialize(result, aiWrite);
try
sp_template.Active := false;
sp_template.StoredProcName := sp_name;
sp_template.Active := true;
aDataset := ServiceSchema.NewDataset(Connection, ‘ResultSets’) as IDAServerDataset;

 while sp_template.HasNextResultSet = true do
 begin
    aDataset.LogicalName := 'table' + inttostr(NoRecSet);
    DataStreamer.WriteDataset(aDataset as IDADataSet, [woSchema, woRows], -1);
    NoRecSet := succ(NoRecSet);
    sp_template.OpenNext;
 end

finally
DataStreamer.Finalize;
end;
end;

Hi,

you can check how many and what exactly tables are present in stream.
use these properties of DataStreamer:

property DatasetCount: integer read GetDatasetCount;
property DatasetNames[Index: integer]: string read GetDatasetNames;

Hi,
Those property place in where? On Server side’s “DataService_Impl”? Then how client side to call the method? Any simple demo about this? Thanks.

Hi,

when you read data from stream, you can use code like

      stream := CoDataService.Create('http://localhost:8099/bin').MyMethod;
      lStreamer.Initialize(stream, aiReadFromBeginning);
      try
        ShowMessage('Stream contains ' + IntToStr(lStreamer.DatasetCount)+ ' tables');
        for i:= 0 to lStreamer.DatasetCount - 1 do 
          ShowMessage('#' + IntToStr(i)+ ' = ' + lStreamer.DatasetNames[i]);
        ...
      finally
        lStreamer.Finalize;
      end;

Hi,
On client side, I need not to check how many datasets. Because I already know which dataset need to use when apply the business logic.
Am I need to add “VCLApplication_Intf” on client side and add the service in “Service Library”?

Hi,

You need if you have added a custom method like “MyMethod” to your DataService

Hi,
I use this solution is OK before. But now my stored procedure will return 18 set of datasets and it can’t show the data start from 14th dataset. I checked SDAC that return correct 18 set of datasets. Is it a bug in DA?

    ClientDataModule.DataStreamer.ReadDataset(ClientDataModule.DataStreamer.DatasetNames[14],buf_rec,true,true,false);

Hi,

what value you have in ClientDataModule.DataStreamer.DatasetCount ?

can you create a simple testcase that reproduces this case, pls?
I need also DDL for creating these tables and this SP.

You can drop email to support@ for keeping privacy

Hi,
DatasetCount value is 19.
I split SP to 2 SPs for debug. The problem still happen in 2nd SP. 1st SP dataset can show correctly. I will try to debug again that prove which part has problem first. Then I will prepare the simple testcase for you. But I don’t know the problem is using “logchanges = false” and “RemoteFetchEnable = false” or not.

Hi,
I tested to insert the records into physical table instead of #temptable in SP. I found it doesn’t insert any records into physical table. I found the problem is using Cursor to do some business logic and generate the record to table. But SDAC need to wait for “OpenNext” to trigger the action. So I guess “ClientDataModule.DataStreamer.ReadDataset” will not trigger the action directly and then return no record result.

Hi,

DataAbstract doesn’t know about specific methods of SDAC like OpenNext.

I can recommend to use custom service method where you can call OpenNext when it is needed

Hi,
I checked my coding in DataService_Impl that already use “OpenNext” to "DataStreamer.WriteDataset and return the specific dataset to application. I also tried to add one more loop before “DataStreamer.WriteDataset”. It also can’t run the MSSQL SP’s cursor loop logic. Lastly, I tried to remove the “select” in SP that only has business logic cursor to insert the records in table. It also doesn’t insert any record into table. It should be a problem in TDatasetWrapper.Create and DataStreamer.WriteDataset that only can handle “select” statement without any Cursor looping logic.

Hi,
I also tried to move those business logic to other SP and exec SP in original SP. It also doesn’t work. It means “DataStreamer.WriteDataset” will not have execute function. Right? If yes, how can I solve this problem to handle multiple datasets?