Forum > Databases
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 [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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:
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 [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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 [+][-]window.onload = function(){var x1 = document.getElementById("main_content_section"); if (x1) { var x = document.getElementsByClassName("geshi");for (var i = 0; i < x.length; i++) { x[i].style.maxHeight='none'; x[i].style.height = Math.min(x[i].clientHeight+15,306)+'px'; x[i].style.resize = "vertical";}};} ---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:
--- Quote ---It is better to treat the columns as attributes and create a one-to-many relation.
--- End quote ---
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.
Navigation
[0] Message Index
[#] Next page