Raw SQL

Hello,
I’am evaluating Remobjects SDK and Data abstract.
In our current 3-tier system all SQL is stored in specialized classes on Server-side.
We want to re-use these SQL-statements.
Is there a way to “inject” a SQL-statement in a TDADataSet (or some other class) instead of storing SQL via the Data Abstract Schema Modeler ?

Freddy

Hi,

You can use ServiceSchema.OnGetSQL event for assigning SQL statements:

procedure TDataService.SchemaGetSQL(Sender: TDASchema; const ElementName: string; ElementType: TDASchemaElementType;
  var SQL: string);
begin
  if ElementName = 'Customers' then SQL := 'SELECT * FROM Customers';
end;

also you can use DAService.OnBeforeGetDatasetData event like:

procedure TDataService.DataAbstractServiceBeforeGetDatasetData(aSender: TObject; const aDataset: IDADataset;
  const aIncludeSchema: Boolean; const aMaxRecords: Integer);
begin
  if aDataset.LogicalName = 'Customers' then
      (aDataset as IDAServerDataset).SQL := 'SELECT * FROM Customers';
end;

in second event you have access to aDataset so you can assign parameters too.

Hi Evgeny,

Altough the proposed solution works, it is not practically usable in our classes.
I am more looking for a solution something like below.
Note: completely on ServerSide in a ServerClass, no userinterface involved

Var
DataSet: TDADataset;

DataSet := TDADataset.Create; // Create a sort of a dataset

// Get connection from the database-connection-pool

// Execute SQL
DataSet.SQL := ‘Select * from CONTACTS where CONTACT_ID = :CONTACT_ID’;
DataSet.ParamByName(‘CONTACT_ID’).AsString := ‘123’;
DataSet.Open;

// Process the result-set
while not DataSet.Eof do

Is this possible?

Hi,

yes, it is possible.
You need to use the IDAConnections.NewDataset method like

procedure TDataService.NewMethod;
...
  ds := Self.Connection.NewDataset('Select * from ....') as IDAServerDataset;
  ds.RefreshParams;
  ds.ParamByName('CONTACT_ID').AsString := ‘123’;
  ds.Open;
  while not ds.Eof do ...

Hi Evgeny,

Nice, works perfectly! Thanks.

One last question:
Can I “extend” TDataService with my own methods ?
I tried to do it like below, but Service-method SomeMethod is not visible at client-side

TDataService = class(TDataAbstractService, IDataService)
DataStreamer: TDABin2DataStreamer;
EcmaScriptProvider: TDASpiderMonkeyScriptProvider;
Schema: TDASchema;
procedure SchemaGetSQL(Sender: TDASchema; const ElementName: string; ElementType: TDASchemaElementType; var SQL: string);
procedure DataAbstractServiceBeforeGetDatasetData(aSender: TObject; const aDataset: IDADataset; const aIncludeSchema: Boolean;
const aMaxRecords: Integer);
private
protected
{ IDataService methods }
public
[ROServiceMethod]
** function SomeMethod(const aParameter: string): string;**
end;

Hi,

Yes, you can extend DataService with custom methods.

Currently (i.e. just after wizard), you have RODL-based application.
you need to launch Service Builder (IDE menu-> Tools->Remoting SDK & DataAbstract -> Service
Builder) and add new method inside Service Builder and later add it to TDataService.

by other hand, you can convert app to CodeFirst application and then add methods as you did:

[ROServiceMethod]
 function SomeMethod(const aParameter: string): string;

Hi,

I converted my test application to a CodeFirst Server.
It works like it should :slight_smile:
Thank you !

Yet another problem.
On the server, I tried to call a servicemethod from another servicemethod, but I get the error ‘Interface not supported’

var
fClassFactory: IROClassFactory;
ClientID: TGUID;
instance: IInterface;

begin
ClientID := ROBinMessage.ClientID;
fClassFactory := GetClassFactory(‘DataService’);
fClassFactory.CreateInstance(ClientID, instance);
result := (instance as IDataService).GetContactData(aContactID); // interface not supported ???
end;

Any idea ?

Hi,

in CodeFirst services you can’t use interfaces like IDataService as is because they don’t contain your service methods declared with [ROServiceMethod].

however if you declare methods in interface, it should work as expected:

IDataService = interface
   function SomeMethod(const aParameter: string): string;
end;

TDataService = class(TDataAbstractService, IDataService)
..
  function SomeMethod(const aParameter: string): string;

Note: CodeFirst server doesn’t use any declarations from _Intf files because _Intf became clients-only files that are generated on-fly from running CodeFirst server.