business logic location

For quite a while I've had pretty good success with putting most of my
business logic in stored procedures and triggers. It's fast, relational,
searchable, and updatable without any recompiling.
At the moment I'm considering supporting both SQL Server and Oracle, and it
seems to me that to support multiple database platforms the business logic
needs to be moved out of the database layer. Not only would this seem
necessary to me, but it would also seem to be ugly, much more complicated,
harder to maintain, and slower, maybe far slower.
This is the first time I've looked at this problem; it was my assessment
years ago that deep business logic (not the minor stuff associated with just
the UI) in hard code was a bad idea. I was hoping someone could share their
point of view on this area with me, maybe point out some examples of how
multiple database support is currently done, and how feasable (automatable)
it is to port data, with stored procedures and triggers, between SQL and
Oracle.
PaulOn Mon, 19 Sep 2005 10:44:04 -0400, PJ6 wrote:

>For quite a while I've had pretty good success with putting most of my
>business logic in stored procedures and triggers. It's fast, relational,
>searchable, and updatable without any recompiling.
>At the moment I'm considering supporting both SQL Server and Oracle, and it
>seems to me that to support multiple database platforms the business logic
>needs to be moved out of the database layer. Not only would this seem
>necessary to me, but it would also seem to be ugly, much more complicated,
>harder to maintain, and slower, maybe far slower.
Hi Paul,
I disagree. Even when porting to Oracle or any other platform, you'll
still have to have the business logic in the data layer. Just think
back: over the last few years, how many client technologies have you
seen come and go? And how many times did you switch database vendor?
Besides: business rules in the front end tend to be much easier to
bypass than business rules in triggers in the database.

>This is the first time I've looked at this problem; it was my assessment
>years ago that deep business logic (not the minor stuff associated with jus
t
>the UI) in hard code was a bad idea. I was hoping someone could share their
>point of view on this area with me, maybe point out some examples of how
>multiple database support is currently done, and how feasable (automatable)
>it is to port data, with stored procedures and triggers, between SQL and
>Oracle.
It helps if you start coding from day one with the idea that you might
one day have to port. Use ANSI-standard whenever feasible. If you do use
proprietary (because ANSI would require an extremely ugly kludge or many
extra lines of code, or because the ANSI version is lots slower),
document it, and describe how the ANSI version looks.
You'll still have a lot of work carved out for you before you'll be
ready to run the same product on SQL Server and Oracle, becuase there
are lots of irritating differences. Expect the porting of data to be
relatively easy, the porting of queries quite complicated and the
porting of stored procedures and triggers to be hell. Especially if you
didn't write the original code with portability as one of the design
objectives...
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:7u01j1tqn6tnoaol3siot2te99bl5psnne@.
4ax.com...
<...>
> Besides: business rules in the front end tend to be much easier to
> bypass than business rules in triggers in the database.
<...>
> You'll still have a lot of work carved out for you before you'll be
> ready to run the same product on SQL Server and Oracle, becuase there
> are lots of irritating differences. Expect the porting of data to be
> relatively easy, the porting of queries quite complicated and the
> porting of stored procedures and triggers to be hell. Especially if you
> didn't write the original code with portability as one of the design
> objectives...
Well I guess I'll to stand by my choice to keep most business logic in the
data layer then, and damn the torpeedos, we won't support Oracle.
MSDE is free anyway.
Paul|||"PJ6" <nobody@.nowhere.net> wrote in message
news:eEAw3rrvFHA.2932@.TK2MSFTNGP10.phx.gbl...
... we won't support Oracle.
Great idea... :-)|||>Especially if you
>didn't write the original code with >portability as one of the design
>objectives...
just wanted to add that the price for portability may be very very
high.
For instance, a wide table, appr. 4100 bytes per row, which menas 1 row
per page. Start using those "horrible proprietary" data types like bit
and smalldatatime, and here you go: 3900 bytes, 2 rows per page!
Having sacrificed portasbility, all of a sudden you get 100% better
performance.
There are also plenty of differences in locking and concurrency, index
behaviour etc.|||On Wed, 21 Sep 2005 10:44:26 -0400, PJ6 wrote:

>"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
> news:7u01j1tqn6tnoaol3siot2te99bl5psnne@.
4ax.com...
><...>
><...>
>Well I guess I'll to stand by my choice to keep most business logic in the
>data layer then, and damn the torpeedos, we won't support Oracle.
Hi Paul,
If the expected extra revenues do not outweigh the expected cost of
having to maintain two versions of the code plus the write-off of the
conversion to Oracle, that'd be the soundest decision.

>MSDE is free anyway.
And ditto for SQL Express (with even less limitation, IIRC).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||On 21 Sep 2005 08:45:19 -0700, Alexander Kuznetsov wrote:

>just wanted to add that the price for portability may be very very
>high.
(snip)
>Having sacrificed portasbility, all of a sudden you get 100% better
>performance.
Hi Alexander,
Great example!
That's exactly why I want portability to be "one of" the design
objectives. Not the only one, nor even the most important one.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)