Hello -
I discovered that you can easily use SQLite memory tables by way of the SQLite driver.
Went ahead and made a simple app to demo it. I wrote it in C11, but it might work as far back as C9. Not sure.
This is an alpha version. If you have any bugs to report or suggestions, please holler.
I hope it can give you some ideas.
Hey Jeff,
Did you try any tests for performance against the regular in-memory driver?
Hi Rick - No performance tests yet. I can see lots of uses for SQLite tables where you want to create stuff on the fly and be able to use SQL on it.
Itās really neat that you can do ATTACH DATABASE for loading additional SQlite disk files. So your OwnerID on the TurboSQL table might be just a single SQlite file, but you can load multiple SQLite files via ATTACH DATABASE.
If you have any SQLite files, you can test this right in my demo app.
Try this:
ATTACH DATABASE ā.\YourSQLiteFile.SQLiteā AS File1
Then Execute that by pressing F8 (or Ctrl+Enter). There wonāt be a response unless thereās an error.
Now you can list the tables in that database file:
SELECT name FROM File1.sqlite_master WHERE type = ātableā
Hopefully, you see some table names there, which you can use to construct another query to list that table.
You can ATTACH multiple databases that way and query multiple tables at once.
And itās all managed within PROP:SQL.
I havenāt tried join yet, but thatās pretty powerful:
John Taylor did a lot of performance testing in his February 2007 article for ClarionMag
Itās the 2007-02 pdf and zip (source files) that contain his SQLite classes
Hi Jeff,
Very nice example. I have a question, why do you call the windows API āInvalidateRectā on EVENT:SIZED?
This is the first time I have seen this done.
Thanks!
Edgard
Thanks Graham. I was trying to find that article but totally forgot the authorās name.
Thatās where I got the āATTACHā idea, but had not considered trying it in PROP:SQL before this week.
IMDD would certainly be more suitable for cases where youād want to use appgen to create forms/browses. I donāt know if a TurboSQL table could be used in the same way (such as for inserting a record on a browse in the standard way). Seems like it would be more trouble than itās worth in that regard.
I see tons of uses for the In-Memory SQLite, but not as a āwhich is better, IMDD or SQlite?ā kind of thing.
But you can also use SQLite for non database related stuff too.
For example, in my demo app, try this:
!To create 4 random 10 character strings.
SELECT hex(randomblob(10)) as R1,hex(randomblob(10)) as R2,hex(randomblob(10)) as R3,hex(randomblob(10)) as R4
!Or select an INT as hex (This requires a newer version of sqlite3.dll because the one that ships with Clarion doesnāt suport printf():):
SELECT printf(ā%08Xā, 99119191)
You can get the SQLIte version of your sqlite3.dll in the demo too. The version I just downloaded is 3.32.2, but the shipping one is 3.7.11. Support for printf() came in 3.8.x
SELECT sqlite_version() AS Ver
These functions are all for free and itās really simple to implement them.
And date/time stuff (with caveats) Date And Time Functions
Thereās a lotta neat stuff in there.
Hi Edgard - Thanks. InvalidateRect forces a re-draw of the rect() in question. I was getting occasional artifacts on the screen when moving the controls around during resize. DISPLAY() would not clear them up so I used a bigger hammer..
I used InvalidateRect() a lot on my C6 apps for certain situations. Maybe a different technique would have worked, but I was unsuccessful at figuring it out with plain Clarion code.
I have updated the repository with some small improvements. instead of parsing sqlite_masterās.sql to get the column names, pragma table_info() seems to work fine.
That allowed me to get rid some SystemString parsing that I didnāt like too much.
Also made it optional to display the data type.
Yet again, the repo has been updated.
Federico Navarro added a window for testing threads and demonstrates that the in-memory data is usable across threads until the turbosql table closes (which disposes of the in-memory data).
He also taught me that if you use ā:memory:ā as the OWNER string (along with the ATTACH line) then you donāt need a disk file at all.
can some one explain this ? DummyDBOwner = ā:memory:ā ! Per Federico Navarro, and it works!
what is this all about, looks interesting?
Stev - SQLite has a built in in-memory database called.ā:memory:ā. By setting the OWNER of the Clarion SQLite FILE to ā:memory:ā, a SQLite disk file is not needed.
However, in order to make use of the memory database, you still have to āATTACHā to it. Then you can dynamically create and fill tables 100% in memory.
Some people have tried using ā:memory:ā as an OWNER in the past, but since it ādidnāt workā, it was assumed that in-memory SQLIte tables donāt work in the Clarion driver. But if they would have also used āATTACHā to attach to the memory database, it would have worked.
thank you very much! where was this documented? and does it apply to any other drivers?
becuase i would like to try it with the russian DLIB runtime databases driver source code we bought. i have found the russians in moscow but they cannot be contacted and we have taken over the source code since we paid a descent some for it.
Stev - :memory: is not a Clarion specific thing itās a built-in database inside SQLite. You can attach to and access it in Clarion via PROP:SQL as demonstrated in the demo. In-Memory Databases
i see thank you for the information.
I modified the demo a little bit to demonstrate how fast it can be to load a table into memory.
Instead of pre-loading the data via an SQL script, the demo now uses a pre-built SQLite 198K record table. It adds 36MB to the repository size though.
On my my VM it takes less than .25 seconds to load those rows into memory. Desktop is about .13 seconds.
Queries are a bit slower than when there are only 500 rows, but itās still pretty dang quick.
I havenāt tested how indexes would impact the loading and query times, but queries are really fast without them.