Articles   Members Online:
-Article/Tip Search
-News Group Search over 21 Million news group articles.
Member Area
-Account Center
-Top 10 NEW!!
-Submit Article/Tip
-Forums Upgraded!!
-My Articles
-Edit Information
-Become a Member
-Why sign up!
-Chat Online!
-Indexes NEW!!
-Build your resume
-Find a job
-Post a job
-Resume Search
-Link to us
Visit Embarcadero
Embarcadero Community
how to implement alternative C/S-like database solutions without having a C/S en Turn on/off line numbers in source code. Switch to Orginial background IDE or DSP color Comment or reply to this aritlce/tip for discussion. Bookmark this article to my favorite article(s). Print this article
An approach to implement alternative C/S-like database solut 28-May-03
Database Others
Delphi 3.x
User Rating
No Votes
# Votes
DSP, Administrator
Reference URL:
			Author: Tomas Rutkauskas

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


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.
2   procedure TDML.PostMTO(const aMTONO: integer; aNewStatus, aHeaders, aNotes: string);
3   var
4     lnstr: string;
5     MTOParams, RetParams: TAstaParamList;
6   begin
7     Screen.Cursor := crHourGlass;
8     RetParams := TAstaParamList.Create;
9     MTOParams := TAstaParamList.Create;
10    try
11      LoadTmpList(pvMTmpList, pvWTmpList);
12      MTOParams.Add;
13      MTOParams[0].Name := UserLoginID;
14      MTOParams[0].AsInteger := aMTONO;
15      MTOParams.Add;
16      MTOParams[1].Name := aNewStatus;
17      MTOParams[1].AsString := aHeaders;
18      MTOParams.FastAdd(aNotes);
19      MTOParams.FastAdd(pvMTmpList.Text);
20      MTOParams.FastAdd(pvWTmpList.Text);
21      RetParams := AstaClientSocket1.SendGetCodedParamList(2100, MTOParams);
22      lnstr := RetParams[0].AsString;
23      ShowMessage('MTO successfully posted at server time: ' + lnstr);
24    finally
25      Screen.Cursor := crDefault;
26      MTOParams.Free;
27      RetParams.Free;
28      pvMTmpList.Clear;
29      pvWTmpList.Clear;
30    end;
31  end;

