* * *

Author Topic: [SOLVED] SDFDataset  (Read 585 times)

scons

  • Full Member
  • ***
  • Posts: 101
[SOLVED] SDFDataset
« on: July 10, 2017, 11:04:57 pm »
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 ?).
« Last Edit: August 31, 2017, 02:53:14 pm by scons »
Windows 10-64bit Lazarus 1.6.4 FPC 3.0.2 + 1.8 RC5

scons

  • Full Member
  • ***
  • Posts: 101
Re: SDFDataset
« Reply #1 on: August 31, 2017, 01:47:49 am »
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 ?
Windows 10-64bit Lazarus 1.6.4 FPC 3.0.2 + 1.8 RC5

scons

  • Full Member
  • ***
  • Posts: 101
Re: SDFDataset
« Reply #2 on: August 31, 2017, 02:52:54 pm »
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 ...  ::)
Windows 10-64bit Lazarus 1.6.4 FPC 3.0.2 + 1.8 RC5

 

Recent

Get Lazarus at SourceForge.net. Fast, secure and Free Open Source software downloads Open Hub project report for Lazarus