Accessing DataBase via 3th server

Problem/Question/Abstract:

Writing n-tier Application for accessing client's app to DataBase without installing Client of Database via 3th server using Indy

Answer:

This is simple sample how organize work client's app with DataBase without installing Database client or organize remote access to database.

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, IdTCPServer, IdBaseComponent, IdComponent,
  IdTCPConnection, IdTCPClient, DBClient, Provider, Grids, DBGrids, DB,
  OracleData, Oracle, IdAntiFreezeBase, IdAntiFreeze;

type
  TForm1 = class(TForm)
    OracleSession1: TOracleSession;
    OracleDataSet1: TOracleDataSet;
    DataSource1: TDataSource;
    DBGrid1: TDBGrid;
    DataSetProvider1: TDataSetProvider;
    ClientDataSet1: TClientDataSet;
    IdTCPClient1: TIdTCPClient;
    IdTCPServer1: TIdTCPServer;
    Button1: TButton;
    Memo1: TMemo;
    IdAntiFreeze1: TIdAntiFreeze;
    procedure Button1Click(Sender: TObject);
    procedure IdTCPServer1Connect(AThread: TIdPeerThread);
    procedure IdTCPServer1Execute(AThread: TIdPeerThread);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure variantToStream(const v: oleVariant; stream: TStream);
var
  p: pointer;
begin
  stream.position := 0;
  stream.size := varArrayHighBound(v, 1) - varArrayLowBound(v, 1) + 1;
  p := varARrayLock(v);
  stream.write(p^, stream.size);
  varARrayUnlock(v);
  stream.position := 0;
end;

procedure VarArrayToStream(const Data: OleVariant; Stream: TStream);
var
  p: Pointer;
begin
  p := VarArrayLock(Data);
  try
    Stream.Write(p^, VarArrayHighBound(Data, 1) + 1); //assuming low bound = 0
  finally
    VarArrayUnlock(Data);
  end;
end;

function StreamToVarArray(Stream: TStream): OleVariant;
var
  p: Pointer;
begin
  Result := VarArrayCreate([0, Stream.Size - 1], varByte);
  p := VarArrayLock(Result);
  try
    Stream.Position := 0; //start from beginning of stream
    Stream.Read(p^, Stream.Size);
  finally
    VarArrayUnlock(Result);
  end;
end;

procedure TForm1.Button1Click(Sender: TObject);
var
  ms: TMemoryStream;
begin
  ms := TMemoryStream.Create;
  if not IdTCPClient1.Connected then
    IdTCPClient1.Connect;
  if IdTCPClient1.Connected then
    Memo1.Lines.Add('connected')
  else
    Memo1.Lines.Add('Not Connected');

  IdTCPClient1.Write('open');
  IdTCPClient1.ReadStream(ms, STrToINt(IdTCPClient1.ReadString(10)));
  ClientDataSet1.Data := StreamToVarArray(ms);
  // ClientDataSet1.LoadFromStream(ms);
  ClientDataSet1.Active := True;
end;

procedure TForm1.IdTCPServer1Connect(AThread: TIdPeerThread);
begin
  //
end;

procedure TForm1.IdTCPServer1Execute(AThread: TIdPeerThread);
var
  s: string;
  ms: TMemoryStream;
begin
  with AThread.Connection do
  begin
    s := ReadString(4);
    if s = 'open' then
    begin
      ms := TMemoryStream.Create;
      VarArrayToStream(DataSetProvider1.Data, ms);
      s := IntToSTr(ms.Size);
      while length(s) < 10 do
      begin
        s := '0' + s;
      end;
      Write(s);
      ms.Seek(0, soFromBeginning);

      WriteStream(ms);
    end;
  end;
end;

end.