Why would anyone want to declare Sql in Xml ?

Apparently there is a tool called iBATIS in the java world, and now in the .NET world too, that is like an O/R mapper except that instead of generating your sql at run-time, or using stored procs, it uses the Sql that you declare in an Xml file.  This just seems totally bizarre to me, and as far as I can tell their main argument for doing this is because they want an O/R mapper, but they want total control over all the Sql, and of course they also like to note that this allows them to avoid recompiles.  I just don't get why anyone would want to write Sql in Xml, and so far the person that has been posting about this tool pretty much makes his entire argument that this is just better, without any supporting rationale -- so what do you think about this tool?

17 Comments

  • Was that last sentence pun intended? :-p

  • Call me slow -- but I still don't even see a pun in the last sentence -- so I guess it was not intentional. Now I really feel stupid.

  • Okay, I looked too and couldn't find the pun either. Help us out! I'm feeling stupid, too.

  • I don't know why you'd use a tool to do that. You could achieve the same thing by putting the SQL statements in resource files.

  • Ok, I got it immediatly... but it might be an age thing. I think the pun is "tool". You asked "so what do you think about this tool?" and that could, by some, be translated to "so what do you think of this moron?".



    As far as to the technical merits of this tool...er... application, I could see this being a robust way to have an instant cross db engine approach in an instant.

  • I don't know if it's what Chris Stewart was referring to, but here in New Zealand "tool" is slang for a person who's a few beers short of a six pack (i.e. not so bright).

    T.

  • Sql in xml is "robust" ? And how is writing sql in xml "instant" ? Regular O/R mappers writes the sql for you -- now that is "instant". But I guess to each his own.

  • 1 point to update your query's instead of all inline...



    That must be the reason...

  • Oh, for the record it's not something I would do or recommend.

  • I did this very same thing years ago in VB6. The idea was to declaratively generate the DAL. One of the cool things was it was very easy to run thru the DB and generate the XML from the stored procs. That being said, I never brought the idea forward to .Net, since code generation is pretty easy, and I never did need the flexibility that I thought I would need with the XML method. Somtimes you got to do things the wrong way in order to learn how to do them the right way.

  • sql in xml is robust imo because it's better than say, a sql query map ala the old site server in plain text files. Is it better than an ormapper? I don't have an opinion there yet. I'm just trying to see things from all angles.



    It's instant because if you have the persistance engine and only need the queries supplied then you could pick up the sql from the xml and not have to worry about supporting 32 different db engines. This could be combined with other approaches (pluggable db-engine communication layers, etc.) to provide a nice system.

  • Ah yes, Jason, I see it distinctly now... And now that I do, it was really funny!



    To Don's point, I think we all have a tendency to attempt building things to the Nth degree of flexibility, and often find that we'll never need it all. I usually find myself in this mode during the early stages of a project (for work or for pleasure). This type of design can really bog things down a lot, and now I try to go with an iterative process that emphasizes refactoring.



    Hope I haven't morphed the subject too much.

  • Using Xml for Sql was suggested to me by the lead architect on the previous project I worked on. I dragged my feet a bit so there was "no time" to implement. Fact is, I was not in favor of it mainly because I think adding flexibility merely to avoid recompiles is overrated. The compiler is a pretty efficient program in my experience. I have created many configuration-driven apps and found them to be a pain when I had to go back in and remember all the rules.

  • The only place I find it useful to have SQL out in XML is to make the SQL more server agnostic (SQL Server/Oracle/DB2)..

    But, then, I am not sure if the OR mapper in question can deal with that correctly or not.



  • Maybe because,



    1. They dont want to put SQL in SPs?

    2. They want to manually change everything everytime there is a DB change (Yup this is Total Control)?



    Hmmm....Im out of ideas here...:)

  • Dear Paul,



    I assume when you say "so far the person that has been posting about this tool" that you are referring to the person who kept commenting on your very excellent article at TheServerSide.net. I must say that the gentlement in question was neither elequent nor very polite in stating the case for iBATIS. I'm not sure if I could do better, since I'm not a member of the iBATIS team, but simply a very happy user of that library. In any case, I'll try to explain why iBATIS is "different"...



    For me, the two features I look for in an ORM tool, with all other features being equal, are the following:

    1. It should not dictate the layout of the inheritance tree for my domain model, i.e. it shouldn't force me to inherit from certain base classes.

    2. It shouldn't dictate or restrict how the database is designed.



    Feature #2 is important because oftentime the database is inherited from a legacy product or is handed down my the DBAs.



    I must confess that my experience with O/R mappers is restricted to two products, one opensource, and the other commercial. And both of them either dictated the class hierarchy or the database design.



    iBATIS doesn't impose either limitation since it maps objects to SQL statements. So it's very tolerating of bad database designs, and of bad domain model designs for that matter.



    By the way, iBATIS does allow the use of stored procesures.



    If I had full control of the databsae design on a given project, I would consider another, more feature-rich code generator or O/R mapper over iBATIS. But if that is not the case, then iBATIS could be the best, if not the only choice.



    Hope this helps.



    -- Mohammad

  • While I know many O/R mappers do impose base classes and/or database designs, I do not think that all of them do. For instance, my mapper does not require a base class, and all interfaces are optional. Also, very little is assumed about your database design, although certainly not every feature is available in all cases (for instance if you use composite keys). I'm pretty sure similar things can be said about a few others, like NHibernate, so I'm not just trying to sale my mapper here.



    Now I can agree that its a nice extra feature to be able to totally specify your own sql in the rare cases where you need to do so, but I believe that there ARE ways to do this already, and the fact is that I want the majority of my cases to have their sql generated for me. So I still fail to see why I would want a tool that does little if anything for me other than take my own sql, especially if I have to define it in an xml file of all things. What are these other ways? Either use a stored proc for those cases, or step out of the automatic "mapper" features and use the DAL features that some of these mappers also include (mine and others).



    Anyhow, thanks for your answer -- it was at least making sense and I don't mean to belittle it if it seems that way. I just think its based on misconceptions of a few mappers, and its also attacking the wrong problems.

Comments have been disabled for this content.