Re: Databases as objects

Thomas Gagne wrote:
> kvnkrkptrck_at_gmail.com wrote:
> > <snip great example>
> Congratulations on that performance improvement. That is an excellent
> example of cohesiveness--individual responsibility--something doing for
> itself what other can not do (as well).
>
> We have no disagreement and our approaches are not in conflict with each
> other. Our system has similar jobs that run entirely inside the
> database. We still create procedures for them, but they are not called
> by application programs. They are relatively large SET processing tasks
> that can only efficiently be executed on sets. They are not
> transactions. They have nothing to do with OLTP.
>
> From what you described, your job didn't really have anything to do
> with an application trying to hydrate or dehydrate objects from the the
> DB, or execute transactions either (something that must be done
> 1000/minute), or require an external stimulus, like a customer, to
> initiate it.
>
> I understand what you are saying. I've done it before and I do it
> still. But I'm still having a communication problem in c.d.t. I
> actually think its a form of stereotyping: no matter what I say, because
> it uses the term "object" prejudice thinks I want to replace set
> processing with Java then take your little sister to the wrong side of
> the tracks.
>
> I'm going to have to work on some examples so c.d.t. can see that what I
> propose to do doesn't require OOPLs or abandoning set processing.
>
> --
> Visit <http://blogs.instreamfinancial.com/anything.php>
> to read my rants on technology and the finance industry.

Thomas,

I believe Marshall's response clearly expresses my overall sentiments. But to address your OLTP comment: I mentioned a function that was originally part of the PRO process but remains as a stored procedure that is called elsewhere in our application code:

validate_project_has_lines (project_name IN, date_range_start IN, date_range_end IN, proj_has_lines_flag OUT, error_flag OUT, error_message OUT);

This function is currently being used by one of our online forms (our application codebase is about 40% OLTP). If you think of the function as an interface to the database, consider the following questions that the form developer may want answered when he sees the form make the call:

• What tables are queried?
• How are the date ranges applied? What if the end-date field of the form is not populated?
• Is there an efficient execution plan for resolving the query?
• Speaking of efficiency, does the query make use of bind variables, or does it use dynamic SQL that will be parsed with each new call?
• Are there any "hidden" filters (i.e. does the function look at approved lines only, or all lines)?
• Is the returned "proj_has_lines_flag" going to be "Y/N", "Yes/No", "X/<null>"?
• What types of exceptions are captured, and what exceptions will propogate?
• Is the returned error_flag going to be "Y/N", "Yes/No", "X/<null>"?

Now, consider the following "pesky SQL" code snippet as an alternative interface to the database:

SELECT CASE WHEN COUNT(*) = 0 THEN 'Y' ELSE 'N' END INTO :PROJECT_HAS_LINES
FROM PROJECTS P, PROJECT_LINES L
WHERE P.PROJECT_ID = L.PROJECT_ID
AND PROJECT_NAME = :P_NAME
AND L.STATUS = 'APPROVED'
AND L.START_DATE <= NVL(:START_DATE, L.START_DATE) AND L.END_DATE >= NVL(:END_DATE, L.END_DATE) This is slightly but not substantially more complex than the above function call (at least, not to anyone familiar with SQL). But consider each of the above questions from the perspective of the forms developer who sees this SQL as the interface to the database. Consider the ease with which a client request: "Hey, the form should actually check if there are APPROVED or SUBMITTED lines" could be handled. How would such a request be handled with the function interface (careful of side effects to other processes)?

In my experience, the approach you advocate is rotten through and through - I have only ever seen it degrade applications, be they batch or OLTP. Though I routinely see the opposite, I have yet to encounter a situation where an application is improved by any metric (even lines of code) by abstracting away embedded SQL. Received on Tue Jan 02 2007 - 17:01:46 CET