Is it possible create customize SP in DA Schema Modeler

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.

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(..)

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.

Hi,

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

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.

Hi,

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

Hi,
Any sample code for this issue? Thanks!

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;

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

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 …

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) :sweat_smile:

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.

Hi,
Thanks a agian. :pray:

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?

Hi,

my code uses

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

why you can’t use existing connection?

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!

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;

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.

Hi,

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

(aDataset.Dataset as TXXXXXQuery).SomeMethod;

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