Friday, December 21, 2007

SQL reserved words checker!

Cool, very usefull especially for all the developers that use ORM technologies and sometimes declare variables or collections with names that are not supposed to use. Its a very common mistake in projects - and many times some very very tricky problems suddenly may come up! So do a refactoring of your ORM related code and remove SQL reserved words- definitely a bad practice!

5 comments:

  1. Great site. Thanks.

    I don't believe though that it's a mistake to use reserved sql words in Java code. It's just a common source of errors. Refactoring is not necessary since the ORM can escape these fields for you (column="`when`")

    My model comes first, so when I want to name a field "when" I'll want to do so.

    thanks

    ReplyDelete
  2. I strongly disagree!


    1. its a bad practise
    2. Most RDBMS will not tolarate this..e.g Oracle will report errors and problems!
    3. There are times especially trouble times where you want either to review you generated SQL, improve perfomances etc etc, or make adjustments...messing around with reserver words and escapre characters its not productive!

    4. it does not scale well in big projects!

    So..again..VERY VERY BAD!

    ReplyDelete
  3. What exactly do you mean with "oracle reporting errors"? I have the same production code with a field="`when`", deployed on oracle, mysql, sql server and postgres. There has been no problem whatsoever as the hibernate dialect will take care of escaping these identifiers.

    In any case you could also map the field with a [field="when" column="xwhen"] fashion, but I still find this unnecessary.
    It is a feature of hibernate, so why not use it?
    hib_docs#mapping-quotedidentifiers

    ReplyDelete
  4. I had certain cases where yes ..the create script would work though..on certain occasions, with very complex queries..the db was reporting errors on column names. Several times!

    Take into account that sometimes you cant use HQL....and you go the classic SQL way..that makes the things complex!

    When it comes to the feature I think its there ..yes very well..but it is not always forced..and it makes the query strings ugly..when you dont want to...(DEBUG).

    Bare in mind that on very complex operations you may not use HQL but plain SQL.

    I dont know..i think its not a feature to follow..its there..it works..but IHMO it is there to prevent other errors..i mean in case you do it..Hibernate will protect you..BUT in general I dont want to do it!

    The use of ORM its a nice thing but I tend not to forget the days where there was not an ORM.. ;)

    its a matter of taste..anyway but I still consider it bad!

    ReplyDelete
  5. Yes, bypassing the ORM (going native with plain SQL) will require you to escape those identifiers manually. But that's just one of the many disadvantages of bypassing the ORM. You'll anyway need to speak the native SQL dialect of the underlying db server.

    My main concern is that I want to be able to entitle my entities and properties exactly as I want them, and the ORM allows me to do so.

    ReplyDelete