Re: Query table vs query Stored procedures/views???

Charles McDonald wrote:

> Hello all
>
> Re: Run inserts and select querys directly on the database tables or
> should all table access be via views /stored procedures?
>
> I have mentioned this ont this newsgroup before, but did not actually
> come up with a definitive answer. I realise that it is more an 'it
> depends' type of issue, but the application architecture is now better
> known, and this may have impact.
>
> The application is to be an n-tier app developed in VB6.
>
> The client will be developed in VB and will access a Business Services
> layer which will reside in COM+ on an application server. This Business
> layer will apply business rules.
> The Business services layer will in turn access a Data services layer,
> which will reside inside COM+ on the same application server. The Data
> services layer will be responsible for all database access, which will
> include kicking off stored procedures. Since we wish to be able to use
> the connection pooling facility of COM+ a single user ID will be used
> for all dB access with security being applied in the middle tier.
>
> Keeping in mind that a primary goal of the architecture is ease of
> maintanance and enhancement what approach should be followed?
>
> We could use the data services layer to build SQL strings as required,
> and then use these directly on the tables, or we could call stored
> procedures with parameters for the filters which then builds and
> executes the query, returning the result to the data services layer.
>
> Conversly, when writing records to the dB we could use stored procedures
> with a host array which does the inserts rather than firing off a host
> of inserts from the data services layer.
>
> I kinda imagine a primary reason for limiting access to be via views and
> stored procedures rather than direct table access is for data security.
> This should not be an issue since the users do not have Oracle logins.
> The feeling is that it will be complicating the issue if we need to keep
> track of a large set of oracle objects.
>
> Later
>
> Charles

I think this is more a question of religion than hard cold fact. And application security requirements weigh heavily in the decision making process.

My answer would be as follows:
Given no performance issues I would perform all table maintenance activities through procedures written in packages. When performance becomes an issue ... you make the compromises necessary to achieve adequate performance without compromising data integrity and security.

Daniel A. Morgan Received on Fri Jun 29 2001 - 12:17:41 CDT