Board index » delphi » Design Question: Delphi or Stored Procedure?

Design Question: Delphi or Stored Procedure?

Environment:
Delphi 4 C/S
Oracle 8.0

I have built a table to hold product mixes for projects.

productmix ( project_num, product_type, percent ) primary key( project_num,
product_type )

In other words, a number of different products for each product. The
restriction for this table is that percent must equal 100 for each
project_num.

I am using ClientDataSets to hold the data. I'm trying to figure out if it
would be better to validate the percent total in Delphi before I post the
data to the server or if I should post the data and use a stored procedure
to validate it. Perhaps even a trigger? I would really prefer server side
processing as I can potentially modify the data directly through another
front end.

Thanks in advance for suggestions.

--
Ken Rachynski
Database Analyst
Evans Consoles
http://evansonline.com/

 

Re:Design Question: Delphi or Stored Procedure?


Well the problem with doing the server side validation, (especially
using a trigger), is that you need to have the entire set validated, and
not the individual rows you are inserting. A trigger will fire on the
insert of each row, and as they won't add up to 100% you will fail the
validation. Now there may be some way around this, but just thinking
about how to implement it gives me a headache, so I've always done this
kind of validation in the app. If anyone does come up with a way to do
this kind of validation on the server, I'd be interested to see how.
Quote
Ken Rachynski wrote:

> Environment:
> Delphi 4 C/S
> Oracle 8.0

> I have built a table to hold product mixes for projects.

> productmix ( project_num, product_type, percent ) primary key( project_num,
> product_type )

> In other words, a number of different products for each product. The
> restriction for this table is that percent must equal 100 for each
> project_num.

> I am using ClientDataSets to hold the data. I'm trying to figure out if it
> would be better to validate the percent total in Delphi before I post the
> data to the server or if I should post the data and use a stored procedure
> to validate it. Perhaps even a trigger? I would really prefer server side
> processing as I can potentially modify the data directly through another
> front end.

> Thanks in advance for suggestions.

> --
> Ken Rachynski
> Database Analyst
> Evans Consoles
> http://evansonline.com/

Re:Design Question: Delphi or Stored Procedure?


millerb...@spicedhamawayaol.com (Bradford C. Miller) wrote in
<38A0569C.92DA1...@spicedhamawayaol.com>:

Quote
>Well the problem with doing the server side validation, (especially
>using a trigger), is that you need to have the entire set validated, and
>not the individual rows you are inserting. A trigger will fire on the
>insert of each row, and as they won't add up to 100% you will fail the
>validation. Now there may be some way around this, but just thinking
>about how to implement it gives me a headache, so I've always done this
>kind of validation in the app. If anyone does come up with a way to do
>this kind of validation on the server, I'd be interested to see how.

I know that I cannot see how to do this except from a stored procedure
called after everything is updated (an exception report perhaps?). Maybe
somebody has solved this type of issue in the past. If not, I plan to do
client side validation prior to submitting the data.

Thanks for the reply.

--
Ken Rachynski
Database Analyst
Evans Consoles
http://evansonline.com/

Re:Design Question: Delphi or Stored Procedure?


Is this a 2 tier or 3 tier app? What you are doing is data (not storage
integrity) validation. It should belong in the business logic (BL) for a
3 tier app, or the User Interface (UI)  for a 2 tier one. The 3 tier
approach would satisfy the requirement (need) for reusing same BL from
another front end (UI).  In the 2 tier case this would be difficult. But
the validation can be done during a onEdit event for the result set
rather than during the onPost event.

Stilian

Quote
Ken Rachynski wrote:

> Environment:
> Delphi 4 C/S
> Oracle 8.0

> I have built a table to hold product mixes for projects.

> productmix ( project_num, product_type, percent ) primary key( project_num,
> product_type )

> In other words, a number of different products for each product. The
> restriction for this table is that percent must equal 100 for each
> project_num.

> I am using ClientDataSets to hold the data. I'm trying to figure out if it
> would be better to validate the percent total in Delphi before I post the
> data to the server or if I should post the data and use a stored procedure
> to validate it. Perhaps even a trigger? I would really prefer server side
> processing as I can potentially modify the data directly through another
> front end.

> Thanks in advance for suggestions.

> --
> Ken Rachynski
> Database Analyst
> Evans Consoles
> http://evansonline.com/

Re:Design Question: Delphi or Stored Procedure?


elenk...@vtls.com (Stilian Elenkov) wrote in
<38A09D90.60674...@vtls.com>:

Quote
>Is this a 2 tier or 3 tier app? What you are doing is data (not storage
>integrity) validation. It should belong in the business logic (BL) for a
>3 tier app, or the User Interface (UI)  for a 2 tier one. The 3 tier
>approach would satisfy the requirement (need) for reusing same BL from
>another front end (UI).  In the 2 tier case this would be difficult. But
>the validation can be done during a onEdit event for the result set
>rather than during the onPost event.

>Stilian

It's a two tier app. I may not have access to change the other front end to
accomodate my business logic. As such, I would prefer to keep the logic at
the server level. If not, then I will see about changing the other front
end.

--
Ken Rachynski
Database Analyst
Evans Consoles
http://evansonline.com/

Re:Design Question: Delphi or Stored Procedure?


I would enforce rules using triggers.

Oracle has several types of triggers (Before update / insert, with or
without conditions to be fired). Oracle 8 even introduced "Instead of"
triggers. This allows to enforce your business rules whatever the front end.
This puts the enforcement of your business rules in a single place.

But it depends on other elements too. Client-side validation can be used to
reduce network traffic. For example, it's frequent to put Javascrip
validation in web pages to avoid sending a clearly erroneous request to the
server (check for required fields and so on).

Achille Carette
Bluegate

Quote
Ken Rachynski <krac...@evansconsoles.com> wrote in message

news:8ED46539Dkrachynec@207.105.83.62...
Quote
> Environment:
> Delphi 4 C/S
> Oracle 8.0

> I have built a table to hold product mixes for projects.

> productmix ( project_num, product_type, percent ) primary
ey( project_num,
> product_type )

> In other words, a number of different products for each product. The
> restriction for this table is that percent must equal 100 for each
> project_num.

> I am using ClientDataSets to hold the data. I'm trying to figure out if it
> would be better to validate the percent total in Delphi before I post the
> data to the server or if I should post the data and use a stored procedure
> to validate it. Perhaps even a trigger? I would really prefer server side
> processing as I can potentially modify the data directly through another
> front end.

> Thanks in advance for suggestions.

> --
> Ken Rachynski
> Database Analyst
> Evans Consoles
> http://evansonline.com/

Other Threads