And here is how a server could receive them and call the server's datamodule to 
33  procedure TIsoFabForm.AstaServerSocket1CodedParamList(Sender: TObject;
34    ClientSocket: TCustomWinSocket; MsgID: Integer; Params: TAstaParamList);
35  var
36    i: integer;
37    TmpStr, TmpStr2, ErrMsg, aUserID: string;
38    MList, WList: TStringList;
39  begin
40    case MsgID of
41      {...}
42      2100: {Post MTO}
43        begin
44          aUserID := Params[0].Name;
45          i := Params[0].AsInteger; {MTONo}
46          MList := TStringList.Create;
47          WList := TStringList.Create;
48          try
49            MList.Text := Params[3].Text;
50            WList.Text := Params[4].Text;
51            DMServer.PostMTO(i, aUserID, Params[1].Name, Params[1].AsString,
52              Params[2].AsString,
53              MList, WList, True);
54            Params.Clear;
55            Params.FastAdd(Now);
56            AstaServerSocket1.SendCodedParamList(ClientSocket, MsgID, Params);
57          finally
58            MList.Free;
59            WList.Free;
60          end;
61        end;
62      {....}
64  procedure TDMServer.PostMTO(const cMTONO: integer; aUserID, aNewStatus, aHeader,
65    aNotes: string; var MList, WList: TStringList; BalanceStock: boolean);
67    procedure PostItHere(const aTbl: TDBISAMTable; var aList: TStringList);
68    var
69      i, deltarecs: integer;
70      lnstr: string;
71    begin
72      aTbl.IndexName := 'MTONO';
73      aTbl.SetRange([cMTONo], [cMTONO]);
74      DeltaRecs := aList.Count - aTbl.RecordCount;
75      if DeltaRecs > 0 then
76      begin
77        for i := 1 to DeltaRecs do
78        begin
79          aTbl.Append;
80          aTbl.FieldByName('MTONO').AsInteger := cMTONO;
81          aTbl.Post;
82        end;
83      end;
84      if DeltaRecs < 0 then
85      begin
86        aTbl.First;
87        for i := 1 to -DeltaRecs do
88          aTbl.delete;
89      end;
90      aTbl.First;
91      for i := 0 to aList.Count - 1 do
92      begin
93        lnstr := aList[i];
94        aTbl.Edit;
95        aTbl.FieldByName('ItemNo').AsString := GetLeftWord(lnstr, #9);
96        aTbl.FieldByName('QCode').AsString := GetLeftWord(lnstr, #9);
97        aTbl.FieldByName('QtyNeed').AsString := GetLeftWord(lnstr, #9);
98        aTbl.FieldByName('QtyRel').AsString := GetLeftWord(lnstr, #9);
99        aTbl.FieldByName('QtyScraps').AsString := GetLeftWord(lnstr, #9);
100       aTbl.FieldByName('UnitCostRel').AsString := GetLeftWord(lnstr, #9);
101       aTbl.FieldByName('TagNo').AsString := GetLeftWord(lnstr, #9);
102       aTbl.Post;
103       aTbl.Next;
104     end;
105     aList.Clear;
106   end;
108 begin
109   if cMTONO <= 0 then
110     exit;
111   if not DB1.InTransaction then
112     DB1.StartTransaction;
113   MTOMain.IndexName := 'MTONO';
114   if not MTOMain.FindKey([cMTONO]) then
115   begin
116     MTOMain.Append;
117     MTOMain.FieldByName('MTONo').AsInteger := cMTONO;
118   end
119   else
120   begin
121     MTOMain.Edit;
122   end;
123   GetLeftWord(aHeader, #9); {discard first column which is MTONO}
124   MTOMain.FieldByName('Status').AsString := GetLeftWord(aHeader, #9);
125   MTOMain.FieldByName('Project').AsString := GetLeftWord(aHeader, #9);
126   MTOMain.FieldByName('Customer').AsString := GetLeftWord(aHeader, #9);
127   MTOMain.FieldByName('ToolOrLateral').AsString := GetLeftWord(aHeader, #9);
128   MTOMain.FieldByName('JobNo').AsString := GetLeftWord(aHeader, #9);
129   MTOMain.FieldByName('SubJob').AsString := GetLeftWord(aHeader, #9);
130   MTOMain.FieldByName('DwgNo').AsString := GetLeftWord(aHeader, #9);
131   MTOMain.FieldByName('SpoolNo').AsString := GetLeftWord(aHeader, #9);
132   MTOMain.FieldByName('System').AsString := GetLeftWord(aHeader, #9);
133   MTOMain.FieldByName('MaterialCode').AsString := GetLeftWord(aHeader, #9);
134   MTOMain.FieldByName('LaborCode').AsString := GetLeftWord(aHeader, #9);
135   MTOMain.FieldByName('DateNeeded').AsString := GetLeftWord(aHeader, #9);
136   MTOMain.FieldByName('DateBuilt').AsString := GetLeftWord(aHeader, #9);
137   MTOMain.FieldByName('DateShipped').AsString := GetLeftWord(aHeader, #9);
138   MTOMain.FieldByName('DateDrawn').AsString := GetLeftWord(aHeader, #9);
139   MTOMain.FieldByName('DateRevised').AsString := GetLeftWord(aHeader, #9);
140   MTOMain.FieldByName('DateRecvd').AsString := GetLeftWord(aHeader, #9);
141   MTOMain.FieldByName('SubmittedBy').AsString := aUserID;
142   MTOMain.FieldByName('Notes').AsString := aNotes;
143   MTOMain.Post;
144   if (BalanceStock) and (MTOMain.FieldByName('Status').AsString <> 'DS') then
145   begin
146     RecomputeMTO(cMTONO, False); {subtract existing MTO Items}
147   end;
148   PostItHere(MTOItems, MList);
149   PostItHere(WeldItems, WList);
150   if BalanceStock then
151   begin
152     RecomputeMTO(cMTONO, True); {add to stock New MTO Items}
153   end;
154   if DB1.InTransaction then
155     DB1.Commit;
156 end;

And of course, here is my versatile GetLeftWord function that can successively 
parse a given string into individual datafields or values:
158 function GetLeftWord(var ASentence: string; ADelimiter: char): string;
159 var
160   i: integer;
161 begin
162   Result := '';
163   i := Pos(ADelimiter, ASentence);
164   if i = 0 then
165   begin
166     Result := Trim(ASentence);
167     ASentence := '';
168     exit;
169   end;
170   if i = 1 then
171     Result := ''
172   else
173     Result := trim(Copy(ASentence, 1, i - 1));
174   Delete(ASentence, 1, i);
175 end;

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

176 procedure TDMServer.UpdateTable(var aTbl: TDBISAMTable; anIndexField, 
177 aFieldStr:
178   string);
179 var
180   anIndexValue, fldname, fldvalue: string;
181 begin
182   aTbl.IndexName := anIndexField;
183   anIndexValue := GetLeftWord(aFieldStr, #9);
184   if aTbl.FindKey([anIndexValue]) then
185   begin
186     aTbl.Edit;
187   end
188   else
189   begin
190     aTbl.Append;
191     aTbl.FieldByName(anIndexField).AsString := anIndexValue;
192   end;
193   while aFieldStr < > '' do
194   begin
195     fldname := GetLeftWord(aFieldStr, #9);
196     fldvalue := GetLeftWord(aFieldStr, #9);
197     if fldname = 'CDT' then
198       continue;
199     try
200       aTbl.FieldByName(fldname).AsString := fldvalue;
201     except
202     end;
203   end;
204   aTbl.FieldByName('CDT').AsDateTime := Now;
205   aTbl.Post;
206   aTbl.FlushBuffers;
207 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.

Vote: How useful do you find this Article/Tip?
Bad Excellent
1 2 3 4 5 6 7 8 9 10


Share this page
Download from Google

Copyright © Mendozi Enterprises LLC