• Edje (unregistered)

    I have actually seen this type of code because there was a CheckStyle rule that checked on string literals.
    A manager would run the tool and tell the developers to 'fix the code'. Resulting in the mess you see in the original post.

    Edwin

  • (cs) in reply to sebmol

    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.

  • Brian (unregistered) in reply to Smash
    Anonymous:

    If "GIMMIE" is the new "SELECT" then they will have to change it to:

    public const string GIMMIE = " GIMMIE ";

    Uhhh, no.  It would be:

    public const string SELECT = " GIMMIE ";

     

  • Magnus (unregistered) in reply to MadCow42

    While I don't think it outweighs the negative sides of writing code like this, there is one thing it helps you with: compile-time spell checking.

    I know I've gotten syntax errors from the BD because of spelling errors a few times when writing dynamic SQL, and if the time it takes to build and redeply is anything like what it is in the system I'm stuck with at the moment, this style of writing SQL could probably actually save development time. It's still a loss as it's not as readable as doing it the sane way, so maintainability suffers (but then again, if maintainance is a different account, it may yet be a win for monopoly money reasons...)

  • (cs) in reply to Brian
    Anonymous:
    Anonymous:

    If "GIMMIE" is the new "SELECT" then they will have to change it to:

    public const string GIMMIE = " GIMMIE ";

    Uhhh, no.  It would be:

    public const string SELECT = " GIMMIE ";

     

    No, you're not thinking enterprisey. It's:

    public const string GIMMIE = " GIMMIE ";
    public const string SELECT = GIMMIE;
  • Anonymous (unregistered)

    Does anyone else notice that:

      public const string UPDATE_LOGON = 
    SqlWords.UPDATE +
    SqlTables.CREDENTIALS +
    SqlWords.SET +
    SqlColumns.CREDENTIALS_LOGON_NAME +
    SqlWords.EQUALS +
    SqlMisc.PARAMS_FIRST +
    SqlWords.COMMA +
    SqlColumns.CREDENTIALS_LOGON_PASS +
    SqlWords.EQUALS +
    SqlMisc.PARAMS_SECOND;
    contains no SqlStupid.WHERE_CLAUSE?

  • beefcake (unregistered) in reply to More Stored Procedures Please!!!
    Anonymous:
    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.


    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!!!


    Well, you go ahead and have a look-see at the table structures and table names and just the sheer amount of closely duplicated tables in those enterprisey application databases that disallow procedures triggers and the like.  They are totally unmanageable.  I have yet to deal with one that wasn't chalk full of hacks to get around the issue.
  • (cs) in reply to Whiskey Tango Foxtrot? Over.
    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?
  • bleah (unregistered) in reply to More Stored Procedures Please!!!
    Anonymous:
    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.


    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!!!


    1) Stored procedures tie you to a particular vendor
        No argument there.
    2) Application logic in the database has to duplicate logic in the application
        Not if it's designed correctly.  You're assuming the code is bad.
    3) Horrible source code control (in every case I've witnessed)
        No argument here.
    4) Lack of OO design, hence the term "stored PROCEDURE"
        Java in oracle, .net in sql 2005, you can do oo in the database, but the bigger the gun to shoot         yourself in the foot with.  Not a valid argument.
    5) Difficulty migrating to a different database vendor/version
        No argument here.  I just don't think it should be done.
    6) Puts load on the database, which doesn't scale/cluster well since it is the data source of record
        As opposed to putting more load on the app server?  I've never had trouble clustering database servers. 
  • Dylan (unregistered) in reply to More Stored Procedures Please!!!

    1) Stored procedures tie you to a particular vendor

    Who cares?

    2) Application logic in the database has to duplicate logic in the application

    Wrong, the logic would be in the db INSTEAD.

    3) Horrible source code control (in every case I've witnessed)

    True, though can be mitigated w/VSS.

    4) Lack of OO design, hence the term "stored PROCEDURE"

    Largely pointless...I don't think data design should be dependent on OO app issues.

    5) Difficulty migrating to a different database vendor/version

    Again, who cares?

    6) Puts load on the database, which doesn't scale/cluster well since it is the data source of record

    Bingo!

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

    I agree with this for the most part, given #6.  Stored procedures are important for several reasons beyond encapsulating business logic (which isn't the best idea).  Compiled execution plans and managing permissions are the biggest.  There are many places where a good stored proc can save the database from suffering a developer writing code that iterates through a recordset and does lookup queries in a way that the db engine could do much better.

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

    I'd say avoiding SPs totally is pretty dumb, which is apparently what the poster was saying.  Sounds like the DBA was trying to cover for lack of expertise in that area.

    Dylan (a DBA)

  • Ass (unregistered) in reply to Jackal von ÖRF

    That's because Hibernate can't do SQL, or complex queries at all.  It has a limited query language of it's own - probably to ensure that it is vendor independant

  • bill ..no fred (unregistered)

    This reminds me of some truly moronic code I had to work with earlier this year, something like:

    query.Append("select").Append("*").Append("from")..etc

    It's more readable dontchaknow. Of course, that was the least of the problems with that project, which was eventually canned.

  • (cs) in reply to Dylan
    Anonymous:

    3) Horrible source code control (in every case I've witnessed)

    True, though can be mitigated w/VSS.


    VSS is horrible.  Said as a person who uses it every day, and who went through a great deal of effort to make set up versioning of MS-SQL stored procedures through VSS.

    (Just in case you decide to do it, the server component needs msvcr70.dll... they don't tell you this when you install it, of course...)
  • (cs) in reply to bill ..no fred

     I actually uttered a choked scream upon reading that. The first WTF that causes me physical discomfort. :|

  • (cs) in reply to [Si]dragon

    check out my avatar!

  • Willie (unregistered) in reply to Smash

    Next on the menu:

    public class RegExSymbols { pubilc const string CHAR_A = "A"; public const string CHAR_a = "a"; public const string KLEENE_STAR = "*"; ... }

    --silly-- while(!stomach.empty()) { for(hamburger = GIMMIE food FROM table WHERE meat = 'cow'; hamburger.stillTasty() || hamburger.notFinished(); hamburger.takeBite()); }

  • (cs)

    This code makes baby Jesus cry :'(

    Seriously, the application I'm working on has a lot of constants, some of which seem needless, but this is so far above and beyond anything from my darkest nightmares that it hurts.  How is it that people get away this this crap?

  • (cs)

    Cool, type safe SQL, now we don't need to wait for LINQ from Microsoft, instead we can use enterprise SQL and have type safe SQL today.

    See here for example using ADO.NET:

    // Assumes that connection is a valid SqlConnection object.
    string queryString = "SLEECT * FROM Prodcuts";
    SqlDataAdapter adapter = new SqlDataAdapter(queryString, connection);
    DataSet customers = new DataSet();
    adapter.Fill(customers, "Prodcuts");

    Will compile but will result in runtime error, which is a big no no for an enterprise application. So instead we use enterprise SQL.

    // Assumes that connection is a valid SqlConnection object.
    string queryString = sqlWords.SLEECT + SqlWords.STAR + SqlWords.FROM + SqlTables.PRODCUTS;
    SqlDataAdapter adapter = new SqlDataAdapter(queryString, connection);
    DataSet customers = new DataSet();
    adapter.Fill(customers, SqlTables.PRODCUTS);

    Will not compile, so the bug will detected and fixed before being even run. I'm pretty sure that's why they did it. Still sucks though.

  • (cs) in reply to pjsson

    Helpful for devs with no SQL knowledge or knowledge of the schema.  They just type

    string queryString = sqlWords.

    and IntelliSense gives them a list of options.

  • (cs) in reply to pjsson
    pjsson:
    DataSet customers = new DataSet();
    adapter.Fill(customers, "Prodcuts");


    Wuddafukk, a mistake in my code, the Dataset object should be named products or so and not customers, copy and paste error. Sorry didn't code review it enough before hitting the Post button.
  • (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 have to agree.  Logic embedded in the database can't possibly be OO.  SQL is a decent query language, but a HORRIBLE programming language.

    I was at a job where not only was some logic in the database, but all of the Java data objects were the result of GENERATED CODE which was automatically generated from each and every database table.  And did I mention that due to legacy requirements, each column's name was limited to sixteen characters?

    This nightmare is the result of two factors:

    1.  Inexperienced DBAs sometimes fancy themselves application coders.  "A little knowledge," as they say.
    2.  Some DBA did it back in 1990, when hardware limitations might have justified it.
  • (cs) in reply to Ricky

    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!
    -Ricky

    Yet another example of premature optimization.  SELECT * may be faster, but it will never be a lot faster except on queries so trivially small that performance doesn't matter.  On real DBMSs, after the first run it gets stored compiled like a stored procedure and the benefit goes away.

    Another issue, it is impossible for an Index to cover a query if you use SELECT *.  There goes one of the more powerful tools that can easily cut 80% of the execution time from some long running queries.  It also has a lot of the problems of weakly typed languages.  Example:  a lot of people like to say that doing SELECT *, either a) allows them to select columns that are added in the future or b) prevents errors when columns are migrated off to other tables.  Well for "a", if the column didn't exist at the time you developed the application, exactly what kind of productive use can you put it to?  And if your application has a mechanism or extensibility, then you can dynamically build the statement.  For "b", sure the statement won't bomb if the column isn't available, but you still won't get the data.  The call to retrieve the data will bomb.  If you don't use the data then why are you selecting it at all?

    SELECT * does not belong in production code.  "Because it's faster" isn't a good enough reason to outweigh the disadvantages.

  • (cs)

    I wouldn't classify this as a WTF at all. It gives you compile-time syntax checking for SQL statements.

    Consider this:

    object result = ExecuteCode("int a = 2; for( int i = 0; i < 100, i++) b += 3;");

    Do you like this code? I hope you would answer "no". Especially because there's a syntax error in it that would be a pain to track down. Why should embedded SQL code be any different?

    Granted, the syntax was pretty ugly. With some clever class design, you could probably get it to look like:

    SqlQuery selectActiveProducts = Select.AllFrom(SqlTables.PRODUCTS).Where(SqlColumns.PRODUCTS_ISACTIVE, Equals, "1");

    This would give you a fairly concise syntax without sacrificing SQLs power. And you'd still get compile time error checking.

    -- bob

  • Arachnid (unregistered) in reply to jsmith
    jsmith:

    Another issue, it is impossible for an Index to cover a query if you use SELECT *.  There goes one of the more powerful tools that can easily cut 80% of the execution time from some long running queries.



    What on earth are you talking about?

    Try this:
    CREATE TABLE test (
      name text,
      postcode integer
    );

    CREATE INDEX test_postcode_idx ON test(postcode);

    Insert a million rows.

    Now:
    EXPLAIN SELECT * FROM test WHERE postcode = 98001;

    I guarantee it will use the test_postcode_idx. There's absolutely no reason it shouldn't. Which columns you select does not in any way affect index usage.
  • (cs) in reply to Pastor_Of_Muppets
    Pastor_Of_Muppets:
    but this is so far above and beyond anything from my darkest nightmares that it hurts.  How is it that people get away this this crap?
    <font size="5">O</font>ften it is because this code is written by the senior project leader who is so great that his code does not need to be peer reviewed.


  • (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.



    Let me start with one confession: during the last 4 years, I made several applications that put (nearly) all logic into the database (in my case, it's PL/SQL in Oracle),

    1) So true, but couldn't care less - our customers already paid for Oracle. Using VB ties you to MS, using Java ties you to Sun. In my country, there is a saying: You have to die one death.
    2) Wrong, since there is nearly no application logic outside the DB
    3) Partialy true, though I found a way to mitigate that
    4) True, but I couldn't care less - OO design is a tool, not a goal
    5) True, see 1)
    6) Don't agree. The effort to run a stored procedure is negligible compare to the overhead of a multi-tier architecture. In many applications, "Logic" is not expensive compared to database access. DB abstraction layers have a tendency to do a lot of useless "SELECT *" through the network. That is a really expensive habit.


  • (cs) in reply to munificent
    munificent:
    I wouldn't classify this as a WTF at all. It gives you compile-time syntax checking for SQL statements.

    Consider this:
    object result = ExecuteCode("int a = 2; for( int i = 0; i < 100, i++) b += 3;");
    Do you like this code? I hope you would answer "no". Especially because there's a syntax error in it that would be a pain to track down. Why should embedded SQL code be any different?

    Granted, the syntax was pretty ugly. With some clever class design, you could probably get it to look like:
    SqlQuery selectActiveProducts = Select.AllFrom(SqlTables.PRODUCTS).Where(SqlColumns.PRODUCTS_ISACTIVE, Equals, "1");
    This would give you a fairly concise syntax without sacrificing SQLs power. And you'd still get compile time error checking.

    -- bob


    If I wanted compile-time syntax checking, there are much better though less verbose possibilities. E.g. preprocessors that do not only check the syntax, but also the names of tables, columns etc.
  • (cs) in reply to munificent
    munificent:
    I wouldn't classify this as a WTF at all. It gives you compile-time syntax checking for SQL statements.

    Consider this:
    object result = ExecuteCode("int a = 2; for( int i = 0; i < 100, i++) b += 3;");
    Do you like this code? I hope you would answer "no". Especially because there's a syntax error in it that would be a pain to track down. Why should embedded SQL code be any different?

    Granted, the syntax was pretty ugly. With some clever class design, you could probably get it to look like:
    SqlQuery selectActiveProducts = Select.AllFrom(SqlTables.PRODUCTS).Where(SqlColumns.PRODUCTS_ISACTIVE, Equals, "1");
    This would give you a fairly concise syntax without sacrificing SQLs power. And you'd still get compile time error checking.

    -- bob


    If I wanted compile-time syntax checking, there are much better though less verbose possibilities. E.g. preprocessors that do not only check the syntax, but also the names of tables, columns etc.
  • (cs) in reply to ammoQ

    sorry for the double post...

  • A Developer (unregistered) in reply to More Stored Procedures Please!!!
    Anonymous:
    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.


    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!!!

    So never use stored procedures? Not even to get a list of primary key elements to allow a Data Access Layer to load or persist it's objects?

    I've not worked on a large "Enterprise" system but having a Factory that allows you to load and save obects for your application sort of makes sense to me, and so it follows that these factories can make good use of views and stored procedures...Besides I'm a developer who doesn't believe developers should write SQL. By this I mean in your developer mindset. When you're writing SQL you should be thinking like a DBA about data, scalability and performance. When you're writing code you should be thinking like a developer and thinking with objects.

  • thornegreb (unregistered) in reply to Arachnid

    I believe he meant that the index can't satisfy the query, in other words, your select * will need to lookup the row location in the index and visit the table to retrieve the value for 'name', but a 'select postcode' can be satisfied with just the index.

  • (cs) in reply to munificent
    munificent:
    I wouldn't classify this as a WTF at all. It gives you compile-time syntax checking for SQL statements.

    Consider this:
    object result = ExecuteCode("int a = 2; for( int i = 0; i < 100, i++) b += 3;");
    Do you like this code? I hope you would answer "no". Especially because there's a syntax error in it that would be a pain to track down. Why should embedded SQL code be any different?

    Granted, the syntax was pretty ugly. With some clever class design, you could probably get it to look like:
    SqlQuery selectActiveProducts = Select.AllFrom(SqlTables.PRODUCTS).Where(SqlColumns.PRODUCTS_ISACTIVE, Equals, "1");

    This would give you a fairly concise syntax without sacrificing SQLs power. And you'd still get compile time error checking.

    -- bob

    Lets see you do a join? Also what happens if SqlColumns.PRODUCTS_ISACTIVE is not a column in SqlTables.PRODUCTS? Its still a runtime error. Its a little better but is it worth it?

    or what if every day your app dies b/c someone is not closing their cursor, and the only clue you have for who is doing it is a "Select * from products where product_isactive='1'". Lets see you find the culprit in you codebase? Do it!

  • (cs) in reply to Arachnid
    Anonymous:
    jsmith:

    Another issue, it is impossible for an Index to cover a query if you use SELECT *.  There goes one of the more powerful tools that can easily cut 80% of the execution time from some long running queries.



    What on earth are you talking about?

    Try this:
    CREATE TABLE test (
      name text,
      postcode integer
    );

    CREATE INDEX test_postcode_idx ON test(postcode);

    Insert a million rows.

    Now:
    EXPLAIN SELECT * FROM test WHERE postcode = 98001;

    I guarantee it will use the test_postcode_idx. There's absolutely no reason it shouldn't. Which columns you select does not in any way affect index usage.

    I didn't say it won't use the index, I said the index won't cover the query.  It's a performance tuning term that means that all the columns being used in the query are contained in the index an the table doesn't even have to be read.  Example:

    CREATE TABLE Employees
    (
      EmployeeID int,
      LastName varchar(30),
      FirstName varchar(30),
      DepartmentID int,
      HireDate datetime,
      SupervisorID int
    )

    Create a compound Index:

    CREATE INDEX in_biteme ON Employees(LastName, DepartmentID)

    Issue the statement:

    SELECT DepartmentID FROM Employees WHERE LastName LIKE 'M%'

    Because of the compound index, it will incur only a very small number of page reads (probably around 100 pages for a 1,000,000 row table).  If the index were only on LastName, it would possibly not be selective enough to even be useful (the same select statement would likely read 20 to 50 percent of the table -- about 3000 pages -- and it's sometime more efficient to read all of the pages in order rather than half of them out of order).

    In case you don't believe me, here are a bunch of links to other people's opinions on covering indexes (note that they are using MSSQL, MySQL, and DB2 in the examples):

    http://www-128.ibm.com/developerworks/db2/library/techarticle/0303kuznetsov/0303kuznetsov.html
    http://www.informit.com/articles/article.asp?p=27015&seqNum=6&rl=1
    http://db.apache.org/derby/docs/dev/tuning/ctunoptimz30768.html
    http://blogs.conchango.com/jamiethomson/archive/2005/09/30/2223.aspx
    http://www.mcse.ms/archive81-2004-10-1161421.html
    http://peter-zaitsev.livejournal.com/6949.html

     

  • (cs) in reply to Destroyer
    Anonymous:
    Romeo:
    Alex Papadimoulis:
      public const string INNER = " INNER ";
      public const string JOIN = " JOIN ";
      public const string INNER_JOIN = " INNER JOIN ";
       


    Allright.  Following this, we may have:
    public const string SELECT_STAR_FROM = " SELECT * FROM ";

    And so on...


     

    How about:

    public const string SELECT_STAR_FROM = SELECT + " " + STAR + " " + FROM;



    That'll never do. Look how many times you're repeating the literal " "! What happens if it changes? You need to do this:

    public const string SPACE = " ";
    public const string SELECT_STAR_FROM = SELECT + SPACE + STAR + FROM;
  • (cs) in reply to ammoQ
    ammoQ:
    munificent:
    I wouldn't classify this as a WTF at all. It gives you compile-time syntax checking for SQL statements.

    Consider this:
    object result = ExecuteCode("int a = 2; for( int i = 0; i < 100, i++) b += 3;");
    Do you like this code? I hope you would answer "no". Especially because there's a syntax error in it that would be a pain to track down. Why should embedded SQL code be any different?

    Granted, the syntax was pretty ugly. With some clever class design, you could probably get it to look like:
    SqlQuery selectActiveProducts = Select.AllFrom(SqlTables.PRODUCTS).Where(SqlColumns.PRODUCTS_ISACTIVE, Equals, "1");
    This would give you a fairly concise syntax without sacrificing SQLs power. And you'd still get compile time error checking.

    -- bob


    If I wanted compile-time syntax checking, there are much better though less verbose possibilities. E.g. preprocessors that do not only check the syntax, but also the names of tables, columns etc.

    ... or stored procedures.
  • (cs) in reply to VGR
    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.
  • A chicken passeth by (unregistered)

    Oh, brother.

    I've seen this format one too many times, and I'm still wondering where these "experts" learnt them from. Students could do better. >.>

  • John (unregistered) in reply to limelight
    limelight:
    Alex Papadimoulis:

    If you're looking at today's code and thinking, gee, shouldn't these be in stored procedures, or views, or something, then you're still thinking in the world of unenterpriseness

      
    /* SNIP */

    public class SqlQueries
    {
    public const string SELECT_ACTIVE_PRODCUTS =
    SqlWords.SELECT +
    SqlWords.STAR +
    SqlWords.FROM +
    SqlTables.PRODUCTS +
    SqlWords.WHERE +
    SqlColumns.PRODUCTS_ISACTIVE +
    SqlWords.EQUALS +
    SqlMisc.NUMBERS_ONE;

    /* SNIP */

    public const string UPDATE_LOGON =
    SqlWords.UPDATE +
    SqlTables.CREDENTIALS +
    SqlWords.SET +
    SqlColumns.CREDENTIALS_LOGON_NAME +
    SqlWords.EQUALS +
    SqlMisc.PARAMS_FIRST +
    SqlWords.COMMA +
    SqlColumns.CREDENTIALS_LOGON_PASS +
    SqlWords.EQUALS +
    SqlMisc.PARAMS_SECOND;
    }

      Looking at the second SQL string, I can't quite figure out where you would need a constant update statement. Other than some kind of "lets reset something or another", where would such a statement be useful? I'm thinking that perhaps this is part of some kind of setup program and it is used to initially condition the database for usage. Since the update statement contains a reference to "PARAMS_FIRST" and "PARAMS_SECOND", it may be used to actually generate a stored procedure (e.g."create procedure <something or="" another=""> <some input="" params=""> as " + UPDATE_LOGON).</some></something>



    I am assuming this is ADO.NET, and there you can use something like
    command.CommandText = "UPDATE table SET FirstName = ? WHERE ...";
    command.CreateParameter();
    command.Parameters(0).Value = "Bob";
    command.ExecuteNonQuery();
    command.Parameters(0).Value = "John";
    command.ExecuteNonQuery();

    or set the parameter value from a loop.  You can also do things like
    UPDATE table SET FirstName = @param1 WHERE ...

    command.Parameters("@param1").Value = "Bob";
  • mctesty (unregistered) in reply to Whiskey Tango Foxtrot? Over.

    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.  Here's the approach we take:

    You have a base DataAccessLayer class.  It has virtual (or abstract or whathaveyou) methods like SQLSelect(), SQLUpdate, SQLInsert, SQLDelete, SQLAlter, etc.  Each of these takes parameters specifying portions of the query.  For example, SQLSelect might have 4 parameters, like: string selectColumns, string joinTables, string where, string orderby, string top.  Then you create classes that inherit from this base class, so you might have a SQLServerDataAccessLayer and a MySQLDataAccessLayer.  Using the factory pattern, you create an instance of the DataAccessLayer based on some configuration settings and the code calls the SQLSelect method in a standard way.  I.e. 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".  This way we don't have to rewrite every sql statement when changing DB platforms.

    I'm interested to know what the WTF community thinks of this approach.

  • J (unregistered) in reply to Ricky
    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!
    -Ricky


    What RBDMS do you use ?

    There are very few instances where select * should be faster then selecting the column names.  You really should have better indexes (ever heard of a covering index?) if you find select * gives you better performance.
  • LordHunter317 (unregistered) in reply to More Stored Procedures Please!!!
    Anonymous:
    2) Application logic in the database has to duplicate logic in the application
    Not if the application is designed correctly.

    4) Lack of OO design, hence the term "stored PROCEDURE"
    Non-sequitur, and OO isn't required for many of these things anyway.  And SQL Server 2K5, Oracle, and PostgreSQL can all use several OO languages for stored procedures.

    5) Difficulty migrating to a different database vendor/version
    Not IME.

    6) Puts load on the database, which doesn't scale/cluster well since it is the data source of record
    The database is frequently eaiser to scale and cluster, because for many tasks,  it has to be capable of doing that anyway.

    More importantly, I'd rather have all my scalability problems in one place, that only have to be solved once, then have to deal with it at the web layer, and the business object layer (if there is a formal one i.e., J2EE EJBs) and then the database anyway.

    Far more importantly, one frequently doesn't have any choice in the matter, especially if multiple systems in multiple langauges need the same business rules anyway.

    and certainly use a good persistence tool to ensure the updates and queries are[ correct.
    Which ensures nothing about business logic.

    A better, more sane thing to argue for would be something akin to a rules-based expert system builtin to a relational database and along side it.  The only product I've ever worked with that did this worked well for what we used it for, but it wasn't fancy and the rules were very strict.

    But this is not very scalable,
    It's perfectly scalable, if designed correctly.

    not Object Oriented at all,
    Not inherently a good thing.

    scott:
    Try using Generics in 2.0.  You can write a complete Persistence layer that is 100% resusable.
    No, you can't.   You can get close, but you can't do this.
  • LordHunter317 (unregistered) in reply to VGR
    VGR:
    I have to agree.  Logic embedded in the database can't possibly be OO.
    Yes, it can be.

    SQL is a decent query language,
    Not really.

    but a HORRIBLE programming language.
    Which is why no one uses it for that.  Every vendor extends it heavily.  More importantly, the procedural stuff (or portions thereof) are only valid in the procedures, so it's really fair at that point to consider distinct languages that are partial-supersets of SQL.
  • Barney (unregistered) in reply to Willie
    while(!stomach.empty()) {
    for(hamburger = GIMMIE food FROM table WHERE meat = 'cow';
    hamburger.stillTasty() || hamburger.notFinished(); hamburger.takeBite())
    }
    Is liable to throw a BiteOutOfBurgerException. You need && instead of ||.
  • The Internet (unregistered) in reply to Arachnid

    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.

  • LordHunter317 (unregistered) in reply to jsmith
    jsmith:
    Yet another example of premature optimization.
    No, it's never optimization.

    Another issue, it is impossible for an Index to cover a query if you use SELECT *.
    This isn't the case.  A WHERE clause still allows for Index coverage.  Oh, you mean including the data records in the index?  That's a hack.  Yes, it can't be done.  It normally shouldn't be anyway.
  • martinl (unregistered) in reply to chazzie
    Anonymous:
    You can't write a perfect generic-for-everything layer any more than you can write a program that does everything with one button push and one line of code that both read "do what I mean".

    But you can! Take a look at the high-level functions of GNU-Script!
    (http://www.art.net/~hopkins/Don/text/gnuscript.html)
  • Ex-MT Worker (unregistered) in reply to martinl

    This is so true, at the academic institution where I work, I actually watched someone more senior than me adding this "enterprisation" to a PeopleSoft SQR report.

    #define SPACE ' '
    #define nul ''
    #define zero 0
    #define TRUE 'TRUE'
    #define FALSE 'FALSE'
    #define IS_NEW 'NEW'
    #define IS_MOD 'MOD'
    #define IS_EXP 'EXP'
    #define IS_ACT 'ACT'


  • (cs) in reply to [Si]dragon

    Is there any time when the SQL syntax might be localized, such as:

    "ESCOJA * DE ..."

    If so, this makes a lot more sense than it does without the context.

  • (cs) in reply to LordHunter317
    Anonymous:
    jsmith:
    Yet another example of premature optimization.
    No, it's never optimization.

    Another issue, it is impossible for an Index to cover a query if you use SELECT *.
    This isn't the case.  A WHERE clause still allows for Index coverage.  Oh, you mean including the data records in the index?  That's a hack.  Yes, it can't be done.  It normally shouldn't be anyway.

    A Hack?  It's a very well respected tuning strategy recommended by all major database vendors.  Please google "covering index" and study the 14,500 results that come back.  Just because you don't know it doesn't make it a hack.
  • LordHunter317 (unregistered) in reply to jsmith
    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.

    Please google "covering index" and study the 14,500 results that come back.
    Non-sequitur.

      Just because you don't know it doesn't make it a hack.
    I'm well aware of the technique, it doesn't change what it is.  In an ideal world, I wouldn't have to do that.

    Frequently, I do.  I never said it wasn't useful.  But it's certainly not a first-case, nor universal optimization.  It's dependent on schema, load, hardware, and a huge number of other factors.

Leave a comment on “Enterprise SQL”

Log In or post as a guest

Replying to comment #64945:

« Return to Article