[Tfug] Any SQL gurus out there?

[Chris Niswander]

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?  Wouldn't that be considered, at a minimum,
>squirrelly?
>
>Jim

Warning: I've done various things with SQL (various vendors & 
implementations), but IMHO I am not a SQL guru. :-)

Do you mean stored procedures (or functions) that can get called when
you make a database query (e.g. by triggers)?

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.  

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.  

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. 

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.

So, you should only use them if in the specific project
their benefits are worth the price of complicating
your design and implementation.  

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. :-)

Half-Sarcastically Yours,

-Chris