PgDac and Datasnap

[legende]

We are planning to convert our 2-tier application (with PgDac) to 3-tier (client-server-database over TCP/IP) using Delphi XE2 Datasnap and eventually remove direct database access from client. As a side note, project is to big to convert entire application at once, so we planning to do it release by release and most of the business logic it written using OOP style.

Problem is, I haven't found good solution how to use PgDac with datasnap. Searching from forum, there is only one result with title "RAD Studio XE Support Available in DACs" 2.0 that says "Expanded multi-tier development with DataSnap". What that means?

Ideally what i want, is to make sql executions from client to database through server and retrieve results.

All help and comments greatly appreciated

[ROD]

Hi, legende

You can take any example of using DataSnap. In the server-side replace TSQLConnection -> TPgConnection and TSQLDataSet -> TPgTable (TPgQuery, TPgStoredProc etc.). This will allow you to extend the functionality of the server side.

Luck.

[legende]

I mean, is there a way using PagDac similary like TDBXConnection.

With dbExpress i can return TDBXConnection in server and execute dynamic query in client side. Small example:

Code: Select all

var
  server: TDatasnapServerMethodsClient;
  connection: TDbxConnection;
  command: TDbxCommand;
  result: TDbxReader;
begin
  server := TDatasnapServerMethodsClient.Create(qDSConnection.DBXConnection);
  connection := server.GetConnection; // returns TDbxConnection to database
  command := connection.CreateCommand;
  command.Text := 'SELECT COUNT(1) FROM clients';
  result := command.ExecuteQuery;
  result.Next;
  ShowMessage(result.Value[0].AsString);

[ROD]

Hi

I do not know the TDatasnapServerMethodsClient class. Could you tell us in more details, what you do in the row:

server := TDatasnapServerMethodsClient.Create(qDSConnection.DBXConnection);

And if you return DBXConnection in this way, can you return TPgConnection in the same way?

[legende]

1) TDatasnapServerMethodsClient is auto-genereted DataSnap client class. Server methods look like

Code: Select all

  TServerMethods = class(TDSServerModule)
    SQLConnection: TSQLConnection; // Database connection - want to replace with PgDac
  public
    function GetConnection: TDBXConnection;
  end;

  function TServerMethods.GetConnection: TDBXConnection;
  begin
    ConnectToDB(SQLConnection);
    Result := SQLConnection.DBXConnection;
  end;

2) server := TDatasnapServerMethodsClient.Create(qDSConnection.DBXConnection);
Connects DataSnap client to DataSnap server.

3) connection := server.GetConnection;
Returns database connection proxy (TDBXConnection)

It's not possible to return TPgConnection the same way, it needs proxy class for this.

[ROD]

Oh, I see.

Actually TPgConnection has no property similar to TSQLConnection.DBXConnection.

However, it doesn't prevent from implementation of the needed functionality, for example: execution of SQL statements with returning the result on the client via the server can be performed in the following way:

Add a function into the class on the server

Code: Select all

function TDatasnapServerMethodsClient.SQLRun(pSQL: AnsiString): AnsiString;
begin
  if not (PgConnect1.Connected) then PgConnect1.Connect;
  Result:= VarToStr(PgConnect1.ExecSQL(pSQL, []));
end; 

Then on the client

Code: Select all

procedure TClientForm.btn1Click(Sender: TObject);
var
  DBClass: TDatasnapServerMethodsClient;
  SQLResult: AnsiString;
begin
  DBClass:= TDBModuleClient.Create(qDSConnection.DBXConnection);
  try
    SQLResult:= DBClass.SQLRun('SELECT COUNT(1) FROM clients');
    if (SQLResult <> '') then ShowMessage( SQLResult );
  finally
    DBClass.free;
  end;
end; 

Please contact us again)

[legende]

Ok, that's what I thought.

Yes, it is easy to implement for basic queries, but its a quite big job to support parameters and multi row result with different kind of fields.

For example "SELECT integer, string, date, stream FROM clients WHERE name LIKE :name"

So, basically i have to options
1) i need devart dbExpress driver to make queries through datasnap TSQLConnection and PgDac for existing functionality to work until our migration is completed,
2) or to write custom proxy for PgDac.

[ROD]

By the way, at the expense of multi-line results, you can return the type of DataSet, as follows:

Code: Select all

DataSet1.Close;
DataSet1.CommandText: = pSql;
DataSet1.Open;
Result: = DataSet1;

On account of the options, yes, you're right.
You either want an easy code and do with the standard functionality, or more time-consuming code, but with more server-side functionality.

[legende]

Thanks for a hint. I finally found a working solution.

Server side components
TPgConnection
TPgQuery
TDataSetProvider (set poAllowCommanedText to true)

Client side components
TSqlConnection (for DataSnap connection)
TDsProviderConnection (for DataSnap server methods)
TClientDataSet (set provider)

Code: Select all

CDS.Close; // TClientDataSet
CDS.CommandText := 'SELECT * FROM clients WHERE name LIKE :name';
CDS.Params.ParamByName('name').AsString := '%rob%';
CDS.Open;

[ROD]

Hi Legende

Yes, it's great. Good luck to you!