Retrieve Multiple Records as TStringList Array.

[nugax]

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]

Excuse the test code, and unfinished area. I was testing..

[bytebites]

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

1. TReturnList[iCnt]:=TStringList.create

[nugax]

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;            

[bytebites]

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

1. SetLength(MailRecordArray,Query.RecordCount)
2. repeat
3. ...