[Solved] SQLDB and MVC...

[gelinp]

Good morning !

I've got a form a liitle bit complex, with only one database connexion (and one transaction).

This form is made of :
* 2 readonly Datasets (list selectors) ;
* 3 Edit datasets (depending from the 2 firsts  thanks to  datasources connexions).

1. I tested with only one database connexion and it was OK, but when I commit one of the 3 edit datasets there is a reset of all Datasets so I have to save bookmarks then open all 5 datasets and refresh the interface...

It's a little bit complicated... So I was looking for a secpond maner...

2. I tested to use 2 databases connexions (and 2 transactions), one for the first 2 datasets readonly and the second for the last 3 datasets in edit mode. But I've got a problem : my first Edit Dataset use a paremter connected to the first datasource of the readonly dataset,  and the connexion dosn't run.... and then the edit interface stay empty !

So what do you suggest to make MVC suitable with SQLDB and complex forms ?

Thank you for your help !

[SymbolicFrank]

Yes, that's how it works.

The easiest way is using the events of the datasets, like AfterUpdate, and setting the parameters for the other sets.

I tend to make a procedure that sets a bookmark, closes all the datasets, opens the first one, goes to the bookmark, fills in the parameter(s) for the second one, opens that, fills in the parameters for the third one, open that, etc.

[gelinp]

Yes you do, I'm very interested to download your procedure if you share it    I suggest you to make a MasterDetail object to encapsulate it and connecte each one to an other. I will try myself to do it...

My problem now is that the my commit doesn't work, I understand this due to the applyupdate which crash the application, but I can't know why... I verified there is Edit mode activated, and all is active... At this time I'm debugging again ...

[SymbolicFrank]

Such a procedure is very application-specific, so I don't have a default one.

The reason I do it like that is, that it prevents problems like you have, by having a lot of events that all try to "fix" things.

The master is where you set the bookmark, all the children is where you set the parameters.

[gelinp]

I'm asking myself is my algorithm is good to update. I understanded it was something like that (short resume):

qyupdate = TZQuery.Create(nil);
qyupdate.datasource := DS;
qyupdate.SQL.text := 'SELECT ...'
qyupdate.edit;
{Fields affectations...}
qyupdate.applyupdate;
transaction.commit;

But this shema failed with a database error message on applyupdate... I 'm reading the book "Getting Started with Lazarus and Free PAscal" explaining an other solution, like that :

qyupdate = TZQuery.Create(nil);
qyupdate.datasource := DS;
qyupdate.SQL.text := 'SELECT ...'
qyupdate.Insert;
{Fields affectations...}
qyupdate.Updaterecord;
Post;

I don't understand why first schema is wrong ?

[mangakissa]

You can't use applyupdates if the dataset is in edit mode. First use post(). This tells tbufdataset there's a record to be updated end create an query to update the record in your table.

[gelinp]

Ok, so now my code is like that :

Code: Pascal  [Select][+][-]

1. procedure TFormMain.EnregistrerClick(Sender: TObject);
2. var
3.   CurID : Integer;
4. begin
5.   if (DataModule1.SQLDetailSeances.Active) then
6.   begin
7.     CurID := SaveRecord(DataModule1.SQLDetailSeances, 'PKseance');
8.     SQLEditSeance.Post;
9.     SQLEditSeance.ApplyUpdates;
10.    SQLTransaction2.Commit;
11. ...

But I've got an error message on applyupdate that said : "operation cannot be performed on an inactive dataset".

I can't understand why my dataset is ainactive, may be it's this code, may be it's an initialisation problem... I have to looking for this inactive reason...

Thank you for your help !

[Graeme]

The joys of DataSet based programming and littering your forms with business rules and millions of events. I'm assuming by MVC you mean Model-View-Controller.

Rather go for a clean separation between the various layers (storage, business rules, UI) of your application. Try tiOPF and Model-GUI-Mediator (very similar to MVC or MVP). The tiOPF framework takes care of the storage and business rule layers. It also hides the need to work with SQL and transactions directly. You can select which database components you want to use, which database servers (or text storage) to use and if you want Client/Server or 3-tier based applications - all controlled via simple compiler defines (no need to change your application code). You as a programmer only work with actual objects (eg: TPerson, TPersonList etc). The Model-GUI-Mediator (also included in the tiOPF repository) manages the UI and allows standard edit controls (eg: TEdit, TLabel) to become "object  aware" and with two-way synchronisation. You can even have multiple UI controls observing the same property of an object. Your forms units end up being very empty, yet fully functional.

