SQLite memory tables when using the Clarion DCT

1

I know it’s possible to do this (and Jeff posted code for it) in hand-coded apps but is it possible when using the DCT?

I’ve tried all sorts of stuff with :memory: as the owner, manually using prop:sql to issue the table creation statement after an ATTACH AS Mem etc etc - but still can’t quite get it done.

2

There’s also Peter trying to do the same thing on the softvelocity.clarion.databasedrivers newsgroup.

3

I think that was covered by John Hickey’s February 20 2021 SQLite presentation for www.Cidc2020.com. He definitely covered memory tables and had a template for it to work in an APP. It also covered a lot of other SQLite stuff.

4

Unfortunately I don’t have access to the CIDC 2020 videos etc and can’t find anything on ClarionLive or OhnoSoft that would help :slightly_frowning_face:

5

I just skimmed the presentation and it absolutely covers using SQLite Memory tables in an APP. John seemed pretty excited to solve that so he moved it to first.

image
image2174×1220 312 KB

Download includes Template and Apps. He covered a LOT about SQLite comparing diffs to other SQL, limitations, and how the Clarion supports it and does not. Does it support BLOBs, what do you need to distribute, etc.

CIDC 2020 OnLine can still be purchased and you get all the content which is 9+ presentations of about 2 hours. Its some great content that is worth the price. You get Dries Donut holes, Mike’s Pivot Table, Andy showing Web APIs.

image
image1934×860 190 KB

image
image1144×316 88 KB

John made the Memory driver caching work with SQLite

image
image2216×1060 273 KB

6

Everyone has access to the recordings - all it takes is money :slight_smile:

Given the ongoing delay to the on-site CIDC 2020 conference we’ve been doing a once-a-month online session. CIDC 2020 attendees get access to the online sessions, and also online access to the actual sessions and training when the event takes place.

You can register at https://www.cidc2020.com/. In addition to the sessions Carl mentioned above, there’s also an exceptional one by Rick Martin on preventing SQL disconnects.

the good news though is that John figured it our from Jeff’s code, so you can too :slight_smile: - it all depends on how much you value your time as to which way you go.

cheers
Bruce

7

Hi Bruce,

It turns out I had figured it out from Jeff’s code, but because of trying numerous times and not deleting the physical SQLite database I thought it hadn’t worked :slight_smile:

As for CIDC I appreciate there’s value for money there but I’m only really investigating this for those who’ve asked on the Clarion newsgroups - I don’t have a use for it currently.
(I have the IMDD driver)

Once I’ve tidied things up I’ll post here and on the newsgroup.

8

OK here is my Clarion/Legacy test SQLite memory example.
Created with Clarion 11 Build 13505

Main ‘tricks’ are the creation of a DummyFile on the application frame thread at the start with /TURBOSQL=TRUE, create unticked and Owner set to :memory: (case seems to matter)

Just a very rough demo so you’ll have to manually increment the ID column
I’ve used a different way from Jeff to get information about the tables in the SQLite database - I query the sqlite_master table to get name,type and the sql that was used to create the table by the driver.MemTest.zip (47.8 KB)

9

Thanks for posting your demo app, Graham :+1: … very helpful.

[It’s interesting to see a Clarion/Legacy app after all these years. I’d forgotten how “simple” they look.]

Based on this advice from the “SQLite File Driver” section of the Database Drivers manual;
… image

I added the following to the “PrepareProcedure” inside your BrowseMEMTEST procedure;

image
… with the following findings;

  • The tip above is well worth noting. Load time for the 1 million rows (on my system) takes 4.3 secs with the LOGOUT & COMMIT - and 7.9 secs without.
  • My conclusion is that this would be a very efficient method of loading a data table into a SQLite memory table for use as a Lookup/Select list.

I am curious, tho. Why is the initial open of the DummyFile in the application thread necessary ?

That is, I know that it is necessary (because I tried it without this step), but I don’t understand what it’s doing (in terms of preparing the ground for subsequent opening of the MEMTEST table) ?

A very minor point regarding the DummyFile; you said you have it declared with “create unticked” - whereas, I found it doesn’t matter either way … tho, of course, Create-enabled is necessary for the MEMTEST table.

Something else I found of interest was this note in the “SQLite Accelerator Driver Strings” section of the Database Drivers manual;
… image

The MEMTEST table is defined exactly as described in this note - but, I wasn’t able to find a way to retrieve the increment (supposedly) generated by SQLite.
It doesn’t “simply appear” in the ID/PrimaryKey field as is suggested by the note.

  • Interestingly, tho, pragma details from the SQLite database do include reference to AUTOINC ?!

    image
    image821×92 6.04 KB

  • Also puzzling is that the Driver String Builder for SQLite for the following selection results in Driver options defined as /AUTOINC=**FALSE** … which is back-to-front, surely ? (I assumed it was).
    image - SQLite does autoincrement

And for something else even more curious !! … I decided to create an ABC/OOP version of Graham’s demo app (Wizard generated), with the same MemTest.Dct as used by Graham’s CW-Legacy app - - - but, to date, I haven’t been able to get it to work. At the point where the MEMTEST table is opened, it fails with a “File could not be opened” error; presumably, because it cannot be CREATEd … Mmm !!

Regards, John Morter … dipping back into Clarion as a diversion during my time in COVID lockdown !