Trouble Getting my CSV file to properly read with my SQL DB
lazarus / 16-Databases

----------------------------------------------------------------------
Jade · 2017-09-06 16:22

So I have gotten my CSV to read into lazarus. I have selected the
fields i want to show that all works fine. Now I am working on
comparing my CSV file to my SQL DB. To determine what files need to be
deleted, which ones are ok, and which ones need to be written up to
the DB from the CSV file. So my code works with the delete and the ok.
For some reason my write code keeps telling me all 18 lines need to be
written onto the SQL DB. I know thats not true cause I hand put in 2
of the information off the CSV file so i could test everything. 
This is the code i have right now

  For CSVCntr := 0 To Length(CSVList) - 1 Do
  Begin
    SQLCntr := 0;
    While (SQLCntr < Length(SQLList))
       and Not (MyWrite (CSVList[CSVCntr],SQLList[SQLCntr]))
        do SQLCntr := SQLCntr + 1;
     If SQLCntr <= Length(SQLList) Then
    Begin
      CSVList[CSVCntr].RecStatus:= rsWrite;
      WriteCnt := WriteCnt + 1;

    end;

  end; 

This is what i have MyWrite = too

Function MyWrite (InCSVRec, InSQLRec : TCustRec) : Boolean;
Begin
   MyWrite := (InSQLRec.LicID = InCSVRec.LicID)
          and (InSQLRec.CustID <> InCSVRec.CustID)
          and (InSQLRec.Password <> InCSVRec.Password);

----------------------------------------------------------------------
Almir.Bispo · 2017-09-06 19:40

I did it for you:
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i //i'll use
tstringlist class as exempleprocedure TForm1.Button1Click(Sender:
TObject);var csvlist,sqllist,csvpersist,sqlpersist:Tstringlist; 
 i:integer;begin  // persistence on ram 
csvpersist:=Tstringlist.create;  sqlpersist:=Tstringlist.create; 
//editable  csvlist:=Tstringlist.create; 
sqllist:=Tstringlist.create;  //set items of csv  csvlist.add('A'); 
csvlist.add('B');  csvlist.add('C');   //set sql list 
sqllist.add('1');//it is not in csv  sqllist.add('B');//it is on csv 
sqllist.add('D');//it is not on csv  sqllist.add('M');//it is not on
csv  sqllist.add('V');//it is not on csv  
sqlpersist.text:=sqllist.text;//  verify csv list  for i:= 0 to
csvlist.count-1 do  begin   //if not exists on sqllist ,then add it 
 if sqllist.IndexOf(csvlist.strings[i])=-1 then   begin   
 //sqlpersist.Add(csvlist.strings[i]+' inserted');   
 sqllist.add(csvlist.strings[i]+' inserted on sql list');   end; 
 //if exists,then,delete from sqllist   if
sqllist.IndexOf(csvlist.strings[i])<>-1 then   begin     //only for
show    sqllist.strings[sqlpersist.IndexOf(csvlist.strings[i])]:='['+c
svlist.strings[i]+' ok]';   end;   end;   //verify csv list to sql
compare   for i:= 0 to sqlpersist.count-1 do   begin    //if it does
exists on csv list,then deletes from sql    if
csvlist.IndexOf(sqlpersist.strings[i])=-1 then    begin     //to real
delete//     sqllist.Delete(i);     //only to show   
 sqllist.Strings[i]:=sqllist.Strings[i] +' Was deleted from sql
list';    end;   end;memo1.text:=sqllist.text;  csvpersist.free; 
csvlist.free;  sqllist.free;  sqlpersist.free;  end;                 
                  http://adltecnologia.blogspot.com.br

----------------------------------------------------------------------
Jade · 2017-09-07 13:24

Var
  i, j     : Integer;
  CSVCntr  : Integer;
  SQLCntr  : Integer;

  TempStr    : String;

begin
  OKCnt   := 0;
  DelCnt  := 0;
  WriteCnt := 0;
  memo1.clear;
  SQLList := Nil;
  CSVList := Nil;

//  SDFDataSet1.FirstLineAsSchema:= True;
//  SDFDataSet1.Active := True;
  SDFDataSet1.First;

  While Not SDFDataSet1.EOF do
  Begin
    i := Length(CSVList);
    SetLength (CSVList,i+1);
    CustRecClear (CSVList);
    CSVList.CustID := SDFDataSet1['CustomerID'];
    CSVList.LicID := SDFDataSet1['LicenseID'];
    CSVList.Password := 'RLK';
    SDFDataSet1.Next;
  end;

  For i := 0 To Length(CSVList) - 1 Do
  Begin
    TempStr  := CSVList.LicID + '  '
              + CSVList.CustID + '  '
              + CSVList.Password;
    Memo1.Lines.Add(TempStr);
  end;

  SQLQuery1.First;

  While Not SQLQuery1.EOF do
  Begin
    i := Length(SQLList);
    SetLength (SQLList, i+1);
    CustRecClear (SQLList);
    SQLList.CustID := SQLQuery1['AU_CustomerID'];
    SQLList.LicID := SQLQuery1['AU_LicenseNum'];
    SQLList.Password:= SQLQuery1['AU_Password'];
    SQLQuery1.Next;
  end;

  For i := 0 To Length(SQLList) - 1 Do
  Begin
    TempStr :=SQLList.LicID + '  '
             + SQLList.CustID + '  '
             + SQLList.Password;
    Memo1.Lines.Add(TempStr);
  end;

// Now detect SQL Items that must be deleted

   For SQLCntr := 0 To Length(SQLList) - 1 Do
   Begin
     CSVCntr := 0;
     While (CSVCntr < Length(CSVList))
         and (SQLList[SQLCntr].LicID <> CSVList[CSVCntr].LicID)
         Do CSVCntr := CSVCntr + 1;
     If CSVCntr >= Length(CSVList) Then
     Begin
       SQLList[SQLCntr].RecStatus:= rsDelete;
       DelCnt := delCnt + 1;

       //SQLQuery1.SQL.Clear;
       //TempStr := 'Select * From ActiveUsers Where AU_LicenseNum = '
       //  + SQLList[SQLCntr].LicID;
       //SQLQuery1.SQL.Add(TempStr );
       //SQLQuery1.ExecSQL;
       //SQLQuery1.First;
       //While Not SQLQuery1.EOF do
       //Begin
       //  i := SQLQuery1.RecordCount;
       //  SQLQuery1.Delete;
       //End;

    end;

  end;

// Now detect SQL Items that are OK as is.

  For CSVCntr := 0 To Length(CSVList) - 1 Do
  Begin
    SQLCntr := 0;
    While (SQLCntr < Length(SQLList))
       and not (MyOk (CSVList[CSVCntr],SQLList[SQLCntr]))
        do SQLCntr := SQLCntr + 1;
     If SQLCntr < Length(SQLList) Then
    Begin
      CSVList[CSVCntr].RecStatus:= rsOK;
      OkCnt := okCnt + 1;

    end;

  end;

  // Now detect CSV items that need to be written to the DB  


This is what i have set for ok and the delete function when i try to
add yours it throws me errors