Saving data from non-dbaware control using TDataset/TDatasource events
lazarus / 16-Databases

----------------------------------------------------------------------
fpcoder · 2016-06-21 20:16

How would you use events to save changed text of the TMemo to DB here?
(Later I want to use TSynEdit so TDBMemo is not an option)

It is a master/detail setup with two tables (actually
categories/items) and a third table where I put the real content
(text). When scrolling items, the memo text is changed, but it must be
automatically saved before a new record replaces the text.

I thought qryContent's BeforeScroll event could be used for this
saving, but it never fires. TDBLookupListBoxes are used to select top
items. ScrollListDataSet is true and when a "detail" item is selected,
the non-dbaware TMemo is populated by the qryDetails OnDataChange
event as you see below.  None of my attempts to use events was fully
successful. Attached project automatically creates a small sample
database for testing.

Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i { TfrmMain
} procedure TfrmMain.dsDetailsDataChange(Sender: TObject; Field:
TField);begin  Memo.Lines.Text :=
DM.qryContent.FieldByName('content_text').AsString; 
DM.TextIsModified:=False;  //Resets TextIsModified made by
Memo.OnChangeend; procedure TfrmMain.MemoChange(Sender:
TObject);begin  if not DM.TextIsModified then   
DM.TextIsModified:=True;end; 
Code: Pascal  [Select][+][-]window.onload = function(){var x1 =
document.getElementById("main_content_section"); if (x1) { var x =
document.getElementsByClassName("geshi");for (var i = 0; i { TDM
} procedure TDM.qryContentAfterPost(DataSet: TDataSet);begin 
TX.CommitRetaining;  TextIsModified:=False;end; procedure
TDM.qryDetailsBeforeScroll(DataSet: TDataSet);begin  if TextIsModified
then  begin    qryContent.Edit;    qryContent.FieldByName('content_tex
t').AsString:=frmMain.Memo.Lines.Text;    qryContent.Post;  end;end; {
Create sample database in program directory..}procedure
TDM.DataModuleCreate(Sender: TObject);var  DBFile: string;begin 
Conn.Close(True);  Conn.Params.Text:='FOREIGN_KEYS=ON';  //Needed for
cascading deletes   DBFile:=ExtractFilePath(Application.ExeName)
+'db.db3';  if FileSize(DBFile) = 0 then  begin   
DeleteFile(DBFile);    ShowMessage('Deleted ZERO size database
file');  end;   if not FileExists(DBFile) then  try   
Conn.ExecuteDirect('CREATE TABLE master (' +                     
 'master_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, ' +          
           'master_pid INTEGER NOT NULL, ' +                     
 'master_name VARCHAR(40) ' +                       ')');   
DM.Conn.Transaction.Commit;    DM.Conn.Close;    
Conn.ExecuteDirect('CREATE TABLE details (' +                     
 'detail_id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL, ' +          
           'detail_name VARCHAR(30), ' +                     
 'master_id INTEGER NOT NULL REFERENCES master ON DELETE CASCADE' +  
                   ')');    DM.Conn.Transaction.Commit;   
DM.Conn.Close;     Conn.ExecuteDirect('CREATE TABLE content (' +     
                'content_id INTEGER PRIMARY KEY AUTOINCREMENT NOT
NULL, ' +                       'content_text TEXT, ' +              
       'detail_id INTEGER NOT NULL REFERENCES details ON DELETE
CASCADE' +                       ')');    DM.Conn.Transaction.Commit; 
  DM.Conn.Close;     DM.Conn.ExecuteDirect(Format('INSERT INTO
master(master_pid, master_name) VALUES(%d,%s)',
[0,QuotedStr('General')]));    DM.Conn.ExecuteDirect(Format('INSERT
INTO master(master_pid, master_name) VALUES(%d,%s)',
[0,QuotedStr('Specific')]));    DM.Conn.Transaction.Commit;   
DM.Conn.Close;     DM.Conn.ExecuteDirect(Format('INSERT INTO
details(master_id, detail_name) VALUES(%d,%s)', [1,QuotedStr('General
item 1')]));    DM.Conn.ExecuteDirect(Format('INSERT INTO
details(master_id, detail_name) VALUES(%d,%s)', [1,QuotedStr('General
item 2')]));    DM.Conn.ExecuteDirect(Format('INSERT INTO
details(master_id, detail_name) VALUES(%d,%s)', [2,QuotedStr('Specific
item 1')]));    DM.Conn.Transaction.Commit;    DM.Conn.Close;    
DM.Conn.ExecuteDirect(Format('INSERT INTO content(detail_id,
content_text) VALUES(%d,%s)', [1,QuotedStr('Some text...')]));   
DM.Conn.ExecuteDirect(Format('INSERT INTO content(detail_id,
content_text) VALUES(%d,%s)', [2,QuotedStr('Some other text...')])); 
 DM.Conn.ExecuteDirect(Format('INSERT INTO content(detail_id,
content_text) VALUES(%d,%s)', [3,QuotedStr('A specific text...')])); 
 DM.Conn.Transaction.Commit;    DM.Conn.Close;   except    on
E:Exception do    begin      TX.Rollback;      ShowMessage( E.Message
);    end;  end;   qryMaster.Open;  qryDetails.Open; 
qryContent.Open;end;

----------------------------------------------------------------------
rvk · 2016-06-21 20:34

Wouldn't it be better to directly create a TDBSynEdit?
(a wrapper around the TSynEdit which is data-aware)

You just need to create a descendant with FDataLink:
http://delphidabbler.com/tips/194
(you could also look at TDBMemo how it's done)

But hasn't anyone done this already?

Edit: Yes... SynEdit should already have a TDBSynEdit.
Why is this not in the Lazarus-port...? Or am I looking in the wrong
place?
https://sourceforge.net/p/synedit/code/HEAD/tree/SynEdit/Source/SynDBE
dit.pas

----------------------------------------------------------------------
fpcoder · 2016-06-22 00:13

Yes, but at the moment I would like to solve the problem that has so
far eluded me: why does qryContent.AfterScroll fire but never
qryContent.BeforeScroll? Thinking about it, it should be impossible to
have "after" without a "before"? :-)

BeforeScroll is missing when navigating with TDBLookupListBox.
Scrolling the dataset manually triggers BeforeScroll just fine:

Code: [Select]if DM.qryContent.EOF then
  DM.qryContent.First
else
  DM.qryContent.Next;

----------------------------------------------------------------------
rvk · 2016-06-22 07:03

Ok, so I will look at the TDBSynEdit later.

For your qryDetailsBeforeScroll()... it fires here correctly.

What are the exact steps you have a problem with?

Quote from: kapibara on June 22, 2016, 02:13:07 am... why does
qryContent.AfterScroll fire but never qryContent.BeforeScroll?
Thinking about it, it should be impossible to have "after" without a
"before"? :-)
B.T.W. To be clear. You are working with a qryDetailsBeforeScroll()...
NOT a qryContent.BeforeScroll() !!

----------------------------------------------------------------------
fpcoder · 2016-06-22 19:36

Now I focus on trying to save from qryContent or dsContent events.
qryContent.BeforeScroll could be used to test and save but it never
fires when you scroll items in the Detail Items DBLookupListBox.

If you give qryContent a BeforeScroll and AfterScroll event with a
ShowMessage inside, and then scroll items in the Detail Items
DBLookupListBox you will see that AfterScroll fires but BeforeScroll
doesn't, and thats a big mystery to me.

----------------------------------------------------------------------
rvk · 2016-06-22 19:58

Quote from: kapibara on June 22, 2016, 09:36:35 pm... you will see
that AfterScroll fires but BeforeScroll doesn't, and thats a big
mystery to me.
The reason BeforeScroll doesn't fire for content is that it doesn't
actually scroll. It does an internal Close/Open on that dataset and
then a positioning (which is why AfterScroll is run). Why it does that
for content (which is connected to detail) and not detail (which is
connected to master), I don't know.

Edit: Argh... the BeforeScroll of detail is triggered by
TDBLookupListBox. NOT by the scrolling of master. So... moving master
does NOT trigger a BeforeScroll in detail and moving detail does NOT
trigger a BeforeScroll in content. It does trigger an AfterScroll in
both cases because of the internalClose/Open for refreshing the
details.

And the reason BeforeScroll works for detail in your case is because
you have a TDBLookupListBox for detail (which retrieves .First and
.Last so it triggers Before and Afterscroll multiple times).

Please note... that if you remove the TDBLookupListBoxes from your
application... your qryDetailsBeforeScroll also stops working. So if
you want to work only with .Next in your master you need to take that
into account and save the memo another way !!!!

(See... a TDBMemo / TDBSynEdit would be much simpler  )