Mega Code Archive
Simple high performance 3 tier apps with Indy and ADO
<![CDATA[
Title: Simple high performance 3-tier apps with Indy and ADO
Question: Now it's easy to create simple high performance middleware applications with Indy and ADO (thanks to OLE-DB resource poooling).
Answer:
First of all sorry for my bad english.
You can read the post of Romeo Lefter (seems to be romanian like me) on this site about this problem, but I'll use a diffrent aproach.
I'll skip the intro and bla..bla part (thanks to Romeo). What we need: Indy (www.nevrona.com/indy, VCL comps for easy TCP/IP communications) kbmMemtable (www.components4developers.com, advanced in-memory table), ADOExpress (Ado component suite included in last version of Delphi and CB) or Adonis (which I prefer). These examples are based on Adonis but are very easy to convert to AdoExpress (the diffrences between them are: ADOExpress has a component called AdoConnection; Adonis has AdoDatabase; the same thing but with diffrent name).
We'll use the same aproach as Internet Explorer: connect/send request/receive responese(data)/disconnect. This model is very scalable and very efficient.
We have following tiers: the client named CL (developed by us), middleware server named MD (developed by us) and database server named DB (can be any SQL database which has a OLE-DB provider with resource pooling - MS SQL SERVER, Oracle, Interbase(http://www.lcpi.lipetsk.ru/prog/eng/, Lipetsk provider)).
What is "resource pooling": you can connect to a db server thru a OLE-DB provider; the second connection to db server is very quick because the first call creates a pool of connections available for this process.
This example is based on MS SQL Server.
CL connects MD (thru Indy), CL sends to MD the sql query/statement, MD connects DB (thru ADO - very quick thanks to resource pooling), DB executes the request and sends response to MD, MD closes the connection to DB and sends the response to CL, which closes the connection to MD. In case of errors an exception is send back.
You can do custom RPC (remote procedure call). An example: when the CL sends the sql query/statement, the statement can look somethink like this: "@SEND_MAIL", and the MD executes a custom code (in this case sending mails) and not pass to DB (because it has @ in front of the string).
The use of TidThreadPoolMgr is very useful because the threads are maintained in a pool and can be used for thousands of requests.
A normal thing to do on MD server is to create dinamically (for every request) a ADOConnection or ADODatabase, a ADOQuery, a kbmMemtable, a VCLZip component if your response to be sent zipped.
To improve the performance a little and the quality of code we create a TSQLThread class with BeforeExecute and AfterExecute procedures to override the standard way (here we can create our components - only once at creation and destruction of thread). YES, the BeforeExecute and AfterExecute are executed once: at the creation and destruction.
MD server:
-------------------------------------------------------------------------
unit uMain;
interface
uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
IdThreadMgr, IdThreadMgrPool, IdBaseComponent, IdComponent, IdTCPServer,
ADODS, Db, kbmMemTable;
type
TSQLThread = class(TIdPeerThread)
protected
FADODb: TADODatabase;
FADOQry: TADOQuery;
Fmt: TkbmMemTable;
procedure BeforeExecute; override;
procedure AfterExecute; override;
public
end;
TForm1 = class(TForm)
TCPServer: TIdTCPServer;
IdThreadMgrPool: TIdThreadMgrPool;
procedure FormCreate(Sender: TObject);
procedure FormDestroy(Sender: TObject);
procedure TCPServerExecute(AThread: TIdPeerThread);
private
{ Private declarations }
public
{ Public declarations }
end;
var
Form1: TForm1;
implementation
uses ActiveX, ComObj;
{$R *.DFM}
procedure TSQLThread.BeforeExecute;
begin
CoInitialize(nil); //initializarea COM; it's required for every thread
FADODb := TADODatabase.Create(nil);
FADODb.DatabaseConnect := 'Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=pubs;Data Source=CAM42';
FADOQry := TADOQuery.Create(nil);
FADOQry.Database := FADODb;
FADOQry.ParamCheck := False;
Fmt := TkbmMemTable.Create(nil);
end;
procedure TSQLThread.AfterExecute;
begin
if Assigned(FADOQry) then
begin
FADOQry.Active:=False;
FreeAndNIL(FADOQry);
end;
if Assigned(FADODb) then
begin
FADODb.Connected:=False;
FreeAndNil(FADODb);
end;
if Assigned(Fmt) then
begin
FreeAndNil(Fmt);
end;
CoUnInitialize;
end;
procedure TForm1.FormCreate(Sender: TObject);
begin
TCPServer.ThreadClass:=TSQLThread; //assign the TSQLThread class
TCPServer.Active := True;
end;
procedure TForm1.FormDestroy(Sender: TObject);
begin
TCPServer.Active := False;
end;
procedure TForm1.TCPServerExecute(AThread: TIdPeerThread);
var
stream: TMemoryStream;
begin
with AThread.Connection do
begin
stream := TMemoryStream.Create;
try
try
with TSQLThread(AThread) do
begin
FADOQry.SQL.Text := ReadLn; //reads the sql command from socket
FADODb.Connected := True;
FADOQry.Open;
with Fmt do
begin
LoadFromDataSet(FADOQry, [mtcpoStructure,mtcpoOnlyActiveFields,mtcpoProperties,mtcpoLookup,mtcpoCalculated]); //loads records returned by DB into memory table
Active := True;
SaveToStream(stream, [mtfSaveData, mtfSaveCalculated,mtfSaveLookup,mtfSaveNonVisible,mtfSaveBlobs,mtfSaveDef,mtfSaveIndexDef,mtfSkipRest,mtfSaveFiltered,mtfSaveInLocalFormat]); //saves the records in to a stream which can be optionally zipped
end;
FADOQry.Close;
FADODb.Connected := False;
end;
WriteStream(stream); //writes the data to CL
Disconnect;
with TSQLThread(AThread).Fmt do
begin
EmptyTable;
Active := False;
end;
except
//if any errors
end;
finally
stream.Free;
end;
end;
end;
end.
---------------------------------------------------------------------------
CL:
---------------------------------------------------------------------------
unit client;
interface
uses
Windows, Messages, SysUtils, Classes, Graphics, Controls, Forms, Dialogs,
IdBaseComponent, IdComponent, IdTCPConnection, IdTCPClient, StdCtrls,
Grids, DBGrids, Db, kbmMemTable;
type
TForm1 = class(TForm)
tblc: TkbmMemTable;
DataSource1: TDataSource;
DBGrid1: TDBGrid;
Button1: TButton;
tcp: TIdTCPClient;
procedure Button1Click(Sender: TObject);
private
{ Private declarations }
public
{ Public declarations }
end;
var
Form1: TForm1;
implementation
{$R *.DFM}
procedure TForm1.Button1Click(Sender: TObject);
var
stream: TMemoryStream;
begin
stream:=TMemoryStream.Create;
try
tcp.Connect;
tcp.WriteLn('select * from authors');
tcp.ReadStream(stream,-1,true);
stream.Position:=0;
tblc.LoadFromStream(stream);
tcp.Disconnect;
finally
stream.Free;
end;
end;
end.
-----------------------------------------------------------------------------
The advantages: very high performance; scalability; can be used on multiple computers(like a cluster); instant deployment of client software;....
This is very easy model to resolve very complicated things. In the future (max 2 months) I'll make a set of componenets (under open source) for easy 3-tier development. In adition to those comps I'll make a ISAPI dll to make this arhitecture ready for Internet (this ISAPI dll is a trick very large used to pass firewalls).
These examples are made in 15 min. All I want to know if the community wants such components?
Thank you.
]]>