TkbmMemSQL as replacement for BDE

[VadimMest]

I am trying to replace BDE on my big old ptoject but I can’t find documentation and examples for TkbmMemSQL for these cases:

• Creating few tables and connecting to these tables from few TkbmMemSQL for concurrent requests
• Storeing a local copy for emergency situation (in case power off) as it does the method TTable.FlushBuffers

Where can I find a proper documentation for kbmMWTable?

 

[kimbomadsen]

Hi,

The memtable itself is largely covered in the help file (PDF) included with the kbmMemTable installer.

The SQL is mostly documented via samples in the SQL demo folder.

In addition SQL functions are documented here:

User defined functions and kbmMemSQL

 

/Kim

[VadimMest]

1. I’ve found only autogenerated kbmmemtable700d.chm in kbmMemTable\Documentation from  04.19.2018  that is zero usefull. The Kbmmemtable700Man.pdf from customer portal   also cantains zero information how it works

2.  Demo.dpr contains few SQL create table (table9) but no one example  how I can connect to this created table.

When I wrote “The replacement for BDE”, I meant:

• Create few tables, open datasets  with datasource and show these data in DbGrids (like TTable)
• Connect to these tables and change data by UPDATE SQL or Check data by SELECT SQL (like TQuery)
• Refresh  dataset and show modfied data in DbGrids

Thanks

Vadim Mescheryakov

 

 

 

• This reply was modified 4 years, 8 months ago by VadimMest.

[kimbomadsen]

“Demo.dpr contains few SQL create table (table9) but no one example  how I can connect to this created table.”

Not sure which demo you are looking at, but the SQLDemo project contains roughly 130 different SQL statements, that operates a couple of memory tables and returns data.

In all cases the tables are bound up to some grids for view.

If you look in TForm1.ExecuteSQL, you will see how 3 memory tables are being build with sample data, indexes (optional) and how they are registered to the SQL component, after which the SQL is executed and the resulting data in the SQL instance used as result for a grid.

/Kim

[VadimMest]

procedure TForm4.Button1Click(Sender: TObject);
begin
FSQL.ExecSQL(‘create table t1 (id Integer, name char(50))’);
FSQL.ExecSQL(‘select * from t1’);

kbmMemTable1.LoadFromDataSet(FSQL, [mtcpoStructure, mtcpoProperties]);

kbmMemTable1.AppendRecord([1, ‘Name 1’]);
kbmMemTable1.AppendRecord([2, ‘Name 2’]);

FSQL.Tables.Clear;

FSQL.Tables.Add(‘t1’, kbmMemTable1);

FSQL1.Tables.Add(‘t1’, kbmMemTable1);

kbmMemTable1.AppendRecord([11, ‘Name 11’]);
kbmMemTable1.AppendRecord([12, ‘Name 12’]);

FSQL1.ExecSQL(‘update t1 set id = id + 100’);
FSQL1.ExecSQL(‘select * from t1’);

FSQL1.AppendRecord([23, ‘Name 23’]);
FSQL1.AppendRecord([23, ‘Name 23’]);

kbmMemTable1.AppendRecord([41, ‘Name 41’]);
kbmMemTable1.AppendRecord([42, ‘Name 42’]);
kbmMemTable1.Refresh;
FSQL.ExecSQL(‘select * from t1’);

dsResult.DataSet := FSQL;
dsResult1.DataSet := FSQL1;

end;

 

[kimbomadsen]

No, FSQL.AppendRecord adds those records to the FSQL memory table instance, not one of the source tables.

Remember TkbmMemSQL is descending from TkbmMemTable and is as such a separate memory table in itself, and thus supports all the features of a regular memory table. AppendRecord operates on the specific memory table, while the SQL INSERT statement operates on the dataset defined as the tablename target of the INSERT statement.
/Kim

[VadimMest]

Thanks, I’ve understood:

I can use: One knmMemTable + Few KbmMemSQL with only modify SQL Statements

I have a table with documents rows in my application (TTable).  User changes these rows by using an interface (it is my code in application)  and my customers have a huge quantity of different external scripts (Fast Script Interpreter) that can change data in this table (make discounts or check free quantity in a stock uses for this purpose TQuery).  Therefore, I am seeking the ability to have a table and few queries that can change data.

 

