-
Jan 5th, 2022, 06:50 PM
#1
Thread Starter
Hyperactive Member
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.
-
Jan 5th, 2022, 07:08 PM
#2
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.
-
Jan 5th, 2022, 07:10 PM
#3
Thread Starter
Hyperactive Member
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.
-
Jan 5th, 2022, 08:31 PM
#4
Thread Starter
Hyperactive Member
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.
-
Jan 5th, 2022, 08:36 PM
#5
Re: vbRichClient3 "Cannot execute SQL-Statement: out of memory"
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?
-
Jan 5th, 2022, 08:42 PM
#6
Thread Starter
Hyperactive Member
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.
-
Jan 5th, 2022, 08:47 PM
#7
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.
-
Jan 5th, 2022, 08:49 PM
#8
Thread Starter
Hyperactive Member
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.
-
Jan 5th, 2022, 08:52 PM
#9
Thread Starter
Hyperactive Member
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.
-
Jan 5th, 2022, 08:55 PM
#10
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
-
Jan 5th, 2022, 09:00 PM
#11
Thread Starter
Hyperactive Member
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.
-
Jan 5th, 2022, 09:07 PM
#12
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.
-
Jan 5th, 2022, 09:14 PM
#13
Thread Starter
Hyperactive Member
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.
-
Jan 5th, 2022, 09:42 PM
#14
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.
-
Jan 6th, 2022, 08:39 AM
#15
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
-
Jan 6th, 2022, 08:56 AM
#16
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.
-
Jan 6th, 2022, 10:24 AM
#17
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>
-
Jan 6th, 2022, 10:29 AM
#18
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"
-
Jan 10th, 2022, 06:57 PM
#19
Thread Starter
Hyperactive Member
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|