dhRichClient3 "Cannot execute SQL-Statement: out of memory"

Hello!

I am using dhRichClient3.

I do a lot with sqlite, and at everything works fine for some time, but then at some point of time I get the error "Cannot execute SQL-Statement: out of memory" when calling an update like this:

Code:

---

    Dim Cmd As dhRichClient3.cCommand
    Set Cmd = cnUser.CreateCommand("UPDATE pages SET pagedatetimemodified=?,pagethumbdirty=? WHERE pageguid=? AND pagebookguid=?")

    Cmd.SetDate 1, Now
    Cmd.SetBoolean 2, True
    Cmd.SetText 3, tPage.Guid
    Cmd.SetText 4, tBook.Guid

    Cmd.Execute

---

This code is fine. I can call it like 20 times, but then at some point of time, it throws this error: "Cannot execute SQL-Statement: out of memory"

I can't really say under which circumstance it happens.

My app shows ~400 mb in the taskmanager.
In the taskmanager, I see that for a split second (right after cmd.Execute) the taskmanager shows 1350 mb for my app.
Then, immediately it flips back to ~400 mb.

So obviously dhRichClient3 does something internally which consumes really much memory for some a small amount of time. What might that be?

How could I debug what causes this out of memory error?

I am using an undo-redo logic with triggers, so the problem is a bit too complex to post it.

That is why I would like to know what I can check first.

Thank you for any hint!

Do you keep the cnUser connection open for the lifetime of the app? It might be worth trying to periodically close and re-open it to see if that helps.

Or perhaps try trapping the "out of memory" error, then close & re-open cnUser, recreate the Cmd object and try re-executing to see if that helps.

That won't be a proper solution, but it's a workaround that might help while you track down the bug.

Yes, I keep cnUser alive and open for the entire lifetime. I have never had any problems with this. Thanks, but I don't want to close the connection. I need to find the real reason, and I was hoping that vbRichClient3.cConnection might give me insights into its memory. Then I could check what makes it choke.

In the taskmanager, I see that for a split second (right after cmd.Execute) the taskmanager shows 1350 mb for my app.
Then, immediately it flips back to ~400 mb.

Quote:

---

Originally Posted by tmighty2

Yes, I keep cnUser alive and open for the entire lifetime. I have never had any problems with this. Thanks, but I don't want to close the connection. I need to find the real reason, and I was hoping that vbRichClient3.cConnection might give me insights into its memory. Then I could check what makes it choke.

---

OK, I would certainly give it a try just to see if it works - might provide some clues as to what's going wrong.

How many records are in the pages table?

How many records are being updated on average by that statement. Approximately anyway - like hundreds, thousands, millions?

Are pagedatetimemodified, pagethumbdirty, pageguid, pagebookguid INDEXed?

I have isolated the problem a bit now.
The trouble is caused by the triggers.
I will edit my initial post accordingly.

For example, if you do something like this and it works on the second try, then that might indicate a memory leak:

Code:

---

    Dim Retry As Boolean
    Dim Cmd As dhRichClient3.cCommand

    Do
      Set Cmd = cnUser.CreateCommand("UPDATE pages SET pagedatetimemodified=?,pagethumbdirty=? WHERE pageguid=? AND pagebookguid=?")
 
      Cmd.SetDate 1, Now
      Cmd.SetBoolean 2, True
      Cmd.SetText 3, tPage.Guid
      Cmd.SetText 4, tBook.Guid
 
      On Error Resume Next
      Err.Clear
      If Cmd.Execute Then
          Retry = False
      Else
          Debug.Assert False
          If InStr(1, Err.Description, "out of memory", vbTextCompare) Then
              If Retry Then
                  On Error GoTo 0
                  Err.Raise vbObjectError, , "Still out of memory after second try!"
              Else
                  ' Re-open DB and retry
                  Retry = True
                  Set Cmd = NOthing
                  Set cnUser = ' Reopen DB here
              End If
          Else
            On Error GoTo 0
            Err.Raise vbObjectError, , "Something else went wrong on the second try!"
          End If
      End If
      On Error Goto 0
    Loop While Retry

---

