An approach to implement alternative C/S-like database solutions without having a C/S engine

Problem/Question/Abstract:

An approach to implement alternative C/S-like database solutions without having a C/S engine

Answer:

I just came from a successful demo to our client. I used ASTA as my messaging middleware together with DBISAM. I did not use the ASTADBISAM server, just the plain ASTAServerSocket.

Here's what I did: The main concept is that only the ASTAServer socket writes to the database tables. All the data that needed to be written come from the ASTA clients and received by ASTAServer socket which writes these information into the database. The Database Tables on the Server machine are shared across all clients as READ-ONLY. All my ASTAClient applications synchronizes the lookup tables through a shared file access and not trhough ASTA. These took advantage of the LAN situation. We tested with 40 clients connected (ASTA client and shared READ-ONLY), the database flies! I avoided using ClientDataSets and AstaClientDataSets. Then we began pulling the plug on some of the Client Machines, and never was there any corruption on the main data.

I am excited because all of the client programs behave as if they are all acting like a single-user local connections! With 40 online connections, it takes under a second to retrieve and post hundreds of detail records from the server, and 40 users doing all of it at the same time!

Now I am at a point of optimizing between a LAN (shared read-only database, TCP/IP write by AstaServerSocket) and a pure TCP/IP connectivity. I am trying to create a client program that will take advantage of a LAN connection whenever available (right now, this is set manually during the initial setup of the client program).

So are you saying that you are reading the data from the clients using standard DBISAM table and/or query components, then writing changes back to the database through ASTA? Can you give us a more concrete example of how you set this up?

The clients are reading the data read-only from a shared folder. Then they are modified on the temporary tables on the client side, then only the modified portions (deltas) are sent to the AstaServer through a coded paramlist. The Astaserver receives the coded paramlist and depending upon the code, parse the data into destination tables for updates or writes, inserts, appends, deletes. One thing nice, is I can code everything on the Server side through tables and filters, wrap it around transactions, and never have to worry about corruption anymore. It is the server which does the actual writing to the shared data. This way, no client would be able to delete the data by accident on the shared folder. Only the server has the wread/write access to the data. Only occassionally I have to send data back to the clients, through coded paramlist via the socket components, that is only when they are registered to be on a modem line. Otherwise, if they are on a LAN, it is always faster for the clients to read the shared tables from a READ-ONLY folder, because the client's computer can do their own individual buffering of the database tables at a far greater capacity and efficiency than routing everything through the socket layer. With respect to security, the password table on the sahred folder is encrypted with DBISAM's own encryption, but not only that, the data contents of fields themselves are individually encrypted with my own encryption, so no one is able to get passwords and user id, they may be able to get the password's table and read the data structure but not the contents which would remain garbled unless they know how to decrypt them using my own algo. This is very good security for me.

My setup was creating the data on the Win2000 server, then it is shared as read-only. Since the AstaServer resides on the Win2000, it is the only program that has direct read/ write privileges to the data unless you set it otherwise.

Here are samples of my client-side codes sent to the server from the client's temporary tables. The whole process really works at lightning speed.

procedure TDML.PostMTO(const aMTONO: integer; aNewStatus, aHeaders, aNotes: string);
var
  lnstr: string;
  MTOParams, RetParams: TAstaParamList;
begin
  Screen.Cursor := crHourGlass;
  RetParams := TAstaParamList.Create;
  MTOParams := TAstaParamList.Create;
  try
    LoadTmpList(pvMTmpList, pvWTmpList);
    MTOParams.Add;
    MTOParams[0].Name := UserLoginID;
    MTOParams[0].AsInteger := aMTONO;
    MTOParams.Add;
    MTOParams[1].Name := aNewStatus;
    MTOParams[1].AsString := aHeaders;
    MTOParams.FastAdd(aNotes);
    MTOParams.FastAdd(pvMTmpList.Text);
    MTOParams.FastAdd(pvWTmpList.Text);
    RetParams := AstaClientSocket1.SendGetCodedParamList(2100, MTOParams);
    lnstr := RetParams[0].AsString;
    ShowMessage('MTO successfully posted at server time: ' + lnstr);
  finally
    Screen.Cursor := crDefault;
    MTOParams.Free;
    RetParams.Free;
    pvMTmpList.Clear;
    pvWTmpList.Clear;
  end;
