Playing with TSQLite3DataSet
lazarus / 16-Databases

----------------------------------------------------------------------
egsuh · 2024-08-03 05:21

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i procedure
TForm1.Button1Click(Sender: TObject);var   ti: integer;   ts :
string;begin   with SQLite3DataSet1 do begin      filename :=
':memory:';      TableName := 'ATest';      for ti := 0 to 150 do    
    FieldDefs.Add(Format('F_%d',[ti]), ftInteger);       if
CreateTable then begin         Active := True;          for ti := 0 to
20 do begin            Append;            fieldbyname('f_0').AsInteger
:= ti;            fieldbyname('f_8').AsInteger := ti * 100;          
Post;         end;      end      else showmessage('create table
failed');   end;    // following codes are for saving to CSV file  
 SQLite3DataSet1.First;   if sd1.execute then begin     
CSVExporter1.FileName := sd1.FileName;      CSVExporter1.Dataset :=
SQLite3DataSet1;      CSVExporter1.Execute;   end;end; 
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 · 2024-08-03 05:34

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 · 2024-08-03 06:34

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][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i CREATE
TABLE article (article_id INT,name VARCHAR(200),col1 INT,col2 INT,col3
VARCHAR(100),col4 REAL,...col5000 VARCHAR(10)); create the following
database structure:
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 CREATE
TABLE article (article_id INT,name VARCHAR(200)); CREATE TABLE
column_def (column_id INT,name VARCHAR(200),data_type INT, /*0-int,
1-real, 2-varchar*/data_size INT /*for varchar -> text
length*/); CREATE TABLE article_attribute (id INT,article_id
INT,column_id INT,int_value INT, /*for integer values*/real_value
REAL, /*for real values*/str_value VARCHAR(250) /*for text values*/);

----------------------------------------------------------------------
egsuh · 2024-08-03 07:06

QuoteIt 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 · 2024-08-03 10:48

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

----------------------------------------------------------------------
egsuh · 2024-08-03 14:14

@paweld

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