[Tfug] Any SQL gurus out there?

[TR]

On Thu, 25 Oct 2007 06:00:41, Chris Niswander
<cn.tfug.account at bitboost.com> wrote:
> At 12:55 AM 10/25/2007 -0700, you wrote:
> >In general terms, how common would it be to include "program code"
> >inside an SQL database?
Very common.  A database without the capability to have code in it is
limited.IMHO.
> > Wouldn't that be considered, at a minimum,
> >squirrelly?
No, and for a lot of reasons.   Complex business rules;  need to do
calculations that require many data points, and latency make doing the
calculation outside the db impractical; repetitive 'cleansing' of
data.
> >
> >Jim
>
> Warning: I've done various things with SQL (various vendors &
> implementations), but IMHO I am not a SQL guru. :-)
me too
>
> Do you mean stored procedures (or functions) that can get called when
> you make a database query (e.g. by triggers)?
generally triggers are fired on DML and not for a SELECT.  But I think
the scope is beyound just triggers.  I avoid triggers myself but they
are a necessary evil.
>
> Not unusual for major systems, IMHO.
>
> Can be useful at least to enforce constraints on your database
> that are tighter or more complicated than the table definitions
> can easily specify.  Or sometimes to do some funky calculations/operations.
> Maybe even as a strange way to to code reuse.
>
> But traditionally, using stored procedures limits the ease of porting your
> database schema, because traditionally each SQL vendor has offered (a)
> (usually proprietary) language(s) for coding stored procedures that is/are
> completely incompatible with just about every other SQL vendor.
I have found portability of db to be on a par with dragons an fairies,
purely a myth.  Even without code in the database port are more like
rewrites.  So much so I do not even try to port.  Such project I
approach as if the original was a prototype.  The supporting
application may ge reused, but I determine what should be in the db as
far as program code.   And that  code that is written in the db is in
fact portable, because the same code works in mySql on Linux the same
as mySql on Windoze.   Tom Kyte of Oracle claims the PL/SQL is the
most portable programming language there is.

> Some vendors have given some work to
> adding support for stored procedures / stored functions in less
> specialized languages, but the language(s) supported depend on the vendor.
> Also, I don't know that even if you find a common non-specialized language,
> you'll get anything like interface
> compatibility between SQL vendors for your stored procedures/functions.
The use of languages like Java, Perl or others in the database are
provides being able to use existing libraries but the code required to
invoke these API are still db specific.

> If someone has practical experience on this question, could you pipe up?
> I already know what some of the web pages and books claim
> on this topic in a very vague, high-level kind of way
> about Java, TCL, Perl, Python, etc.
> but not having *actually used* all these supposed support methods
> for less proprietary languages,
> I'm reluctant to make any definite claims
> about how good and cross-vendor portable they are.
Support for vendor neutral application frame works exist and can
generally be made to speak the various db dialects.   As far as in the
db code I would be very suspicious.

> What I have seen in practical use recently is Microsoft SQL Server
> support for .NET runtime as a relatively new option for stored
> procedure languages, but for FLOSS aficionados, this M$ stuff is
> not really what we are most interested in using.


> So it's natural I'm interested in what other people have seen re
> recent trends in non-specialized languages and cross-vendor compatibility
> for stored procedures/stored functions.
>
> So, in short, stored procedures often add another language
> to what maintainers need to know, and traditionally have been utterly
> unportable between SQL vendors.
I am less keen finding this holy grail of a cross db stored program
language.   Vendors can take advantage with a proprietary language by
utilizing low level db structures.  If a standard language is created
it would pander to the lowest common features and get vendor specific
'enhancements'  that would make it only moderately useful and
portable.  SQL, my gawd, is not always portable with out some rewrite
and don get me started about explain plans and how to get the best
one.

> So, you should only use them if in the specific project
> their benefits are worth the price of complicating
> your design and implementation.
A true statement only use something if you need it.  But I have found
that the benefits are always there.


> Or, like, ya know, if your system *doesn't* need it,
> but you need 'experience with foo'
> as a tick list item on your resume even if the portability
> and maintenance drawbacks screw your customers later on.  :-)
>
> 'Cuz, ya know, like, tick lists on the resume are more important than
> making a system no more complicated than it needs to be, like, ya know?
> Like, doing the best thing for your customers/clients/stakeholders is
> like *practically always* technologically self-obseleting and therefore a
> CLM. :-)
Or get so good at it you are always in demand.

> Half-Sarcastically Yours,
>
> -Chris
>
> _______________________________________________
> Tucson Free Unix Group - tfug at tfug.org
> Subscription Options:
> http://www.tfug.org/mailman/listinfo/tfug_tfug.org
>