Recent

Author Topic: SQLdb, What is the order of events or How to validate a record before update  (Read 4146 times)

rodolfo

  • Newbie
  • Posts: 2
hi,

this is my first participation on this forum, I'm new to lazarus and pretend to write a small DB application using SQLite and SQLdb.

Did the http://wiki.freepascal.org/Lazarus_Database_Overview and the SQLdb TutorialX series (a very sweet idea but lacking the minimum to achieve a functional DB app) and still have many questions:

How to validate a record before update to the database?

How to cancel that update, being an existing record update or an a addition, if the validation fails?

How to cancel a navegation to another record if there are pending updates and the validation fails?

If there are calculated fields that depend on the user input, where do I make those  calculations prior to the post?

If there are calculated fields that need to be, well, 'calculated' when the record changes, where do I do that?

What is the order of the events?

Thank you in advance for your help.

valdir.marcos

  • Hero Member
  • *****
  • Posts: 1106
Hi Rodolfo.

You have many questions, I'll try to answer some of them as simple as I can since you didn't mention how much your understand about programming in general and about Delphi or Free Pascal and Lazarus in particular .

hi,

this is my first participation on this forum, I'm new to lazarus and pretend to write a small DB application using SQLite and SQLdb.

Did the http://wiki.freepascal.org/Lazarus_Database_Overview and the SQLdb TutorialX series (a very sweet idea but lacking the minimum to achieve a functional DB app) and still have many questions:

Are you Brazilian? If so, watch out that "pretend" is a false cognate and means "fingir" while "intend" means "pretender":
http://www.teclasap.com.br/falsos-cognatos-pretend/
http://blog.influx.com.br/2014/03/14/quando-usar-to-pretend-e-to-intend/

The more polite you are, the better answers you can achieve.
FPC and Lazarus are open source projects made by great effort of many people for many years, mostly on their spare time and without payment.
The same can be said about documentation, tutorials, wikis, videos, answers on forums, etc.
People do their best, but they can not supply all of everybody's needs.
So, avoid criticizing somebody's else work without providing a better free and open solution to replace it.
Your are not forbidden to ask for improvements or new features, but you have to be polite and patient, and accept a big NO most of the time as a normal response for your requests...

When you say "a functional DB app", it can be many things. I will try to help you on the first steps, but I cannot be fast nor do all the work for you. You must do your part and study and search to fill the gaps...

Quote
How to validate a record before update to the database?

There are many ways.
Your can validate each field or the whole record using RAD events, OOP or even DB triggers and constrains.

A very simple example in RAD approach would be using the event Exit of each dbware component, for example:
Code: Pascal  [Select][+][-]
  1. procedure TForm1.DBEdit1Exit(Sender: TObject);
  2. begin
  3.   if SQLQuery1.FieldByName('FieldName1').AsInteger < 0 then
  4.   begin
  5.     TDBEdit(Sender).SetFocus;
  6.     ShowMessage('Value can not be negative.');
  7.   end;
  8. end;
  9.  

Quote
How to cancel that update, being an existing record update or an a addition, if the validation fails?
Again, there are many strategies.
You can use a second SQLQuery to search if the record already exists and no update is applied at all or you can use a rollback/commit in a try/except to manage the situation:
Code: Pascal  [Select][+][-]
  1. procedure TForm1.SQLQuery1AfterPost(DataSet: TDataSet);
  2. begin
  3.   try
  4.     SQLQuery1.ApplyUpdates(-1);
  5.     SQLTransaction1.Commit;
  6.     ShowMessage('Record update succeeded!');
  7.    except
  8.     SQLTransaction1.Rollback;
  9.     ShowMessage('Record update failed!');
  10.    end;
  11. end;
  12.  

Quote
How to cancel a navigation to another record if there are pending updates and the validation fails?
Study the event BeforeScroll of SQLQuery.
There are also the properties CachedUpdates and UpdatesPending on Delphi, that you could also search for similar behavior on FPC.

Quote
If there are calculated fields that depend on the user input, where do I make those  calculations prior to the post?
Study the events Exit of each dbware component, such as DBEdit, DBGRid, etc, and BeforePost and AfterPost of SQLQuery.

Quote
If there are calculated fields that need to be, well, 'calculated' when the record changes, where do I do that?
Study the events Exit of each dbware component, such as DBEdit, DBGRid, etc, and AfterScroll of SQLQuery.

Quote
What is the order of the events?
It depends. What events are you talking about?
For example, some events of TForm:
https://www.thoughtco.com/life-cycle-of-a-delphi-form-1058011
http://www.askingbox.com/info/delphi-event-order-of-a-form-from-oncreate-to-ondestroy
Quote
Thank you in advance for your help.

You are welcome.
Many people, include me, will try to help you, but I strongly suggest that you do your part by studying, searching on forum, wiki, Google, etc, and, above all, be polite, patient and reasonable.
« Last Edit: April 17, 2017, 01:50:38 am by valdir.marcos »

valdir.marcos

  • Hero Member
  • *****
  • Posts: 1106
Please, take a look on this extra information:

http://www.freepascal.org/docs-html/fcl/db/tdataset.html
TDataSet
property CanModify: Boolean; [r]  Can the data in the dataset be modified
property Modified:  Boolean; [r]  Was the current record modified ?

http://www.freepascal.org/docs-html/fcl/db/tdataset.canmodify.html
http://www.freepascal.org/docs-html/fcl/db/tdataset.modified.html

Board index » delphi » UpdatesPending
http://www.delphigroups.info/2/ee/253717.html

Delphi - TDataSet determine if it was modified when is in insert/edit state
http://stackoverflow.com/questions/10013603/delphi-tdataset-determine-if-it-was-modified-when-is-in-insert-edit-state

rodolfo

  • Newbie
  • Posts: 2
hi Valdir,

many thanks for your replies.

Not Brazilian but close, portuguese, and you're right, it was a poor choice of words. It is really my intention to write an application that, hopefully, may assist me at work. Thank you for the correction and also for the urls.

For the second part, reading it again, must admit that it was, again, a very poor choice of words and this time born not from ignorance or disrespect but from the frustration I was feeling at that moment. Sorry, really, and thank you for taking the time to educate me.

For the programming part, I'm going to check your many points, and try to work this out.

Once again, thank you for your interest, time and effort.

Obrigado,
eu

mangakissa

  • Hero Member
  • *****
  • Posts: 1131
Quote
If there are calculated fields that depend on the user input, where do I make those  calculations prior to the post?
More advanced is creating a calculated field in the field editor. Clicking with the right mouse button you get a popup menu with the option edit fields on TSQLQuery. In the listbox you can create a calculated field. But before that you have to put all your fields into the listbox. Otherwise SQLdb doesn't know which query he has to create. Also the property Calculatedfields must be set on 'true'.
If the 'persistent fields' are created, you also have a property Onvalidate for checking your input.

As I say more advanced but very useful.
Lazarus 2.06 (64b) / FPC 3.0.4 / Windows 10
stucked on Delphi 10.3.1

valdir.marcos

  • Hero Member
  • *****
  • Posts: 1106
Hi Rodolfo.
You're welcome.
Feel free to ask about your DB application and we'll be happy to help you.

 

TinyPortal © 2005-2018