end;

And here is how a server could receive them and call the server's datamodule to write:

procedure TIsoFabForm.AstaServerSocket1CodedParamList(Sender: TObject;
  ClientSocket: TCustomWinSocket; MsgID: Integer; Params: TAstaParamList);
var
  i: integer;
  TmpStr, TmpStr2, ErrMsg, aUserID: string;
  MList, WList: TStringList;
begin
  case MsgID of
    {...}
    2100: {Post MTO}
      begin
        aUserID := Params[0].Name;
        i := Params[0].AsInteger; {MTONo}
        MList := TStringList.Create;
        WList := TStringList.Create;
        try
          MList.Text := Params[3].Text;
          WList.Text := Params[4].Text;
          DMServer.PostMTO(i, aUserID, Params[1].Name, Params[1].AsString,
            Params[2].AsString,
            MList, WList, True);
          Params.Clear;
          Params.FastAdd(Now);
          AstaServerSocket1.SendCodedParamList(ClientSocket, MsgID, Params);
        finally
          MList.Free;
          WList.Free;
        end;
      end;
    {....}

procedure TDMServer.PostMTO(const cMTONO: integer; aUserID, aNewStatus, aHeader,
  aNotes: string; var MList, WList: TStringList; BalanceStock: boolean);

  procedure PostItHere(const aTbl: TDBISAMTable; var aList: TStringList);
  var
    i, deltarecs: integer;
    lnstr: string;
  begin
    aTbl.IndexName := 'MTONO';
    aTbl.SetRange([cMTONo], [cMTONO]);
    DeltaRecs := aList.Count - aTbl.RecordCount;
    if DeltaRecs > 0 then
    begin
      for i := 1 to DeltaRecs do
      begin
        aTbl.Append;
        aTbl.FieldByName('MTONO').AsInteger := cMTONO;
        aTbl.Post;
      end;
    end;
    if DeltaRecs < 0 then
    begin
      aTbl.First;
      for i := 1 to -DeltaRecs do
        aTbl.delete;
    end;
    aTbl.First;
    for i := 0 to aList.Count - 1 do
    begin
      lnstr := aList[i];
      aTbl.Edit;
      aTbl.FieldByName('ItemNo').AsString := GetLeftWord(lnstr, #9);
      aTbl.FieldByName('QCode').AsString := GetLeftWord(lnstr, #9);
      aTbl.FieldByName('QtyNeed').AsString := GetLeftWord(lnstr, #9);
      aTbl.FieldByName('QtyRel').AsString := GetLeftWord(lnstr, #9);
      aTbl.FieldByName('QtyScraps').AsString := GetLeftWord(lnstr, #9);
      aTbl.FieldByName('UnitCostRel').AsString := GetLeftWord(lnstr, #9);
      aTbl.FieldByName('TagNo').AsString := GetLeftWord(lnstr, #9);
      aTbl.Post;
      aTbl.Next;
    end;
    aList.Clear;
  end;

begin
  if cMTONO <= 0 then
    exit;
  if not DB1.InTransaction then
    DB1.StartTransaction;
  MTOMain.IndexName := 'MTONO';
  if not MTOMain.FindKey([cMTONO]) then
  begin
    MTOMain.Append;
    MTOMain.FieldByName('MTONo').AsInteger := cMTONO;
  end
  else
  begin
    MTOMain.Edit;
  end;
  GetLeftWord(aHeader, #9); {discard first column which is MTONO}
  MTOMain.FieldByName('Status').AsString := GetLeftWord(aHeader, #9);
  MTOMain.FieldByName('Project').AsString := GetLeftWord(aHeader, #9);
  MTOMain.FieldByName('Customer').AsString := GetLeftWord(aHeader, #9);
  MTOMain.FieldByName('ToolOrLateral').AsString := GetLeftWord(aHeader, #9);
  MTOMain.FieldByName('JobNo').AsString := GetLeftWord(aHeader, #9);
  MTOMain.FieldByName('SubJob').AsString := GetLeftWord(aHeader, #9);
  MTOMain.FieldByName('DwgNo').AsString := GetLeftWord(aHeader, #9);
  MTOMain.FieldByName('SpoolNo').AsString := GetLeftWord(aHeader, #9);
  MTOMain.FieldByName('System').AsString := GetLeftWord(aHeader, #9);
  MTOMain.FieldByName('MaterialCode').AsString := GetLeftWord(aHeader, #9);
  MTOMain.FieldByName('LaborCode').AsString := GetLeftWord(aHeader, #9);
  MTOMain.FieldByName('DateNeeded').AsString := GetLeftWord(aHeader, #9);
  MTOMain.FieldByName('DateBuilt').AsString := GetLeftWord(aHeader, #9);
  MTOMain.FieldByName('DateShipped').AsString := GetLeftWord(aHeader, #9);
  MTOMain.FieldByName('DateDrawn').AsString := GetLeftWord(aHeader, #9);
  MTOMain.FieldByName('DateRevised').AsString := GetLeftWord(aHeader, #9);
  MTOMain.FieldByName('DateRecvd').AsString := GetLeftWord(aHeader, #9);
  MTOMain.FieldByName('SubmittedBy').AsString := aUserID;
  MTOMain.FieldByName('Notes').AsString := aNotes;
  MTOMain.Post;
  if (BalanceStock) and (MTOMain.FieldByName('Status').AsString <> 'DS') then
  begin
    RecomputeMTO(cMTONO, False); {subtract existing MTO Items}
  end;
  PostItHere(MTOItems, MList);
  PostItHere(WeldItems, WList);
  if BalanceStock then
  begin
    RecomputeMTO(cMTONO, True); {add to stock New MTO Items}
  end;
  if DB1.InTransaction then
    DB1.Commit;
end;

And of course, here is my versatile GetLeftWord function that can successively parse a given string into individual datafields or values:

function GetLeftWord(var ASentence: string; ADelimiter: char): string;
var
  i: integer;
begin
  Result := '';
  i := Pos(ADelimiter, ASentence);
  if i = 0 then
  begin
    Result := Trim(ASentence);
    ASentence := '';
    exit;
  end;
  if i = 1 then
    Result := ''
  else
    Result := trim(Copy(ASentence, 1, i - 1));
  Delete(ASentence, 1, i);
end;

I also made intensive use of routines like this to update any table, just pass it a series of strings:

procedure TDMServer.UpdateTable(var aTbl: TDBISAMTable; anIndexField, aFieldStr:
  string);
var
  anIndexValue, fldname, fldvalue: string;
begin
  aTbl.IndexName := anIndexField;
  anIndexValue := GetLeftWord(aFieldStr, #9);
  if aTbl.FindKey([anIndexValue]) then
  begin
    aTbl.Edit;
  end
  else
  begin
    aTbl.Append;
    aTbl.FieldByName(anIndexField).AsString := anIndexValue;
  end;
  while aFieldStr < > '' do
  begin
    fldname := GetLeftWord(aFieldStr, #9);
    fldvalue := GetLeftWord(aFieldStr, #9);
    if fldname = 'CDT' then
      continue;
    try
      aTbl.FieldByName(fldname).AsString := fldvalue;
    except
    end;
  end;
  aTbl.FieldByName('CDT').AsDateTime := Now;
  aTbl.Post;
  aTbl.FlushBuffers;
end;

That's a very interesting approach and I can see how it could speed things up yet still give you the data integrity you look for with a client server approach. I can also see where it could simplify some of the typical c/s user interface issues as well. For instance you could let a client open an entire table, then view and scroll through the data in a grid component without having to send all of that data through the pipeline. Obviously you wouldn't want your remote clients to do that, but such screens could easily be limited to only the people connected via LAN. It would also allow multiple large queries to run simultaneously (such as for reports) without slowing up everything else. If the main objective is server side control of data (such as enforcement of business rules) and elimination of corruption then this technique should work very well.