• Mike H (unregistered)

    I've done something like this, but in C++, where you can't use + on a char*

    We were using MFC, which has CString as the "string class."

    So instead of doing something like

    CString statement = CString("SELECT ") + arg1 + CString(" FROM ") + arg2 .....

    You use constants defined above. It makes more sense for constants used repeatedly.

  • (cs) in reply to frits
    frits:
    luis.espinal:
    ... stuff ... see previous post

    So...What is your one size fits all solution to using SQL directly from Java or any other general purpose language?

    Uh, since you seem to have a problem reading, let me quote a piece of the post that you were replying with that question, in bold and red just in case you still miss it:

    me:
    There are better, more solid alternatives to do just that - SQL mappers, SQL statements dynamically extracted from property files, OR mappers, etc.

    Don't tell me that those aren't general solutions to the problem of coding RDBMS access logic within a general purpose language.

    Besides, since I never suggested one, but several solutions (refer to the text above quoted in red and bold for your reading assistance), your question about my "one-size-fit-all" solution to the software development problem in question seems to be a little bit out of place and of a facetious, knee-jerking kind.

    frits:
    What if the code is intended to be used with several different DBMS implementations?

    Uh, OR mappers, one of the solutions included in the text above, generously quoted in red and bold to assist you on your reading comprehension?

    Better yet, could you please tell me which solution other than OR mappers happen to be the most popularly used when you have an application that might be deployed against multiple DBMS implementations?

    Or even better, let's look again at the the featured piece of code that happens to be the focus of this discussion.

    How does its usage of in-code constants for string literals help the code intended to for usage with several different DBMS implementations?

    At the end of the day, the overwhelming amount of applications are intended from the start to use one type of database system.

    The overwhelming class of JEE apps out there are data centric, they happen to exist to provide access to existing data, their relations and the business behavior imbued in them. That data overwhelmingly exist in databases from the same vendor, or from a set of vendors known a-priori.

    Your question only applies to applications for which, they, the applications themselves, are the focus, and they just need a way to persist themselves. Data is secondary, it does not exist a priori and has no lifecycle outside the application itself. For that, the RDBS is just another volatile configuration parameter, not a established architectural requirement.

    And for that class of applications (a small subset of the applications written in JEE), OR mappers (one of the things mentioned in the text quoted in red and bold, are the way to go.

    Having SQL statements in constants that require code changes and re-compilation with every change, that's not the way to go.

    Nice red herring riding a straw man. Try harder.

  • (cs) in reply to luis.espinal
    luis.espinal:
    frits:
    luis.espinal:
    ... stuff ... see previous post

    So...What is your one size fits all solution to using SQL directly from Java or any other general purpose language?

    Uh, since you seem to have a problem reading, let me quote a piece of the post that you were replying with that question, in bold and red just in case you still miss it:

    me:
    There are better, more solid alternatives to do just that - SQL mappers, SQL statements dynamically extracted from property files, OR mappers, etc.

    Don't tell me that those aren't general solutions to the problem of coding RDBMS access logic within a general purpose language.

    Besides, since I never suggested one, but several solutions (refer to the text above quoted in red and bold for your reading assistance), your question about my "one-size-fit-all" solution to the software development problem in question seems to be a little bit out of place and of a facetious, knee-jerking kind.

    frits:
    What if the code is intended to be used with several different DBMS implementations?

    Uh, OR mappers, one of the solutions included in the text above, generously quoted in red and bold to assist you on your reading comprehension?

    Better yet, could you please tell me which solution other than OR mappers happen to be the most popularly used when you have an application that might be deployed against multiple DBMS implementations?

    Or even better, let's look again at the the featured piece of code that happens to be the focus of this discussion.

    How does its usage of in-code constants for string literals help the code intended to for usage with several different DBMS implementations?

    At the end of the day, the overwhelming amount of applications are intended from the start to use one type of database system.

    The overwhelming class of JEE apps out there are data centric, they happen to exist to provide access to existing data, their relations and the business behavior imbued in them. That data overwhelmingly exist in databases from the same vendor, or from a set of vendors known a-priori.

    Your question only applies to applications for which, they, the applications themselves, are the focus, and they just need a way to persist themselves. Data is secondary, it does not exist a priori and has no lifecycle outside the application itself. For that, the RDBS is just another volatile configuration parameter, not a established architectural requirement.

    And for that class of applications (a small subset of the applications written in JEE), OR mappers (one of the things mentioned in the text quoted in red and bold, are the way to go.

    Having SQL statements in constants that require code changes and re-compilation with every change, that's not the way to go.

    Nice red herring riding a straw man. Try harder.

    Wow, you have a real chip on your shoulder. My original question would have been better phrased as follows:

    "Do you think that all SQL statements written in a general purpose language like Java are WTFs, no matter the context?"

    Remember, no amount of psuedo-intellectual masturbatory rants on the internet will fill the empty hole inside. No one cares how smart you think you are.

  • (cs) in reply to frits
    frits:
    Wow, you have a real chip on your shoulder. My original question would have been better phrased as follows:

    "Do you think that all SQL statements written in a general purpose language like Java are WTFs, no matter the context?"

    Yes, it should have been better re-phrased like that, and my response would have been of a different tone and content.

    But it was not, in addition to the fact that your question - the question that you posted as is was answered in the very same post that you were quoting from. Don't like the answers you get, perhaps then revisit the nature of your questions.

    frits:
    Remember, no amount of psuedo-intellectual masturbatory rants on the internet will fill the empty hole inside. No one cares how smart you think you are.

    Except for the fact that my rant wasn't targeted to the whole audience to get them to e-worship me (nice strawman btw.) It was addressed to a very specific question you posted, and the implication that I have or was suggesting to have a 'one-size-fit-all' solution (which I was not.) Again, if that wasn't your intention, then learn to express yourself better with questions appropriate for the responses you wish to get.

    Now, for the sake of entertaining your revisited question:

    frits:
    Do you think that all SQL statements written in a general purpose language like Java are WTFs, no matter the context?"

    Nope. That is not my opinion, nor that impression can be derived from my post.

    Obviously there are legitimate cases where you need to hand write SQL statements in a general purpose language. You can have memory constrains that prevent you from using a OR mapper. You can be in a diskless environment and thus unable to externalize sql statements. Maybe you are developing a prototype. Or maybe you have this critical make-it-or-break-it "hot fix" to implement in production right now this second and the only way to get it out is by hard coding a sql statement in a jsp page that can perhaps if time and resources are available can be refactored out later.

    But all those are fringe cases. In the absence of additional information or some specific context, in the general case, in this time and age, with what we know about how to develop software, with all the tools and practices that we have available in JEE/.NET/Grails/whatever, yes, it is a WTF.

    More on to the general usage of constants, doing something like this:

    static public final String SELECT = "select ";

    Is no different from doing this in C/C++:

    #define BEGIN { #define END }

    Or in general, such a thing falls under the same type of things as this:

    static public final int ONE = 1;

    A constant is not just an immutable value with a name slapped to it, and its usage does not necessarily guarantee any level of engineering appropriateness.

    For a constant to be valid, its existence must have some intrinsic functional value. And its usage should also have some sort of functional value that is intrinsic, irreducible and that is not superfluous.

    A constant (or anything for that matter) whose usage is superfluous is just a misguided use of the language support for constants. It doesn't amount to good software engineering practices.

    In fact, it amounts to useless coding rituals, almost like superstitious usage of chicken bones and silver bullets.

    A constant representing a stand-alone or parametrized SQL statement is a valid constant - it can be later replaced with an externalized one if you so desired w/o breaking the code using it.

    A bunch of constants representing SQL lexical tokens for building SQL statements (unless you have a requirement to build arbitrary SQL statements on the fly), that is not a valid use of constants.

    Now, take that rant and berate it as a pseudo-intellectual masturbatory rant if that gives you comfort.

  • nick (unregistered)

    Oh, look. Someone wrote a barely functional piecemeal wrapper for about 1/5000th of MySQL flavored SQL.

    Hallelujah.

  • coyo (unregistered) in reply to frits
    frits:
    So...What is your one size fits all solution to using SQL directly from Java or any other general purpose language? What if the code is intended to be used with several different DBMS implementations?

    Ah, "what if" coding. The kissing cousin of premature optimization.

  • (cs) in reply to panschk
    panschk:
    The point is really not to "plan for the future", but to prevent typos when writing SQL. I think it's a pretty good idea actually.

    Hey, I have an idea. How about testing the sql before you embed it? Just a thought but I find that when I execute the query statements in tool of choice before hand I can figure out exactly where the typos are. Just thought I'd share.

    Or better yet: don't friggin embed sql in your app. Put it a s'proc in the database where it can be massaged, handled, properly vetted, and you are forced to parameterize the thing. But I guess that is really asking too much.

    I truly believe that programmers who code this way and have their apps broken into because of sql injection ought to have a 1 year minimum jail sentence and their name put on a wall of shame.

  • Dave (unregistered) in reply to Been there

    It's NOT WRONG??!?!?! Seriously? I'm still waiting for the punchline.

    Please provide your name, that of your company and any projects you're working on, so that those of us who aren't complete idiots can be sure to steer clear.

  • (cs) in reply to frits
    frits:
    "Do you think that all SQL statements written in a general purpose language like Java are WTFs, no matter the context?"

    I do. Keep it in the database. Period.

    First, it has no meaning outside the context of the dbms that it's running against.

    Second, to tweak you don't have to redeploy the app.

    Third, professional DBA's are more comfortable reading sql in their own tool.. Not a developers.

    Fourth, dbms' won't even compile a s'proc if it has typos.

    I could go on about security and more but the point is the very idea embodied in the class file shown is repugnant.

  • Anon (unregistered)

    After all these nice string constants, the real WTF is in this line:

    LoggingSystem.getLogger().inform(3, strQuery);

    Seems like the LoggingSystem has been written by some other guy.

  • (cs) in reply to clively

    I'd say your response has more to do with power and control than anything else.

  • (cs) in reply to frits
    frits:
    I'd say your response has more to do with power and control than anything else.

    So? Does it prove it wrong?

    Nice straw man (read you suck at building logical arguments.) But then again, it is easier to say that than to present a logical counter argument.

    But just to play with that little 'power trip' straw man that you just built, let's assume for the sake that indeed my response is more of an ego trip than anything else...

    ... it's still right. There are good software coding practices and bad software coding practices.

    SQL strings in code are a WTF, whether as literal strings or as glorified constants. So that's the answer to your last question.

    If you are a coder worth his salary, you can either accept that statement or demonstrate with logically sound arguments that my claim is wrong.

    Or you can just build a comfortable straw man, avoid the issue and simply claim that I'm on a e-power trip (and worse still, keep believing that such coding practices are acceptable while working and getting paid as a software professional.)

    Whatever suits your (absent or present) programming and logical argumentative skills, dude.

  • (cs) in reply to luis.espinal

    Hey dude, don't call me dude.

    Isn't calling everything a straw man, a straw man?

    BTW- this isn't debate club, kiddo. I don't owe you a detailed, iron-clad argument. You make a lot of bare assertion fallacies, yourself (I can look up logical fallcies on the interwebs, too). I couldn't care less if you believe what I write here-- I'm just having fun. You, on the other hand, must either be the submitter of this fine article, or just an uptight jerk.

  • Haxxor (unregistered)

    TRWTF is injection attack exposure.

    mstrSelect = " *, (DROP TABLE USERS) as OMG"

  • Max Romantschuk (unregistered)

    Some DB abstraction layers have some code like this, but it makes sense when you build queries in an OOP fashion and make the whole interface portable across DBMSs.

    Sure, it's a hassle, but it's quite nice to build a huge query with calling methods conditionally instead of having a huge mess of ifs and string concatenation.

    The key is to keep the query as an object until the time it needs to be executed, then you actually have a semantic model of the query that can be serialized into anything really.

  • John Carter (unregistered) in reply to Haxxor

    Why is your sql in ALL CAPS? DOES IT LOOK SERIOUSER THIS WAY?

  • (cs) in reply to frits
    frits:
    Hey dude, don't call me dude.

    Isn't calling everything a straw man, a straw man?

    BTW- this isn't debate club, kiddo. I don't owe you a detailed, iron-clad argument. You make a lot of bare assertion fallacies, yourself (I can look up logical fallcies on the interwebs, too). I couldn't care less if you believe what I write here-- I'm just having fun. You, on the other hand, must either be the submitter of this fine article, or just an uptight jerk.

    Sure. You win.

  • (cs) in reply to Max Romantschuk
    Max Romantschuk:
    Some DB abstraction layers have some code like this, but it makes sense when you build queries in an OOP fashion and make the whole interface portable across DBMSs.

    You bring a good point that code like this would be found in DB abstraction layers. But it seems to me that the existence of tried-and-true, mature OR/SQL mappers like TopLink, Hibernate or iBatis make approaches like this like re-inventing the wheel.

    There would have to be some really unique requirements to make this type of approach more suitable than using an existing OR mapper.

    Max Romantschuk:
    Sure, it's a hassle, but it's quite nice to build a huge query with calling methods conditionally instead of having a huge mess of ifs and string concatenation.

    The key is to keep the query as an object until the time it needs to be executed, then you actually have a semantic model of the query that can be serialized into anything really.

    The thing about building huge queries is that, most of the time, you know all your queries a-priori, specially if you have a solid OO model (or data model), or (as it's usually the case), you have a pre-established ER model and (if you are lucky), it is well-normalized or at least not so messed up that you can't determine what queries to use to get X or Y until run-time.

    This is a gross generalization, mind you, but a sign that your ER-model is well-normalized (or at least close to it) is that you can determine your queries a-priori, however complex the queries might be. This generalization does not include ad-hoc reporting requirements, though.

  • Stanley H. Tweedle (unregistered)

    One great reason for this technique is less literals, so the executable code is smaller. Code with lots on SQL would be lots smaller.

    Executable shrinking is quite important for embedded devices due to storage and memory constrants on such devices.

    Which provides another WTF: Why would an embedded device's programming be chock full of SQL?

  • coyo (unregistered) in reply to luis.espinal
    luis.espinal:
    SQL strings in code are a WTF, whether as literal strings or as glorified constants. So that's the answer to your last question.

    Most SQL interface libraries have query parameters. Use them properly and there are no security concerns.

    There are times where you need dynamically built queries. Programs writing programs is actually a significant part of projects of consequence.

    In the world of stable projects (a very nice and actually real place), the SQL is not going to change very much at all. You then don't need to waste effort and add complexity by shoehorning a system to read queries from external sources. Stored procedures can be nice, but that is not always available or appropriate.

  • Design Pattern (unregistered) in reply to Haxxor
    Haxxor:
    TRWTF is injection attack exposure.

    mstrSelect = " *, (DROP TABLE USERS) as OMG"

    Clever, but this would be an attack of an application developer, not an application user.

    If you can't trust your application delelopers enough that you expect them to attack your database, why do you run the application with DBO permissions (which would be required for a "DROP TABLE").

    Applicaton developers will not have any need for these clever attacks, they can simply type:

    strUpdate = DELETE + FROM + mstrTable;

    (Does not even require DBO permissions).

  • JJ (unregistered)

    You all get it wrong. The real WTF is that there is no OR.

  • (cs) in reply to coyo
    coyo:
    luis.espinal:
    SQL strings in code are a WTF, whether as literal strings or as glorified constants. So that's the answer to your last question.

    Most SQL interface libraries have query parameters. Use them properly and there are no security concerns.

    There are times where you need dynamically built queries. Programs writing programs is actually a significant part of projects of consequence.

    In the world of stable projects (a very nice and actually real place), the SQL is not going to change very much at all. You then don't need to waste effort and add complexity by shoehorning a system to read queries from external sources. Stored procedures can be nice, but that is not always available or appropriate.

    No argument there, at all. I would still be incline to either externalize parametrized SQL statements or (at worst), define constants for the same parametrized statements. I would avoid constantify SQL fragments, though (like the code sample being discussed) unless I really, really have a real requirement to build queries on the fly.

  • Martin Tetreault (unregistered)

    And, best of all, this way of coding catch misspelled mistake at COMPILE TIME !!!

  • Brandon (unregistered)

    Now he doesn't have to put quotes around his SQL keywords when he's concatenating strings!

  • coyo (unregistered) in reply to luis.espinal
    luis.espinal:

    No argument there, at all. I would still be incline to either externalize parametrized SQL statements or (at worst), define constants for the same parametrized statements. I would avoid constantify SQL fragments, though (like the code sample being discussed) unless I really, really have a real requirement to build queries on the fly.

    I agree with you. I would not want to constantify those fragments either, and I don't like the code sample given.

    I don't like to externalize though for queries used exactly once. I want to see it there in the code and not have to hover over some text or go searching for whatever XML file. If the queries are going to be changing a lot, or are based on a local setup, that would be a useful case for the externalization for me.

    To those who say : 'well, this catches an error on compile time' miss the mark. That almost says to me "Well, it compiles, I don't have to check if its giving the correct answer".

    Captcha decet : I'd be a more decet poster if I used spell check more often.

  • someone (unregistered)

    Defining the strings as named constants is a standard practice to prevent the typos inside the strings. Now if you mistype the constant name, the compiler will immediately tell you about it. But if you spread the strings as such around the code, you won't know about the typos until you actually try to run the query and it fails. Look at the X11 sources for another example.

  • Cote (unregistered) in reply to Homer

    My coworker has gobs of this in every app he writes. He says he's the most skilled programmer in our area. Who are you to criticize brilliant coworkers standard code? Haha

  • tristique (unregistered) in reply to panschk
    panschk:
    The point is really not to "plan for the future", but to prevent typos when writing SQL. I think it's a pretty good idea actually.

    And as we all know, typos account for all the errors in SQL, so the net gain is that we can skip testing the queries! Hurray for compile time!

  • CapCity (unregistered)

    Huge advantage is that if SQL standards were to replace SELECT with something else, this code only needs changed in one place.

    <facepalm>
  • Ando (unregistered)

    Cool strLINQ!!!!!

  • GeniusCodeMonkey (unregistered)

    Haven't people heard of calling Stored Procedures? Never build up SQL from within code. Always call a Stored Procedure to get your data. Its a lot simplier and more maintainable.

  • DcA (unregistered)

    Assuming this is Java, I would recommend the use of a StringBuilder or StringBuffer and using .append(), instead of simple string concatenation. But, that's just me.

  • coyo (unregistered) in reply to GeniusCodeMonkey
    GeniusCodeMonkey:
    Haven't people heard of calling Stored Procedures? Never build up SQL from within code. Always call a Stored Procedure to get your data. Its a lot simplier and more maintainable.

    There are times to use stored procedures, but I would not say its simpler and more maintainable. If I have a sql query in the code, the code around it gives it context. I can edit it right there and don't have to hover over or track down what the query is doing. The reasons I can see for the stored procedures are based on the nuts and bolts of performace. "Simpler and Easier" is extremely subjective.

    Also consider the case of a common code base and a similar table structure spread across multiple databases. To update the query in the code just takes changing that code. To update the stored procedure involves going to each database instance and updating it.

    Saying 'always do this' is like saying 'One Size Fits All' in a world of anerexics and overeaters.

    Captcha : dignissim - dignity for my narcissism.

  • Patrick (unregistered) in reply to Homer

    This is the beginning of any heavy duty DBA layer... it's just terribly incomplete.

  • Anonymous (unregistered) in reply to DcA
    DcA:
    Assuming this is Java, I would recommend the use of a StringBuilder or StringBuffer and using .append(), instead of simple string concatenation. But, that's just me.
    And your code would run slower as a result. Using a StringBuffer for a measly 3 appends will reduce performance when compared to regular string concatenation. Profile it some time.
  • (cs) in reply to Anonymous
    Anonymous:
    DcA:
    Assuming this is Java, I would recommend the use of a StringBuilder or StringBuffer and using .append(), instead of simple string concatenation. But, that's just me.
    And your code would run slower as a result. Using a StringBuffer for a measly 3 appends will reduce performance when compared to regular string concatenation. Profile it some time.

    Profile this.

  • André (unregistered)

    You have to admit that it's kind of qute though?

  • what (unregistered) in reply to Been there

    Are you kidding me? This code is terrible. Your IDE and other tools are supposed to be working for you; not the other way around. If you ever find yourself rewriting (perfectly good) code to simply satisfying a tool, you need to reconsider what the tool is being used for.

  • bananaman (unregistered)

    just because the author can't imagine where using something like this would be more economically viable than alternatives doesn't mean it doesn't exist (it does, trust me).

    FAIL by lack of imagination

  • Aaron Digulla (unregistered) in reply to Been there

    Additional benefit: You can search all places where you run a SELECT this way (if your IDE can show you all places where a constant is being used which should be all by now).

    It's a bit tedious but really not a WTF. In fact, I'm always putting string literals (which I need more than once) into a constant these days because it solves so many problems and needs just 5 key strokes (Eclipse: Alt-Shift-Right to select the whole string, Ctrl-1, Down, Down (to select "Extract to constant"), RETURN (to accept the name)).

  • Jarle (unregistered)

    Have you ever heard about sql injection? I hope for the love of God that you are not running this in your production environment!!!

  • SB (unregistered) in reply to Homer

    Seen (and done) this loads of times before, years ago, when memory usage was critical. Static string constants means these strings aren't duplicated with every instance of the class, saving memory.

    Simple.

    No problem ordering in descending order either - just need to add a " DESC" at the end of mstrOrder.

  • Lucas Stark (unregistered)

    I do this sometimes, to prevent typos but mostly because ReSharper complains about strings that can be converted to constants. This might have been by design, or maybe just trying to satisfy a rule in their IDE.

  • Just Someone (unregistered) in reply to Homer

    sql injection using mstrSelect/smtrTable....

    but i guess its a moot point since you can delete anything you want....

  • DK (unregistered)

    Some companies have ridiculously stringent requirements about externalizing String constants. While this makes sense as a way to enforce consistency when Strings are used in several places, the most-often-used justification, that it prevents the same String from being created twice in memory, is actually false, as Java compilers these days do that on their own.

Leave a comment on “Piecemeal SQL”

Log In or post as a guest

Replying to comment #:

Log Out

« Return to Article