• (cs) in reply to anonymous
    Anonymous:
    Alex Papadimoulis:

    If you're looking at today's code and thinking, gee, shouldn't these be in stored procedures, or views, or something...



    For some reason or another, I've worked with many DBA's in the past that absolutely forbid the use of stored procedures, views, triggers, etc (basically anything besides tables and indexes).  Basically, the argument is that once you start doing that, you're starting to embed application logic in the database.

    I don't know if I'm for or against this kind of practice.... Has anybody dealt with DBA's who take this stance?


    Well, once you start using stored procedures and foreign keys and triggers and other fancy bells and whistles like that, you run a real risk of actually enforcing data integrity, and nobody wants that as far as I can tell.

  • (cs) in reply to LordHunter317

    In order for a performance optimization to be considered a hack, it would have to be implemented in a way that somehow detracts from the intended design.  Building an optimized data storage engine for a project would usually be considered a hack unless there are really strange circumstances.

    However, a covering index has no hack elements to it.  It performs its job without any adverse effects (there are zero non-performance side effects of implementing covering indexes).  It is totally reversible in seconds even while the system is running.  It is transparent to the application.  It does not introduce any new maintenance issues.  It is widely understood.  It is how the database vendors intended the solution to be designed (the performance tuning courseware from both Oracle and Microsoft teach covering indexes, I don't know if IBM does, but it is on their website).

    Indexes are performance tools, you include what gives the best performance.  There is no concept of "what you should include" other than what works best.  A database that tends to be read more will (and should) have more indexes that include more columns.  I could understand your point if there were even a single downside other than the incredibly obvious and well-known downside of indexing in general -- it slows down data manipulation.

    Do you consider the fact that you have to turn your steering wheel counterclockwise in order to turn your car left a hack?  That is how it was designed, but I feel "I shouldn't have to do it".  Therefore, it is a hack.  Brillant.

  • LordHunter317 (unregistered) in reply to jsmith
    jsmith:
    In order for a performance optimization to be considered a hack, it would have to be implemented in a way that somehow detracts from the intended design.
    And the intended purpose of an index is to index data rows.  Not to store the data I'm actually interested in.  The moment you include a column that's not in the WHERE clause, you've exceeded the intended purpose of an index (generally).

    However, a covering index has no hack elements to it.
    Yes, it does.  It, by definition, contains more information than is required to index the table.

    It performs its job without any adverse effects (there are zero non-performance side effects of implementing covering indexes).
    First, you're wrong; second, that's irrelevant to deciding whether it's a hack or not.

    It is totally reversible in seconds even while the system is running.
    Wrong, situation dependent.  Index builds on new tables may take hours.

    It does not introduce any new maintenance issues.
    Wrong, depending on the data, adding new indexes may extend maintaince time or cause other issues.  PostgreSQL for example, requires periodic reindexing that takes time, especially on large indexes.  Most other databases have other such pitfalls. 

    It is widely understood.
    And?  Irrelevant to determining whether it's a hack or not.

    It is how the database vendors intended the solution to be designed
    No, they didn't.  If that were true, we'd use covering indexes all the time and we don't.  It is a solution.

    Indexes are performance tools, you include what gives the best performance.
    No, that is not there sole purpose in many RDBMS.  They enforce data integrity in several.

    I could understand your point if there were even a single downside other than the incredibly obvious and well-known downside of indexing in general -- it slows down data manipulation.
    There are, you seem to be quite igorant of the realities of DB maintance and planning.

    Do you consider the fact that you have to turn your steering wheel counterclockwise in order to turn your car left a hack?  That is how it was designed, but I feel "I shouldn't have to do it".  Therefore, it is a hack.  Brillant.
    Except this isn't a comparable scenario.  Indexes were meant to index, not store data for retreival.  They're mearly lists of pointers to the actual data, ordered in some useful fashion.  They're addtionally used for relational integrity in certain situations, though not all DBMS use them for that, and it's not the only way to enforce uniqueness.

  • Anonymous coward (unregistered) in reply to LordHunter317

    Do you two want a ruler?

  • (cs) in reply to jsmith

    The old "should we have stored procedures debate" misses one important thing out.

    Sometimes it is the only way to get something done with out granting excessive rights to a user. For example, we had a situation where one user needed to be able to truncate the table in another user's schema. To do this the user needs to be able to DROP the table. But, Oracle doesn't allow you to drop another user's objects, so the only way to do this is to grant DROP ANY rights to the object needing to do the truncate.

    Or, you can write a stored procedure which the dropping user calls and only has the rights to run that. Safe, secure, no problem.

  • (cs)

    ....we learned how vital enterprise application are for proactive organizations leveraging collective synergy to think outside the box and formulate their key objectives into a win-win game plan with a quality-driven ...

    BINGO

    That was quick

  • Huibert Gill (unregistered)

    Yes, but...

    shouldn't these "constants" be read from a config file?
    Just in case some DB starts to use "GET" instead of "SELECT".

    just my 2ct.

    Happy Hacking!

  • (cs) in reply to Jeff S
    Jeff S:

    SQL is horrible programming language.  But it is an excellent *set-based* data processing language.   Literally hundreds of times more efficient than using one of them fancy OOP languages to process the rows one by one at the client.


    Well, SQL-implementations (so to speak) are quite good. I'm not sure it's really great as language but that's what we have.

    Database (or other) vendors propably don't want to go through pains of standardizing any radically different language.
  • Erwan (unregistered) in reply to Ragnaros
    Anonymous:

    IDIOTS!!!



    Shouldn't this be EnterpriseConstants.IDIOTS ?
  • (cs) in reply to Erwan
    Anonymous:
    Anonymous:

    IDIOTS!!!



    Shouldn't this be EnterpriseConstants.IDIOTS ?


    Just in case "IDIOTS" ever gets redefined as "marketing visionaries"...
  • Abdul (unregistered)

    never mind all else..that person can't spell the word "PRODUCTS"

    "public const string SELECT_ACTIVE_PRODCUTS "

     

     

     

  • Drum D. (unregistered)

    I'm still a student and surely have produced a lot of WTFs myself, but I can't believe that people who produce such unbelievable crap actually are allowed to stay in their job.

  • Justin (unregistered) in reply to anonymous
    Anonymous:
    Alex Papadimoulis:

    If you're looking at today's code and thinking, gee, shouldn't these be in stored procedures, or views, or something...



    For some reason or another, I've worked with many DBA's in the past that absolutely forbid the use of stored procedures, views, triggers, etc (basically anything besides tables and indexes).  Basically, the argument is that once you start doing that, you're starting to embed application logic in the database.



    No decent DBA would do that, as it is an application developer's decision whether or not to embed.

    My argument (accepted most places) is that there should be an API to the database, and the middle tier should be a client to the database.  In fact, it should be impossible to connect as a user with generic 'select' privileges, much less insert or update.

    That way, the database developer can ensure that all the selects which are required are (a) efficient, (b) auditable and (c) EFFICIENT.

    Justin.
  • (cs) in reply to Jeff S
    Jeff S:
    Anonymous:
    Alex Papadimoulis:

    If you're looking at today's code and thinking, gee, shouldn't these be in stored procedures, or views, or something...



    For some reason or another, I've worked with many DBA's in the past that absolutely forbid the use of stored procedures, views, triggers, etc (basically anything besides tables and indexes).  Basically, the argument is that once you start doing that, you're starting to embed application logic in the database.

    I don't know if I'm for or against this kind of practice.... Has anybody dealt with DBA's who take this stance?


    While today's post is a great WTF, if what you are saying about those DBAs is true, then they have taken the top "WTF" prize for the day.  That might be one of the stupidest things I have ever heard.  Did you really work with *many* of them? That's a scary thought.


    Well, here in the wholly-owned subsidiary of ENGLISH_DEFS.whatAClockMeasures + " " + ENGLISH_DEFS.personWhoIssuesACaution, we have a fun mix of DBAs, often in the same groups, who either demand or will never use STPs.. That one's funky. I've seen apps need to be competely rewritten when the backend support eprson changes because they will no longer allow direct sql, and I've seen the opposite.
  • annoyingmous (unregistered) in reply to anonymous
    Anonymous:
    Alex Papadimoulis:

    If you're looking at today's code and thinking, gee, shouldn't these be in stored procedures, or views, or something...



    For some reason or another, I've worked with many DBA's in the past that absolutely forbid the use of stored procedures, views, triggers, etc (basically anything besides tables and indexes).  Basically, the argument is that once you start doing that, you're starting to embed application logic in the database.

    I don't know if I'm for or against this kind of practice.... Has anybody dealt with DBA's who take this stance?

    CAPTCHA: SOLDIER, I mean
    CAPTCHA: salad


    You mean DBAs that refuse to do any work?
  • (cs) in reply to [Si]dragon

    Well... at least they declared those as 'const' and not 'static readonly'...

  • Mr Goaty (unregistered) in reply to Huibert Gill

    Quality suggestion, which begs the questions: 

    1. Why on Earth would you ever want to embed SQL statements within your source code?
    2. Does it really have any place in there at all?
    3. Surely it gets in the way of any surrounding logic?
    4. Why is embedding the SQL into source code favourable to reading in from a config file or a resource bundle (or similar)?
    5. Is it wrong of me to want to invite unholy retribution upon all those that embed SQL within their source code?  :D
    Fantastic debate BTW, it's been good reading!
    Kind Regards.

  • (cs) in reply to Code Slave
    Anonymous:

    <font color="#000000">Perhaps that's n-2'th, but there's no way I'm going to hex of binary.</font>



    Allow me...

    public class SqlBits
    {
        public const int SqlBitZero = 0;
        public const int SqlBitOne = 1;
        public const int SqlBitFileNotFound = 2;
    }

    public class SqlBytes
    {
        public const
    byte SqlByteZero = (SqlBits.SqlBitZero << 8) | (SqlBits.SqlBitZero << 7) | (SqlBits.SqlBitZero << 6) | (SqlBits.SqlBitZero << 5) | (SqlBits.SqlBitZero << 4) | (SqlBits.SqlBitZero << 3) | (SqlBits.SqlBitZero << 2) | (SqlBits.SqlBitZero << 1) | (SqlBits.SqlBitZero << 0);
        public const byte SqlByteOne = (SqlBits.SqlBitZero << 8) | (SqlBits.SqlBitZero << 7) | (SqlBits.SqlBitZero << 6) | (SqlBits.SqlBitZero << 5) | (SqlBits.SqlBitZero << 4) | (SqlBits.SqlBitZero << 3) | (SqlBits.SqlBitZero << 2) | (SqlBits.SqlBitZero << 1) | (SqlBits.SqlBitOne << 0);
        // ...snip...
        public const byte SqlByteTwoHundredAndFiftyFive = (SqlBits.SqlBitOne << 8) | (SqlBits.SqlBitOne << 7) | (SqlBits.SqlBitOne << 6) | (SqlBits.SqlBitOne << 5) | (SqlBits.SqlBitOne << 4) | (SqlBits.SqlBitOne << 3) | (SqlBits.SqlBitOne << 2) | (SqlBits.SqlBitOne << 1) | (SqlBits.SqlBitOne << 0);
    }

    public class SqlChars
    {
        public const char SqlCharAsciiSpace = (char) SqlBytes.SqlByteThirtyTwo;
        public const char SqlCharAsciiA = (char) SqlBytes.SqlByteSixtyFive;
        public const char SqlCharAsciiB = (char) SqlBytes.SqlByteSixtySix;
        // brain explodes
    }
  • (cs) in reply to Mr Goaty
    Anonymous:
    Quality suggestion, which begs the questions: 
    1. Why on Earth would you ever want to embed SQL statements within your source code?
    2. Does it really have any place in there at all?
    3. Surely it gets in the way of any surrounding logic?
    4. Why is embedding the SQL into source code favourable to reading in from a config file or a resource bundle (or similar)?
    5. Is it wrong of me to want to invite unholy retribution upon all those that embed SQL within their source code?  :D
    Fantastic debate BTW, it's been good reading!
    Kind Regards.


    1. To access the database ;-)
    There are better alternatives like Hibernate, but sometimes they are an overkill. It's arguably a bad idea to bury SQL deep inside the business logic but it's IMO ok to do it in a source module dedicated to database access. Some people have a tendency to add more and more levels of  indirection but this only complicates things and (in many cases) gains nothing.
    4. a) It's easier to understand the code if you don't have to lookup the config files.
    b) If a statement needs to access a lot of host variables (like an insert or update statement), putting it into a config file doesn't improve anything.
    c) If you use a precompiler, it can check the SQL code during compile time.
    5. Yes
  • (cs) in reply to Iago

    Er, spot the deliberate mistake.  Yeah, deliberate.  Honest.

  • Dan (unregistered) in reply to sebmol

    How about creating a code generator to generate the data access layer for the database? Although you will spend a lot of time implementing such a generator, you may save a lot of time when the database is big or when its schema changes. And of course you can reuse such a tool for more than one application.

  • boicy (unregistered) in reply to bleah

    "Java in oracle,"

    hahhahahahahahah...

    you were joking right? oh...

  • (cs) in reply to boicy
    Anonymous:
    "Java in oracle,"

    hahhahahahahahah...

    you were joking right? oh...

    Java stored procedures (in Oracle) are usefull for ... well, those things you probably wouldn't do in the database anyway. Like file system and network operations. For business logic, PL/SQL is the better choice.
  • (cs) in reply to LordHunter317
    Anonymous:
    jsmith:
    In order for a performance optimization to be considered a hack, it would have to be implemented in a way that somehow detracts from the intended design.
    And the intended purpose of an index is to index data rows.  Not to store the data I'm actually interested in.  The moment you include a column that's not in the WHERE clause, you've exceeded the intended purpose of an index (generally).

    I like how you state the any use outside of "indexing rows" is a hack, but down below you mention indexes can be used to implement data integrity (which is more of a hack than covering indexes could ever be).  Just because it's outside your definition of what indexes should do, doesn't make it so.

    Anonymous:
    However, a covering index has no hack elements to it.
    Yes, it does.  It, by definition, contains more information than is required to index the table.

    Covering indexes simply index better than non-covering indexes.  By that logic clustered indexes and fillfactors are beyond the basic concept of indexing, so they are hacks too.  Unless, of course, you apply recursive logic so that anything you approve of isn't a hack simply because you approve of it.

    Anonymous:
    It performs its job without any adverse effects (there are zero non-performance side effects of implementing covering indexes).
    First, you're wrong; second, that's irrelevant to deciding whether it's a hack or not.

    So how would you define a hack?  Something that annoys you?

    Anonymous:
    It is totally reversible in seconds even while the system is running.
    Wrong, situation dependent.  Index builds on new tables may take hours.

    Sure index builds can take hours.  But, I said "reversible".  Removal of non-clustered indexes happens in seconds on even the largest tables.  Only non-clustered indexes can be covering indexes, so my statement is correct.

    Anonymous:
    It does not introduce any new maintenance issues.
    Wrong, depending on the data, adding new indexes may extend maintaince time or cause other issues.  PostgreSQL for example, requires periodic reindexing that takes time, especially on large indexes.  Most other databases have other such pitfalls. 

    I said "new" maintenance issues.  All DBMSs require index maintenance, but DBAs already know that.  Also, indexing strategies change often so any DBA worth his paycheck will have an automated process of some sort that finds and rebuilds all indexes.  In MSSQL, it's a freakin' wizard.

    Anonymous:
    It is widely understood.
    And?  Irrelevant to determining whether it's a hack or not.

    Back to what is a hack.  Now you have rejected "has weird side effects" and "obscure" from the definition of hack.  What's left?

    Anonymous:
    It is how the database vendors intended the solution to be designed
    No, they didn't.  If that were true, we'd use covering indexes all the time and we don't.  It is a solution.

    Like all indexes, they introduce overhead.  So, you never always use any indexes.  If you never use them, then you are leaving a lot of tuning on the table.  Since we often use them, when appropriate, they are a ligitimate solution just like defaults, check constraints, stored procedures, and 500 other solutions.

    Anonymous:
    Indexes are performance tools, you include what gives the best performance.
    No, that is not there sole purpose in many RDBMS.  They enforce data integrity in several.

    Ahhhh... so indexes have a side purpose other than performance.  So, we don't implement them for best performance.  Instead, we implement them according to our philisophical opions about how Dr. E. F. Cobb would have used them in the 1970s.  So, how do you feel about JOINs?

    Anonymous:
    I could understand your point if there were even a single downside other than the incredibly obvious and well-known downside of indexing in general -- it slows down data manipulation.
    There are, you seem to be quite igorant of the realities of DB maintance and planning.

    Nope.  It happens to be something I do rather well.

    Anonymous:
    Do you consider the fact that you have to turn your steering wheel counterclockwise in order to turn your car left a hack?  That is how it was designed, but I feel "I shouldn't have to do it".  Therefore, it is a hack.  Brillant.
    Except this isn't a comparable scenario.  Indexes were meant to index, not store data for retreival.  They're mearly lists of pointers to the actual data, ordered in some useful fashion.  They're addtionally used for relational integrity in certain situations, though not all DBMS use them for that, and it's not the only way to enforce uniqueness.


    Covering indexes are being used for data retrieval.  Just optimized one step beyond non-covering indexes.  It's like the fundamantals of Object Oriented Programming: it doesn't matter how an ArrayList is implemented, what matters is what it's does.  As long as you use it in appropriate situations, it will work for you.  The writers of your chosen framework will evolve the internal implementation of ArrayList in future versions, and they won't even tell you they did it.  Indexes are the same way -- every major database vendor has evolved indexes to the point where they will shortcut the table lookup step if possible.  So, we take advantage of that by sometimes creating indexes that encourage shortcutting.  Having some sort of chip on your shoulder about doing so is a bit weird.
  • (cs) in reply to ammoQ
    ammoQ:


    1. To access the database ;-)
    There are better alternatives like Hibernate, but sometimes they are an overkill. It's arguably a bad idea to bury SQL deep inside the business logic but it's IMO ok to do it in a source module dedicated to database access. Some people have a tendency to add more and more levels of  indirection but this only complicates things and (in many cases) gains nothing.
    4. a) It's easier to understand the code if you don't have to lookup the config files.
    b) If a statement needs to access a lot of host variables (like an insert or update statement), putting it into a config file doesn't improve anything.
    c) If you use a precompiler, it can check the SQL code during compile time.
    5. Yes


    All right, I'll give you that one about a source module dedicated to database access, but I've seen so many bad examples where inexperienced programmers litter their code with SQL statements, where it would be far more readable to lookup the sql from some place else, especially if (something common to all coding debates, the dreaded 'if') the statement is itself well-named.  I guess this is the "levels of indirection" you talk about?  Arguably, a source module dedicated to database access/statements is a form of indirection, but any full sql statement embedded along side logic is a maintenance nightmare, especially when SQL statements need to be re-used across an application.  Couple this with the inherent "Copy & Paste Inheritance" used by the general OO software-engineering public (ok, VB programmers ;P) and such a simple "Why not embed?" turns into a "I'm sure I've updated all the copies of that SQL... Where's the last one/ten?". 

    So, I'd ask you to pick from one of the follwing:
    Snippet one:
    <font size="2">// Lots of logic
    String mrQuery = "SELECT wibble FROM wobble WHERE bounce = ?";
    // Do thngs with mrQuery</font>

    Snippet two:
    <font style="font-family: Courier New;" size="2">// At top of class, file or whereever constants are declared
    public static final String mrQuery = "SELECT wibble FROM wobble WHERE bounce = ?";
    // Later in file
    // Do things with mrQuery</font>

    Snippet three:
    <font size="2">public interface MrQueryAndOtherAnimals
    {
        // other queries
        String mrQuery = "SELECT wibble... you get the picture";
        // other queries
    }</font>

    <font style="font-family: Courier New; color: rgb(0, 100, 0);" size="2">// At some other place:
    // Do something with MrQueryAndOtherAnimals.mrQuery;</font>

    Snippet four:
    <font size="2">// ok, I'm getting board, so assume the lookup set up is done elsewhere
    String mrQuery = someLookupMechanism.find("mrQuery");

    // Do things with mrQuery that mrsQuery may find disturbing</font>

    Do you see my point?  There's creeping indirection, but arguably snippet one leads to much more of a maintenance headace than say snippet 3, and snippet four is bordering upon the pathalogical.

    The goal for me here is to formulate a strategy that:
    1. Isn't a complete pain in the rump to implement.
    2. Isn't a complete pain in the left ventrical to maintain.
    3. Encourages developers to think of the "right" solution, rather than just the "quick" solution (which may involve shooting your line manager if he's a complete smacktard, or finding a better job/line manager.  I;ve worked for Line Manager who's philosophy was always "Do it the quick way".  He's caused so many problems that he surely must be punishing us all for some indiscretion during a former life)
    We've got three large systems here, one in C++ and two in Java.  The C++ project and one of the Java projects use embedded SQL and are both complete nightmares to maintain.  The amount of effort it takes to make seemingly simple changes are now immense.  The second Java project has its SQL embedded into a suite of configuration files which gives us:
    1. Ability to review SQL without reviewing the source code.
    2. Abilty to review the source code without the SQL.
    3. Review both together (you obviously need to!) :)
    4. A step in the right direction for database vendor independence (theoretically) without the need to recompile.
    5. Changes to the SQL are not spread across multiple files across multiple packages, they are neatly contained together, giving benefits for source control as the number of affected files for a given change can be small, again without having to recompile.
    This is not the first time this has happened.  Unless you are bloomin careful and repeatedly beat your developers with the correctness-stick, emdedding your SQL in anything other than a mechanism that supports simple and striaghtforward re-usablility is the equivalent of sticking forks in your legs and wondering why you are bleeding.  Anyhoo, I'll not convert any of you, Software Engineers by their very nature are stubborn beasts, but it's fun to soapbox :D

    Kind regards
  • Nick (unregistered) in reply to Corentin
    Anonymous:
    A great design indeed, if you want to write your SQL requests in klingon language.

    Truly an Enterprise system! (despite the lack of XML)


    Wouldn't you then want the statements defined in a properties file?

    Anyways, I can think of at least one reason for doing that.  Maybe they got tired of people making typos and not catching them in unit testing.  Now when they misspell 'SELECT' they will just generate a compile error...
  • (cs) in reply to mpswaim
    mpswaim:

    Anonymous:
    Thats not really bad.
    In fact, if you do a test -- you will see that using select * is on average faster that specifying all the columns!

    But what if the number of columns changes? There's some stuff (.net typed datasets, among others) that get really upset when the number of columns changes. I've stored multiple values in a column because it was easier to decode values than to patch all the applications that assumed a certain number of columns. Eventually I went on a rampage and removed every "select *" from all the application code and stored procedures I could find. Life is better now.



    OMG I can't believe my eyes...
    mpswaim:

    I've stored multiple values in a column because it was easier to decode values than to patch all the applications that assumed a certain number of columns.


    Tell me where you work... I never ever ever want to work there...
  • (cs) in reply to LordHunter317
    Anonymous:
    jsmith:
    A Hack?
    Yes, I shouldn't have to include the data I'm interested in in the index, mearly what I'm indexing by.

    It's a very well respected tuning strategy recommended by all major database vendors.
    Which doesn't change the fact it's a hack, like many performance optimizations are.


    If you have to do that you elsewhere made a BIG design error. It is a hack...
  • geekgirl (unregistered) in reply to Mr Goaty

    First of all can I say how much I enjoy this forum and how much I am learning!

    In my organisation (I'm quite new), we LOVE stored procedures. Rather than running an update statement, we'll create a stored procedure that takes all the variables we need and create an update statement using them. None of these is ever reused or anything, we make a new one with table names hardcoded into the procedure each time.

    We also wrap up all sorts of processes that would be much nicer in a "real" programming language. For example today my task is to write a new stored procedure which writes data to a file, calls a shell command to combine that with some formatting information and write the result to another file, call another shell script to turn that result into a PDF and then send an email with the PDF file attached.

    What do you guys think about doing things in this way? It "smells" wrong to me, I don't really want to be operating on files from inside a stored procedure, but I'm not sure what I'm basing this on. The star programmer says its always the right way so that we can change programming languages in the future, but I'm not sure we're using the right tools for the right jobs. Any thoughts? I'm calling the procedure from a programming language that is quite capable of doing what is needed!

  • (cs) in reply to Mr Goaty

    MrGoaty, why would I want to use the same SQL-Statement (as a string) in different places? Why not have exactly one method that does that query, and call that method whereever necessary?

    Problem of the config file method:

    Let's say you have a query that can be retrieved by calling

    <font size="2">String query = someLookupMechanism.find("mrQuery");</font>
    <font size="2"></font>
    <font size="2">
    How do you find all parts of the code that use mrQuery? Can be difficult if a wise guy writes code like that:

    </font>
    <font size="2">String query = someLookupMechanism.find((person.isMale()?"mr":"ms")+"Query");</font>

    <font size="2"></font>

    <font size="2">
    </font>
    If you can't easily find the places where a query is used, changing a query becomes dangerous.
    (Of course the same can happen with methods called by the means of reflection, but that seems rather unlikely to me)

  • (cs) in reply to geekgirl
    Anonymous:
    It "smells" wrong to me, I don't really want to be operating on files from inside a stored procedure, but I'm not sure what I'm basing this on. The star programmer says its always the right way so that we can change programming languages in the future, but I'm not sure we're using the right tools for the right jobs. Any thoughts? I'm calling the procedure from a programming language that is quite capable of doing what is needed!


    I'd say your instincts are good :)  However, unless you have the political and persuasive skills of a social genius, then you will be stuck whilst the "star programmer" maintains any political power.  I've tried to convince a manager that reinventing J2EE wheel was a bad idea, that nearly cost me my job (I didn't realise he was on £100 an hour and thus needed an incredibly long task to perform).

    The database is there to databasey things, like store information so it can be read back later.  Can I think of another analogy?  Yeah, why not.  It's like cooking all your food on the hot engine block of your car, you can do it, sure it may even look cool, but the kitchen cooker is designed for that job and would be a far better tool to use.  Safer too :)

    Kind regards,
  • (cs) in reply to geekgirl
    geekgirl:
    First of all can I say how much I enjoy this forum and how much I am learning!

    In my organisation (I'm quite new), we LOVE stored procedures. Rather than running an update statement, we'll create a stored procedure that takes all the variables we need and create an update statement using them. None of these is ever reused or anything, we make a new one with table names hardcoded into the procedure each time.

    We also wrap up all sorts of processes that would be much nicer in a "real" programming language. For example today my task is to write a new stored procedure which writes data to a file, calls a shell command to combine that with some formatting information and write the result to another file, call another shell script to turn that result into a PDF and then send an email with the PDF file attached.

    What do you guys think about doing things in this way? It "smells" wrong to me, I don't really want to be operating on files from inside a stored procedure, but I'm not sure what I'm basing this on. The star programmer says its always the right way so that we can change programming languages in the future, but I'm not sure we're using the right tools for the right jobs. Any thoughts? I'm calling the procedure from a programming language that is quite capable of doing what is needed!



    Unless you have most or all logic in stored procedures, I would not recommend writing files from a stored procedures. Oracle's DBMS_FILE has some limits and there are also security and performance issues. For example, the formatting script is called with the OS privileges of the database process, so it has the necessary rights to destroy the database.

  • (cs) in reply to ammoQ
    ammoQ:
    MrGoaty, why would I want to use the same SQL-Statement (as a string) in different places? Why not have exactly one method that does that query, and call that method whereever necessary?

    Problem of the config file method:

    Let's say you have a query that can be retrieved by calling
    <font size="2">String query = someLookupMechanism.find("mrQuery");</font>
    <font size="2"></font>
    <font size="2">
    How do you find all parts of the code that use mrQuery? Can be difficult if a wise guy writes code like that:

    </font>
    <font size="2">String query = someLookupMechanism.find((person.isMale()?"mr":"ms")+"Query");</font>
    <font size="2"></font>
    <font size="2">
    </font>
    If you can't easily find the places where a query is used, changing a query becomes dangerous.
    (Of course the same can happen with methods called by the means of reflection, but that seems rather unlikely to me)


    I'd say you are technically correct, which we all know is the best kind of correct :)  TBH I'd already described snippet 4 as bordering upon the pathalogical and the method by which I'd chosen to advocate its use does open itself to this kind of retort.  I should've been more robust.

    Anyway, I believe we are beginning to argue a very similar philosophical point, which is good. I think we are both in agreement that randomly shoving SQL statements here, there and everywhere is tantamount to sociopathic behaviour, and that an organised approach is far superior. We merely differ in the means by which we'd choose to organise them.  Thank you, this has been enlightening :)
  • (cs) in reply to Mr Goaty

    TBH, my view on the problem is strongly influenced by my history of C programming.

    In C (using Oracle's preprocessor), it's eigher

    EXEC SQL select foo, bloo, doo into :foo:i1, :bloo:i2, :doo:i3 from bar where foofoo=:foofoo;

    or two pages of complex code to create the statement "object", assign the host variables, execute the statement, read the results etc.
    It requires a certain amount of masochism not to embedd SQL in that situation.

  • (cs) in reply to More Stored Procedures Please!!!
    Anonymous:

    Wow, trying to keep application logic out of a database is the *stupidest* thing you've ever heard?  If there is one property of "enterprise" applications, it is the fact they are often systems that deal with many data sources and services, often performing logic between the disparate sources.  But lets list some reasons to keep the logic out of the database.

    1) Stored procedures tie you to a particular vendor
    2) Application logic in the database has to duplicate logic in the application
    3) Horrible source code control (in every case I've witnessed)
    4) Lack of OO design, hence the term "stored PROCEDURE"
    5) Difficulty migrating to a different database vendor/version
    6) Puts load on the database, which doesn't scale/cluster well since it is the data source of record

    Instead, put the logic in your objects, keep the database as minimal as possible, and certainly use a good persistence tool to ensure the updates and queries are correct.

    I admit, you can instaed take a very data-centric view of things, and many people successfully work under the idea that the database *IS* the application, and everything else is just a GUI/front end.  But this is not very scalable, not Object Oriented at all, and IMHO is only suitable for smaller systems, and certainly not large "enterprise" ;) systems.  All these points can, of course, be debated...

    But wow, this is the *stupidest* thing you've ever heard!?! WTF!!!


    I would say that treating the entire database as an object, and then having stored procedures being treated as accessor methods is probably the best thing you can do.
    Application developers should look at the database as an amorphous blob of data, and use the given methods to access that data.

    Stored procedures tie you to a vendor. True. OTOH, not having stored procedures is worse.

    Lack of version control -- not too difficult to fix.

    Application logic duplicates data logic. I would say that this is a pretty small thing to worry about. It violates the DRY principle, and I would remove that logic from the application itself.
  • (cs) in reply to devdas
    devdas:


    I would say that treating the entire database as an object, and then having stored procedures being treated as accessor methods is probably the best thing you can do.



    This "object" is the reference implementation of the God Object antipattern, right?
    ;-)
  • (cs) in reply to Dylan
    Anonymous:

    1) Stored procedures tie you to a particular vendor

    Who cares?

    5) Difficulty migrating to a different database vendor/version

    Again, who cares?



    In the company I work for we mostly use MS-SQL Server for our application, then we had a customer come along, a far far bigger customer than all the existing ones combined.  They wanted to use Oracle, no way they'd use SQL Server.  We could do this very simply because we had almost no code that was SQL Server specific.  We got a several million pound contract and completed the conversion very smoothly.  Now we can run our application on any combination of AIX / Windows and Oracle / T-SQL.

    Don't be so short sighted.


    I don't get the WTF in this article.  It looks like it was code from a DB back end that no application programmer should have to touch - there are probably two or more layers of code on top of it.  I would much rather use something like this using recordset objects than directly embedding SQL everywhere.  Far easier to maintain.
  • (cs) in reply to firewireguy
    firewireguy:


    In the company I work for we mostly use MS-SQL Server for our application, then we had a customer come along, a far far bigger customer than all the existing ones combined.  They wanted to use Oracle, no way they'd use SQL Server.  We could do this very simply because we had almost no code that was SQL Server specific.  We got a several million pound contract and completed the conversion very smoothly.  Now we can run our application on any combination of AIX / Windows and Oracle / T-SQL.

    Don't be so short sighted.


    You are right, if you make some kind of standard software (for customers yet to come), DB independence is a good thing.
    For in-house development or customized software, database switches are very unlikely.
  • (cs) in reply to The Internet
    Anonymous:

    SELECT * - I take comfort in knowing exactly what data columns are going to be returned. If a column name changes (for whatever reason) I would prefer to have the code break rather than have the error go undetected.



    At first I couldn't tell which side of this discussion you were supporting. However, even in using "select *", your code is still likely to break even if you are only accessing the columns by column number (shudder), because now you're putting "project_id" in the variables for customer name...
  • belugabob (unregistered) in reply to Iago

    Iago:
    Er, spot the deliberate mistake.  Yeah, deliberate.  Honest.

    If you're talking about the ninth bit, don't worry about it - that will come in handy for parity checks![:D]

     

  • (cs) in reply to ammoQ
    ammoQ:
    TBH, my view on the problem is strongly influenced by my history of C programming.

    In C (using Oracle's preprocessor), it's eigher

    EXEC SQL select foo, bloo, doo into :foo:i1, :bloo:i2, :doo:i3 from bar where foofoo=:foofoo;

    or two pages of complex code to create the statement "object", assign the host variables, execute the statement, read the results etc.
    It requires a certain amount of masochism not to embedd SQL in that situation.


    That's a fair point.  I suppose it's horses for courses isn't it?  In my experience, the embedding in Java and C++ has caused an awful lot of avoidable pain, but it depends very much on how the SQL is used and with what library or mechanism.  Fascinating! :)  However, if anyone does it here, POW!!!! ;)

    Kind regards.
  • (cs) in reply to Dan
    Anonymous:
    How about creating a code generator to generate the data access layer for the database? Although you will spend a lot of time implementing such a generator, you may save a lot of time when the database is big or when its schema changes. And of course you can reuse such a tool for more than one application.


    So basicly you mean something like Middlegen?

    http://sourceforge.net/projects/middlegen
  • LordHunter317 (unregistered) in reply to jsmith
    jsmith:
    I like how you state the any use outside of "indexing rows" is a hack, but down below you mention indexes can be used to implement data integrity (which is more of a hack than covering indexes could ever be).
    I didn't revise this statement.  They have two purposes.  I would go as so far to say that an index and an unique index are different beasts, simply because the latter are usually automatically generated and destroyed and have extreme constraints placed on them as to index method used and the like.

    Covering indexes simply index better than non-covering indexes.
    No, they don't.  Once I have all the information needed to index the database, any other data is excessive.

    By that logic clustered indexes and fillfactors are beyond the basic concept of indexing, so they are hacks too.
    Rather, they are implementation details.  However, you're correct in that they're beyond the basic concept.

    Anonymous:
    So how would you define a hack?
    Any usage that abuses the abstract data structure.  For a relational database, that's probably reasonable enough.  And they're all over the place.  Your problem is that you're assuming hack == bad, but that's no tthe case. 

    Anonymous:
    But, I said "reversible".
    And who cares?  The entire point of an RDBMS is that most operations are indeed reversible.

    Anonymous:
    I said "new" maintenance issues.
    Compouding existing issues is an isuse in and of itself.

    Anonymous:
    Like all indexes, they introduce overhead.  So, you never always use any indexes.  If you never use them, then you are leaving a lot of tuning on the table.  Since we often use them, when appropriate,
    Which is a far cry from saying their the vendor's intended solution.  They are one solution of many.  There are many other solutions, all legimiate, all recommended, to RDBMS performance problems.

    Covering indexes are being used for data retrieval.  Just optimized one step beyond non-covering indexes.
    And that optimization changes how the index is being used.  It's no longer merely a pointer.

    It's like the fundamantals of Object Oriented Programming: it doesn't matter how an ArrayList is implemented, what matters is what it's does.
    Ahh, but we frequently do care about both.  We care about all sorts of semantics about an ArrayList's implementation.

    Having some sort of chip on your shoulder about doing so is a bit weird.
    I don't in the least, beyond the normal early optimization one.
  • (cs) in reply to Jackal von ÖRF

    Jackal von ÖRF:
    Whiskey Tango Foxtrot:

    sebmol:
    So we all agree here that this is a stupid way of doing things. But let me ask you then: how do you write a generic data access layer for a SQL database? Or do you even do that? I can understand that performing direct queries makes a lot of sense in a desktop application, but how does that scale on a multithreaded web or server application? And how do you automate schema changes? Any thoughts?


    You cannot write a generic data access layer that works on all database systems because, dispite many similarities, they all do things very differently. The best you can do is separate your request from data from your implementation of the request for data, such as through an interface:

    public interface SampleDataAccessAbstraction
    {
      Person [] getPersonList();
      Person getPersonDetails(int personNumber);
      Person [] searchForPerson(string request);

    }

    You then implement that interface for each type of database you are using, and have each implementation instantiate the Person class for you. Sorry, but that's the only way you can do a "generic" data access layer.


    How about persistence tools such as Hibernate (http://www.hibernate.org/)? On the web site it says that Hibernate supports any JDBC-compliant database (more than 20 is mentioned). Does anybody here have experience on it or similar programs?

    They accomplish "genericity" (don't you just love coining your own terms? I do!) by doing exactly what I suggested: by providing an interface and knowing about and dealing with all of the issues with all of the databases that they support behind the scenes. As another poster said in response to your original question "with a lot of code". Even then, Hibernate still lets you get platform specific by allowing you to do direct queries on the database in a platform-specific way, essentially defeating the concept of a generic data access layer.

    Still, that's a moot point, because Hibernate is not a generic access layer, it is an object-relational mapping framework.  

  • (cs)

    This qualifies as a textbook example of "WTF". And I agree with the trend of recent postings - that "enterprise" is the new rallying cry of clueless managment. I guess they finally retired "Object Oriented Land Ho!"

    And I'm so happy to see "Object Oriented" being retired. This week I'm asked to add some features to a java component that we're licensing. The features are important to our business - and these changes affect the UI, the network security, and some of the runtime behavior. So, when I started looking into the code base, its 500 classes. Many many situations are simply object-oriented-silliness: Class A derives from Class B which implements Interface C, D and E. Seems straightforward enough, but, there are no other instances of ClassB - not instantiated, not derived from... its just an mental-abstraction placeholder! Sure, maybe someone was planning to use 'class B' elsewhere. Maybe there are other code bases that use 'class B'....but, given this repeated pattern, i conclude that  most likely, its just OO run amok.

    What are other signs of OO silliness are to be found? Entire class declarations that are essentially constants. The idea of over-riding static values in a class architecture is clever, and I can appreciate its usefulness. But, not as a design paradigm. Especially not for primitive data types like INTs and STRINGs.

    What kind of silliness can we expect from the "enterprise" cry ? So far, I've seen blind allegiance to "it must be XML" - to the point of utter stupidity. In the 90s, it made sense to marshall data from native data to stream an object from one process to another and do so in a standard format (call it COM, CORBA, RMI - whatever flavor you like), and there was the silliness of making anything and everything 'COM' compatible. There was inherent waste and overhead, and when applied apporpriately, those drawbacks were overcome by great gains in interoperability. Now, we take the same concept and add the bloat of text representation of data, and forced inclusion of structure.
    Again, there will be gains in interoperability between disconnected processes. But, already, I'm seeing code where parameters are being passed from a process to another via an XML tag set! A simple string user-id being passed as an xml node selection. Rediculousness. But its got enterprise written all over it.

    Taking that a step further, it would seem that "enterprise" silliness will lead to entire XML schemas, XSLT transforms, and XML datasets simply to exchange runtime values between processes.

  • Biff (unregistered) in reply to Jeff S
    Jeff S:
    VGR:

    I have to agree.  Logic embedded in the database can't possibly be OO.  SQL is a decent query language, but a HORRIBLE programming language.


    SQL is horrible programming language.  But it is an excellent *set-based* data processing language.   Literally hundreds of times more efficient than using one of them fancy OOP languages to process the rows one by one at the client.


    You can write a database and SQL interpreter in one of them fancy OOP langauges.  You can use data structures and even indexes into collections of objects.  Your claim is quite silly.  OO does not imply iterating linearly over a list of rows/objects/whatever.
  • OMG (unregistered) in reply to devdas
    devdas:
    Anonymous:

    Wow, trying to keep application logic out of a database is the *stupidest* thing you've ever heard?  If there is one property of "enterprise" applications, it is the fact they are often systems that deal with many data sources and services, often performing logic between the disparate sources.  But lets list some reasons to keep the logic out of the database.

    1) Stored procedures tie you to a particular vendor
    2) Application logic in the database has to duplicate logic in the application
    3) Horrible source code control (in every case I've witnessed)
    4) Lack of OO design, hence the term "stored PROCEDURE"
    5) Difficulty migrating to a different database vendor/version
    6) Puts load on the database, which doesn't scale/cluster well since it is the data source of record

    Instead, put the logic in your objects, keep the database as minimal as possible, and certainly use a good persistence tool to ensure the updates and queries are correct.

    I admit, you can instaed take a very data-centric view of things, and many people successfully work under the idea that the database *IS* the application, and everything else is just a GUI/front end.  But this is not very scalable, not Object Oriented at all, and IMHO is only suitable for smaller systems, and certainly not large "enterprise" ;) systems.  All these points can, of course, be debated...

    But wow, this is the *stupidest* thing you've ever heard!?! WTF!!!


    I would say that treating the entire database as an object, and then having stored procedures being treated as accessor methods is probably the best thing you can do.
    Application developers should look at the database as an amorphous blob of data, and use the given methods to access that data.

    Stored procedures tie you to a vendor. True. OTOH, not having stored procedures is worse.

    Lack of version control -- not too difficult to fix.

    Application logic duplicates data logic. I would say that this is a pretty small thing to worry about. It violates the DRY principle, and I would remove that logic from the application itself.


    No logic in the application?  What, do I query the database everytime the user fills out a field to make sure they have done something consistant?  Do I just spout out errors from the database when their stored procedure fails?  How the hell are you supposed to do object oriented development with NO LOGIC in the OBJECTS, and instead put into COBOL like stored procedures?
  • (cs) in reply to Biff
    Anonymous:
    Jeff S:
    VGR:

    I have to agree.  Logic embedded in the database can't possibly be OO.  SQL is a decent query language, but a HORRIBLE programming language.


    SQL is horrible programming language.  But it is an excellent *set-based* data processing language.   Literally hundreds of times more efficient than using one of them fancy OOP languages to process the rows one by one at the client.


    You can write a database and SQL interpreter in one of them fancy OOP langauges.  You can use data structures and even indexes into collections of objects.  Your claim is quite silly.  OO does not imply iterating linearly over a list of rows/objects/whatever.


    Biff -- I thought we were discussing ways to use existing database products.  If you want to go ahead and write your own database engine from scratch, by all means go for it. 

  • (cs) in reply to mctesty

    Anonymous:
    I have to disagree.  We make use of a generic data access layer pattern at the software development company I work for when we are concerned that our customers will want to change the database platform.

    Have you ever heard of ODBC? Or, perhaps, ADO / ADO.NET?

    You are on a road to maintenance hell. If you are in the business of creating custom software, then unless you Invent Everything, you will depend on something else, be it a PDF-creator, Active Directory, File System, etc.

    What you *should* be doing is designing your system so external dependency are kept in one logical layer: for example, the data access layer. This way, if you ever need to support a different vendor, it's simply a matter of editing the classes in your DAL and changing this. The time involved in doing this is magnitudes smaller than the time trying to invent some "generic data access layer".

    Anonymous:
    ... the call might look like:  SQLSelect("*", "Employees", "employeeID > 0", "hireDate", "1");  The SQLServerDataAccessLayer would create and run "SELECT TOP 1 * FROM Employees WHERE employeeID > 0 ORDER BY hireDate" returning a dataset.  The MySQLDataAccessLayer would create and run "SELECT * FROM Employees WHERE employeeID > 0 ORDER BY hireDate LIMIT 1". 

    What are you going to do when you need to optimize a query? You can't -- everyone does it differently. Orable uses comments, SQL Server uses keywords, DB2 uses [I have no idea]. So now your entire "generic layer" is useless.

    This is yet another reason you need to be using stored procedure. Every real database [*] has them, and they all function the same way when you use something like ODBC or your own invented "abstract data layer." That's the whole point of stored procedures -- simplifed access to a complex database system.

    [*] I do not acknowledge MySql as anything more than a toy. See http://weblogs.asp.net/alex_papadimoulis/archive/2005/10/26/428527.aspx.

  • (cs) in reply to OMG
    OMG:

    No logic in the application? What, do I query the database everytime the user fills out a field to make sure they have done something consistant? Do I just spout out errors from the database when their stored procedure fails?

    This is quite possible, exactly like that. The performance is better than you think.

    How the hell are you supposed to do object oriented development with NO LOGIC in the OBJECTS, and instead put into COBOL like stored procedures?

    At least in Oracle, stored procedures are not COBOL-like, rather Pascal-like (or ADA-like, to be more precise).
    This kind of development is not OO.

Leave a comment on “Enterprise SQL”

Log In or post as a guest

Replying to comment #:

« Return to Article