Recent

Author Topic: Retrieve Multiple Records as TStringList Array.  (Read 1075 times)

nugax

  • Full Member
  • ***
  • Posts: 225
Retrieve Multiple Records as TStringList Array.
« on: February 08, 2022, 05:48:45 pm »
I created a type:


Code: Pascal  [Select][+][-]
  1. type
  2.   TStringListArray = array of TStringList;  
  3.  
  4.  

I am trying to retrieve a bunch of text(string) fields from a sqlite3 database. I have the return code as below. The issue is, its failing (cant see errors) on the
[
code=pascal]TReturnList[iCnt].Add(Query.FieldByName('mailid').AsString);   
[/code]

I cannot figure out why.

Can someone assist - full function below:

Code: Pascal  [Select][+][-]
  1.  //Get All Mail Array
  2.   function GetMailFromLocalMailConf(): TStringListArray;
  3.   var
  4.     Connect: TSQLite3Connection;
  5.     Trans: TSQLTransaction;
  6.     Query: TSQLQuery;
  7.     db_name: string[100];
  8.     sqlhandle: string[255];
  9.     TReturnList: TStringListArray;
  10.     iCnt: integer;
  11.  
  12.   begin
  13.     //Setup SQL
  14.     db_name := programinfo.cbbsInfo.sLocalMailDB;
  15.     sqlhandle := 'SELECT mailid, to_id, from_id, subject, date, read, message FROM localmail WHERE to_id = ' + userhandler.recCurrentUser.UserID.ToString;
  16.     write('SQL' + sqlhandle);
  17.     //Code to update password from handle
  18.     Connect := TSQLite3Connection.Create(nil);
  19.     Query := TSQLQuery.Create(nil);
  20.     Trans := TSQLTransaction.Create(Connect);
  21.     Connect.Transaction := Trans;
  22.     Connect.DatabaseName := db_name;
  23.     Trans.StartTransaction;  // opens Connect, EInOutError if SQLite not installed
  24.     Query.SQL.Text := sqlhandle;
  25.     write('here1');
  26.     Query.Database := Connect;  //Connection for SQL
  27.   write('here23');
  28.     Query.Open;
  29.     //boolFoundUser := (Query.RecordCount > 0) and (Query.FieldByName('password').AsString <> '');
  30.  
  31.     TReturnList.
  32.  
  33.  
  34.     write('here');
  35.  
  36.     iCnt := 1;
  37.  
  38.  
  39.  
  40.     repeat
  41.       TReturnList[iCnt].Add(Query.FieldByName('mailid').AsString);
  42.       TReturnList[iCnt].Add(Query.FieldByName('to_id').AsString);
  43.       TReturnList[iCnt].Add(Query.FieldByName('from_id').AsString);
  44.       TReturnList[iCnt].Add(Query.FieldByName('subject').AsString);
  45.       TReturnList[iCnt].Add(Query.FieldByName('date').AsString);
  46.       TReturnList[iCnt].Add(Query.FieldByName('read').AsString);
  47.       TReturnList[iCnt].Add(Query.FieldByName('message').AsString);
  48.       iCnt := iCnt + 1;
  49.       write('Adding');
  50.       query.Next;
  51.     until (iCnt = Query.RecordCount);
  52.  
  53.     {
  54.     while not Query.EOF do
  55.     begin
  56.  
  57.       TReturnList[iCnt].Add(Query.FieldByName('mailid').AsString);
  58.       TReturnList[iCnt].Add(Query.FieldByName('to_id').AsString);
  59.       TReturnList[iCnt].Add(Query.FieldByName('from_id').AsString);
  60.       TReturnList[iCnt].Add(Query.FieldByName('subject').AsString);
  61.       TReturnList[iCnt].Add(Query.FieldByName('date').AsString);
  62.       TReturnList[iCnt].Add(Query.FieldByName('read').AsString);
  63.       TReturnList[iCnt].Add(Query.FieldByName('message').AsString);
  64.  
  65.       Write('local + ' + TReturnList[iCnt].ToString);
  66.  
  67.       iCnt := iCnt + 1;
  68.     end;
  69.     }
  70.       Query.Close;
  71.     Query.Free;
  72.     Connect.Free;
  73.     exit(TReturnList);
  74.  
  75.   end;
  76.      
  77.  
  78.  
  79.  
-Nugax

nugax

  • Full Member
  • ***
  • Posts: 225
Re: Retrieve Multiple Records as TStringList Array.
« Reply #1 on: February 08, 2022, 05:50:22 pm »
Excuse the test code, and unfinished area. I was testing..
-Nugax

bytebites

  • Hero Member
  • *****
  • Posts: 504
Re: Retrieve Multiple Records as TStringList Array.
« Reply #2 on: February 08, 2022, 06:18:50 pm »
Code: Pascal  [Select][+][-]
  1. TReturnList[iCnt]:=TStringList.create

nugax

  • Full Member
  • ***
  • Posts: 225
