Dataset SQL

[P.curtis]

Hi All,

I have DB (RxMemoryDataset). I would like to perform some SQL commands on the dataset. Is this possible? Or how would I go about doing something like UPDATE .... SET X = Y WHERE IDX  = Z?

Thanks for your help / advice.

[wp]

As far as I can see in the source code, TRxMemory Dataset does not support SQL. To my knowledge, the only memory-dataset with SQL support is TZMQueryDataset in the ZMSQL package (available via OPM).

[P.curtis]

I've had a look, but I can't get my head round it. The demos are unclear 

How to create a memory DB, and perform simple SQL operations .....

[wp]

I've never used ZMSQL, so I cannot help specifically. Did you see the wiki https://wiki.lazarus.freepascal.org/ZMSQL?

[rvk]

How to create a memory DB, and perform simple SQL operations .....

Maybe you can explain WHY you want this.
With that information maybe some more useful suggestions can be given.

For example, SQLite can have a database in memory. And there are others.

[P.curtis]

I want it for speed, I just tried SQLite and it didn't suit me. A full blown server. MARIADB ..., is overkill.

A memory based DB is ideal for this applications requirements.

[rvk]

I want it for speed, I just tried SQLite and it didn't suit me. A full blown server. MARIADB ..., is overkill.

A memory based DB is ideal for this applications requirements.

SQLite isn't a full blown server. It's just a small dll. And with :memory: as connectionstring you can make a memory-database. What didn't suit you?

But ok... There are others.
https://en.m.wikipedia.org/wiki/List_of_in-memory_databases

[af0815]

A memory based DB is ideal for this applications requirements.

And SQL is overkill for a memory based DB. And if you want to change data like a update, iterate throug the DB. BTW: It is often better to use collections or specialiesd lists to get speed. 

[P.curtis]

Thats why the second part of my question was how to handle something like an UPATE in this type of dataset.

[wp]

Why don't you use the TDataset methods Locate, Edit, Append, Post?

[P.curtis]

Thats my point. If I have a DB of say 1000 records and I want to do an UPDATE that will affect a lot of the the records, do I have to LOCATE POST CHANGE DATA POST all the records or am I missing something?

[af0815]

I think you have do it in this way, if you use this db. No way out.

Is there a special indication for using THIS kind of db.

[wp]

My first impression is: you have a memory dataset, and 1000 records is not much for an in-memory operation. Did you try? If you did: did you call Disable/EnableControls, or disconnect the visual controls from the datasource?

Try the attached demo. It uses a TBufDataset which comes with the default installation of Lazarus and is a memory dataset (similar to Delphi's ClientDataset). A click on the left button fills the dataset with 10,000 records (integer ID field, float X and Y fields) where the X field contains random integers from 0 to 9. A click on the right button iterates through all the records and replaces the Y value by Y*1000 when X=5. With about 1000 records affected this is completed in some tens of milliseconds.

[P.curtis]

@WP Thanks for your input. I have decided to go the SQLite way. Which has produced more headaches

I'll post another question.

Thanks.