NOTE: That's some ugly code, but it's just to quickly test whether re-opening the connection makes any difference at all.

Yes, that works.
If I close the connection and open it again, and I repeat the command, then the error is not raised again.

The problem occurs if I store a 6 mb blob in the table and if I use a trigger for an undo operation.

When the trigger is called, it does something which causes a 1 gb spike in memory usage for a split second.

My trigger is this:

Code:

---

CREATE TEMP TRIGGER IF NOT EXISTS pages_ut AFTER UPDATE ON pages BEGIN INSERT INTO undolog VALUES(NULL,'UPDATE pages SET PageGUID='||quote(old.PageGUID)||',PageLCID='||quote(old.PageLCID)||',PageDateTimeModified='||quote(old.PageDateTimeModified)||',PageIsStartPage='||quote(old.PageIsStartPage)||',PageDescription='||quote(old.PageDescription)||',PageOrigPageGUID='||quote(old.PageOrigPageGUID)||',PageOrigBookGUID='||quote(old.PageOrigBookGUID)||',PageBookGUID='||quote(old.PageBookGUID)||',PageJumpBackMode='||quote(old.PageJumpBackMode)||',PageBackgroundImageBinData='||quote(old.PageBackgroundImageBinData)||',PageBackgroundImageStretchMode='||quote(old.PageBackgroundImageStretchMode)||',PageBackgroundImageUse='||quote(old.PageBackgroundImageUse)||',PagePreviewBinData='||quote(old.PagePreviewBinData)||',PageFunctionExEnter='||quote(old.PageFunctionExEnter)||',PageFunctionExLeave='||quote(old.PageFunctionExLeave)||',PageTextMacros='||quote(old.PageTextMacros)||',PageScanGroups='||quote(old.PageScanGroups)||',Pag
eWidth='||quote(old.PageWidth)||',PageHeight='||quote(old.PageHeight)||',PageWorkSpaceHeight='||quote(old.PageWorkSpaceHeight)||',PageAlignment='||quote(old.PageAlignment)||',PageJumpCondition='||quote(old.PageJumpCondition)||',PageJoystickStartCellGUID='||quote(old.PageJoystickStartCellGUID)||',pagetitleOriginal='||quote(old.pagetitleOriginal)||',pagetitleOriginalLCID='||quote(old.pagetitleOriginalLCID)||',pagetitleOriginalAssembled='||quote(old.pagetitleOriginalAssembled)||',pagetitleCurrentTranslation='||quote(old.pagetitleCurrentTranslation)||',pagetitleCurrentLCID='||quote(old.pagetitleCurrentLCID)||',pagetitleCurrentOriginal='||quote(old.pagetitleCurrentOriginal)||',pagetitleCurrentOriginalLCID='||quote(old.pagetitleCurrentOriginalLCID)||',pageTag='||quote(old.pageTag)||',pageEnterSpeakText='||quote(old.pageEnterSpeakText)||',pageEnterSpeakType='||quote(old.pageEnterSpeakType)||',pageEnterSpeakTranslationID='||quote(old.pageEnterSpeakTranslationID)||',pageLeaveSpeakText='||quote(old.pageLeaveSpeakText)
||',pageLeaveSpeakType='||quote(old.pageLeaveSpeakType)||',pageLeaveSpeakTranslationID='||quote(old.pageLeaveSpeakTranslationID)||',pageTemplateID='||quote(old.pageTemplateID)||',pagethumbdirty='||quote(old.pagethumbdirty)||',pageIsAllowedForRandomPageJumps='||quote(old.pageIsAllowedForRandomPageJumps)||',pageSwitchOptions='||quote(old.pageSwitchOptions)||',pageRandomPageGroup='||quote(old.pageRandomPageGroup)||',pagefrom='||quote(old.pagefrom)||',pageto='||quote(old.pageto)||',pagewords='||quote(old.pagewords)||',pageBackgroundMode='||quote(old.pageBackgroundMode)||',pageGradientColor1='||quote(old.pageGradientColor1)||',pageGradientColor2='||quote(old.pageGradientColor2)||',pageGradientAlignment='||quote(old.pageGradientAlignment)||',pageSpeakAccordingTo='||quote(old.pageSpeakAccordingTo)||',pagetagid='||quote(old.pagetagid)||',pageindividualdesign='||quote(old.pageindividualdesign)||',pagecelldistance='||quote(old.pagecelldistance)||',pageInternalAutocorrection='||quote(old.pageInternalAutocorrection)||',
pagetype='||quote(old.pagetype)||',pagegrammarcatmain='||quote(old.pagegrammarcatmain)||',pageappearanceguid='||quote(old.pageappearanceguid)||',pageshowinworkspace='||quote(old.pageshowinworkspace)||',pageshowfurthercell='||quote(old.pageshowfurthercell)||' WHERE rowid='||old.rowid);END

---

If I want to undo an operation, I say something like this (please note that this Undo operation is not part of the problem, I'm just showing it to explain what the "undo" triggers are for and how I use them:

Code:

---

    Dim r As dhRichClient3.cRecordset
    Set r = cnUser.OpenRecordset("SELECT sql,seq FROM undolog WHERE seq>" & uToPoint & " ORDER BY seq DESC")
   
    Dim Cmd As dhRichClient3.cCommand
   
    cnUser.BeginTrans
    Debug.Print r.RecordCount
    Do While Not r.EOF
        Dim sThis$
        sThis = r!SQL
        cnUser.Execute sThis
    r.MoveNext
    Loop
   
    cnUser.CommitTrans

---

Might be worth trying EXPLAIN QUERY PLAN. Something like this:

Code:

---

  Dim Rs as cRecordset
  set Rs = cnUsers.OpenRecordset("EXPLAIN QUERY PLAN UPDATE pages SET pagedatetimemodified='PUT A DATE HERE',pagethumbdirty=1 WHERE pageguid='PUT A PAGE GUID HERE' AND pagebookguid='PUT A BOOK GUID HERE'")
    ' Loop through all fields & records in Rs and print the results then post them here

---

Here is my EXPLAIN QUERY PLAN code:

Code:

---

    Dim Cmd As dhRichClient3.cCommand
    Set Cmd = cnUser.CreateCommand("UPDATE pages SET pagedatetimemodified=?,pagethumbdirty=? WHERE pageguid=? AND pagebookguid=?")

    Cmd.SetDate 1, Now
    Cmd.SetBoolean 2, True
    Cmd.SetText 3, tPage.Guid
    Cmd.SetText 4, tBook.Guid

    Cmd.Execute
   
    Dim r As dhRichClient3.cRecordset
    Set r = cnUser.OpenRecordset("EXPLAIN QUERY PLAN " & Cmd.SQL)
   
    Dim s$
    Dim l&
    s = ""
    For l = 1 To r.RecordCount
        Dim c&
        For c = 0 To r.Fields.Count - 1
            s = s & vbNewLine & r.Fields(c).Name & ": " & r.Fields(c).value
        Next c
    r.MoveNext
    Next l
   
    Debug.Print s

---

It says:

Quote:

---

order: 0
from: 0
detail: TABLE pages WITH INDEX idx_PageBookGuid_pages

---

So this Trigger is INSERTing all the individual fields values from one record into a single field (as a replayable SQL statement):

Code:

---

        CREATE
                TEMP TRIGGER IF NOT EXISTS pages_ut AFTER UPDATE
                                        ON pages BEGIN INSERT
                                        INTO
                                                undolog
                                        VALUES (
                                                NULL
                                                ,'UPDATE pages SET PageGUID=' || quote (old.PageGUID) || ',PageLCID=' || quote (old.PageLCID) || ',PageDateTimeModified=' || quote (old.PageDateTimeModified) || ',PageIsStartPage=' || quote (old.PageIsStartPage) || ',PageDescription=' || quote (old.PageDescription) || ',PageOrigPageGUID=' || quote (old.PageOrigPageGUID) || ',PageOrigBookGUID=' || quote (old.PageOrigBookGUID) || ',PageBookGUID=' || quote (old.PageBookGUID) || ',PageJumpBackMode=' || quote (old.PageJumpBackMode) || ',PageBackgroundImageBinData=' || quote (old.PageBackgroundImageBinData) || ',PageBackgroundImageStretchMode=' || quote (old.PageBackgroundImageStretchMode) || ',PageBackgroundImageUse=' || quote (old.PageBackgroundImageUse) || ',PagePreviewBinData=' || quote (old.PagePreviewBinData) || ',PageFunctionExEnter=' || quote (old.PageFunctionExEnter) || ',PageFunctionExLeave=' || quote (old.PageFunctionExLeave) || ',PageTextMacros=' || quote (old.PageTextMacros) || ',PageScanGroups=' || quote (old.PageScanGroups) || ',PageWidth=' || quote (old.PageWidth) || ',PageHeight=' || quote (old.PageHeight) || ',PageWorkSpaceHeight=' || quote (old.PageWorkSpaceHeight) || ',PageAlignment=' || quote (old.PageAlignment) || ',PageJumpCondition=' || quote (old.PageJumpCondition) || ',PageJoystickStartCellGUID=' || quote (old.PageJoystickStartCellGUID) || ',pagetitleOriginal=' || quote (old.pagetitleOriginal) || ',pagetitleOriginalLCID=' || quote (old.pagetitleOriginalLCID) || ',pagetitleOriginalAssembled=' || quote (old.pagetitleOriginalAssembled) || ',pagetitleCurrentTranslation=' || quote (old.pagetitleCurrentTranslation) || ',pagetitleCurrentLCID=' || quote (old.pagetitleCurrentLCID) || ',pagetitleCurrentOriginal=' || quote (old.pagetitleCurrentOriginal) || ',pagetitleCurrentOriginalLCID=' || quote (old.pagetitleCurrentOriginalLCID) || ',pageTag=' || quote (old.pageTag) || ',pageEnterSpeakText=' || quote (old.pageEnterSpeakText) || ',pageEnterSpeakType=' || quote (old.pageEnterSpeakType) || ',pageEnterSpeakTranslationID=' || quote (old.pageEnterSpeakTranslationID) || ',pageLeaveSpeakText=' || quote (old.pageLeaveSpeakText) || ',pageLeaveSpeakType=' || quote (old.pageLeaveSpeakType) || ',pageLeaveSpeakTranslationID=' || quote (old.pageLeaveSpeakTranslationID) || ',pageTemplateID=' || quote (old.pageTemplateID) || ',pagethumbdirty=' || quote (old.pagethumbdirty) || ',pageIsAllowedForRandomPageJumps=' || quote (old.pageIsAllowedForRandomPageJumps) || ',pageSwitchOptions=' || quote (old.pageSwitchOptions) || ',pageRandomPageGroup=' || quote (old.pageRandomPageGroup) || ',pagefrom=' || quote (old.pagefrom) || ',pageto=' || quote (old.pageto) || ',pagewords=' || quote (old.pagewords) || ',pageBackgroundMode=' || quote (old.pageBackgroundMode) || ',pageGradientColor1=' || quote (old.pageGradientColor1) || ',pageGradientColor2=' || quote (old.pageGradientColor2) || ',pageGradientAlignment=' || quote (old.pageGradientAlignment) || ',pageSpeakAccordingTo=' || quote (old.pageSpeakAccordingTo) || ',pagetagid=' || quote (old.pagetagid) || ',pageindividualdesign=' || quote (old.pageindividualdesign) || ',pagecelldistance=' || quote (old.pagecelldistance) || ',pageInternalAutocorrection=' || quote (old.pageInternalAutocorrection) || ',pagetype=' || quote (old.pagetype) || ',pagegrammarcatmain=' || quote (old.pagegrammarcatmain) || ',pageappearanceguid=' || quote (old.pageappearanceguid) || ',pageshowinworkspace=' || quote (old.pageshowinworkspace) || ',pageshowfurthercell=' || quote (old.pageshowfurthercell) || ' WHERE rowid=' || old.rowid
                                        )
        ;
        END

---

It might be better to do an INSERT...SELECT on a second table with a duplicate schema. That is unless there are different undo-able action types, which makes things a bit trickier. Perhaps instead of inserting the binary data in the field, you could add an "undoblobs" table and insert the big binary data there. Your massive UNDO update statement could grab the binary data from the second undoblobs table by ID.

I also have a trigger that is connected with a DELETE sql statement:

Code:

---

CREATE TEMP TRIGGER IF NOT EXISTS pages_dt BEFORE DELETE ON pages BEGIN INSERT INTO undolog VALUES(NULL,'INSERT INTO pages(rowid,PageGUID,PageLCID,PageDateTimeModified,PageIsStartPage,PageDescription,PageOrigPageGUID,PageOrigBookGUID,PageBookGUID,PageJumpBackMode,PageBackgroundImageBinData,PageBackgroundImageStretchMode,PageBackgroundImageUse,PagePreviewBinData,PageFunctionExEnter,PageFunctionExLeave,PageTextMacros,PageScanGroups,PageWidth,PageHeight,PageWorkSpaceHeight,PageAlignment,PageJumpCondition,PageJoystickStartCellGUID,pagetitleOriginal,pagetitleOriginalLCID,pagetitleOriginalAssembled,pagetitleCurrentTranslation,pagetitleCurrentLCID,pagetitleCurrentOriginal,pagetitleCurrentOriginalLCID,pageTag,pageEnterSpeakText,pageEnterSpeakType,pageEnterSpeakTranslationID,pageLeaveSpeakText,pageLeaveSpeakType,pageLeaveSpeakTranslationID,pageTemplateID,pagethumbdirty,pageIsAllowedForRandomPageJumps,pageSwitchOptions,pageRandomPageGroup,pagefrom,pageto,pagewords,pageBackgroundMode,pageGradientColor1,pageGradientColo
r2,pageGradientAlignment,pageSpeakAccordingTo,pagetagid,pageindividualdesign,pagecelldistance,pageInternalAutocorrection,pagetype,pagegrammarcatmain,pageappearanceguid,pageshowinworkspace,pageshowfurthercell) VALUES('||old.rowid||','||quote(old.PageGUID)||','||quote(old.PageLCID)||','||quote(old.PageDateTimeModified)||','||quote(old.PageIsStartPage)||','||quote(old.PageDescription)||','||quote(old.PageOrigPageGUID)||','||quote(old.PageOrigBookGUID)||','||quote(old.PageBookGUID)||','||quote(old.PageJumpBackMode)||','||quote(old.PageBackgroundImageBinData)||','||quote(old.PageBackgroundImageStretchMode)||','||quote(old.PageBackgroundImageUse)||','||quote(old.PagePreviewBinData)||','||quote(old.PageFunctionExEnter)||','||quote(old.PageFunctionExLeave)||','||quote(old.PageTextMacros)||','||quote(old.PageScanGroups)||','||quote(old.PageWidth)||','||quote(old.PageHeight)||','||quote(old.PageWorkSpaceHeight)||','||quote(old.PageAlignment)||','||quote(old.PageJumpCondition)||','||quote(old.PageJoystickStartCellGUID)
||','||quote(old.pagetitleOriginal)||','||quote(old.pagetitleOriginalLCID)||','||quote(old.pagetitleOriginalAssembled)||','||quote(old.pagetitleCurrentTranslation)||','||quote(old.pagetitleCurrentLCID)||','||quote(old.pagetitleCurrentOriginal)||','||quote(old.pagetitleCurrentOriginalLCID)||','||quote(old.pageTag)||','||quote(old.pageEnterSpeakText)||','||quote(old.pageEnterSpeakType)||','||quote(old.pageEnterSpeakTranslationID)||','||quote(old.pageLeaveSpeakText)||','||quote(old.pageLeaveSpeakType)||','||quote(old.pageLeaveSpeakTranslationID)||','||quote(old.pageTemplateID)||','||quote(old.pagethumbdirty)||','||quote(old.pageIsAllowedForRandomPageJumps)||','||quote(old.pageSwitchOptions)||','||quote(old.pageRandomPageGroup)||','||quote(old.pagefrom)||','||quote(old.pageto)||','||quote(old.pagewords)||','||quote(old.pageBackgroundMode)||','||quote(old.pageGradientColor1)||','||quote(old.pageGradientColor2)||','||quote(old.pageGradientAlignment)||','||quote(old.pageSpeakAccordingTo)||','||quote(old.pagetagid)|
|','||quote(old.pageindividualdesign)||','||quote(old.pagecelldistance)||','||quote(old.pageInternalAutocorrection)||','||quote(old.pagetype)||','||quote(old.pagegrammarcatmain)||','||quote(old.pageappearanceguid)||','||quote(old.pageshowinworkspace)||','||quote(old.pageshowfurthercell)||')');END

---

And here is the undlog create statement:

Code:

---

cnUser.Execute "CREATE TEMP TABLE IF NOT EXISTS undolog(seq INTEGER PRIMARY KEY, sql TEXT)"

---

Could you show me how to implement this undoblobs idea?

I'm just stepping away from my computer for bit, but the idea would be to have 2 undo tables:

undolog and undoblobs.

Instead of inserting the binary/blob/image data inside the undolog sql field, you stick it in the undoblobs table. Your undolog SQL statement would then pull the blobs from the undoblobs table. I'll try to post an example a bit later.

In my compilation-setup I influence certain defaults via SQLite-PreProcessor Flags -
and one of them is (IIRC) - that the "SQLite temp_store" is by default set to "MEMORY".
https://www.sqlite.org/pragma.html#pragma_temp_store

That means, that temporary indexes and temp-tables are (by default) created "InMemory" -
which in nearly all scenarios works well - and performs best.

Your case (with the large "backup-blobs") might be a bit more "special" (in terms of more requested temp-memory) -
so you might want to make an attempt, to switch the engine (on that DB-Connection) to a "File-based temp-store" explicitely via:
MyCnn.Execute "PRAGMA temp_store = FILE"

If that doesn't help - the next place to look would be in your own code -
explicitely pre-checking for potentially empty ByteArrays (which have an Ubound of -1).

--------------------------------------------

That said - dhRichClient3 is quite old (about 13 years now), so in the meantime a whole lot of stuff -
(in the SQLite-code itself, as well as in the RC-wrapper) - was optimized and/or "hardened".

The newer COM-wrapper-versions should code-wise be compatible with your own sources, and thus -
switching your Project-Ref to e.g. a fresh version of the RC6 -
(working on a copy of your DB and your Code-Sources, checking whether the behaviour can still be observed),
is worth a try as well.

Olaf

Here's a example of how you could split the blobs out to another undo table. I'm sure there's a better way to do this, but my code should be OK for demonstrating the idea. I recommend playing around with the SQL below in an app like DB Browser for SQLite.

Code:

---

create table if not exists pages (id integer primary key, title text, page_thumb blob, page_full blob);
create table if not exists undolog (id integer primary key, pageid integer, updatecmd text);
create table if not exists undoblobs (id integer primary key, undoid integer, page_thumb blob, page_full blob);

create trigger if not exists trg_update_pages before update on pages begin
        insert into undolog
        values (null, old.id, 'UPDATE pages SET title=' || quote(old.title) || ',');
end;

create trigger if not exists trg_insert_undolog after insert on undolog BEGIN
        update undolog set updatecmd = updatecmd ||
        'page_thumb=(select page_thumb from undoblobs where undoid=' || new.id || '),
          page_full=(select page_full from undoblobs where undoid=' || new.id || ')
          WHERE id=' || pageid WHERE id=new.id;
        insert into undoblobs
        values (null,
        new.id,
        (select page_thumb from pages where id=new.pageid),
        (select page_full from pages where id=new.pageid)
        );
end;

---

There is at least one problem with the above - after running the trg_update_pages trigger when undoing it will perform an update that will insert a new undolog/undoblobs record. I'm not sure how you are avoiding that in your existing program, or if that is behaviour you want.

Btw, you can reduce `trg_update_pages` to `INSERT INTO undolog(pageid) VALUES (old.id)` and outsource complete build up of `updatecmd` value to `trg_insert_undolog` instead of partially constructing it and then updating it more in `trg_insert_undolog`

cheers,
</wqw>

Thanks @wqweto - I knew there was a better way to go about it.

In fact, I almost ended my post with "Experiment with this or wait for Olaf or wqweto to reply with a better way to it" ;)

Thanks to all who contributed!