Advice on how to handle Queries between SOAP client and server

rfwoolf
rfwoolf used Ask the Experts™
on
I am converting an existing client-server app into a SOAP client-server app.
The old app has a bunch of TADOQuerys.
So I found a way of replacing each TADOQuery on the CLIENT with a TClientDataSet and TSoapConnection
and getting it to fetch records from a TClientDataSet and TDataSetProvider on the SERVER.

But the problem comes in with 2 things:
1: I need a way to execute dynamic 'once-off' queries where I supply the SQL in my CLIENT, it gets executed on the SOAP Server side, and somehow on my client I get back the result set in a dataset. I tried to think this through but came into some hitches so it would be good if a pro could assist on this.

2: I think it would be crazy to create a TClientDataSet (and TDataSetProvider) in the *SERVER* SOAP data module for every TADOQuery in my *CLIENT* app because that wouldn't be good practise -- I should only be creating the 'standard' queries that clients would want to use, and for everything else using dynamic queries. Or should I do all?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2010
Commented:
>> Or should I do all?

It's your choice. Both ways work. The choice depends on amount and kind of your queries.

You can send your queries to the DataSetProvider via CommandText property of ClientDataSet. It is sent to the provider when you open ClientDataset or when you call the its Execute method.

Options property of DataSetProvider should include poAllowCommandText.

Author

Commented:
That's a pretty good solution aflarin, thanks!
 
I'm almost there but now I'm having a problem getting my SOAP server's Interface's Implementation of a function to execute a Query on the SOAPDataModule -- I can't even seem to refer to the SOAPDataModule or any of its methods or objects - I've added sDataMod to the uses clause and then tried:
  QryCustom.SQL.text := 'xxx'; //Undeclared identifier 'QryCustom'
  sDataMod.QryCustom.sql.text := 'hh'; //Undeclared identifier 'QryCustom'
  sdatamod.ISoapDataMod42.qrycustom.sql.text := 'hh'; Object or class type required
  sdatamod.TSoapDataMod42.qrycustom.sql.text := 'hh'; // Method identifier expected
  sdatamod.SoapDataMod42.QryCustom.sql.text := 'hh'; //Undeclared identifier 'SoapDataMod42'

and attached is the code where QryCustom is declared in unit sDataMod
Unit sDataMod;

interface

uses SysUtils, Classes, InvokeRegistry, Midas, SOAPMidas, SOAPDm, DB,
  ADODB, Provider;

type
  ISoapDataMod42 = interface(IAppServerSOAP)
    ['{970D7AF6-84AD-4E15-A4FA-980693038571}']
//    procedure SOAPCustomQuery(SQL : string; ProviderName : string);
  end;

  TSoapDataMod42 = class(TSoapDataModule, ISoapDataMod42, IAppServerSOAP, IAppServer)
    dspOrdersByAClient: TDataSetProvider;
    Q_OrdersByAClient: TADOQuery;
    ADOConnection1: TADOConnection;
    dspCustom: TDataSetProvider;
    QryCustom: TADOQuery;
    dspPriceListsOfAClient: TDataSetProvider;
    Q_PriceListsOfAClient: TADOQuery;
  private

  public
    function SOAPCustomQuery(pSQL : string) : TADOQuery;
    //procedure SOAPCustomQuery(pSQL : string; ProviderName : string);
    //procedure SOAPCustomQueryFree(ProviderName : string);
  end;

implementation

{$R *.DFM}

procedure TSoapDataMod42CreateInstance(out obj: TObject);
begin
  obj := TSoapDataMod42.Create(nil);
end;

{procedure TSoapDataMod42.SOAPCustomQuery(pSQL : string; ProviderName : string);
begin
  with TADOQuery.create(self) do
  begin
    Name := 'q' + ProviderName;
    SQL.text := pSQL;
    Connection := ADOConnection1;
  end;

  with TDataSetProvider.create(self) do
  begin
    Name := ProviderName;
    DataSet := TADOQuery(FindComponent('q' + ProviderName));
  end;
end;     }

{procedure TSoapDataMod42.SOAPCustomQueryFree(ProviderName : string);
begin
  TDataSetProvider(FindComponent(ProviderName)).Free;
  TADOQuery(FindComponent('q' + ProviderName)).Free;
end; }

function TSoapDataMod42.SOAPCustomQuery(pSQL : string) : TADOQuery;
begin
  QryCustom.SQL.text := pSQL;
  QryCustom.Open;
  result := QryCustom;
end;

initialization
   InvRegistry.RegisterInvokableClass(TSoapDataMod42, TSoapDataMod42CreateInstance);
   InvRegistry.RegisterInterface(TypeInfo(ISoapDataMod42));
end.

Open in new window

Author

Commented:
Okay I think I figured it out...
The DataModule doesn't exist yet. You have to create an instance of it and then reference it. I was trying to refer to an ISoapDataMod42 and of course TSoapDataMod42 is not an instance.
So this would work in a function inside an implementation:
var
  dm : TSoapDataMod42;
begin
  dm := TSoapDataMod42.create(Nil);
  try
    dm.QryCustom.sql.text := 'hh'; //it now knows what QryCustom is!!!
  finally;
    dm.free;
  end;
end;

Commented:
SoapDataMod42... the "42' sounds familiar ;-)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial