Results 1 to 19 of 19

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

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2017
    Posts
    344

    Question 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!
    Last edited by tmighty2; Jan 5th, 2022 at 09:04 PM.

  2. #2
    PowerPoster
    Join Date
    Aug 2010
    Location
    Canada
    Posts
    2,414

    Re: vbRichClient3 "Cannot execute SQL-Statement: out of memory"

    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.

  3. #3

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2017
    Posts
    344

    Re: vbRichClient3 "Cannot execute SQL-Statement: out of memory"

    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.

  4. #4

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2017
    Posts
    344

    Re: vbRichClient3 "Cannot execute SQL-Statement: out of memory"

    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.

  5. #5
    PowerPoster
    Join Date
    Aug 2010
    Location
    Canada
    Posts
    2,414

    Re: vbRichClient3 "Cannot execute SQL-Statement: out of memory"

    Quote Originally Posted by tmighty2 View Post
    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?

  6. #6

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2017
    Posts
    344

    Re: vbRichClient3 "Cannot execute SQL-Statement: out of memory"

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

  7. #7
    PowerPoster
    Join Date
    Aug 2010
    Location
    Canada
    Posts
    2,414

    Re: vbRichClient3 "Cannot execute SQL-Statement: out of memory"

    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.

  8. #8

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2017
    Posts
    344

    Re: vbRichClient3 "Cannot execute SQL-Statement: out of memory"

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

  9. #9

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2017
    Posts
    344

    Re: vbRichClient3 "Cannot execute SQL-Statement: out of memory"

    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
    Last edited by tmighty2; Jan 5th, 2022 at 08:55 PM.

  10. #10
    PowerPoster
    Join Date
    Aug 2010
    Location
    Canada
    Posts
    2,414

    Re: vbRichClient3 "Cannot execute SQL-Statement: out of memory"

    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

  11. #11

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2017
    Posts
    344

    Re: vbRichClient3 "Cannot execute SQL-Statement: out of memory"

    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:
    order: 0
    from: 0
    detail: TABLE pages WITH INDEX idx_PageBookGuid_pages
    Last edited by tmighty2; Jan 5th, 2022 at 09:03 PM.

  12. #12
    PowerPoster
    Join Date
    Aug 2010
    Location
    Canada
    Posts
    2,414

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

    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.

  13. #13

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2017
    Posts
    344

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

    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?
    Last edited by tmighty2; Jan 5th, 2022 at 09:28 PM.

  14. #14
    PowerPoster
    Join Date
    Aug 2010
    Location
    Canada
    Posts
    2,414

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

    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.

  15. #15
    PowerPoster
    Join Date
    Jun 2013
    Posts
    7,219

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

    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

  16. #16
    PowerPoster
    Join Date
    Aug 2010
    Location
    Canada
    Posts
    2,414

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

    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.

  17. #17
    PowerPoster wqweto's Avatar
    Join Date
    May 2011
    Location
    Sofia, Bulgaria
    Posts
    5,121

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

    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>

  18. #18
    PowerPoster
    Join Date
    Aug 2010
    Location
    Canada
    Posts
    2,414

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

    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"

  19. #19

    Thread Starter
    Hyperactive Member
    Join Date
    Jul 2017
    Posts
    344

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

    Thanks to all who contributed!

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width