• balazs (cs)

    protected static final String FRIST = "FRIST";

  • Unisol (unregistered)

    I can understand the desire to move SQL literals to constants (benefits: interning/autocompletion/cleaner looking code), but what strikes me is that they didn't use these constants after defining them.

  • Geoff (unregistered)

    I am not sure its exactly a WTF to make some SQL reserved words constants. BRACE_BEGIN though is going a bit far. Especially when its actually a open parenthesis. One would normally expect it to be a brace '{'. That might be just an example of these constants acting as exactly the abstraction layer they were put in to provide. The really amazing part though is some of the SQL strings have spaces before, after, or before and after the token while others don't. So I guess you have to scroll up and read though the constants to know if you need to append additional spaces or not when using them or have a really good memory.

  • Black Bart (unregistered)

    Hey, I think I could use this idea: they are the first coding shop to be ready for SQL implementations in any language; they just need to update the constants and VOILA: Globalized SQL. The string definitions should be moved to a resource file though.

  • balazs (cs) in reply to Unisol
    Unisol:
    I can understand the desire to move SQL literals to constants (benefits: interning/autocompletion/cleaner looking code), but what strikes me is that they _didn't_ use these constants after defining them.

    They did use some, but still they just turned the whole thing to an unreadable mess. A better approach: jOOQ (have not tried yet, but looks promising for projects where JPA is not an option nor needed)

  • Musaran (unregistered)

    How does one use BETWEEN ?

    Why are ISNULL and ISNOTNULL the only ones not using _ naming ?

    Why is MIN's value lowercase ?

    What is the purpose of ONE_DAY_MINUS_ONE_MILLISEC ? Er, let me rephrase that : Is there a legitimate reason to have such value ?

  • TroelsL (unregistered)

    This will only solve syntax changes, not semantic changes. Obviously, there is a need to code a more elaborate framework that will handle that case also.

  • PoPSiCLe (unregistered)

    This is not so much an abstraction layer as a horrible calling on the dark ones.

    How is this in any way easier than just telling whomever is supposed to be using this how to write proper SQL? Or just have them use certain key words like INSERT, DELETE, UPDATE etc. and then create the correct syntax depending on what they're trying to do, if the users are that stupid?

  • pjt33 (cs)

    It's essentially http://thedailywtf.com/Articles/InsertSql().aspx all over again.

  • Customer (unregistered) in reply to Musaran
    Musaran:

    What is the purpose of ONE_DAY_MINUS_ONE_MILLISEC ? Er, let me rephrase that : Is there a legitimate reason to have such value ?

    There can be a legitimate reason: a "datcolumn between '2013-05-22' and '2013-05-23'" will select all dates from midnight 2013-05-22 up to and including midnight 05-23. So if you don't want to include that second midnight and still want to use "between", you could use "a millisecond less than that" (if database precision doesn't round it up to midnight...)

  • Steenbergh (unregistered)

    Bonus points for the inconsistent use of spaces in the literals. Some have a space before and after the word, some only before, some none at all...

  • faoileag (unregistered) in reply to Musaran
    Musaran:
    What is the purpose of ONE_DAY_MINUS_ONE_MILLISEC ? Er, let me rephrase that : Is there a legitimate reason to have such value ?
    Yup, there is. Typical student behaviour expressed in code would look like this:

    for (i = 1; i <= ONE_DAY_IN_MILLISECONDS; i++) { // sleep, yawn, eat, watch telly, party or go surfing if (i == ONE_DAY_MINUS_ONE_MILLISEC) { // burst into frantic activity to meet some // study-related deadline } }

  • faoileag (unregistered) in reply to Steenbergh
    Steenbergh:
    Bonus points for the inconsistent use of spaces in the literals. Some have a space before and after the word, some only before, some none at all...
    And they didn't use the SPACE constant to boot. If space ever changes, that will mean a lot of work because all the other constants are littered with space literals.

    That's very bad practice, having string literals like " " all over the place ;-)

  • Unisol (unregistered) in reply to PoPSiCLe
    PoPSiCLe:
    This is not so much an abstraction layer as a horrible calling on the dark ones.
    Wow. Permission to use this phrase as a skype status?
  • Kris (unregistered) in reply to faoileag
    faoileag:
    Steenbergh:
    Bonus points for the inconsistent use of spaces in the literals. Some have a space before and after the word, some only before, some none at all...
    And they didn't use the SPACE constant to boot. If space ever changes, that will mean a lot of work because all the other constants are littered with space literals.

    That's very bad practice, having string literals like " " all over the place ;-)

    Looks like the spaces are set up so you don't have to include any padding spaces in the strings you're concatenating these constants with. still fugly but at leaset some thought went into that (just not quite enough).

  • Princess Peach (unregistered)

    Let's hope they never want to call one of those pesky stored procedures. Code is always so much better when it contains raw SQL.

  • just me (unregistered) in reply to balazs
    balazs:
    Unisol:
    I can understand the desire to move SQL literals to constants (benefits: interning/autocompletion/cleaner looking code), but what strikes me is that they _didn't_ use these constants after defining them.

    They did use some, but still they just turned the whole thing to an unreadable mess. A better approach: jOOQ (have not tried yet, but looks promising for projects where JPA is not an option nor needed)

    While I don't know jOOQ (I haven't written any Java in quite some time), it looks similar to sqlalchemy's SQL expression layer. I find that approach an enormeous improvement over literal SQL, and clearly the way to go when building SQL query strings.

    DISCLAIMER: I don't do SQL on my day job, only for hobby projects; so I don't really know how that kind of stuff scales.

  • Martin (unregistered)

    It should have read in this from a file, this way they didn't even have to recompile the application for it to work when we someday change comma to something else.

  • chris (unregistered) in reply to TroelsL
    TroelsL:
    This will only solve syntax changes, not semantic changes. Obviously, there is a need to code a more elaborate framework that will handle that case also.
    public class SQLToNeo4JConverter implements SQLToObjectDbConverter {
       // have fun....
    }
    
  • TGV (cs) in reply to Customer
    Customer:
    There can be a legitimate reason: a "datcolumn between '2013-05-22' and '2013-05-23'" will select all dates from midnight 2013-05-22 up to and including midnight 05-23. So if you don't want to include that second midnight and still want to use "between", you could use "a millisecond less than that" (if database precision doesn't round it up to midnight...)
    If that's a legitimate reason, it's a legitimate reason for a bug. If you've got enough data, you'll find records created .5ms before midnight. There's nothing wrong with the < operator.
  • faoileag (unregistered) in reply to TroelsL
    TroelsL:
    This will only solve syntax changes, not semantic changes. Obviously, there is a need to code a more elaborate framework that will handle that case also.
    Well, this class is called "DAOJDBCBase", so I expect they also have something like "DAOCSVBase" and "DAOINIBase".

    The real data access object ("MyDAO") would then get an instance of the right "DAO...Base" for the job from a "DAOPersistencyAccessFactory" which in turn chooses the right "DAO...Base" implementation using a "DAOBaseSelectorStrategy" object.

    To be more enterprisey, somewhere in all this XML should play a role, but for the sake of illustration it is not necessary.

  • Steve The Cynic (cs) in reply to Musaran
    Musaran:
    How does one use BETWEEN ?

    Why are ISNULL and ISNOTNULL the only ones not using _ naming ?

    Why is MIN's value lowercase ?

    What is the purpose of ONE_DAY_MINUS_ONE_MILLISEC ? Er, let me rephrase that : Is there a legitimate reason to have such value ?

    There might or might not be a legitimate reason to have a named constant that contains a number that represents that amount of time, but ONE_DAY_MINUS_ONE_MILLISEC doesn't specify the units.

    It is reasonable (aside perhaps from SQL not supporting it) to expect a constant with this name to be 86399999000 (microseconds) or 86399.999 (seconds) or even 863999990000 (hectananoseconds, the increment of time in NTFS timestamps) or 0,999999988425926 (days, approximately).

    And it's a bad name for another reason - it doesn't explain what it is for. As written, the name describes (aside from the lack of units) what it represents at the most literal level, but why would I need such a thing? As it is, it is just as bad as our old friends:

    const int ZERO = 0;
    const int ONE = 1;
  • solfish (unregistered)

    I once made something alarmingly similar to this. I re-visited it recently and wondered wtf I was thinking. It didn't go quite as far as this though. No constants for brackets though I did do one for "=".

    It seemed like a good idea at the time, my only justification is that it was a situation where we couldn't use sprocs.

  • Smug Unix User (unregistered)

    This one isn't so bad you just need to write a little helper app that translates your raw SQL command into a giant string concatenation. Another function to reverse the translation and you can make it a plugin for your SQL execution tool of choice. At first glance this might seem like a lot of extra work, but when you dabble in storing SQL inside of your application you have already opened the pandora's box of WTF.

    nobis - you have nobis writing SQL with constants.

  • Julia (cs)

    More bonus points for simply append()ing column names into selects and inserts. Cue mandatory Bobby Tables reference in 3... 2... 1...

  • faoileag (unregistered)

    There are two more interesting things about the code nobody seems to have mentioned so far:

    First, where does "useLiteralCustomerId" come from? It's not a parameter of the methods in which it is used, it's not a member variable... does Java allow global variables?

    Second: CustIdUtil.getCustIdAsString(). This looks like a call to a static method to me, which begs the question: do all customers have the same id?

  • Iggy (unregistered)

    StringBuilder sql = new StringBuilder(1024); <---- sql.append(UPDATE).append(viewname).append(SET).append(columsForUpdate); if (additionalColumns != null && additionalColumns.length() != 0) { sql.append(additionalColumns); }

    Just wondering how the 1024 will hold the whole string, if there are a lot of additional Columns???

  • Unisol (unregistered) in reply to Iggy
    Iggy:
    StringBuilder sql = new StringBuilder(1024); <---- sql.append(UPDATE).append(viewname).append(SET).append(columsForUpdate); if (additionalColumns != null && additionalColumns.length() != 0) { sql.append(additionalColumns); }

    Just wondering how the 1024 will hold the whole string, if there are a lot of additional Columns???

    StringBuilder capacity will be extended - 1024 defines initial size.

  • VeeTwo (unregistered)

    I would like to update the following additional columns, please:

    • name
    • lastname
    • ' WHERE 1=0; SELECT password FROM Users;
  • Anonymous (unregistered) in reply to TGV
    TGV:
    If that's a legitimate reason, it's a legitimate reason for a bug. If you've got enough data, you'll find records created .5ms before midnight. There's nothing wrong with the < operator.

    Not necessarily. It depends on the RDBMS, data type and precision. For example in Oracle the DATE data type has a precision of seconds so you'd never have a record that's half a second between 23:59:59 on one day and 00:00:00 on the next.

  • Anom anom anom (unregistered)

    TRWTF is another article about constant abuse.

  • JArkinstall (unregistered)

    Such dumbassery is rather common, unfortunately.

    It provides no real cross-syntax benefit (even in such cases it makes more sense to run the full statement through a translator, to provide a bit of flexibility), but it's slower for developers to write, slower for the compiler (how many string concatenations does it take to change a light bulb?), and adds more chance of error (spacing).

    Every time someone tries to make their SQL "easier" with constants, a good programmer dies.

  • Chris L (unregistered)
    Comment held for moderation.
  • dwmc (unregistered)

    TRWTF is assuming that all of your readers are sexually attracted to females and are at all interested in marriage, assuming the option is even available to them. "We change our clothes, jobs and sometimes even wives." Way to annoy at least 2 large groups of people there.

  • Chris L (unregistered) in reply to dwmc

    What a stupid comment.

    Sometimes, we change our jobs - BUT WHAT ABOUT UNEMPLOYED PEOPLE, OR COLLEGE KIDS WHO'VE NEVER HAD JOBS? Someone think of the kids!

  • vil1 (unregistered)

    TRWTF : StringBuffer

  • Jellineck (unregistered) in reply to dwmc
    dwmc:
    TRWTF is assuming that all of your readers are sexually attracted to females and are at all interested in marriage, assuming the option is even available to them. "We change our clothes, jobs and sometimes even wives." Way to annoy at least 2 large groups of people there.

    Henny Youngman must drive you crazy, then.

  • dr memals (unregistered)

    thats not a brace '(', this is a brace '{'

    just imagine Crocodile Dundee reading that

  • OldCoder (unregistered) in reply to JArkinstall
    JArkinstall:
    Such dumbassery is rather common, unfortunately.

    It provides no real cross-syntax benefit (even in such cases it makes more sense to run the full statement through a translator, to provide a bit of flexibility), but it's slower for developers to write, slower for the compiler (how many string concatenations does it take to change a light bulb?), and adds more chance of error (spacing).

    Every time someone tries to make their SQL "easier" with constants, a good programmer dies.

    I suspect this has nothing at all to do with allowing for localisation or if the spelling of SQL literals ever change but instead is to save memory by avoiding the repeat of literals.

    These people never learn. They probably use more memory by calling these daft functions all the time than they ever did by using literals. Which the compiler is going to rationalise away in any case.

  • Sutherlands (cs) in reply to Unisol
    Unisol:
    I can understand the desire to move SQL literals to constants (benefits: interning/autocompletion/cleaner looking code),
    Er, no, at least not on two of them. You may get autocompletion (is that really going to make a difference?), but it's definitely not cleaner looking code and literal strings are interned anyway.
  • Sutherlands (cs) in reply to dwmc
    dwmc:
    TRWTF is assuming that all of your readers are sexually attracted to females and are at all interested in marriage, assuming the option is even available to them. "We change our clothes, jobs and sometimes even wives." Way to annoy at least 2 large groups of people there.
    Don't forget about the homeless streaker. How insensitive!
  • Saver of daylight (unregistered)

    question: how many hours has one day?

    once you found the right answer you see the real WTF in that: ONE_DAY_MINUS_ONE_MILLISEC

  • C-Derb (unregistered) in reply to Geoff
    Geoff:
    I am not sure its exactly a WTF to make some SQL reserved words constants. BRACE_BEGIN though is going a bit far. Especially when its actually a open parenthesis. One would normally expect it to be a brace '{'. That might be just an example of these constants acting as exactly the abstraction layer they were put in to provide. The really amazing part though is some of the SQL strings have spaces before, after, or before and after the token while others don't. So I guess you have to scroll up and read though the constants to know if you need to append additional spaces or not when using them or have a really good memory.
     </div></BLOCKQUOTE>TRWTF: Desperate attempts to justify absolutely shitty code.  It never gets old.
    
  • gregsdennis (unregistered)

    They expected the vocabulary of the query language to change and planned for it appropriately. What happens if the grammar also changes?

  • ForFoxSake (unregistered)

    Why? WHy?? WHY???

  • herby (cs) in reply to ForFoxSake
    ForFoxSake:
    Why? WHy?? WHY???
    Because, BEcause, BECAUSE!!
  • xaade (cs) in reply to Martin
    Martin:
    It should have read in this from a file, this way they didn't even have to recompile the application for it to work when we someday change comma to something else.

    Oh, we can put them in an XML file, so we can easily change that.

    Then the code that parses the XML file can load up another CSV file that tells how to parse XML, in case that changes.

    Finally we need a manifest that contains the current character that represents ',' in case the deliminator in our CSV ever changes.

  • qbolec (unregistered)

    do you know that :

    1. "INSERT IGNORE " known from MySQL is written "INSERT OR IGNORE " in Sqlite?
    2. "<=>" operator from MySQL is written " IS " in Sqlite?
    3. Some people suggest to use Sqlite (with memory engine) for (unit/integration) testing apps which then run in production environment with MySQL?
    4. can you imagine the fun you have, the day you realize you have to define
    <?php
    class PDOSqlite extends PDOEx
    {
      public function insert_ignore_command(){
        return 'INSERT OR IGNORE';
      }
      public function strong_equality_operator(){
        return ' IS ';
      }
    }
    ?>
    

    even though you know people will laugh at you?

  • xaade (cs) in reply to dwmc
    dwmc:
    TRWTF is assuming that all of your readers are sexually attracted to females and are at all interested in marriage, assuming the option is even available to them. "We change our clothes, jobs and sometimes even wives." Way to annoy at least 2 large groups of people there.

    It's the reader's job to insert replacers for content that doesn't directly apply to them.

    I've lost all patience for the easily offended. So much so that I have a one gesture for them, and it only needs one finger.

    Otherwise, he really should have wrote this: "We change our [Whatever applies], [Another whatever applies], and sometimes even [more shocking that could apply]."

    And, if it takes offending two large groups (which you won't, because most women I know aren't offended by terms like waiter and tailor, only the wastrels that don't have anything better to do since we stop making soap operas 20 years ago), so I don't have to read a damn MadLibs game, then so be it.

    It's not the writer's responsibility to make sure every statement applies to every reader.

  • chubertdev (cs) in reply to balazs

    [Deprecated()]

    balazs:
    protected static final String FRIST = "FRIST";

Leave a comment on “The More Things Change...”

Log In or post as a guest

Replying to comment #:

« Return to Article