When SQL is just too much…

September 13, 2007

Just received description for my next project starting next monday. And I have to say that I’m in for quite a shock. First, it’s an on going project wich developed in VB6. Yup, the old VB. But it uses some nifty technology (old tech) like DCOM and ActiveX. (Yes, you’ll get security warning when you opened it in your browser). But that alone is not enough, the project, although heavily relies on DB, doesn’t have a DAL. ?!#%%!?. So what did they do, they implement all the logic and the data formatting directly into the PL/SQL. (doh!). No, I don’t speak latent PL/SQL or T-SQL and I have no plan nor intentions on going into that directions. The SQL that I’m talking here is the kind of SQL strings that have multiple queries inside, have multiple aliases, using cursors, formats returning data, use stored procedures, and uses pipelining. One of the shorter ones have like 100 lines of SQL with never seen before operators and directives with attributes like %ROWTYPE and %NOTFOUND, and DECODEs inside DECODEs, constant strings and direct substitutions. (sigh)… when will they ever learn that database IS A DATABASE. It is good for returning data but NOT PROCESSING. That’s what applications do, PROCESSING DATA. When system designers blur the line like this, it is bound to get uncontrollable at some point. Luckily this is only a small project so faults should not propagate very far… or maybe that’s only my prejudice?


2 Responses to “When SQL is just too much…”

  1. Rana Deep Says:

    Its good to know that u also love vb6 as that of me.i would really appreciate if u could have provided with a little more info about the topic u have written bout.by the way,dude i am preparing my own blog and i will invite u for that[hope so 🙂 ].

  2. Andrew Says:

    sorry to tell you this, but I don’t really have any love for VB6. it’s just so happen that I HAVE to use VB6 because the base code were coded in VB6. if you look at my previous entries then you it should become obvious to you that I’m into .NET. In fact, I’m the .NET specialist in my company. but if you ever have the chance to live in Japan, then you’ll know that .NET is not yet popular in Japan. Japan, as it has always been, is slow in adapting to new technologies, so they still prefer JAVA AND VB6.

    consider this PL/SQL Function:

    Function TEST ( ... ) RETURN my_type_ PIPELINED IS
    CURSOR cur IS
    SELECT AA||' day', BB, CC, DD, DECODE ( OR1, 1, ' ', DECODE ( OR2, 0, '-', DEF1 ) ), NULL FROM ( SELECT EE A1, FF A2, GG A3, HH A4, TO_CHAR ( TRUNC ( A.sum / 100 ), 'FM999,999' ) LST , SIGN ( NVL... ) B1 FROM SELECT AAA, BBB, SUM ( ... ), FROM XX WHERE ... AND ... AND ... ) A, ( SELECT ) B, ( SELECT ) C;
    my_type rec_;
    OPEN cur
    FETCH ...
    PIPE ...
    CLOSE cur
    *some exception handlings*

    and that’s one of the simple ones. this is just unbelievable. didn’t you noticed the formattings done with DECODE and TO_CHARs and TRUNCs?

    by the way,dude i am preparing my own blog and i will invite u for that

    thanks, just drop me a line ..

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: