Board index » delphi » What is/are the best ways to handle RI from within an application?

What is/are the best ways to handle RI from within an application?

Hi, I'm trying to assertain the "best" way of handling RI and
existence testing from within my application...

Please note that I am using an OO approach within my application
design and not standard DBaware controls...

I have a class:

TAddress = class(TObject)
  fUIDAddressCode: TIDCode;
  faddressLine1
  fAddressLine2
  fCountryCode: TIDCode;
  fCountry: TCountry
end;

excluding re-use (for now) the things I need to check are..

fUIDAddressCode does not already exist within the DB when I create a
new address object...

and

fCountryCode must exist within the DB, or the update will not be
performed.

is it acceptable to issue single item selects...

QRY.String := 'Select * FROM DBfCountry WHERE DBfCountryCode =
fCountryCode';
....
if QRY.RowsAffected <> 1 then Raise 'Don't be silly!'

every time I wish to save/update the record?

Likewise I can check the rows affected to test for an existing
record..

The Backend DB may also have these, and other, constraints built in...
how do others handle SQL errors from the DB?

Do you issue a generic "Server reported an error", or do you trap the
error and try to map it to a specific area/field on the display?

Are the messages returned by a SQL server (SQL92) standard in format
so any SQL implimentation will always [should] return the same message
for the same error?

Jon
Integrated 400 Solutions Ltd.

Jonathan Wilson, AS/400 consultant/director

24 Hours: 07775 638904

 

Re:What is/are the best ways to handle RI from within an application?


Quote
Jonathan Wilson wrote:

> Please note that I am using an OO approach within my application
> design and not standard DBaware controls...

        Makes no difference, although I would point out that there's nothing
about OO which prevents the use of data-aware controls.  There's a great
article on the subject here:

http://www.gexperts.com

        ...if you're intrested.  ("Managing application settings.")

        Moving right along:

Quote
> Do you issue a generic "Server reported an error", or do you trap the
> error and try to map it to a specific area/field on the display?

        I don't catch exceptions when it's at all avoidable.  The whole point
of raising them is to stop things dead.  I have a centralized
Application.OnException handler which can determine what the error was
and give an English message to the user.  This keeps the message out of
the data module (important since same DM may be used in non-interactive
apps) and centralizes the error-handling logic.

        HTH,

        -Craig

--
Craig Stuntz (TeamB)       Senior Developer, Vertex Systems Corp.
Delphi/InterBase weblog:   http://delphi.weblogs.com
Use Borland servers; posts via others are not seen by TeamB.
For more info, see http://www.borland.com/newsgroups/genl_faqs.html

Re:What is/are the best ways to handle RI from within an application?


Isn't that what the DBMS is for?

Re:What is/are the best ways to handle RI from within an application?


Quote
Justin Pitts wrote:

> Isn't that what the DBMS is for?

        Yes.  He was asking how to present database exceptions to the user.

        -Craig

--
Craig Stuntz (TeamB)       Senior Developer, Vertex Systems Corp.
Delphi/InterBase weblog:   http://delphi.weblogs.com
Use Borland servers; posts via others are not seen by TeamB.
For more info, see http://www.borland.com/newsgroups/genl_faqs.html

Re:What is/are the best ways to handle RI from within an application?


Quote
> QRY.String := 'Select * FROM DBfCountry WHERE DBfCountryCode =
> fCountryCode';
> ....
> if QRY.RowsAffected <> 1 then Raise 'Don't be silly!'

Jonathan,
  I think RowsAffected is only set for Deletes and Updates, not for Selects.
(The Delphi Help says "Inspect RowsAffected to determine how many rows were
updated or deleted by the last query operation.")

  Personally, I use the IsEmpty property to see if there are any result rows
from a Select statement.  Have you tested to see if RowsAffected works for
Selects?  (If it does, then please feel free to ignore me. <g>)

-Howard

Re:What is/are the best ways to handle RI from within an application?


Indeed he was.

Jonathan, I apologize for the asinine reply.

Are you using ADO or the BDE? I know you get SQLSTATE back in the ADO errors
collection, but I do not know if these are standard across vendors.

Re:What is/are the best ways to handle RI from within an application?


Quote
Howard Moon wrote:

> Have you tested to see if RowsAffected works for
> Selects?  (If it does, then please feel free to ignore me. <g>)

        Some DB back ends do not support this.  Others might.

        -Craig

--
Craig Stuntz (TeamB)       Senior Developer, Vertex Systems Corp.
Delphi/InterBase weblog:   http://delphi.weblogs.com
Use Borland servers; posts via others are not seen by TeamB.
For more info, see http://www.borland.com/newsgroups/genl_faqs.html

Re:What is/are the best ways to handle RI from within an application?


On Fri, 13 Apr 2001 11:04:02 -0400, "Justin Pitts"

Quote
<jpi...@cnstores.com> wrote:
>Indeed he was.

>Jonathan, I apologize for the asinine reply.

 :-)

Quote

>Are you using ADO or the BDE? I know you get SQLSTATE back in the ADO errors
>collection, but I do not know if these are standard across vendors.

BDE at present (Although I may pay the extra 100odd GBP for ADO)

My problem with letting the DB handle RI exclusivly is the following.

If the DB has RI to 20 other tables, yet returns an error once the
first RI constraint is broken the user may have to "test" 20
individual times before the record posts!

I want the user to be able to "just key" in the codes to the other
tables... this is far quicker for data entry purposes (give them a
blank form like a page of paper), also if a related table is a stock
table then it could have millions of items, I don't think building a
dropdown box is a good idea :-)

Also I'm trying to centralise all the vaidation within my specific
data object, which is totally seperated from the gui and DB... this
allows the user to pick the DB server (As long as it uses a reasonable
subset of SQL92)

I'll let the DB/ODBC throw up any errors they may, trap them, and pass
a string list back to the user of my class containing all of these
messages, along with any other validation I may have performed (eg
DateOfBirth makes person 200 years old)

Some of the validation may infact be performed in 3 places
simultainiously (I dont have a proplem with this) eg.

WebForm tests that a persons name has been entered, if this and the
rest of the form validation is correct then this data is passed to my
TMyPerson class, which agains checks the name, if all is valid here
then the data is posted to the DB, which again may have this test (so
updates outside of my application don't courrupt the DB data).

Jon.

PS.

Is it possible to access ADO (a MS "thing") without buying into the
wrappers supplied by borland... how simple is it to, say, write my own
wrapper arround the DLL's to allow just enough access to do the simple
bits, eg. connect, execute SQL, wrap errors, extract the resultant
data...

Is it worth it, or would the man hours be better spent in hard cash to
borland?

Jon
Integrated 400 Solutions Ltd.

Jonathan Wilson, AS/400 consultant/director

24 Hours: 07775 638904

Other Threads