Re: Retrieve Multiple Records as TStringList Array.
« Reply #3 on: February 08, 2022, 06:29:49 pm »
So I changed it to a array of records and it does the exact same thing. Where would you initialize (create) the record?

Code: Pascal  [Select][+][-]
  1. type
  2.   LocalMailRecord = record
  3.     mailid  : string;
  4.     to_id   : string;
  5.     from_id : string;
  6.     subject : string;
  7.     date    : string;
  8.     read    : string;
  9.     message : string;
  10.  
  11.   end;
  12.  
  13. type
  14.   LocalMailArray = array of LocalMailRecord;  




Code: Pascal  [Select][+][-]
  1. //Get All Mail Array
  2. function GetMailFromLocalMailConf(): LocalMailArray;
  3. var
  4.   Connect: TSQLite3Connection;
  5.   Trans: TSQLTransaction;
  6.   Query: TSQLQuery;
  7.   db_name: string[100];
  8.   sqlhandle: string[255];
  9.   MailRecordArray : LocalMailArray;
  10.   iCnt: integer;
  11.  
  12.  
  13. begin
  14.   //Setup SQL
  15.   db_name := programinfo.cbbsInfo.sLocalMailDB;
  16.   sqlhandle := 'SELECT mailid, to_id, from_id, subject, date, read, message FROM localmail WHERE to_id = ' +
  17.     userhandler.recCurrentUser.UserID.ToString;
  18.   Write('SQL' + sqlhandle);
  19.   //Code to update password from handle
  20.   Connect := TSQLite3Connection.Create(nil);
  21.   Query := TSQLQuery.Create(nil);
  22.   Trans := TSQLTransaction.Create(Connect);
  23.   Connect.Transaction := Trans;
  24.   Connect.DatabaseName := db_name;
  25.   Trans.StartTransaction;  // opens Connect, EInOutError if SQLite not installed
  26.   Query.SQL.Text := sqlhandle;
  27.   Write('here1');
  28.   Query.Database := Connect;  //Connection for SQL
  29.   Write('here23');
  30.   Query.Open;
  31.   //boolFoundUser := (Query.RecordCount > 0) and (Query.FieldByName('password').AsString <> '');
  32.  
  33.  
  34.  
  35.  
  36.   Write('here');
  37.  
  38.   iCnt := 1;
  39.  
  40.  
  41.  
  42.   repeat
  43.    // test := Query.FieldByName('mailid').AsString;
  44.     // TReturnList[iCnt].create;
  45.     Write('at least here');
  46.     write('test data: ' +  Query.FieldByName('subject').AsString);
  47.     MailRecordArray[iCnt] := LocalMailArray.
  48.     MailRecordArray[iCnt].mailid := Query.FieldByName('mailid').AsString;
  49.    // TReturnList[iCnt] := TStringList.Create;
  50.   //  TReturnList[iCnt].Add(test);
  51.     Write('ahah!');
  52.     {
  53.     TReturnList[iCnt].Add(Query.FieldByName('to_id').AsString);
  54.     TReturnList[iCnt].Add(Query.FieldByName('from_id').AsString);
  55.     TReturnList[iCnt].Add(Query.FieldByName('subject').AsString);
  56.     TReturnList[iCnt].Add(Query.FieldByName('date').AsString);
  57.     TReturnList[iCnt].Add(Query.FieldByName('read').AsString);
  58.     TReturnList[iCnt].Add(Query.FieldByName('message').AsString);
  59.     }
  60.     iCnt := iCnt + 1;
  61.     Write('Adding');
  62.     query.Next;
  63.   until (iCnt = Query.RecordCount);
  64.  
  65.     {
  66.     while not Query.EOF do
  67.     begin
  68.  
  69.       TReturnList[iCnt].Add(Query.FieldByName('mailid').AsString);
  70.       TReturnList[iCnt].Add(Query.FieldByName('to_id').AsString);
  71.       TReturnList[iCnt].Add(Query.FieldByName('from_id').AsString);
  72.       TReturnList[iCnt].Add(Query.FieldByName('subject').AsString);
  73.       TReturnList[iCnt].Add(Query.FieldByName('date').AsString);
  74.       TReturnList[iCnt].Add(Query.FieldByName('read').AsString);
  75.       TReturnList[iCnt].Add(Query.FieldByName('message').AsString);
  76.  
  77.       Write('local + ' + TReturnList[iCnt].ToString);
  78.  
  79.       iCnt := iCnt + 1;
  80.     end;
  81.     }
  82.   Query.Close;
  83.   Query.Free;
  84.   Connect.Free;
  85.   exit(MailRecordArray); //Return Mail Array
  86.  
  87. end;            
-Nugax

bytebites

  • Hero Member
  • *****
  • Posts: 504
Re: Retrieve Multiple Records as TStringList Array.
« Reply #4 on: February 08, 2022, 06:35:26 pm »

Code: Pascal  [Select][+][-]
  1. SetLength(MailRecordArray,Query.RecordCount)
  2. repeat
  3. ...

 

TinyPortal © 2005-2018