[CLOSED] Are there any limit to field numbers in TBufDataSet?

[egsuh]

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]

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]

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.lazarus.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]

I'm also curious what case requires such pushing of db columns number limits.

[egsuh]

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]

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

You said there:

The 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]

I second paweld in his advice to change the export method. The other is not very reasonable.

[Zvoni]

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]

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][+][-]

1.    
2.  
3.     pid := 0;
4.     bufdataset1.first;
5.    while not bufdataset1.eof do begin
6.          tid := bufdataset1.fieldbyname('id').asinteger;
7.          if tid <> pid then begin  
8.             bufdataset2.append;
9.             bufdataset2.fieldbyname('id').asinteger := tid;
10.             bufdataset2.post;
11.             pid := tid;
12.          end;
13.        
14.          bufDataSet2.FieldByName(BufdataSet1.FieldByName('FieldName').AsString).AsInteger
15.                     := bufdataset1.Fieldbyname('value').AsIntger
16.         bufDataSet1.Next;
17.    end;
18.  
19.     with TCSVExporter.Create(nil) do begin
20.        Dataset:= BufDataSet2;
21.        FileName:= SaveDialog1.FileName;
22.        Execute;
23.        Free;
24.     end;
25.  

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]

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]

So, the issue actually is transposing from Rows to columns....*sigh*

[egsuh]

So, 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]

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]

So, 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]

@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.