tiOPF:  http://www.tiopf.com
tiOPF wiki page for FPC and Lazarus:   http://wiki.freepascal.org/tiOPF

An article I wrote explaining Model-GUI-Mediator (MGM) - though the MGM implementation include with tiOPF is magnitudes more advance than what the article describes.
  http://geldenhuys.co.uk/articles/

For further support or information on tiOPF, I welcome you to join the dedicated support newsgroup for further discussions. Details can be found here:  http://tiopf.sourceforge.net/Support.shtml

[gelinp]

I know about TIOPF but my software is small and I wouldn't read a big book again...

What I need is only the right schema to update into a dataset (master/detail).

[balazsszekely]

But I've got an error message on applyupdate that said : "operation cannot be performed on an inactive dataset".

Did you use Commit before?  Because Commit will close the transaction. Switch to CommitRetaining instead.

SQLTransactionX.CommitRetaining;

[Graeme]

I know about TIOPF but my software is small and I wouldn't read a big book again...

tiOPF is equally suited for small and large applications. The tiOPFMapper tool makes it a breeze to get a working BOM and visitor classes written (fully compilable units) - it can literally be done in a minute or two. Also just because an application is small, doesn't mean you don't have to design it well. ;-)

As for your usage of datasets and manual transaction handling and so forth, I unfortunately can't give any further advice. I haven't used that method of writing database applications in over 10 years.

[gelinp]

I founded the reason of my error into Wiki pascal Tutorial 2 : Editing chapter. There the code below, explaining the reason was the transaction not started, so the error message 'inactive dataset' is not about the dataset itself !

Code: Pascal  [Select][+][-]

1. procedure Tform1.SaveChanges;
2. // Saves edits done by user, if any.
3. begin
4.   try
5.     if SQLTransaction1.Active then
6.     // Only if we are within a started transaction;
7.     // otherwise you get "Operation cannot be performed on an inactive dataset"
8.     begin
9.       SQLQuery1.ApplyUpdates; //Pass user-generated changes back to database...
10.       SQLTransaction1.Commit; //... and commit them using the transaction.
11.       //SQLTransaction1.Active now is false
12.     end;
13.   except
14.   on E: EDatabaseError do
15.     begin
16.       MessageDlg('Error', 'A database error has occurred. Technical error message: ' +
17.         E.Message, mtError, [mbOK], 0);
18.       Edit1.Text := '';
19.     end;
20.   end;
21. end;

So, below is my code. I can't understand why the second assertion failed, telling me that Post inactive the transaction ! Why Post close the transaction before ApplyUpdates ?

Code: Pascal  [Select][+][-]

1. procedure TFormMain.EnregistrerClick(Sender: TObject);
2. var
3.   CurID : Integer;
4. begin
5.  
6.   if (DataModule1.SQLDetailSeances.Active and DataModule1.SQLTransaction2.Active) then
7.   begin
8.     CurID := SaveRecord(DataModule1.SQLDetailSeances, 'PKseance');
9.     Assert(DataModule1.SQLTransaction2.Active); // TRUE, it's OK ...
10.     DataModule1.SQLEditSeance.Post;
11.     Assert(DataModule1.SQLTransaction2.Active); // FALSE ! Why Post close the transaction before ApplyUpdates ?
12.     DataModule1.SQLEditSeance.ApplyUpdates;
13.     DataModule1.SQLTransaction2.Commit;
14.  
15.  
16.     DataModule1.SQLMasterSequences.Open;
17.     DataModule1.SQLDetailSeances.Open;
18.     DataModule1.SQLDetailSeances.Refresh;
19.     LocateRecord(DataModule1.SQLDetailSeances, 'PKseance', CurID);
20.  
21.     //DataModule1.SQLEditSeance.ParamByName('PKseance').AsInteger := DataModule1.SQLDetailSeances.FieldByName('PKseance').AsInteger;
22.     DataModule1.SQLEditSeance.Open;
23.     DataModule1.SQLEditSeance.Refresh;
24.     PageControl1.Refresh;
25.   end;
26. end;

About tiOPF I know it will be a good think for me to help design MVC with my application. But there is a problem with framework like this, it's the pveread to learn it, even if I write few code I have to read all the book. And also if there is a bug with my code, this bug could run into the framework and then it will be difficult to get the source of the error...