[SOLVED] SDFDataset

[scons]

Hi all,

Following question is just out of curiosity. Regarding this topic I have been doing some test with SQLite.

I have come up with following SQL commands:

Code: Pascal  [Select][+][-]

1. procedure TForm1.BtnSYNCClick(Sender: TObject);
2. var
3.   s: string;
4.   h: integer;
5. begin
6.   s := DBGrid1.SelectedField.AsString;
7.   h := DBGrid1.Columns.Items[0].Field.AsInteger;
8.   SQLQuery1.Close;
9.   SQLQuery1.SQL.Clear;
10.   SQLQuery1.SQL.Add('update PROJECT set TRANSPORT = "' +s+ '" where HPOS = "' +IntToStr(h)+ '"');
11.   SQLQuery1.ExecSQL;
12.   SQLTransAction1.Commit;
13.   SQLQuery1.Close;
14. end;
15.  

Which basically sets all the fields in a column the same as the selected field if the value of Column[0] matches (selection from a DBGrid Picklist).

Second one is:

Code: SQL  [Select][+][-]

1. SELECT TRANSPORT, SUM(WEIGHT) AS TOTALWEIGHT
2. FROM PROJECT
3. GROUP BY TRANSPORT;
4.  

then

Code: SQL  [Select][+][-]

1. UPDATE TRANSPORT
2. SET
3. TOTALWEIGHT = (SELECT TOTALWEIGHT FROM SUMTRANSWEIGHT WHERE TRANSPORT = TRUCK)

An SQL query to create a view and update a table which is used (also in a DBGrid) to show weight totals

Can you do these things also within an SDFdataset ?
Just for speed comparison it would be nice to see differences, if there are any ... (sqlite = database, sdfdataset = memory ?).

[scons]

Okay, had some time left to try some more this is what I have so far:

I have 2 sdfdatasets filled from different csv files with different content.
However 1 dataset contains the same values as the other on, althought they are less in quantity.

Sdfdataset1 contains:

Code: Text  [Select][+][-]

1. PNR;MTL;QTY; ... ;SLR;SRR; ... ;LGT; ... ;RLR;RRR, ...
2. 75001;Alu;2; ... ;0.0;0.0; ... ;0; ... ;0.0;0.3; ...
3. 75002;Alu;3; ... ;0.1;0.0; ... ;501; ... ;0.0;0.3; ...
4. 75008;Alu;1; ... ;0.0;0.0; ... ;0; ... ;0.0;0.0; ...
5. 40002;Alu;1; ... ;0.0;0.2; ... ;0; ... ;0.0;0.0; ...
6. 40001;Alu;3; ... ;0.0;0.0; ... ;213; ... ;0.0;0.0; ...
7. 40003;Alu;15; ... ;0.0;1.0; ... ;111; ... ;0.0;1.0; ...
8. 45001;Alu;201; ... ;0.0;0.0; ... ;0; ... ;0.0;0.0; ...
9. ...
10.  

Sdfdataset2 contains only this:

Code: Text  [Select][+][-]

1. PNR1;LGT;ZGHL1;ZGHL2;ZGHR1;ZGHR2
2. 40001;0;0.0;0.2;0.0;0.0
3. 40002;0;0.0;0.0;0.0;0.0
4. 40003;0;0.0;1.0;0.0;1.0
5. 75008;0;0.0;0.0;0.0;0.0
6.  

So I want to update sdfdataset 2 with some values of sdfdataset1

PNR and PNR1 contain the same numbers (data) and will be used as a match.

so if PNR1 matches PNR, LGT, ZGHL1, ZGHL2, ZGHR1, ZGHR2 should be updated with the values from LGT, SLR, SRR, RLR, RRR

This is what I have so far but it stays on the first value of sdfdataset1 (it gets stuck in a loop)

Code: Pascal  [Select][+][-]

1.       F := SdfDataSet1.FieldByName('PNR');
2.       G := SdfDataSet2.FieldByName('PNR1');
3.  
4.       SdfDataSet2.DisableControls;
5.       try
6.         SdfDataSet2.First;
7.         while not SdfDataSet2.EOF do
8.          SdfDataSet1.First;
9.          while not SdfDataSet1.EOF do
10.               if VarSameValue(F.Value, G.Value) then
11.               begin
12.                 //writeln('succes');
13.                 SdfDataSet2.Fields[x].AsInteger := SdfDataSet1.Fields[y].AsInteger;
14.                 SdfDataSet2.Fields[v].AsInteger := SdfDataSet1.Fields[w].AsInteger;
15.                 ...
16.                 Exit;
17.            end else begin
18.            writeln('no succes');
19.            SdfDataSet1.Next;
20.            end;
21.            SdfDataSet2.Next;
22.        finally
23.          SdfDataSet2.EnableControls;
24.        end;
25.  

So what am I doing wrong here ?

[scons]

After some hours of testing, this seems to work:

Code: Pascal  [Select][+][-]

1.    F := SdfDataSet1.FieldByName('PNR');
2.    SdfDataSet1.DisableControls;
3.    SdfDataSet2.DisableControls;
4.  
5.     SdfDataSet2.First;
6.     while not (SdfDataSet2.EOF) do
7.     begin
8.       s1 := SdfDataSet2.FieldByName('PNR1').AsString;
9.       SdfDataSet1.First;
10.       while not (SdfDataSet1.EOF) do
11.       begin
12.         s2 := SdfDataSet1.FieldByName('PNR').AsString;
13.         s3 := SdfDataSet1.FieldByName('SL').AsString;
14.         s4 := SdfDataSet1.FieldByName('SR').AsString;
15.         s5 := SdfDataSet1.FieldByName('FL').AsString;
16.         s6 := SdfDataSet1.FieldByName('FR').AsString;
17.         if VarSameValue(F.Value, s1) then
18.         begin
19.           writeln('succes : ' + s1 + ' - ' + s2);
20.           SdfDataSet2.Edit;
21.           SdfDataSet2.FieldByName('ZAAGHL1').AsString := s3;
22.           SdfDataSet2.FieldByName('ZAAGHL2').AsString := s4;
23.           SdfDataSet2.FieldByName('ZAAGHR1').AsString := s5;
24.           SdfDataSet2.FieldByName('ZAAGHR2').AsString := s6;
25.           SdfDataSet2.Post;
26.         end
27.         else
28.         begin
29.           writeln('no succes : ' + s1 + ' - ' + s2);
30.         end;
31.         SdfDataSet1.Next;
32.       end;
33.       SdfDataSet2.Next;
34.     end;
35.     SdfDataSet1.EnableControls;
36.     SdfDataSet2.EnableControls;
37.  

Now I have to lay down for a while, I have a headache ...