Confirming sql query’s, professional method
lazarus / 16-Databases

----------------------------------------------------------------------
christensen · 2010-05-04 14:34

Hi to everyone!

I have some questions regarding sql query’s.
After using: select, insert, update and delete query’s how can I get
confirm for those query’s?
I’ve solved some of them but I’m curios how can I solve in
professional mode?

For insert: before using insert query I select the last ID and after
insert again I select the last ID, when I get both of ID’s I compare
them and if I the first selected ID is bigger than the last it mean
that the insert query was ok.

For delete:
before using delete query I select the last ID and after insert again
I select the last ID, when I get both of ID’s I compare them and if I
the first selected ID is smaller than the last it mean that the delete
query was ok.

For upgrade:
I don’t find any ‘professional’ solution only to compare the all
fields for the ID. Selecting the ID for what I want to edit and after
editing to compare with form fields (field with records) and the
records for the same ID from database.

For select confirmation I don’t know any solutions! Only check if I
get something after query. Ex.

If sqlQuery.fields[1].asSting=’’ then
      Begin
      //something isn’t good, I get an empty result.

      End
      Else
      Begin
      // I get something in return!

      End;


Can somebody help me out whit a professional solution, please.

Thanks in advanced!

----------------------------------------------------------------------
José Mejuto · 2010-05-04 15:15

Quote from: christensen on May 04, 2010, 04:34:03 pmHi to everyone!
I have some questions regarding sql query’s.
After using: select, insert, update and delete query’s how can I get
confirm for those query’s?
I’ve solved some of them but I’m curios how can I solve in
professional mode?
[...]

Hello,

If you do not get any exception after open, or execute the SQL the
operation was successful. If something goes wrong you will get some
kind of exception, and the kind of exception and the text message will
inform you about the error nature.

Your tests for Insert and Delete are wrong, they only work in single
user, single thread, single process environment, and SQL is productive
specially in multi-everything situations.

Example, after you insert a register with ID=10, and before you
perform the check, ofther user can delete the ID=10 and your check
will fail and the insert was correct.

----------------------------------------------------------------------
christensen · 2010-05-04 18:30

at the moment i work for single user but for future maybe i will have
problems and for that i ask for professional solution.

how can I solve this?

----------------------------------------------------------------------
Marc · 2010-05-04 20:14

Quote from: christensen on May 04, 2010, 08:30:57 pmat the moment i
work for single user but for future maybe i will have problems and for
that i ask for professional solution.

how can I solve this?

inserts:
use serials or sequences (or how they are named on your database).
Some databases allow them to be generated automatically when you
insert a new record.

deletes:
just execute the delete statement. If no error then the delete was ok.

updates:
most times if you use a query or table component, it handles this for
you. No need to worry.