Playing with TSQLite3DataSet

[egsuh]

Hello,

I'm playing with TSQLite3DataSet. This is quite interesting. Like following codes, by setting (database) filename as ":memory:" it runs only within memory (as if TBufDataSet of Lazarus).

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

1. procedure TForm1.Button1Click(Sender: TObject);
2. var
3.    ti: integer;
4.    ts : string;
5. begin
6.    with SQLite3DataSet1 do begin
7.       filename := ':memory:';
8.       TableName := 'ATest';
9.       for ti := 0 to 150 do
10.           FieldDefs.Add(Format('F_%d',[ti]), ftInteger);
11.  
12.       if CreateTable then begin
13.          Active := True;
14.  
15.          for ti := 0 to 20 do begin
16.             Append;
17.             fieldbyname('f_0').AsInteger := ti;
18.             fieldbyname('f_8').AsInteger := ti * 100;
19.             Post;
20.          end;
21.       end
22.       else showmessage('create table failed');
23.    end;
24.  
25.    // following codes are for saving to CSV file
26.    SQLite3DataSet1.First;
27.    if sd1.execute then begin
28.       CSVExporter1.FileName := sd1.FileName;
29.       CSVExporter1.Dataset := SQLite3DataSet1;
30.       CSVExporter1.Execute;
31.    end;
32. end;
33.  

I have three questions:

1) Does this still require "sqlite.dll"?  I assume Yes.
2) I need more than about 2000 fields, up to roughly 5,000. Is there any way that I can set this to around 5000 with TBufDataSet (or any other Lazarus in-memory dataset)?  i.e. I need,  for ti := 1 to 3000 do fielddefs.add.
3) SQLite says I can extend the maximum field number to 32767 by "re-compiling" sqlite3. If anybody has done already, can I get a copy of it? I cannot compile C.

And any other options that I can use to extend to more than 2000 columns in DB table format?

[egsuh]

I tried with TBufDataSet and TCSVDataSet. They can create more than 2000 columns (fields) but the latter fields are filled with 0's and some data are not displayed correctly.

[paweld]

In my opinion, such a large number of columns is a bad idea. It is better to treat the columns as attributes and create a one-to-many relation.

For example, instead of:

Code: SQL  [Select][+][-]

1. CREATE TABLE article (
2. article_id INT,
3. name VARCHAR(200),
4. col1 INT,
5. col2 INT,
6. col3 VARCHAR(100),
7. col4 REAL,
8. ...
9. col5000 VARCHAR(10));
10.  

create the following database structure:

Code: SQL  [Select][+][-]

1. CREATE TABLE article (
2. article_id INT,
3. name VARCHAR(200));
4.  
5. CREATE TABLE column_def (
6. column_id INT,
7. name VARCHAR(200),
8. data_type INT, /*0-int, 1-real, 2-varchar*/
9. data_size INT /*for varchar -> text length*/
10. );
11.  
12. CREATE TABLE article_attribute (
13. id INT,
14. article_id INT,
15. column_id INT,
16. int_value INT, /*for integer values*/
17. real_value REAL, /*for real values*/
18. str_value VARCHAR(250) /*for text values*/
19. );
20.  

[egsuh]

It is better to treat the columns as attributes and create a one-to-many relation.

I have such relations. I need to convert them to Excel-like format. The table will not be used within database operation. It is only for export.

[paweld]

Instead of creating a table, change the export method. An example is attached.

[egsuh]

@paweld

Thank you for your example. I'll check your codes one by one.