SQL statements in TMemDataset/ TBufDataset
lazarus / 16-Databases

----------------------------------------------------------------------
Ronan · 2016-11-27 09:52

DEar All,

Internet search didn't retrieve anything, so I kindly would like to
ask the community about that, Can we execute the sql statement in the
TMemDataset/TBufataset or is there any trick to do that ?

Regards,

----------------------------------------------------------------------
vrull · 2016-11-27 19:38

I don't think so.
From my understanding you should chain TSQLConnection to TSQLQuery,
execute your SQL statement in TSQLquery and copy the dataset into
TMemDataSet / TBufDataSet. One of these components have in-built copy
function, you can take it and adjust for another, if needed. Your grid
or other data-aware components will have their data in the in-memory
dataset after this operation, and you can close the transaction. This
is especially important for SQLite, otherwise you can't execute update
queries.

The proper chain would be TSQLConnection -> TSQLTransaction ->
TSQLQuery.

----------------------------------------------------------------------
ttomas · 2016-11-28 00:14

You can try zmsql
http://wiki.freepascal.org/ZMSQL

----------------------------------------------------------------------
Ronan · 2016-11-28 06:44

@vrull, yes that really remains as only tangible solution, but since
I'm porting from SQL based application due to slow reading/writing
I'll try to keep on in-memory database implementiation.

@ttomas,
When porting from SQL based application to in memory database app my
first option was ZMSQL, but later I changed  my mind and decided to go
with plain LAzarus database implementations, such as TmemDataset,
TBufDataset. Based on documentation for ZMSQL it seems that it handle
very elementary level SQL statement, what happens if I try to use
generators with join statement, can you share your experiance and shed
more lights into that?

Regards,

----------------------------------------------------------------------
Thaddy · 2016-11-28 10:25

I would use an in memory Sqlite3 database in that case. (:MEMORY: as
special filename).

----------------------------------------------------------------------
Ronan · 2016-11-28 11:35

Thanks, I considered that as an option in the past against
Firebird(which I use currently), the thing with SqlLite3 that remains
in my mind, when I was deciding to use it or not, it wasn't free for
commercial use. Interestingly I now check it and there is not anything
that I can interpret or deduce as "NOT free for commercial use".

Can you comment on whether the SQLlite can be used in commercial
applications or not.

REgards,

----------------------------------------------------------------------
Thaddy · 2016-11-28 12:09

Sqlite is and has always been free for ANY use. It has one of the
least restrictive licenses possible, to the extend that you only can
not claim that you have written SQlite yourself.

----------------------------------------------------------------------
mangakissa · 2016-11-28 18:36

Maybe you have think about ORM.