Is it possible create customize SP in DA Schema Modeler

TIMOTHY_LAM · September 6, 2021, 6:08am

Hi,
Is it possible create customize SP in Schema Modeler? The reason is SQL DB belong to Vendor. I must not create the new SP in SQL DB. So I think should I create the SQL in Schema Modeler.

EvgenyK · September 6, 2021, 8:29am

Hi,

you can create a specific connection for each vendor, like:

Vendor1
Vendor2
after this, you can specify default connection in Delphi in runtime

common/usual tables can use connection type connection:

Also you can customize .daSchema for each user in Delphi code - patch current schema or provide .daSchema and load it from file in runtime like

DASchema.LoadFromFile(..)

TIMOTHY_LAM · September 6, 2021, 9:41am

Hi,
Maybe my wording not clearly. Vendor application has their own SP in SQL DB. But I need to re-use their SP with some business logic customize. Is it possible copy the SP statement into Schema Modeler’s statements? Then it will not touch vendor application DB.

EvgenyK · September 6, 2021, 9:45am

Hi,

I don’t think that SDAC support this feature
as a result, DA/SDAC driver will not support this too

TIMOTHY_LAM · September 6, 2021, 9:54am

Hi,
SDAC can support multiple datasets return. So I can use Dataset.OpenNext to add some business logic by SDAC VirtualQuery. DAMemDataTable can’t support Dataset.OpenNext. So I wish DA Schema Modeler can create customize SP.

EvgenyK · September 6, 2021, 10:12am

Hi,

you can put to stream all datasets.
However it requires some manual coding

TIMOTHY_LAM · September 6, 2021, 1:53pm

Hi,
Any sample code for this issue? Thanks!

EvgenyK · September 6, 2021, 2:14pm

Hi,

custom method like:

function TDataService.MyMethod: Binary;
var
  aDataset: IDAServerDataset;
begin
  result := Binary.Create;
  DataStreamer.Initialize(result, aiWrite);
  try
     aDataset := ServiceSchema.NewDataset(Connection, 'Orders') as IDAServerDataset;
     aDataset.LogicalName := 'table1';
     // set params if needed
     aDataset.Open;
     DataStreamer.WriteDataset(aDataset, [woSchema, woRows], -1);

     aDataset.LogicalName := 'table2';
     (aDataset.Dataset as TMSQuery).OpenNext;
     DataStreamer.WriteDataset(aDataset, [woSchema, woRows], -1);

     ...

     aDataset.LogicalName := 'tableN';
     (aDataset.Dataset as TMSQuery).OpenNext;
     DataStreamer.WriteDataset(aDataset, [woSchema, woRows], -1);
  finally
    DataStreamer.Finalize;
  end;
end;

on client-side tables from that stream can be read as

      stream := CoDataService.Create('http://localhost:8099/bin').MyMethod;
      lStreamer.Initialize(stream, aiReadFromBeginning);
      try
        lStreamer.ReadDataset(ltable1.LogicalName, ltable1, True); //ltable1.LogicalName = 'table1'
        lStreamer.ReadDataset(ltable2.LogicalName, ltable2, True); //ltable2.LogicalName = 'table2'
        ...
        lStreamer.ReadDataset(ltableN.LogicalName, ltableN, True); //ltableN.LogicalName = 'tableN'
      finally
        lStreamer.Finalize;
      end;

TIMOTHY_LAM · September 6, 2021, 2:21pm

Hi,
Many thanks. I will try it. Is it possible implement multiple datasets feature in future?

EvgenyK · September 6, 2021, 2:27pm

Hi,

it is a specific feature of one driver (SDAC).
I don’t think that others drivers like IBX, FireDAC, ADO, etc will support it …

TIMOTHY_LAM · September 6, 2021, 2:32pm

Hi,
Noted with thanks. If ADO, it has NextRecordSet to handle multiple datasets. If 2-tiers components, it should have multiple datasets feature. If using memory dataset, it will not have this feature. (As I know)

EvgenyK · September 6, 2021, 3:03pm

Hi,

you can adjust my code and add table name and params. as a result you will have universal method that returns all datasets from selected table.

TIMOTHY_LAM · September 7, 2021, 1:01am

Hi,
Thanks a agian.

TIMOTHY_LAM · January 13, 2022, 8:33am

Hi,
I tried your suggestion code today. It has an error on “WriteDataset”. It should be the parameters different. I’m using DA v1527. Besides, I found it needs to using Devart SDAC that handle SP multiple result sets. Then I need to create the other SDAC connection in Devart compoent and it will be a 2-tier instead of 3-tier. Right?

EvgenyK · January 13, 2022, 8:40am

Hi,

my code uses

function TDADataStreamer.WriteDataset(const Source: IDADataset; Options: TDAWriteOptions;
   MaxRows: integer = AllRows): integer;

why you can’t use existing connection?

TIMOTHY_LAM · January 13, 2022, 8:52am

Hi,
It has the following error.

I tried to add “uDASDACDriver” that still have undeclared identifier error. TMSQuery is Devart’s class that I need to add “MSAccess”. Then the error is gone. So I need to create Devart’s connection. Or my understanding is wrong?

Thanks!

EvgenyK · January 13, 2022, 9:10am

Hi,

use

DataStreamer.WriteDataset(aDataset as IDADataset, [woSchema, woRows], -1);

if your connection string is started with SDAC?, you already have SDAC connection.

update

(aDataset.Dataset as TMSQuery).OpenNext;

with

if not (aDataset.Dataset is TMSQuery) then raise Exception.Create('SDAC connection is required!');
(aDataset.Dataset as TMSQuery).OpenNext;

TIMOTHY_LAM · January 13, 2022, 10:01am

Hi,
The first issue is solved. Thanks.

But about SDAC connection issue, I haven’t to using Devart’s SDAC component now. My application is migrate to using DA. That’s mean your solution still need to using SDAC component to doing the middleware. Right? Will DA consider to implement that handle SP return multiple result sets? I found many applications (ERP, MES. HR) who also using SP to return multiple reset sets. Because many different information need to show in the same UI with multiple pages. It can improve the performance instead of execute different SPs.

EvgenyK · January 13, 2022, 10:16am

Hi,

if your current driver (UniDAC, ADO, FireDAC, etc) supports multiply datasets in SP, you can use it like

(aDataset.Dataset as TXXXXXQuery).SomeMethod;

TIMOTHY_LAM · January 13, 2022, 12:34pm

Noted with thanks. That’s mean I must to create the other data connection for other driver. Because my application only use DA.