Author: Tomas Rutkauskas
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.
1
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
write:
32
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 {....}
63
64 procedure TDMServer.PostMTO(const cMTONO: integer; aUserID, aNewStatus, aHeader,
65 aNotes: string; var MList, WList: TStringList; BalanceStock: boolean);
66
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;
107
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:
157
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.
|
