[CLOSED] Are there any limit to field numbers in TBufDataSet? lazarus / 16-Databases ---------------------------------------------------------------------- egsuh · 2024-07-31 10:09 Probably around 2048 fields, or so. Even though I create more fields than that number (via FieldDefs.Add), creating dataset itself does not raise exception. But most columns of the dataset are filled with zeros and the dataset does not operate correctly afterwards. I found that in the case of SQLite3: - default SQLITE_MAX_COLUMN is 2000. - but its max column number is 32767. ---------------------------------------------------------------------- Richard Marriott · 2024-09-02 08:09 In practice this will not be a limitation as the fields are stored in a pointer list, the maximum items being an 32 bit integer (say around 2 billion) DIV 64 (the size of a pointer in a 64bit OS). Much more likely to be limited by RAM storage space for all the fields and data, or (if using TDBGrid) the useful number of columns for the application. ---------------------------------------------------------------------- paweld · 2024-09-02 08:29 As I wrote you before creating a table with such a large number of columns is a very very bad idea - I don't know why you insist on it. If you don't like the example I gave in this post: https://forum.lazar us.freepascal.org/index.php/topic,68096.msg525544.html#msg525544 then describe your case, maybe we can come up with something else. But the limit on columns exists in every database engine, and you are unlikely to get around it. ---------------------------------------------------------------------- alpine · 2024-09-02 08:44 I'm also curious what case requires such pushing of db columns number limits. ---------------------------------------------------------------------- egsuh · 2024-09-02 09:06 It's simply a temporary table for exporting data in format for statistical packages. Please refer to following thread if interested. https://forum.lazarus.freepascal.org/index.php/topic,68096.0.html ---------------------------------------------------------------------- dseligo · 2024-09-02 09:11 Quote from: egsuh on September 02, 2024, 11:06:07 amIt's simply a temporary table for exporting data in format for statistical packages. Please refer to following thread if interested. https://forum.lazarus.freepascal.org/index.php/topic,68096.0.html You said there: QuoteThe table will not be used within database operation. Why don't you use arrays then? Or if you insist on DB, you could use more tables in sync (i.e. same primary key). ---------------------------------------------------------------------- alpine · 2024-09-02 09:18 I second paweld in his advice to change the export method. The other is not very reasonable. ---------------------------------------------------------------------- Zvoni · 2024-09-02 09:19 Everything the others said. IMO, there are two ways around it WITHOUT recompiling sqlite3.dll, 1) is to use a "1:1-Extension"-table. meaning: You have Table1 with ID, Col1....Col1999, Table2 with ID, Col2000.....Col3999, Table3 with ID, Col4000...Col4999 (=5K Columns), with the caveat, that all ID's MUST be the same. You could even declare the ID's in Table2 and Table3 as ForeignKey to Table1 including ON DELETE CASCADE You'd need 3 Queries, since the SQLITE_MAX_COLUMN  also limits the max count of Columns RETURNED by a Select. That said: Create your BufDataset with your 5K Fields, run the first Query, and fill up the first 2K Columns of your BufDataset, run the second query, run the third query... etc. 2) Reorganize your Table to use JSON Meaning: You have Table1 with ID and a single Column "MyJSON", and that JSON-Column contains the 5K Key/Values you'd have in separate columns. When filling up your BufDataset, you'd have to "expand" the JSON- Values to its corresponding Field in the BufDataset. Any MaxLength-Limit for such a JSON-Column not withstanding ---------------------------------------------------------------------- egsuh · 2024-09-02 09:33 To explain more, table structure used in my DB applications are (let's say bufdataset1):         ID   fieldname   value         001       F1            1         001       F2            4         001       F3           12           ......................         001       F3500    998         002       F1            5         002       F2            3         002       F3           32           ......................         002       F3500    999 And I want to export it to csv with following formats (let's say bufdataset2).           ID    F1   F2   F3 ....   F3500           001   1     4    12 ...       998           002   5     3    32 ...       999            ................................... This is easy once the second table is made, I can use dbexport with following way. This is very convenient because I only have to order the first table based on the ID only.         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         pid := 0;     bufdataset1.first;    while not bufdataset1.eof do begin        tid := bufdataset1.fieldbyname('id').asinteger;          if tid <> pid then begin               bufdataset2.append;             bufdataset2.fieldbyname('id').asinteger := tid;             bufdataset2.post;             pid := tid;          end;               bufDataSet2.FieldByName(BufdataSet1.FieldByName('FieldName').AsString ).AsInteger                     := bufdataset1.Fieldbyname('value').AsIntger        bufDataSet1.Next;   end;      with TCSVExporter.Create(nil) do begin       Dataset:= BufDataSet2;       FileName:= SaveDialog1.FileName;       Execute;     Free;    end;  Currently I'm using other way. First, I sort the bufdataset1 both on ID and fieldname, and then directly write to text file (.csv). ---------------------------------------------------------------------- egsuh · 2024-09-02 09:36 The difficulty is the fieldnames are dynamically created. The difficulty comes from this dynamic character (not static ones). And sorting records based on the fieldnames (not alphabetical order. They have their own orders) is not a simple issue. And I still have to think other ways for cases when the whole number of IDs reaches several hundred thousands (300,000 etc). ---------------------------------------------------------------------- Zvoni · 2024-09-02 09:46 So, the issue actually is transposing from Rows to columns....*sigh* ---------------------------------------------------------------------- egsuh · 2024-09-02 10:08 QuoteSo, the issue actually is transposing from Rows to columns....*sigh* It's not that simple. Some field values are missing, and sorting the records (finding orders of fields) is not simple. I devised several algorithms, and among them, defining a new table is the most easy and reliable way. I only asked whether there are limitations in column numbers, and how to re-compile SQLite3. But I welcome new approaches suggested, e.g. from alpine. ---------------------------------------------------------------------- Zvoni · 2024-09-02 10:50 Your issue is still Transposing from Rows to Columns. It doesn't matter if there are Values missing. The question is, which FieldNames to "jump" over if the Value is missing OK, just out of my left sleeve (nothing tested) as an algorithm 1) Since you wrote that some FieldValues are missing (meaning: ID003 only has 3000 records compared to ID002, which has 3500 records and so on) Do a SELECT DISTINCT FieldName FROM MyTable ORDER BY FieldName (and yes, i saw that you said, the order is not that simple). You need all POSSIBLE FieldNames which actually occur in your Table 2) Once you have the FieldNames, create your Export-BufDataset in one go 3) Then it's basically what you showed above (staying on the Export- BufDataset-Record until you hit a new ID) Everything else (recompiling sqlite3) would be a last resort, if everything else fails. Someone just has to download a "newest" sqlite3.dll, overwrite your custom one, and KABOOM ---------------------------------------------------------------------- alpine · 2024-09-02 11:02 Quote from: egsuh on September 02, 2024, 12:08:22 pmQuoteSo, the issue actually is transposing from Rows to columns....*sigh* It's not that simple. Some field values are missing, and sorting the records (finding orders of fields) is not simple. I devised several algorithms, and among them, defining a new table is the most easy and reliable way. I only asked whether there are limitations in column numbers, and how to re-compile SQLite3. But I welcome new approaches suggested, e.g. from alpine. Quick suggestion would be if you define an auxiliary table: Code: [Select]      N    fieldname       1     F1       2     F2       3     F3     ...     ...    3500     F3500 with all fieldnames included, then with an OUTER JOIN ON fieldname + ORDER BY ID,N you'll get the full set of spreadsheet cell coordinates (x=N, y=ID). PS: Perhaps you could do it also with CTE and without the aux table. ---------------------------------------------------------------------- egsuh · 2024-09-02 11:29 @Zvoni, alpine, With due respect, please do not waste your time and effort in this issue any more. I have thought over all methods you have suggested and they are implemented already or not feasible. Sorry I cannot explain the whole issues here. I’m looking forward to hearing from you on my other deficient knowledge. ---------------------------------------------------------------------- Thaddy · 2024-09-02 13:30 Quote from: Zvoni on September 02, 2024, 11:46:14 amSo, the issue actually is transposing from Rows to columns....*sigh* That is not that strange: the correct name is a pivot table. Which is not uncommon. Delphi once came with such component. It is also in e.g. Excel. The limits are defined by available memory and the high value of the ordinal type used and squared divided by the size of a single record. I expect a table of, say 3500 rows can easily be translated to a 90 degree pivot table in memory on modern hardware. If it is sane is another question... What I describe as a pivot table is actually the simplest transform it can do. Pivot tables, as a concept, can do way more. Most SQL dialects support this. Meaning I think you are right, Zvoni: it is a common operation. ---------------------------------------------------------------------- Zvoni · 2024-09-02 13:46 Quote from: Thaddy on September 02, 2024, 03:30:55 pmQuote from: Zvoni on September 02, 2024, 11:46:14 amSo, the issue actually is transposing from Rows to columns....*sigh* That is not that strange: the correct name is a pivot table. Which is not uncommon. Delphi once came with such component. It is also in e.g. Excel. The limits are defined by available memory and the high value of the ordinal type used and squared divided by the size of a single record. I expect a table of, say 3500 rows can easily be translated to a 90 degree pivot table in memory on modern hardware. If it is sane is another question... What I describe as a pivot table is actually the simplest transform it can do. Pivot tables, as a concept, can do way more. Most SQL dialects support this. Meaning I think you are right, Zvoni: it is a common operation. Err....right.....that.... I always confuse transposing with pivot (as i know it from Excel). Probably because i'm used to aggregation happening when i pivot in Excel (which in OP's case is NOT the case as i understand it. No aggregation there) ---------------------------------------------------------------------- Thaddy · 2024-09-03 13:37 No? It is a simple rotate, where pivots in general allow for any dimension. It is still a pivot. Rubic's cube anyone?  It is a transposition on any dimension of the data. One of those is transpose x,y to y,x. Rubic uses x,y,z and challenges you to perform the least of the possible transpositions to solve his puzzle. ---------------------------------------------------------------------- alpine · 2024-09-03 16:30 Here is a CTE select that will do the job : Code: SQL  [Select][+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i WITH bufdataset1(ID, fieldname, val) -- <------- That must be the real table with data AS (  VALUES  ( 001, 'F1',  1),  ( 001, 'F2',  4),  ( 001, 'F3',  12),  ( 001, 'F20', 202),  ( 001, 'F35', 998),  ( 002, 'F1',  5),  ( 002, 'F2',  3),  ( 002, 'F3',  32),  ( 002, 'F33', 333),  ( 002, 'F35', 999),  ( 003, 'F12', 42)),flds(N, fn) AS (  VALUES(1, 'F1')  UNION ALL  SELECT     N + 1, 'F' || CAST(N + 1 AS TEXT)  FROM     flds  LIMIT 35 -- <------------- Here limit must be adjusted accordingly (3500?)),tablerows(M)AS (  VALUES(1)  UNION ALL  SELECT     M + 1  FROM     tablerows   WHERE     M < (SELECT MAX(ID) FROM bufdataset1))SELECT   N x, M y, fn, valFROM   tablerows, flds LEFT OUTER JOIN bufdataset1 ON     tablerows.M = bufdataset1.ID AND flds.fn = bufdataset1.fieldnameORDER BY  tablerows.M, flds.N Of course, bufdataset1 must be an existing table as described in reply #8, not as a CTE which here is just for the example. Also the LIMIT for flds should be adjusted as needed. It gives a result like: Code: [Select]x      y        fn     val 1 1 F1 1 2 1 F2 4 3 1 F3 12 4 1 F4 NULL 5 1 F5 NULL 6 1 F6 NULL ... 35      3       F35     NULL The rows count will be N*M thus covering the whole spreadsheet as required. The only thing needed is to iterate over it and use x,y in e.g. 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 FPSpreadsheet.TsWorksheet.WriteNumber(y, x, val, ...) ---------------------------------------------------------------------- egsuh · 2024-09-04 04:25 Thank you for your interests and advises, but please do not waste your time and cognitive resources. Ordering the first table is not simple. The ordering should be based on 64 or so characters long string. They are not in the order of field name. Each fieldname has to refer to another data structure (in my case TFPGMap).  I can define fields  (in correct order) of the second table from the TFPGMap (dynamically). Following is my real codes. It directly writes to text file, instead of creating any memory-residing structure. In the beginning I used BufDataSet as the target (bufdataset2 in the previous example), and did not have any problem with most questionnaires for 5 years, until a new project's total column count reached 2000 or something. A solution that hit me first was to expand the column number limit of a table. I don't think it is possible with TBufDataSet, so I thought of SQLite3, and I found it says I can expand the column number limit by recompiling the sources, which is not a very good idea actually. I have to distribute sqlite3.dll with every copy of my application. Then I tested ordering the first table (bufdataset1 in the previous example, and temptable in following example).  I was afraid that it may be time-consuming if the number of rows of the first table is very large. As result, not that bad when I applied "filter". Most of the table cells are sparse, and number of rows of each ID is not large in most cases.  Then, the program writes the content to a file on the disk. There could be improvements on the performance of following codes, but that does not deserve the effort from business point of view. This runs on terminal, neither DB nor Web server. Many thanks again for the warmest concerns. 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       tcount := 0;       tempTable.IndexFieldNames:= 'RID;VarOrderStr;ROrder';      tempTable.Filtered:= True;       AssignFile(f, fname);      Rewrite(f);       write(f, 'SID,RID,STATUS,IVID,Start,Last,');                    for ti := 0 to OrderedVarList.Count-1 do begin         AVar := OrderedVarList.Data[ti];         if AVar.MaxCount + AVar.MaxOpenCount = 1             then Write(f, AVar.VarName(0), ',')                               else begin               for tj := 1 to AVar.MaxCount do Write(f, AVar.VarName(tj), ',');               for tj := 1 to AVar.MaxOpenCount do Write(f, AVar.VarName(-tj), ',');            end;      end;      Writeln(f);       trRes.First;      while not trRes.Eof do begin         with trRes do begin            trid:= FieldByName('rid').AsInteger;            ts:= Format('%s,%d,%s,%s,"%s","%s",',                 [FieldByName('sid').AsString,                  trid,                FieldByName('Status').AsString,                  FieldByName('Ivid').AsString,                  FieldByName('Created').AsString,                  FieldByName('LTime').AsString]);         end;         write(f, ts);        TempTable.Filter:= Format('RID=%d', [trid]);          tempTable.first;         ti := 0;         ts := tempTable.FieldByName('VarOrderStr').AsString ;         while (not tempTable.Eof) and (ti < OrderedVarList.Count) do begin             AVar := OrderedVarList.Data[ti];             tss := OrderedVarList.Keys[ti];             for tj := 1 to AVar.MaxCount do begin                 if (ts = tss) then begin                     write (f, temptable.FieldByName('rcode').AsInteger);                   temptable.next;                     if temptable.eof then break                   else ts := tempTable.FieldByName('VarOrderStr').AsString ;                 end;               write (f, ',');             end;              if tempTable.EOF then break;             for tj := 1 to AVar.MaxOpenCount do begin                 if (ts = tss) then begin                     write (f, temptable.FieldByName('rcode').AsInteger);                   temptable.next;                     if temptable.eof then break                   else ts := tempTable.FieldByName('VarOrderStr').AsString ;                 end;               write (f, ',');             end;             inc(ti);       end;          writeln(f);         trRes.Next;         tcount += 1;         if Assigned(SendMessage) then            if (tcount mod 50) = 0 then SendMessage(Format('%d respondents processed', [tcount]));       Application.ProcessMessages;      end;      Close(f);