The First question: How do I make this code below easy?

FSQL.ExecSQL(‘create table t1 (id Integer, name char(50))’);
FSQL.ExecSQL(‘select * from t1’);
kbmMemTable1.LoadFromDataSet(FSQL, [mtcpoStructure, mtcpoProperties]);

The statement ‘create table t1’ creates the table in memory but I didn’t figure out how can I use this table directly

The Second question: In my project, I often use TQuery for changing selected rows as Dataset (Edit Post)

But this code also doesn’t change original data in the original table:

FSQL1.ExecSQL(‘select * from t1’);
FSQL1.First;
while not FSQL1.Eof do
begin
FSQL1.Edit;
FSQL1.FieldByName(‘Name’).AsString := ‘Name Edited’;
FSQL1.Post;
FSQL1.Next;
end;

Thanks,
Vadim Mescheryakov

 

 

[VadimMest]

I found two solutions:

1. TKbmMemSQL (only for creating a table)   +  TKbmMemtable + TVirtualQuery (from DevArt UNIDAC)  works properly.
2. Changing ‘Create table’ from SQL Statement to AddField + TVirtualTable  + TVirtualQuery also works properly and faster than the first solution

[VadimMest]

Few interesting facts about performance

I run tests for 100 000 records. I know it’s a ridiculous amount of records, but my laptop very fast and I need to see how it affects my client’s workplaces.

The pair  TKbmMemtable + TVirtualQuery works very slowly, very.
The pairs of TKbmMemtable + TkbmMemSQL  and  TVirtualTable  + TVirtualQuery  work fast and about similar
The BDE pair ( TTable + TQuery) is the fastest on my laptop with nwme SSD, on the old hard disk is slower but it isn’t dramatic.  BDE is the best 🙂

[kimbomadsen]

Hi,

>The First question: How do I make this code below easy?
>FSQL.ExecSQL(‘create table t1 (id Integer, name char(50))’);
>FSQL.ExecSQL(‘select * from t1’);
>kbmMemTable1.LoadFromDataSet(FSQL, [mtcpoStructure, mtcpoProperties]);

I am not at all sure what exactly you want to do.

However here is an example:

Add a TDBGrid and a TDatasource to a VCL form, hook them up.

Add a TButton and add to its event handler:

procedure TForm1.Button1Click(Sender: TObject);
var
   mt:TkbmMemTable;
begin
     if FMS<>nil then
        FreeAndNil(FMS);

     // Use SQL against an existing memory table.

     // Define existing memory table.
     mt:=TkbmMemTable.Create(nil);
     with mt.FieldDefs.AddFieldDef do
     begin
          Name:='Field1';
          DataType:=ftString;
          Size:=50;
     end;
     mt.Open;
     mt.AppendRecord(['String1']);
     mt.AppendRecord(['String2']);

     // Operate using SQL on the memory table, now accessible as mt.
     FMS:=TkbmMemSQL.Create(nil);
     FMS.Tables.Add('mt',mt,true);
     FMS.ExecSQL('SELECT count(*) as ''COUNT'' FROM mt');
     DataSource1.DataSet:=FMS;

     // Will leave one record with one field (COUNT) in the memory table ms. The value will be 2.
end;

Add another TButton and add to its event handler:

procedure TForm1.Button2Click(Sender: TObject);
begin
     if FMS<>nil then
        FreeAndNil(FMS);

     // Operate using SQL on no preexisting memory tables or data.
     FMS:=TkbmMemSQL.Create(nil);
     FMS.ExecSQL('CREATE TABLE t1 (id Integer, name char(50))');
     FMS.ExecSQL('INSERT INTO t1 (id,name) VALUES (1,''STRING1'')');
     FMS.ExecSQL('INSERT INTO t1 (id,name) VALUES (2,''STRING2'')');
     FMS.ExecSQL('INSERT INTO t1 (id,name) VALUES (3,''STRING3'')');

     FMS.ExecSQL('SELECT count(*) as ''COUNT'' FROM t1');
     DataSource1.DataSet:=FMS;
     // Will leave one record with one field (COUNT) in the memory table ms. The value will be 3.
end;

Adding records to a memory table by using SQL is not the fastest way. AppendRecord or similar is.

[Author]

Posts