Page 1 of 1
PgDac and Datasnap
Posted: Wed 09 May 2012 13:40
by 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
Re: PgDac and Datasnap
Posted: Thu 10 May 2012 10:29
by 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.
Re: PgDac and Datasnap
Posted: Thu 10 May 2012 14:40
by 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);
Re: PgDac and Datasnap
Posted: Mon 14 May 2012 16:36
by 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?
Re: PgDac and Datasnap
Posted: Tue 15 May 2012 06:56
by 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.
Re: PgDac and Datasnap
Posted: Tue 15 May 2012 15:29
by 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)
Re: PgDac and Datasnap
Posted: Wed 16 May 2012 14:31
by 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.
Re: PgDac and Datasnap
Posted: Thu 17 May 2012 10:30
by 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.
Re: PgDac and Datasnap
Posted: Mon 21 May 2012 11:51
by 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;
Re: PgDac and Datasnap
Posted: Mon 21 May 2012 12:01
by ROD
Hi Legende
Yes, it's great. Good luck to you!