Discussions

News: Opinion: what if Java had native support for SQL?

  1. I attended a seminar by Anders Hejlsberg, Microsoft Chief Architect for C#. He said that Microsoft were working on native support for SQL in C#. This sounds like an idea implemented in Java years back, SQLJ. It looks like C# is trying to learn the same things Java programmers learned years ago.

    He didn't go into much detail. He asked the audience, who were primarily Java developers, whether anyone worked with databases and SQL and whether the developers found it difficult to embed cryptic SQL code in the Java or C# code.

    In the Java community, the focus now is on persistence frameworks like EJB, IBATIS, or Hibernate, among others. SQLJ was an attempt to bind SQL into Java, from a few years ago, relying on a preprocessing step for translation. Today, though, it looks like hardly anyone uses it – and Microsoft sees it as a good idea to work on.

    What does this mean? Is it a good idea? If so, why has SQLJ disappeared, for all intents and purposes?

    Threaded Messages (220)

  2. I attended a seminar by Anders Hejlsberg, Microsoft Chief Architect for C#. He said that Microsoft were working on native support for SQL in C#. This sounds like an idea implemented in Java years back, SQLJ.
    This sounds like an idea implemented in PL/1 years back.
  3. ORM inpedance mismatch[ Go to top ]

    What would be the benefit of this approach? Validating the Sql syntactically? To which dialect? Providing some code completion inside IDE's? To which database schema?

    SQL is certainly not a good choice to mix with OOP code since it's an entirely different thing. There are better ways of manipulating queries like the Criteria API in Hibernate wich is both OOP and elegant. The two worlds cannot be simply bridged syntactically since there is a more profound impedance mismatch.

    Indeed there are many candidates for such a language adaptation but we should do it differently by making easier to plugin some interpreted languages.

    Sure vendor lock-in is a good ideea for Microsoft and mixing the code provides that. It might be just fine for C# developers but I'm sure that is not an option for Java developers.
  4. ORM inpedance mismatch[ Go to top ]

    Sure vendor lock-in is a good idea for Microsoft and mixing the code provides that. It might be just fine for C# developers but I'm sure that is not an option for Java developers.

    Well said. I think embedded SQL is a disastrous idea. We have recently had a major advance in Java with the JDO 2.0 specification being approved. Soon we will have EJB 3.0 as well. We should maintain progress towards increasing vendor independence and truly portable query languages. Embedding vendor-specific SQL (or some limited functionality portable SQL subset) in Java is a huge backward step.
  5. ORM inpedance mismatch[ Go to top ]

    Well said. I think embedded SQL is a disastrous idea.

    True. But calling Java from the DB (Java stored Proc) is a different matter.
  6. ORM inpedance mismatch[ Go to top ]

    Well said. I think embedded SQL is a disastrous idea.
    True. But calling Java from the DB (Java stored Proc) is a different matter.

    Well yes, but surely there are real problems with testing and refactoring if you change the data model (or database)? There are all those hidden stored procs to take into account...
  7. ORM inpedance mismatch[ Go to top ]

    Sure vendor lock-in is a good idea for Microsoft and mixing the code provides that. It might be just fine for C# developers but I'm sure that is not an option for Java developers.
    Well said. I think embedded SQL is a disastrous idea. We have recently had a major advance in Java with the JDO 2.0 specification being approved. Soon we will have EJB 3.0 as well. We should maintain progress towards increasing vendor independence and truly portable query languages. Embedding vendor-specific SQL (or some limited functionality portable SQL subset) in Java is a huge backward step.

    That is complete baloney.

    Why is it that so many java 'propeller heads' think that you should hide the database behind abstraction layer after abstraction layer?

    Where I work, they have standardized on Oracle. They are not moving off of it. JDBC is already an abstraction layer from the database. The only thing that is really vendor specific is the SQL.

    You can solve really complex problems with vendor specific SQL that is hard or takes 10x as much code in java through some 'orm' api.

    Where I work, we had to implement 2 projects in ORM for 'political' reasons (clueless managers). By the end of the second project our team was *BEGGING* to go back to JDBC and the full power of vendor specific SQL.

    Having an easy way to integrate SQL into Java would be great.

    95% of the world uses relational databases, we don't need to 'persistence api' to hide that or make it easy for the 5% of object databases or whatever.
  8. ORM inpedance mismatch[ Go to top ]

    Having an easy way to integrate SQL into Java would be great. 95% of the world uses relational databases, we don't need to 'persistence api' to hide that

    +1

    .V
  9. ORM inpedance mismatch[ Go to top ]

    That is complete baloney.Why is it that so many java 'propeller heads' think that you should hide the database behind abstraction layer after abstraction layer?

    Because some of us 'propeller heads' have real-life experience of the horrors of vendor dependence. You may disagree, but I think the phrase 'complete baloney' is unhelpful, as objections to vendor-specific SQL are common and based on good principles and real situations.
    Where I work, they have standardized on Oracle. They are not moving off of it.

    I wish I could count the number of times I have heard that said about a particular vendor's product! The phrase 'famous last words' comes to mind.
    JDBC is already an abstraction layer from the database.

    No it isn't! It is an abstraction of how you access the database. It is contains very few abstractions of the database facilities or the query language, which explains why there are so many functions in JDBC to test what the database can and can't do!
    The only thing that is really vendor specific is the SQL.

    The 'only' thing? That is a major part of how a database is used.
    You can solve really complex problems with vendor specific SQL that is hard or takes 10x as much code in java through some 'orm' api.Where I work, we had to implement 2 projects in ORM for 'political' reasons (clueless managers). By the end of the second project our team was *BEGGING* to go back to JDBC and the full power of vendor specific SQL.Having an easy way to integrate SQL into Java would be great.

    Have a look at Hibernate query language, or JDOQL 2.0. You will find almost all the features of SQL.
    95% of the world uses relational databases, we don't need to 'persistence api' to hide that or make it easy for the 5% of object databases or whatever.

    Yes we do. 95% uses relational databases, but that 95% does not use the SAME relational database!

    There are supposed standards for SQL, but none of those standards are fully implemented by any vendor. In contrast, every vendor that implements JDO 2.0 must implement the full standard, including all the features of the rich JDOQL 2.0 language.
  10. ORM inpedance mismatch[ Go to top ]

    Because some of us 'propeller heads' have real-life experience of the horrors of vendor dependence.

    .....

    95% uses relational databases, but that 95% does not use the SAME relational database!

    and to everything else Steve said in his post: +1.

    As for the idea of embedding SQL constructs into an OO programming language - it gets a deserved place in my list of the stupidest things I have ever heard, regarding programming.

    I might, also, add that I am not surprised to hear such things from the "father" of Dephi and copy-machine that produced C#, but I guess that would be too personal and inappropriate? :P LOL
  11. ORM inpedance mismatch[ Go to top ]

    Sure vendor lock-in is a good idea for Microsoft and mixing the code provides that. It might be just fine for C# developers but I'm sure that is not an option for Java developers.
    Well said. I think embedded SQL is a disastrous idea. We have recently had a major advance in Java with the JDO 2.0 specification being approved. Soon we will have EJB 3.0 as well. We should maintain progress towards increasing vendor independence and truly portable query languages. Embedding vendor-specific SQL (or some limited functionality portable SQL subset) in Java is a huge backward step.
    That is complete baloney.Why is it that so many java 'propeller heads' think that you should hide the database behind abstraction layer after abstraction layer?Where I work, they have standardized on Oracle. They are not moving off of it. JDBC is already an abstraction layer from the database. The only thing that is really vendor specific is the SQL.You can solve really complex problems with vendor specific SQL that is hard or takes 10x as much code in java through some 'orm' api.Where I work, we had to implement 2 projects in ORM for 'political' reasons (clueless managers). By the end of the second project our team was *BEGGING* to go back to JDBC and the full power of vendor specific SQL.Having an easy way to integrate SQL into Java would be great.95% of the world uses relational databases, we don't need to 'persistence api' to hide that or make it easy for the 5% of object databases or whatever.

    Portability may not even be a requirement. We wrote a project for a large govt. agency. We demoed it to another govt. agency who said "Sweet! That's exactly what we want!"

    Who knew? The thing was VERY customized for the first, but the second, completely different set of users liked it.

    Except for one problem. The second set wanted it on SQL Server instead of Oracle.

    Well, since we'd used Hibernate we changed the dialect and created the new database in a couple of days. Then proceeded to spend several weeks porting over the places where the application used raw jdbc from Oracle to SQL Server.

    Throw a new third-party jdbc driver into the mix and we were done.

    I think we all know enough not to necessarily code to phantom requirements, but if you get some flexibility for free, it is foolish not to use it.
  12. Phantom requirements, indeed.[ Go to top ]

    I'm all for flexibility, myself, but deciding that database independence is part of the requirements and that it comes "for free" is not wise, in my opinion.

    If the cost isn't that high, fine. I'm a HUGE believer in the ability to move components back and forth, and it's one of the main reasons I really like EJB CMP. (Remember that?)

    But let's be real; it comes at a price. If you can bear the price, fine. If not... there's nothing WRONG with that. It's just a factor of the requirements phase.
  13. Not phantom requirements[ Go to top ]

    I'm all for flexibility, myself, but deciding that database independence is part of the requirements and that it comes "for free" is not wise, in my opinion. If the cost isn't that high, fine. I'm a HUGE believer in the ability to move components back and forth, and it's one of the main reasons I really like EJB CMP. (Remember that?)But let's be real; it comes at a price. If you can bear the price, fine. If not... there's nothing WRONG with that. It's just a factor of the requirements phase.

    The cost of database independence may actually turn out to be a huge saving if ORM is used in the right way. I believe that transparent persistence is the key to major code saving and improvements in code transparency and maintainability. Instead of the explicit retrieval of objects as in EJB, or direct use of SQL statements as in JDBC (or embedded SQL), simple manipulations of objects result in the generation of SQL in the background. If necessary, this SQL can be explicitly tuned for performance, but otherwise it can be left to the ORM to generate and optimise. Surely this saving of coding effort should be considered to potentially be a significant benefit.

    I'm not saying this is always the case; my point is that database independence and ORM should not always be viewed as a cost - there can be real positive benefits.
  14. Phantom requirements, indeed.[ Go to top ]

    I'm all for flexibility, myself, but deciding that database independence is part of the requirements and that it comes "for free" is not wise, in my opinion. If the cost isn't that high, fine. I'm a HUGE believer in the ability to move components back and forth, and it's one of the main reasons I really like EJB CMP. (Remember that?)But let's be real; it comes at a price. If you can bear the price, fine. If not... there's nothing WRONG with that. It's just a factor of the requirements phase.

    That's my point. Database portability WASN'T a requirement. This was a customized, very specific application that just happened to be what another totally different customer wanted. Except that they wanted it on SQL Server instead of Oracle.

    Using Hibernate gave us the portability we needed for free as I said. The use of Hibernate had no impact on the design, so I'm going to have to reject your assertions about my wisdom.

    I've lived experience to the contrary.
  15. That's my point. Database portability WASN'T a requirement. This was a customized, very specific application that just happened to be what another totally different customer wanted. Except that they wanted it on SQL Server instead of Oracle.Using Hibernate gave us the portability we needed for free as I said. The use of Hibernate had no impact on the design, so I'm going to have to reject your assertions about my wisdom. I've lived experience to the contrary.

    I didn't make an assertion about your wisdom.

    I said that portability comes at a price. Your application turned out to have a hidden (i.e., future) requirement for portability, which you had via Hibernate. That's great, a success story for you that didn't require prescience on your part or anything like that. You ended up with portability by - well, not entirely ACCIDENT, but it wasn't a requirement and then turned into one.

    As I said, that's good. I tend to program for portability as well because most projects I work on tend to be components. Components need to avoid vendor lock-in where possible, otherwise they're not very good components.

    But I'm not fooling myself by saying that my components are as fast as they could be, or as efficient as they could be, despite their portability. It's the opposite, in most cases (note "most cases" there.) This was my original point, which had NOTHING to do with your wisdom: portability comes at a price, the fact that you can't tune for general circumstances and get specific performance benefits.

    If you have an Oracle datasource, you can do CERTAIN Oracle-like things and get your database to sing. Likewise, on SQL Server, you can do certain SQL Server-like things and get your database to sing.

    Doing Oracle-like things on SQL Server... doesn't really work. The inverse is also true.

    A general-purpose library like Hibernate has a chance of doing a really good job, because of the dialects, but I'll still take a good Oracle DBA over Hibernate, if the requirements mandate it.

    And sometimes they do.
  16. Phantom requirements, indeed.[ Go to top ]

    A general-purpose library like Hibernate has a chance of doing a really good job, because of the dialects, but I'll still take a good Oracle DBA over Hibernate, if the requirements mandate it. And sometimes they do.

    Why not use both? Good ORM systems allow you to drop through to the database with native SQL and stored procedures where you feel it is necessary. It is not a case of one OR the other!
  17. Phantom requirements, indeed.[ Go to top ]

    A general-purpose library like Hibernate has a chance of doing a really good job, because of the dialects, but I'll still take a good Oracle DBA over Hibernate, if the requirements mandate it. And sometimes they do.
    Why not use both? Good ORM systems allow you to drop through to the database with native SQL and stored procedures where you feel it is necessary. It is not a case of one OR the other!

    Because this gets rid of the whole point in the first place, at which the whole thing started: a revival of the idea of SQLJ.

    Steve, what's your goal here? To get me to use Hibernate? If that's the point, well, stop: I already have Hibernate experience, as well as experience with a number of other persistence frameworks; I don't need an advocate for one or the other, especially when said advocate isn't offering specific requirements for the framework to fulfill.
  18. Phantom requirements, indeed.[ Go to top ]

    Because this gets rid of the whole point in the first place, at which the whole thing started: a revival of the idea of SQLJ.Steve, what's your goal here? To get me to use Hibernate? If that's the point, well, stop: I already have Hibernate experience, as well as experience with a number of other persistence frameworks; I don't need an advocate for one or the other, especially when said advocate isn't offering specific requirements for the framework to fulfill.

    This isn't personal at all. I was simply responding to to the statement "I'll still take a good Oracle DBA over Hibernate, if the requirements mandate it.".

    This seemed to me to offer a simple choice. Either use Oracle (and the associated SQL), OR Hibernate (my interpretation of the phrase 'over Hibernate'). If the choice was Oracle then embedded SQL could be a way to go (as with SQL/J). Therefore your statement seemed directly relevant to the subject of this thread.

    I'm assuming that posts to public forums like these are intended to express views which can be taken generally. Therefore it is appropriate to make general replies which are not intended to be read as direct advice to the original poster.
  19. Phantom requirements, indeed.[ Go to top ]

    This isn't personal at all. I was simply responding to to the statement "I'll still take a good Oracle DBA over Hibernate, if the requirements mandate it." This seemed to me to offer a simple choice. Either use Oracle (and the associated SQL), OR Hibernate (my interpretation of the phrase 'over Hibernate'). If the choice was Oracle then embedded SQL could be a way to go (as with SQL/J). Therefore your statement seemed directly relevant to the subject of this thread. I'm assuming that posts to public forums like these are intended to express views which can be taken generally.

    Steve, SQLJ had its own issues, not necessarily related to binding in SQL. I think the reasons SQLJ is, um, "hard to find" today apply to other packages too.

    I've said a few times that I'm not in either camp, except as requirements dictate. When I'm writing components, I tend to use generic data persistence (like Hibernate, et al) because I see vendor lock-in to be a bad thing for a component.

    As an architect, however, I simply have to recognize that there are costs involved - runtime or performance costs, even maintenance costs. Every choice you or I make, architecturally, affects those costs. Sometimes they go down. (The case of being able to move from db to db with little effort is a good example of that.)

    Sometimes they go up. Hibernate has to work with the general case, and I'm aware that you can write pass-through to handle specific cases, which is great. But that doesn't change the number of aspects you have to manage, and I refuse to pretend that a general-case solution will always be better than a vertical solution, even though I'm happy to admit that it often can be better than a vertical solution. (Yes, you read that right.)
    Therefore it is appropriate to make general replies which are not intended to be read as direct advice to the original poster.

    Hmm, I thought I was responding to you, in context. :)
  20. Phantom requirements, indeed.[ Go to top ]

    I refuse to pretend that a general-case solution will always be better than a vertical solution, even though I'm happy to admit that it often can be better than a vertical solution. (Yes, you read that right.)

    I guess I just feel that the potential cost of converting a 'vertical solution' into a 'general solution' is potentially disastrous, so I tend to go for generality as a form of insurance against this.
    Therefore it is appropriate to make general replies which are not intended to be read as direct advice to the original poster.
    Hmm, I thought I was responding to you, in context. :)

    I guess I feel that posting here is like when an actor talks on stage. What they say may be seem like it is directed to an individual, but they should always assume that their lines are being directed out to the audience :)
  21. Phantom requirements, indeed.[ Go to top ]

    I refuse to pretend that a general-case solution will always be better than a vertical solution, even though I'm happy to admit that it often can be better than a vertical solution. (Yes, you read that right.)
    I guess I just feel that the potential cost of converting a 'vertical solution' into a 'general solution' is potentially disastrous, so I tend to go for generality as a form of insurance against this.
    Agreed. So do I. Yet, as I said... requirements, requirements, requirements.
    Therefore it is appropriate to make general replies which are not intended to be read as direct advice to the original poster.
    Hmm, I thought I was responding to you, in context. :)
    I guess I feel that posting here is like when an actor talks on stage. What they say may be seem like it is directed to an individual, but they should always assume that their lines are being directed out to the audience :)

    Sure. Yet nobody reconstructs an entire context of a conversation - you'd end up with block quotes of block quotes of block quotes... ad nauseum. :)
  22. But I'm not fooling myself by saying that my components are as fast as they could be, or as efficient as they could be, despite their portability. It's the opposite, in most cases (note "most cases" there.) This was my original point, which had NOTHING to do with your wisdom: portability comes at a price, the fact that you can't tune for general circumstances and get specific performance benefits.

    Well said all around. In fact, we did have specific parts of the application that were tied to Oracle. We had a need to use the DECODE and that particular command apparently wasn't available to SQL Server(I didn't work on the port.), so I agree with you about the price.
  23. Get it done[ Go to top ]

    I left programming for the small business world and one of the first rules to shape my daily attitude is the need to get the job done.

    If it makes sense within the objective, if there're resources available, allow the developers to do it. I pity the purists; they end up losing the game - most of the time.

    Take Rickard Oberg. There's so many looking down on AOP. He enjoys success with his CMS/Portal app.

    I would have native SQL in Java - as a language extension. Just keep it simple.
  24. Groovy[ Go to top ]

    Soounds Groovy and iBatisy.

    .V
  25. didn't sqlj come out in 99?[ Go to top ]

    I remember playing with sqlJ back when oracle 8i was released. I would think Oracle moving to toplink suggests that ORM is a more maintainable solution. Even if SqlJ was fast, the trade-off of having sql embedded in the source may not be acceptable for many projects.
  26. didn't sqlj come out in 99?[ Go to top ]

    I remember playing with sqlJ back when oracle 8i was released. I would think Oracle moving to toplink suggests that ORM is a more maintainable solution. Even if SqlJ was fast, the trade-off of having sql embedded in the source may not be acceptable for many projects.

    Yupp at that time I also played around with SQLJ (on DB2).
    The performance was great, but as a whole it wasn't worth the hazzle.

    Now I am happily using Hibernate, and I must say that the kind of flexibility offered by such ORM tools far outweight the few drawbacks, at least in my situation.
  27. didn't sqlj come out in 99?[ Go to top ]

    I remember playing with sqlJ back when oracle 8i was released. I would think Oracle moving to toplink suggests that ORM is a more maintainable solution. Even if SqlJ was fast, the trade-off of having sql embedded in the source may not be acceptable for many projects.

    Well, I did not play with sqlJ back in 99 (I was committed to C++ at that time), but I did use Oracle's Pro*C a lot. Though it was the fastest solution for C / C++ applications, Oracle itself did recommend using more flexible solutions when possible, like PL/SQL and TopLink, or, when it became available, JDBC.

    Of course there are many that prefer embedded SQL even for more complex projects. But I noticed that these are the same architects who prefer bad designs over clean designs. So I tend to agree to those who would rather ban embedded SQL once and for all rather than make it a native solution.

    The fact that MS is planning embedded SQL for C# suggests, IMHO, that its architectural visions might not be "the best ones around", to use an understatement.
  28. Not a moment too soon...[ Go to top ]

    Native SQL support does not necessarily mean a DBMS has to be involved. I'd love to see a "Table" as a language datatype (much like an array), with SQL-like operations.

    To put it another way: Collections need to move out of libraries and become a language-supported feature. Not a moment too soon, in my opinion.
  29. Well, I suppose this is really bad idea. Especially taking into consideration where SQLJ is now.

    Having SQL statements in Java, by my opinion, is just a design flaw. Such code is hard to maintain and read.

    Of course, this could have sense for some small applications - probably like ones that are used to perform student assignment. However, for real-life applications with large lifecycle I even can't imagine how hard maintenance and support for such solution could be (especially if database scheme should be changed from time to time).

    Another reason why embedding SQL is not necessary - there are huge amount of persistence frameworks nowadays. Proper design should assume isolation of data access mechanisms from business logic of application, so ideally changing the way how database (if one is used) is accessed and modified should be transparent for remaining part of application (of course, this is ideal situation, but still). Proper O/R framework could let you be closer to such goal, but embedded SQL – definitely could not.

    For one of our projects we’ve developed small and simple framework that allowed us use external XML based registry of named SQL queries (in general, the main idea was pretty close to iBatis, but with some differences). It was real pleasure to perform development and testing – since during development queries registry was stored in just plain XML file, it was possible to update it even without restart of application server. I just can’t imagine how not convenient the entire process could be if SQL is embedded.

    By the way, it’s quite simple to find more candidates to embed directly into Java syntax, except SQL, for example – XPath, regular expressions, EJB QL – just to name a few. Of course, this is technically possible, but as result we got the same monster as PL/1 – language overloaded by features. That’s why I do believe that Java and SQL remain separate languages.

    Why MS is interested in such approach – well, probably this is just secret “Microsoft way”, who knows? Probably this is due to lack of persistence frameworks, probably they try to force all C# developer learn SQL :)

    Best regards,
    Andrew Sazonov
    http://www.softamis.biz
    SoftAMIS – Your source of high quality Java and Web solutions!
  30. SQLJ (or SQL/C) vs C# 3.0[ Go to top ]

    They are very different.

    Embedded SQL/C was the first one. It essentially is a
    preprocessor. SQLJ is exactly the same. There is no
    compiler based strict type checking on sql & language
    type system.

    C# 3.0 does not copy SQLJ preprocessor. The compiler understands the sql datatypes and implements type checking
    with CLR types.

    En experimental clr language system is available with SQL
    Xml, CLR types as C-omega (http://research.microsoft.com/Comega/).

    Ricky
  31. I could be wrong, but Oracle's sqlj tool also performed basic validation. Having the compiler check the sql datatype doesn't get around the fact the application has sql embedded, which to many projects is not acceptable. It's nice to have low level binding between objects and rowsets, but it isn't always acceptable to do that. for some projects, it might be ok.

    peter
  32. Going off on a tangent[ Go to top ]

    The thing that's being lost here is that given that MS owns SQL Server and C#/.Net, they can specify a connection layer that avoids the SQL interpreter. E.g., a view of the internal structure. With that in mind,

    a) connectivity to "other" DB vendors isn't a big issue
    b) Any talk of preprocessors, whether "lite" like JDBC that addresses differences in SQL standards & is also a connector, or something heavier, isn't the point.

    So, do you look at something that provides JDO functionality through an API that exposes the database engine as object DB? E.g., a Hibernate where the JDBC layer is factored out?
  33. Going off on a tangent[ Go to top ]

    This is point well made. MS can dictate the interface between C# and SQLServer. IMHO, most of us who would like to be database agnostic would prefer a layered approach leveraging a persistence framework.
  34. Going off on a tangent[ Go to top ]

    Unless of course another database agnostic API were to show up, which is where I was going with this...

    Now does this mean that a vendor-neutral API would want to expose the underlying datastore as remotely-accessible persistent objects, a la an Object Database? I'm too sure, although that's certainly an option...
  35. We already have made a huge step back with Java 1.5 and Generics.

    From an architectural point of view a central systems like RDBMS do not fit with distributed thing like EJB. We all should think about new ways, concepts and so on and not think about how to reinvent the wheel. http://www.prevayler.org/wiki.jsp?topic=Welcome
  36. We already have made a huge step back with Java 1.5 and Generics.

    Amen brother.
  37. OQL[ Go to top ]

    Well i don't like the mix Relational Database and a Object Oriented language. I prefer an implementation in Java compiler to a Object Query Languague (OQL).

    Javac compiling a simple OQL


    []s
  38. OQL[ Go to top ]

    +1 on this
    I would just simplify and add lightweight types...


    class Person {
       private String name;
       private long yearsOld;
       // etc...

       public Person(String name, long yearsOld) {
           this.name = name;
           this.yearsOld = yearsOld;
       }

       // sets and gets...
    }

    public class Main {
       public static void main(String[] args) {

           // Lets create the database
           Collection<Person> myData = ...

           // select adults
           Collection<{String, long}> results = select p.name, p.yearsOld from myData where myData.yearsOld > 21;

           // Then iterate
           for ({String, long} rec : results) {
              String name = rec[0];
              long age = rec[1];
              System.out.println(name + " is " + age + " years old!");
           }
       }


    We gain strong typing there, less place for confusion and/or errors
  39. OQL[ Go to top ]

    they already have this for .NET 1.1, it's called Comega, but it's in research stage at this point.
  40. Strictly evaluating the idea I think it is a good one.
    I do not think we should limit our thinking to SQLJ, iBatis, or other implementations..., but we should think in terms of the practical implications first.

    How many times did you find yourself in the situation where you needed to write an integrity checks in java just to make sure that some common or custom types, or enumeration of types match the ones on the database. Delegating this step to some common feature would be a welcome step, and it would incrase the productivity - in my opinion.
    Plus, how many times did been writing an application that does not have a relational back end (unless you are a "lucky" and you have a mainframe on the back end).

    This feature could be implemented in Java in such a way that primary OO nature of the Java language is not affected. However, OO nature is not a religion, it is a benefit, and so we should not avoid other beneficiary technologies because they do not chant together with OO.

    Finally, if C# does it - Java will have it in few years anyway (unless JSR completely falls apart).
  41. As always, Microsoft looks around in the current solution space, copies and improves the best working solution.

    The language that had SQL build in from day 1 is ABAP, the language SAP uses as the programming language for the R/3 application server.
    Some colleagues of mine describe the (early ABAP) as "Cobol, mixed in with SQL", but nowadays, you can even do OO in ABAP.

    Of course, the R/3+ABAP combination is a pain in the aXX with respect to lots of things (no real version control, pure database repository for code, very basic IDE, ...).
    On the other hand, the concepts of integrating SQL in ABAP (including strong typing, consistent from database to GUI) are perhaps one of the cornerstones of the SAP success story.

    I remember quite well the time i did my first ABAP hacking, and was puzzled by the fact that ABAP doesn't support arrays or lists, and that there is nothing like the Java collection framework.
    But of course, everything that isn't a scalar is either a structure or a table, so pure ABAP programmers's are not even aware that any other data structures exists.

    To sum it up: SQL support in C# is ABAP reloaded.

    Bye,

    Jürgen
  42. As always, Microsoft looks around in the current solution space, copies and improves the best working solution.

    Not sure what universe you have been living in, but the microsoft approach is actually: Microsoft looks around, finds something that is doing a good job that they don't own, then either bully the owner into selling, or if that doesn't work, they throw together a knockoff and drive the competitor out of business. Their knock is typically buggy, poorly thought out, redundant, and no longer supported six years later.

    How many change of API's have they forced us through (OLE, com, dcom and now .net). They did it with IE and Netscape, kerberos, and they have tried to do it with C# and Java. C# was created to to, if not destroy Java, at least to diminish it.
    They are like the typical bully, since they are not as smart, they rely on their bulk and muscle to get their way. Their mantra is not 'Embrace and Extend', it is 'Usurp and Destroy'. Microsoft has never done anything that is a pleasant surprize.
  43. missing the point[ Go to top ]

    I hate to spoil the party here, but what about situations where the DBA's have the DB locked down and developers are required to ask the DBA for queries? Embedding sql in the code is an absolutely "no, no". It has nothing to do with technology and everything to do with policy and responsibilities. If the DBA's responsibility is to insure things work correctly and the database has 99.9999% up time, they are not going to let developers write queries. Doesn't matter if developers like it or not. In those cases, things like comega and slqj is not appropriate. Not unless you want to be fired and have the DBA's jump down your neck.

    I would only consider this kind of solution acceptable if the database is small, 99% up time is not needed, there's no DBA and developers are allowed to write queries.

    my bias half pence

    peter
  44. Batch jobs?[ Go to top ]

    The ORM market has a lot of choices and the java can handle small chunks of code reasonably well. Batch jobs that work on millions of rows is another story all together. There is nothing in the java toolset to handle this market.

    If there was some kind of java code that could run on the database server that could provide the speed of pure sql and the programming constructs of java that would be great.
  45. Batch jobs?[ Go to top ]

    The ORM market has a lot of choices and the java can handle small chunks of code reasonably well. Batch jobs that work on millions of rows is another story all together. There is nothing in the java toolset to handle this market. If there was some kind of java code that could run on the database server that could provide the speed of pure sql and the programming constructs of java that would be great.

    Not true. Ibatis.
  46. Batch jobs?[ Go to top ]

    Not true. Ibatis.
    Or O/R Broker
  47. Batch jobs?[ Go to top ]

    Not true. Ibatis.
    Or O/R Broker
    I like SQL friendly frameworks too, this is my attempt http://voruta.sf.net But probably stored procedures is the best way to access RDBMS anyway, SP help to avoid RPC layers and complex frameworks to access data, so it must be the most performat and maintainable architecture.
  48. Batch jobs?[ Go to top ]

    Not true. Ibatis.
    Or O/R Broker
    I like SQL friendly frameworks too, this is my attempt http://voruta.sf.net But probably stored procedures is the best way to access RDBMS anyway, SP help to avoid RPC layers and complex frameworks to access data, so it must be the most performat and maintainable architecture.

    Well, I don't agree with that. I've worked on systems that used stored procedures exclusively. My opinion is that they are harder to develop, test, and maintain.

    In addition, said application is tightly coupled to the database, and as I've seen, there may be a time the database needs to change.
  49. Batch jobs?[ Go to top ]

    Stored procedures help for portability too, you can call it from any programming language. EJB stuff is for JAVA only (or it needs hacks like WS for integration) it is not as performant as SP and I do not think client side procedures are secure. I do not think it is a problem to migrate SP to different DB, there are many migration tools, you can use standard server side langues for SP too.
    I do not think SP are harder to develop, test, and maintain, you use data driven language to access data aka business logic (but It can be a problem if you are trieng to use data driven language for something more than business logic).
  50. Batch jobs?[ Go to top ]

    Stored procedures help for portability too, you can call it from any programming language. EJB stuff is for JAVA only (or it needs hacks like WS for integration) it is not as performant as SP and I do not think client side procedures are secure. I do not think it is a problem to migrate SP to different DB, there are many migration tools, you can use standard server side langues for SP too.I do not think SP are harder to develop, test, and maintain, you use data driven language to access data aka business logic (but It can be a problem if you are trieng to use data driven language for something more than business logic).

    Stored procedures really don't help much. The standard language for stored procedures provided with most DBs is dramatically different (compare PL/SQL with pgplSQL for example). Using a different language for stored procedures (e.g. Java) can help somewhat (at least the basic language is the same), but even so there are big differences. (for example, postgresql only supports functions and triggers).

    Another problem is that the range of stored procedure languages supported by different database vendors is very variable.

    If you are looking at ways to improve portability, using stored procedures are not the way to go.
  51. Batch jobs?[ Go to top ]

    Postgre SQL supports many programming languages (including JAVA) for stored procedures and it is possible to add more languages like TSQL or plSQL. But it looks like nobody has problems with existing languages. ORM itself has more portability problems than RDBMS, there are more ORM standards and versions anyway. So I think ORM adds no portability .
  52. Batch jobs?[ Go to top ]

    Postgre SQL supports many programming languages (including JAVA) for stored procedures and it is possible to add more languages like TSQL or plSQL.

    You are missing the point of my post. Please show Java stored procedure support in SQL server. Or support for the postgresql dialect of plSQL in Oracle.
    ORM itself has more portability problems than RDBMS, there are more ORM standards and versions anyway. So I think ORM adds no portability .

    This is nonsense! We are not talking about portability between ORMs, we are talking about portability between databases. This is as silly as to question Java's portability between OSes because you can't port all Java code to C#!

    That ORMs such as Hibernate provide easy portability between database is beyond question.

    If you want portability between ORMs, use JDO. Soon you will have even more portability between ORM vendors with EJB 3.0.
  53. Batch jobs?[ Go to top ]

    This is as silly as to question Java's portability between OSes because you can't port all Java code to C#!
    Yes, it is silly. Do you understand how it is silly to question SQL portability after this nonsence exange ? ORM portability sounds silly for me too.
  54. Batch jobs?[ Go to top ]

    This is as silly as to question Java's portability between OSes because you can't port all Java code to C#!
    Yes, it is silly. Do you understand how it is silly to question SQL portability after this nonsence exange ? ORM portability sounds silly for me too.

    Whether it sounds silly to you or not is irrelevant; it works. You do get database vendor lock-in if you use full-featured SQL. You get almost no database vendor lock-in if you use full-featured HQL with Hibernate. If you use JDO 2.0 you get no vendor lock-in with the ORM either. Soon you will be able to use EJB 3.0 in the same way.

    I think we have been through this before on previous threads: it was proven that ORMs such as Hibernate automatically make use of vendor-specific database features (giving you good performance) without requiring that you write vendor-specific code or query language.

    You may not like this approach, but I fail to see how this is 'nonsense', or stating the very well known incompatibilities of SQL and stored procedures between DB products is 'silly'.
  55. Batch jobs?[ Go to top ]

    Stored procedure migration problem is the same as JAVA to .NET or .NET to JAVA migration problem http://www.handyarchive.com/free/tsql-to-plsql/
    SQL itself is portable, it a standard (there are standard languages for stored procedures too) Are you trieng to discredit RDBMS vendors using this portability problem 'nonsense' ?
  56. Batch jobs?[ Go to top ]

    Stored procedure migration problem is the same as JAVA to .NET or .NET to JAVA migration problem

    It is an equivalent problem, but not the same problem, obviously, as it is not what we are discussing. I wish to be able to move easily between database vendors, not between Java and .NET.
    there are standard languages for stored procedures too

    I'm sure there are, but having standards is of no practical use if no-one implements them consistently.
    Are you trieng to discredit RDBMS vendors using this portability problem 'nonsense' ?

    Well, to be blunt, I am! Lack of full support for SQL standards is a hardly a credit to the industry.

    You can easily prove that this portability matter is 'nonsense' by taking up my challenge, and providing an example of common SQL stored procedure code of reasonable size (not just a couple of lines) that will work on, say PostgreSQL, Oracle and SQL Server.

    If you can, I will accept that I have misunderstood the situation. I will have learnt something, and would be grateful for the lesson! If you can't, I don't believe you can keep calling this matter 'nonsense'.
  57. Batch jobs?[ Go to top ]

    I see nothing wrong in portability, I just hate to see anti SQL propoganda.
  58. Batch jobs?[ Go to top ]

    Stored procedure migration problem is the same as JAVA to .NET or .NET to JAVA migration problem http://www.handyarchive.com/free/tsql-to-plsql/SQL itself is portable, it a standard (there are standard languages for stored procedures too) Are you trieng to discredit RDBMS vendors using this portability problem 'nonsense' ?

    This simply isn't true. SQL simply isn't completely portable. I've worked on DB2, SQL Server, Oracle, some Informix, some smaller Sybase, and I've seen differences.
  59. Batch jobs?[ Go to top ]

    Stored procedure migration problem is the same as JAVA to .NET or .NET to JAVA migration problem http://www.handyarchive.com/free/tsql-to-plsql/SQL itself is portable, it a standard (there are standard languages for stored procedures too) Are you trieng to discredit RDBMS vendors using this portability problem 'nonsense' ?
    This simply isn't true. SQL simply isn't completely portable. I've worked on DB2, SQL Server, Oracle, some Informix, some smaller Sybase, and I've seen differences.

    +1

    Also, I can't see how posting a link to a migration tool that is designed to help deal with the differences in stored procedures between database vendors is supposed to illustrate the portability of SQL. Quite the reverse. Especially when the migration tool website states that it can't do 100% of the job and manual effort is needed.
  60. Batch jobs?[ Go to top ]

    This stuff is designed for "action" languages like JAVA, I see no reasons to convert PL, but I hope it can help if somebody likes or has a reason to migrate programs.
    SQL doe's not need any migration tools, it is portable. It is possible to find some exeptional case, but it is not a reason to discredit technology. I see nothing wrong to extend standard and probably it is better than to move all experimental stuff to standard.
    Probably ORM is usefull stuff if it is used in the right way, but it must be more usefull to implement adapter in JDBC level if it used to solve migration problems. I do not think it is a good idea to use ORM as workaround for bug in driver, it sounds like a bug driven architecture.
  61. Batch jobs?[ Go to top ]

    Batch jobs typically pull data from the database to the Application server layer and transform them in some way and then save them into the database. In most non trivial applications you are pulling in a object tree from the database to the business tier. So the sql is run, data sent over the wire, the result sets iterated over the the corresponding java objects created. The business logic is performed and the the data is sent back the same way it came in.

    Now in some cases it makes sense to do all this using ORM but in some cases it may make more sense to do all the processing in the database. Do not bring it into the application tier at all. Dont wate the network bandwidth to move the data from the database box to the application tier box.

    PL/SQL is not the most efficient way to code business logic. I would prefer to do it Java. If you work in a shop where series of Batch jobs run all night long you will understand my pain.
  62. Batch jobs?[ Go to top ]

    Batch jobs typically pull data from the database to the Application server layer and transform them in some way and then save them into the database. In most non trivial applications you are pulling in a object tree from the database to the business tier. So the sql is run, data sent over the wire, the result sets iterated over the the corresponding java objects created. The business logic is performed and the the data is sent back the same way it came in.Now in some cases it makes sense to do all this using ORM but in some cases it may make more sense to do all the processing in the database. Do not bring it into the application tier at all. Dont wate the network bandwidth to move the data from the database box to the application tier box. PL/SQL is not the most efficient way to code business logic. I would prefer to do it Java. If you work in a shop where series of Batch jobs run all night long you will understand my pain.
    I worked on one of those. Well, one that was written in Java. I've done many COBOL batch jobs (and others). The Java app ran 24 hrs day on the Mainframe under Unix and accessed DB2 on the mainframe (minor read/write data) and accessed Oracle on Unix (major read only data). If we could have run the code on the Unix/DB box, it would probably have run much faster. But the problem was not Java, but architecture. But it still was pretty quick.
  63. Batch jobs?[ Go to top ]

    This stuff is designed for "action" languages like JAVA, I see no reasons to convert PL, but I hope it can help if somebody likes or has a reason to migrate programs.

    No. The link you gave was for primarily for conversion of SQL stored procedures.
    SQL doe's not need any migration tools, it is portable.

    You can keep on saying this, but you aren't providing evidence to back it up. As you refused to take up my challenge of providing portable PL/SQL, I give you another one; this is taken from a schema I have been working with recently. Please provide portable SQL which defines a table with, say, 3 text-containing fields of length more than 4k chars and at least one boolean or BIT type in Oracle, PostgreSQL and MySQL.
    It is possible to find some exeptional case, but it is not a reason to discredit technology.

    Here is a very common, non-exceptional case. I'm using Oracle. Much of my SQL code (not stored procedures - just statements) uses the powerful (and popular for Oracle users) DECODE function. Please show me any other database where this function is available.
    I see nothing wrong to extend standard and probably it is better than to move all experimental stuff to standard.

    The problem is not extensions to the standard, it is incompleteness of implementations of standards.
    Probably ORM is usefull stuff if it is used in the right way, but it must be more usefull to implement adapter in JDBC level if it used to solve migration problems. I do not think it is a good idea to use ORM as workaround for bug in driver,

    ORM is nothing to with bugs in drivers. It is used to provide access to databases in terms of objects.

    Anyway, I have set you a challenge - you can easily prove SQL portable by providing portable SQL for the table definitions and showing how widely available the DECODE function (very common in Oracle SQL) is.
  64. Batch jobs?[ Go to top ]

    OK, I was wrong. I am not expert and probably "DECODE" and DDL is the major RDBMS flaw, how JDOQL helps to fix it ?
  65. Batch jobs?[ Go to top ]

    OK, I was wrong. I am not expert and probably "DECODE" and DDL is the major RDBMS flaw, how JDOQL helps to fix it ?

    I did not say that they were major flaws, I just gave them as examples of lack of portability that are going to be encountered frequently.

    Please note that the SQL CREATE command is not merely 'DDL', it is part of the SQL standard, as are the data types you can define. It is an important part of the standard that is not fully implemented even by major vendors, which makes my point perfectly.

    JDO 2.0 fixes the SQL CREATE and datatype incompatibilities because there is a standard mapping layer. Java data types can be mapped to the appropriate types allowed by the particular database, with no changes required in your Java code or your queries. It fixes the DECODE issue because JDOQL is an enforced standard - all features of it (not just a subset) must be implemented for a product to be labelled as JDO compatible. The particular JDO implementation is then able to use the Oracle DECODE function if required, or some alternative CASE-based construct, depending on the database product, with no changes in your code or query language. It does not hide the database differences - it can transparently make good use of the differences between databases.

    As you have been unable to provide either a sample of portable SQL stored procedures, or portable SQL for the definition of even a simple schema, I don't think future use of the statement 'SQL is portable' is backed by the evidence. Sure, just about every RDMBs will support simple SELECT statements etc., but a serious application will require far more than that: table definitions, stored procedures, triggers, transaction control etc., areas in which compatibility between RDMBSes is very poor.
  66. Batch jobs?[ Go to top ]

    .JDO 2.0 fixes the SQL CREATE and datatype incompatibilities because there is a standard mapping layer. Java data types can be mapped to the appropriate types allowed by the particular database, with no changes required in your Java code or your queries.
    Ok, I am starting to like JDO. Is this mapping definition portable ?
     It fixes the DECODE issue because JDOQL is an enforced standard - all features of it (not just a subset) must be implemented for a product to be labelled as JDO compatible. The particular JDO implementation is then able to use the Oracle DECODE function if required, or some alternative CASE-based construct, depending on the database product, with no changes in your code or query language.
    It is a strange problem, I thought "CASE" is supported by Oracle. Probably JDO is a better standard if can fix Oracle.
  67. Batch jobs?[ Go to top ]

    Ok, I am starting to like JDO. Is this mapping definition portable ?

    Yes. You can transfer the mapping between JDO vendors since JDO 2.0.
    It is a strange problem, I thought "CASE" is supported by Oracle.

    It is, but as I'm sure you know, it is not directly equivalent to DECODE. As you also know full well, many users are still operating Oracle 8, which does not have CASE.
    Probably JDO is a better standard if can fix Oracle.

    I realise you are being ironic here. The issued being 'fixed' is, of course, not Oracle, but SQL portability.

    What you are trying to argue, I assume, is that because a few parameters may have to be changed in a mapping file to indicate that you are using Oracle rather than MySQL this is in some way 'not portable'.

    I would suggest that to compare a change of a few lines in a configuration file which, after all, is a 'mapping' file to the weeks or months of work involved in migrating possibly tens thousands of lines of SQL, stored procedures, DDL and triggers, is ridiculous, and not any way to justify an argument. Would you claim that a Java program was not portable if you had to change entries in a .properties file to cope with different installations? If you are, no-one will take you seriously.

    And, of course, this is irrelevant to your claim that 'SQL is portable'.
  68. Batch jobs?[ Go to top ]

    Yes, if you want to find something not portable in RDBMS then you will find it. It is possible to find not portable stuff in JDO implementations too, but I do not want to start discredit JDO vendors using names or examples from documentation.
  69. Yes, if you want to find something not portable in RDBMS then you will find it. It is possible to find not portable stuff in JDO implementations too, but I do not want to start discredit JDO vendors using names or examples from documentation.

    Yes, but you are evading the issue yet again. We are taking about SQL and RDMBS portability. the portability or otherwise of JDO implementations has no relevance whatsoever. JDO is not the RDBMS, it is what you can use to get RDMBS portability. Even if there was just one JDO vendor, with one implementation, you still get portability between databases.

    The problem with RDMBS is that you don't have to look very far to find lack of portability - it is a fundamental aspect of any major SQL application!

    I believe you know this, but you are trying to confuse the matter by discussing the totally irrelevant matter of supposed ORM incompatibilites. None of this has any relevance to the matter of SQL portability, and I don't think you are helping your case with these evasions.
  70. Yes, but you are evading the issue yet again. We are taking about SQL and RDMBS portability. the portability or otherwise of JDO implementations has no relevance whatsoever. JDO is not the RDBMS, it is what you can use to get RDMBS portability.
    I just think RDBMS is as portable as JDO, if it is not portable then JDO is not portable in the same way. Is it better to be locked by JDO than RDBMS ?
  71. I just think RDBMS is as portable as JDO, if it is not portable then JDO is not portable in the same way. Is it better to be locked by JDO than RDBMS ?

    Okay, let's leave the domain of opinions and enter the domain of verifiable facts.

    1) SQL code written for SQL Server does not work on Oracle or other RDBMS. I have written enough SQL on both Sybase and Oracle and Interbase and Cloudscape, etc. etc. to know that. I do not think anyone here could really disagree. And please do not tell me you can write in ANSI-92 SQL: no real application can work with ANSI SQL standards, just like no real application could work with ANSI Pascal (though there are lots of real applications written in Delphi).

    2) JDBC (and consequently JDO or Hibernate) code written for Oracle DOES work on other RDBMS. Try it out.

    This is due to an intrinsic lack of crucial features in standard SQL (or missing standard compliance when the standard does exist), and to the fact that Oracle became the de facto standard for RDBMS, so nobody has really bothered to improve the SQL Standard since 1992 - i.e. 13 years ago.

    Notice that this is similar to what was about to happen with JDO. Everybody thought "Since we are all using Hibernate and are all going to move to, at most, a hibernate-like EJB-cmp, why bother to improve JDO?". Luckily, the Java Community roared so loud that this did not happen, though I must admit I did not support the uproar. This means that we are all going to move to Hibernate-EJB3, nonetheless, but the existence of an alternate standard (JDO) can save us from a possible monopoly situation.

    Uh, and to be clear this is not a JDO apology, I use
    Hibernate, not JDO.
  72. What about Sql97?[ Go to top ]

    And please do not tell me you can write in ANSI-92 SQL: no real application can work with ANSI SQL standards, just like no real application could work with ANSI Pascal (though there are lots of real applications written in Delphi).
    2) JDBC (and consequently JDO or Hibernate) code written for Oracle DOES work on other RDBMS. Try it out.This is due to an intrinsic lack of crucial features in standard SQL (or missing standard compliance when the standard does exist), and to the fact that Oracle became the de facto standard for RDBMS, so nobody has really bothered to improve the SQL Standard since 1992 - i.e. 13 years ago.

    I thought the Sql standard was updated in 99? According to this page http://www.vbip.com/books/1861001800/chapter_1800_02.asp, the most recent update was in 99.

    peter
  73. What about Sql97?[ Go to top ]

    I thought the Sql standard was updated in 99? According to this page http://www.vbip.com/books/1861001800/chapter_1800_02.asp, the most recent update was in 99.peter

    Ugh. My fault. I missed a spec reference!

    However, the standard is still lacking in relevant areas. I.e. some fundamental details of the APIs are left to vendors.
  74. SQL code written for SQL Server does not work on Oracle or other RDBMS. I have written enough SQL on both Sybase and Oracle and Interbase and Cloudscape, etc. etc. to know that. I do not think anyone here could really disagree. And please do not tell me you can write in ANSI-92 SQL: no real application can work with ANSI SQL standards, just like no real application could work with ANSI Pascal (though there are lots of real applications written in Delphi).


    Nice reality check!


    PJ Murray

    CodeFutures Software

    Java Code Generation for Data Persistence
  75. BTW you can use "FIPS Flagger" if you have problems with oracle portability, doe's JDO implementations provide this kind of stuff too ?
  76. BTW you can use "FIPS Flagger" if you have problems with oracle portability, doe's JDO implementations provide this kind of stuff too ?

    They aren't needed in JDO 2.0 as it is a complete full-featured specification where no vendor extensions are needed to write an application.

    The problem with SQL implementations such as Oracle is that you are more likely than not to end up using non-standard features in even small applications (as I demonstrated for stored procedures and table definitions).
  77. Is it better to be locked by JDO than RDBMS ?
    The effort to migrate from one JDO implementation to a different one is infinitely smaller than the effor to migrate from one RDBMS product to another.
  78. Yes, but you are evading the issue yet again. We are taking about SQL and RDMBS portability. the portability or otherwise of JDO implementations has no relevance whatsoever. JDO is not the RDBMS, it is what you can use to get RDMBS portability.
    I just think RDBMS is as portable as JDO, if it is not portable then JDO is not portable in the same way.

    Obviously, you can think what you like. What matters is providing evidence to back up what you think if you want others to share your view. I asked you to provide evidence that RDBMS is portable, and you couldn't. Therefore, I have to disagree with what you think.
    Is it better to be locked by JDO than RDBMS ?

    1) You aren't locked by JDO. It is a multi-vendor specification that has (unlike SQL) to be fully implemented by each vendor.

    2) Even if you were locked by JDO, yes that is better than being locked by the RDBMS. It is good business practice to be able to tender for the most expensive parts of an IT system (usually the database engine). Also, it is good business practice to give your clients choice. Your client may not want an 'Oracle application' or a 'MySQL application' - they may want freedom.
  79. I asked you to provide evidence that RDBMS is portable, and you couldn't.
    Probably it is not portable, it depends on definition, it is as portable as JDO. ORM itself is a good RDBMS portabilty example it just needs a trivial adapter for advanced features.
    I will happy if you will try to advocate JDO or EJB without discrediting SQL and RDBMS technology.
  80. I asked you to provide evidence that RDBMS is portable, and you couldn't.
    Probably it is not portable, it depends on definition, it is as portable as JDO.

    The definition is easy. How many lines of coding do you need to change to migrate the software to a different RDBMS? If you use SQL you will most likely need to change a considerable number of lines. You gave us a link to a site which was selling tools to help with this migration. With JDO the migration can be as simple as changing an entry for a database type in a JDO tool.

    Therefore it is not as portable as JDO; any attempt to state this is simply ignoring facts, and no amount of wishful thinking or attempting to play with words will make it so.
    I will happy if you will try to advocate JDO or EJB without discrediting SQL and RDBMS technology.

    I am not attempting to discredit SQL and RDBMS technology. These technologies are a vital part of almost all my current IT projects. The matter of portability has no relevance to the matter of the quality of individual SQL implementations and RDBMS.
  81. I am not attempting to discredit SQL and RDBMS technology. These technologies are a vital part of almost all my current IT projects. The matter of portability has no relevance to the matter of the quality of individual SQL implementations and RDBMS.
    Most implementations claim SQL standard support. Probably you are better portability expert, but I see nothing wrong with SQL portability. I am sure vendors will be happy if you will register problems discovered by you in bugtracker. You can start from PostgreSQL this bug list is outdated and needs expert to review.
  82. Most implementations claim SQL standard support.

    It's called 'marketing'.
    Probably you are better portability expert, but I see nothing wrong with SQL portability.

    Then provide me with the portable SQL stored procedures and portable SQL statements I requested. They are simple examples. If you can't see anything wrong with SQL portability they should present you with no problems.
    I am sure vendors will be happy if you will register problems discovered by you in bugtracker.

    Yes, of course. I'm sure if I go to a major commercial database vendor as an individual and ask for them to add (for example) missing SQL standard variable types to their implementation I should be able to pick up an amended version of their product in the near future. Dream on.

    Yet again you are evading the issue. We have to deal with the SQL implementations as they are now, not how they might be in several years assuming there was a massive user campaign for standards compliance.
  83. Then provide me with the portable SQL stored procedures
    We disucssed SQLJ it is about portable SQL stored procedures too. Probably it is dead, but it means we can expect a new standard if SQL stored procedure portability is the real problem.
    and portable SQL statements I requested.
    This is trivial, see log produced by ORM. Exotic query is more exception than rule.
    Yes, of course. I'm sure if I go to a major commercial database vendor as an individual and ask for them to add (for example) missing SQL standard variable types to their implementation I should be able to pick up an amended version of their product in the near future. Dream on.Yet again you are evading the issue. We have to deal with the SQL implementations as they are now, not how they might be in several years assuming there was a massive user campaign for standards compliance.
    If vendor claims SQL standard support, but it doe's not work as documented then it is a defect. I think you have the right to ignore vendor if it ignores defects and customers. It must be possible to evaluate software before to use it. Vendors we are talking about claim SQL standard support in product documentation. As I understand false 'marketing' is a crime.
  84. Then provide me with the portable SQL stored procedures
    We disucssed SQLJ it is about portable SQL stored procedures too. Probably it is dead, but it means we can expect a new standard if SQL stored procedure portability is the real problem.

    Good. Let me know when this standard is out. Until then I'll have to deal with things as they are.
    and portable SQL statements I requested.
    This is trivial, see log produced by ORM.

    Wrong. As I said, ORMs overcome portability problems by generating vendor-specific SQL. The log produced by an ORM running on Oracle will give different SQL from the log of the ORM running on PostgreSQL. So, I'm afraid you aren't going to be able to see portable SQL statements that way.

    I assumed that you would be able to give the examples I requested, as you claim that SQL is portable.
    Exotic query is more exception than rule.

    I'm not after exotic examples. Any non-trival example of portable SQL stored procedure code would do. Also, I would hardly call my schemas exotic.
    If vendor claims SQL standard support, but it doe's not work as documented then it is a defect.

    But, of course, they don't claim support, do they? They use phrases like: "partial conformance". They provide detailed lists of SQL standard features they don't support, along with lists of features they do.
  85. Good. Let me know when this standard is out. Until then I'll have to deal with things as they are.
    You try can try to experiment with it right now:
    http://gborg.postgresql.org/project/pljava/projdisplay.php
  86. Good. Let me know when this standard is out. Until then I'll have to deal with things as they are.
    You try can try to experiment with it right now: http://gborg.postgresql.org/project/pljava/projdisplay.php

    So, as an example of a 'new standard for portable SQL stored procedures', you provide an implementation of Java stored procedures that runs only on PostgreSQL.

    I can't see any point in continuing this discussion!
  87. It is an attempt to implement standard procedures, I respect this work, but I prefer pgplSQL, it just my personal preference.
  88. It is an attempt to implement standard procedures, I respect this work, but I prefer pgplSQL, it just my personal preference.

    We were discussing portability. Let me quote you:
    but it means we can expect a new standard if SQL stored procedure portability is the real problem

    Although I like the idea of Java stored procedures, neither that product or pgplSQL are portable between databases. I also like PostgreSQL - it has been very effective on several websites I have set up. However, I also need to use Oracle as well, often in the same project. I need to be able to move rapidly between these databases. My JDO product allows me to transfer even large applications between these databases without a single change in my Java code or query language. JDBC would not allow this (because of the differences in SQL) and SQL embedded in Java would not either. This is why I consider embedded SQL to be such a bad idea.
  89. I take my statements back if you have Oracle to PostgreSQL portability problems on JDBC level and it is by design.
  90. I take my statements back if you have Oracle to PostgreSQL portability problems on JDBC level and it is by design.

    You will have to excuse me, but sometimes I have problems understanding what you are trying to say. I don't understand what you mean by 'it is by design'. The problem is not at the JDBC level. It is because JDBC does not isolate my code from the underlying SQL. For example, I need to use locking. The locking commands are different over the range of database I might want to be using: MySQL, Oracle, PostgreSQL, SQL Server. Neither JDBC or embedded SQL would allow me to write portable code because they do not hide these differences.
  91. I take my statements back if you have Oracle to PostgreSQL portability problems on JDBC level and it is by design.
    You will have to excuse me, but sometimes I have problems understanding what you are trying to say. I don't understand what you mean by 'it is by design'. The problem is not at the JDBC level. It is because JDBC does not isolate my code from the underlying SQL. For example, I need to use locking. The locking commands are different over the range of database I might want to be using: MySQL, Oracle, PostgreSQL, SQL Server. Neither JDBC or embedded SQL would allow me to write portable code because they do not hide these differences.
    Yes, locking is very implementation specific it depends on concurrency control implementation. JDBC can not force concurrency control implementation, "abstraction" for this stuff are "isolation levels" and I see no way to "abstract" concurrency control algorythm implementation specific stuff. You tool is very smart if it can "abstract" locking in the right way.
  92. Yes, locking is very implementation specific it depends on concurrency control implementation. JDBC can not force concurrency control implementation, "abstraction" for this stuff are "isolation levels" and I see no way to "abstract" concurrency control algorythm implementation specific stuff. You tool is very smart if it can "abstract" locking in the right way.

    This is nothing to do with details of implementation - it is a matter of different SQL commands for different database engines. Further proof of lack of portability even for simple locking/transaction commands.

    My JDO tool knows this and handles it for me so that my code is portable.
  93. Locking is a phisical data access aspect, it doe's not belong to SQL query, SQL queries are designed for 'business logic', I like phisical data access stuff in queries too, but I do not think it is a good reason to blame SQL.
  94. Locking is a phisical data access aspect, it doe's not belong to SQL query, SQL queries are designed for 'business logic', I like phisical data access stuff in queries too, but I do not think it is a good reason to blame SQL.

    Locking is not physical - it is part of the logic for controlling shared access. Even to request the same control of shared access in different RDBMSes can require different SQL. This control is part of the design of an application. With JDO this control is portable.
  95. I happy if JDO solves concurrency control problem better, but I thin "isolation level" is a good compromise too. Probably you write more advanced applications, but I found RDBMS manages concurrency control in the right way with "isolation level" abstraction too.
  96. I happy if JDO solves concurrency control problem better, but I thin "isolation level" is a good compromise too. Probably you write more advanced applications, but I found RDBMS manages concurrency control in the right way with "isolation level" abstraction too.

    I write pretty straightforward apps. But this is wildly off topic, and has nothing to do with the advantages or disadvantages of embedded SQL or your claim that SQL is portable. All I can say is that I have had serious problems with with SQL portability over the years, and JDO has largely solved that for me.

    Perhaps we should leave it there.
  97. Perhaps we should leave it there.
    Yes, it becomes a dialog. We can continue it next time.
  98. Batch jobs?[ Go to top ]

    Postgre SQL supports many programming languages (including JAVA) for stored procedures and it is possible to add more languages like TSQL or plSQL. But it looks like nobody has problems with existing languages. ORM itself has more portability problems than RDBMS, there are more ORM standards and versions anyway. So I think ORM adds no portability .

    Well, you'd better tell that to the Hibernate guys. They are under the impression that they support many databases.
  99. Batch jobs?[ Go to top ]

    you use data driven language to access data aka business logic (but It can be a problem if you are trieng to use data driven language for something more than business logic)

    Well, that's a new one.
  100. Batch jobs?[ Go to top ]

    you use data driven language to access data aka business logic (but It can be a problem if you are trieng to use data driven language for something more than business logic)
    Well, that's a new one.
    I was told in some ORM thread "business logic" and data access is the same, I just trust ORM experts.
  101. Batch jobs?[ Go to top ]

    The ORM market has a lot of choices and the java can handle small chunks of code reasonably well. Batch jobs that work on millions of rows is another story all together. There is nothing in the java toolset to handle this market.

    I totally disagree. The idea that ORM can't handle very large data sets is a myth. I use a JDO implementation that works very effectively on large datasets, and I have heard of ORMs being used for millions of rows without problems. ORMs can definitely be used for batch jobs.
  102. Batch jobs?[ Go to top ]

    Are ORMs really suitable for large sets? My understanding is that they have a lot of overhead for concurrency control and transactions, since they are designed for update, rather than query. Also, they will traverse the entire cursor and can produce a huge collection in Java. Using embedded SQL, on the other hand, you can iterate through the result set.

    Another queer(y) situation is when the SQL SELECT is a join across many tables and is not updateable, e.g., for a report. Using an ORM would require definition of an unusual, large, and unupdateable data object.
  103. Batch jobs - How we did it[ Go to top ]

    Yes, probably current ORM are not fully suitable to handling large sets of data and we have some performance issues with EJB 2.0 CMP during performing queries of data.

    Another area where ORM technologies are not applicable or not convenient is reports and joins - I'm absolutely agreed with you.

    However, all these situations may be easily solved by using tool that represents additional layer over plain JDBC and definitely don't require embedding SQL into Java code.

    For example, as far as I know, iBatis allow doing this. From the other side, it's possible to create own small framework, if necessary. Due to some specific requirements of one of our project, we were forced to develop own framework that was used to perform querying of data.

    If someone is interested how we did it, please take a look to this small PDF document ( SoftAMIS_QueryEngine.pdf) that provides brief overview of our architecture. I hope it could be useful.

    Best regards,
    Andrew Sazonov
    http://www.softamis.biz
    SoftAMIS - Your source of high quality Java and Web solutions!
  104. Batch jobs - How we did it[ Go to top ]

    Another area where ORM technologies are not applicable or not convenient is reports and joins - I'm absolutely agreed with you.

    Could you explain this? why should ORM not be applicable in this area?
  105. Batch jobs - How we did it[ Go to top ]

    Another area where ORM technologies are not applicable or not convenient is reports and joins - I'm absolutely agreed with you.
    Could you explain this? why should ORM not be applicable in this area?
    I agree. I was just doing a Quick&Dirty report (for Demo purposes) using SQL/JDBC and I wish I had taken the time to use my ORM (get the enviroment set up) because it would have been alot easier.

    From what I have seen, many people try to write reports off of complex queries that shouldn't be written the way they are writing them. Where are the DBAs when you need them? ;)
  106. My understanding is that they have a lot of overhead for concurrency control and transactions, since they are designed for update, rather than query.

    This really depends on the particular pathway through code, at least in Kodo.
    Also, they will traverse the entire cursor and can produce a huge collection in Java.

    It would be a very naive ORM tool indeed that didn't support database cursors intelligently.
    Another queer(y) situation is when the SQL SELECT is a join across many tables and is not updateable, e.g., for a report. Using an ORM would require definition of an unusual, large, and unupdateable data object.

    This isn't the case for JDO2 or EJB3. Consider the following:

    Query q = pm.newQuery("select avg(salary) as averageSalary, address.state as state into com.example.SalaryInfo from com.example.Employee group by address.state order by avg(salary) descending");
    List<SalaryInfo> infos = (List<SalaryInfo>)q.execute();

    Query q = em.createQuery("SELECT NEW com.example.SalaryInfo(AVG(e.salary), a.state) FROM Employee e JOIN e.address AS a GROUP BY a.state ORDER BY AVG(e.salary) DESCENDING");
    List<SalaryInfo> infos = (List<SalaryInfo>)q.getResultList();

    (Yes, in both cases, infos might be backed by scrollable cursor.)
    (Yay, erasure!)
    (Boo, no preview in TSS.com!)

    -Patrick

    --
    Patrick Linskey
    http://solarmetric.com
  107. Stored procedures in java[ Go to top ]

    If there was some kind of java code that could run on the database server that could provide the speed of pure sql and the programming constructs of java that would be great.

    I'm no expert on the topic, but I know that with DB2 you can write stored procedures (= runs on db server) in Java (= can use java programming constructs).

    I would be surprised if none of the other major databases has this ability...

    /J
  108. Stored procedures in java[ Go to top ]

    If there was some kind of java code that could run on the database server that could provide the speed of pure sql and the programming constructs of java that would be great.
    I'm no expert on the topic, but I know that with DB2 you can write stored procedures (= runs on db server) in Java (= can use java programming constructs). I would be surprised if none of the other major databases has this ability... /J

    in the case of Oracle, java runs at the same level as Sql, so the performance of java stored procedures is the same as Sql. Atleast that's my understanding from using it. Since i don't work for Oracle, I can't verify how that is achieved or what it means at the lowest levels.

    peter
  109. missing the point[ Go to top ]

    ORM tools generate embedded SQL -- it's just more transparent.

    My experience with why SQLJ didn't take is that there wasn't a compelling value to switch from stored procedures to using SQLJ. And DBA's in the larger mainframe shops I worked at were viscious against java so stored procedures ended up being the standard. Plus the DB human resources on projects were proficient with DB tools not java tools. Not too mention all the vendor specific nuances that were a pain. And the management pissing contests on control were always fun.

    I agree with the uptime statement you make. However, I'm surprised DBAs allow ORM tools in shops at all. The last DBA I worked with would force us to submit ALL generated SQL statements from ORM tools for review before he would sign off on migration to production.
  110. missing the point[ Go to top ]

    ORM tools generate embedded SQL -- it's just more transparent.My experience with why SQLJ didn't take is that there wasn't a compelling value to switch from stored procedures to using SQLJ. And DBA's in the larger mainframe shops I worked at were viscious against java so stored procedures ended up being the standard. Plus the DB human resources on projects were proficient with DB tools not java tools. Not too mention all the vendor specific nuances that were a pain. And the management pissing contests on control were always fun.I agree with the uptime statement you make. However, I'm surprised DBAs allow ORM tools in shops at all. The last DBA I worked with would force us to submit ALL generated SQL statements from ORM tools for review before he would sign off on migration to production.

    I can't blame the DBA in those cases though. It may sound like a major pain in the rear, but he's the one that has to wake up and fix the problem if gets to that point. If I was a DBA and my job depended on the database being up all the time, I'd be a bit paranoid too. A good way to get a bad surprise is being lazy and not reviewing the generated sql.

    peter
  111. missing the point[ Go to top ]

    If I was a DBA and my job depended on the database being up all the time,
    So why is he in this position? I would say that it is not his responsiblity. At least not anymore. Mainframe dbs are a different beast.
  112. missing the point[ Go to top ]

    Plus the DB human resources on projects were proficient with DB tools not java tools.
    This is probably the single biggest reason. I've observed the same thing.
    The last DBA I worked with would force us to submit ALL generated SQL statements from ORM tools for review before he would sign off on migration to production.
    He/she could have spent their time better elsewhere. Unfortunately most people, including DBAs, don't realize how good RMDBSs have gotten.
  113. diligence[ Go to top ]

    Plus the DB human resources on projects were proficient with DB tools not java tools.
    This is probably the single biggest reason. I've observed the same thing.
    The last DBA I worked with would force us to submit ALL generated SQL statements from ORM tools for review before he would sign off on migration to production.
    He/she could have spent their time better elsewhere. Unfortunately most people, including DBAs, don't realize how good RMDBSs have gotten.

    In my own experience, it has more to do with diligence. It's not that the DBA's don't know. In many cases they do and are experts. It's really about being diligent to extremes and trying to make sure everything that can be checked is checked. Some might call it paranoid, but sometimes you have to be paranoid when SLA's are driving the requirements. I've seen SLA's where the company has to pay the customer in the event the performance doesn't meet the requirements. In those cases, every little thing that causes your system to take longer than it should costs you real money.

    peter
  114. diligence[ Go to top ]

    Plus the DB human resources on projects were proficient with DB tools not java tools.
    This is probably the single biggest reason. I've observed the same thing.
    The last DBA I worked with would force us to submit ALL generated SQL statements from ORM tools for review before he would sign off on migration to production.
    He/she could have spent their time better elsewhere. Unfortunately most people, including DBAs, don't realize how good RMDBSs have gotten.
    In my own experience, it has more to do with diligence. It's not that the DBA's don't know. In many cases they do and are experts. It's really about being diligent to extremes and trying to make sure everything that can be checked is checked. Some might call it paranoid, but sometimes you have to be paranoid when SLA's are driving the requirements. I've seen SLA's where the company has to pay the customer in the event the performance doesn't meet the requirements. In those cases, every little thing that causes your system to take longer than it should costs you real money.peter
    I understand. Our experiences are different and I can see where in your experience they provide value. But I would say that that is a very small percentage of development and that more often than not, they don't provide the right value in the right area.
  115. missing the point[ Go to top ]

    I would only consider this kind of solution acceptable if the database is small, 99% up time is not needed, there's no DBA and developers are allowed to write queries.my bias half pencepeter
    Peter,
    These things are not mutually exclusive. You can have your cake and eat it too. The question is are you allowed, not if it will work or not. With modern RDMBS, the role of the DBA has change, or least should have (I'd have to dig an old copy of DB2 Mag to show that they agree with me :) ).
  116. From my experience in a well-designed application most SQL (or HQL or even EJB QL) ends up being dynamically generated anyway. This is the only way to achieve reuse at the SQL statement level - usually application has to use many similar SQL statements or variations of the same "core" statement.
    So unless the preprocessor can support SQL fragments (which adds some complexity), this feature would not be very useful.

    Alexander.
  117. In fact it does, the syntax is all there, we just have to use it and provide optimized implementations that convert plain Java code to database query syntax, to take advantage of indexes. My favourite way to write a Java query looks like this:

    List list = database.list(new Query(){
        boolean select(Person person){
            return person.getAge() > 21
                && person.getName().startsWith("C");
        }
        
    });

    The principle of the syntax used above builds on an approach named "Safe Queries" by William R. Cook and the Evaluation/Candidate feature already available in db4o today. Further links are supplied on my blog.

    Writing queries this way is typesafe, they will be checked at compile-time, they are 100% refactorable by an IDE, they are truly object-oriented and OO encapsulation can be respected.

    Because very few special concepts are added to plain Java, we are likely to see this as a standard one day.

    The clue to get plain Java queries to execute fast:
    A bytecode/sourcecode analyzer needs to convert the Java code to query syntax that can use indices. We will take this on.

    Best,
    Carl
    --
    Carl Rosenberger
    Chief Software Architect
    db4objects Inc.
    http://www.db4o.com
  118. List list = database.list(new Query(){
        boolean select(Person person){
            return person.getAge() > 21
                && person.getName().startsWith("C");
        }
        
    });
    Why would I ever want to twist like that ?
    What's wrong with
    SELECT * FROM PERSON WHERE AGE > :1 and NAME LIKE 'C%'
    and some way of keeping this query out of the code (like iBATiS does) ?
    Why the need to reinvent the way we query? Why disregard the SQL standard? Why pretend it's code instead? What do you gain? The data model is where you start anyway, not the other way around, so there's no reason to use code to generate SQL. Why can't people accept that not everything has to be Java ?!
  119. List list = database.list(new Query(){&nbsp;&nbsp;&nbsp;&nbsp;boolean select(Person person){&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;return person.getAge() > 21&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&amp;&amp; person.getName().startsWith("C");&nbsp;&nbsp;&nbsp;&nbsp;}&nbsp;&nbsp;&nbsp;&nbsp;});
    What's wrong with SELECT * FROM PERSON WHERE AGE > :1 and NAME LIKE 'C%'and some way of keeping this query out of the code (like iBATiS does) ?
    The downsides of your statement are that it:
    - will not be reactored automatically by the IDE, when you change the name of a field or a method
    - does not provide support for polymorphism and inheritance
    - does not respect OO encapsulation
    - makes it a lot more difficult to build a query from reusable small query components constraint-by-constraint with multiple method calls.
    Here is a verbose example for what I mean.
    - will look much more like a "twist" in your code when you use JDBC, Statement objects add want to get multiple parameters into the SQL statement
    - will not return objects
    - can be subject to string injection attacks
    - will require more ressources
    - will require a statement parser and therefore provide worse execution time
    - is not checked by the compiler for errors and typos
    - operates in a different language system and will require you to do special work to process formatted data like dates, raw byte[] or blobs
    - is subject to the object-relational mismatch and will be
    dreadfully slow for trees and deep inheritance trees
    - will require you to create and maintain two schemas: your classes and your relational tables
    Why the need to reinvent the way we query? Why disregard the SQL standard?
    In Java we are working with objects. It is easier and faster to store objects. It is easier and faster to use the Java language semantics for querying. As others have written here before, SQL is a bad standard because SQL vendors do not use a common dialect for political reasons and SQL databases are not exchangeable. If we would use Java for querying, there would be no doubts about the syntax.
    Why pretend it's code instead?
    Queries are code.
    The data model is where you start anyway, not the other way around, so there's no reason to use code to generate SQL.
    This may be the way you work but it is not generally true. The majority of future database applications will start from the object-oriented class model and work from there. Think beyond a mainframe database in an enterprise. Every single little device will run a database engine in the future.
    --
    Carl Rosenberger
    db4objects Inc.
    Safe Queries
  120. Downsides of using SQL[ Go to top ]

    I like you S.O.D.A., but can you describe how it coll without crappy SQL examples ?
  121. I'm sorry but you sound like a fanboy to me.
    - SQL queries do not need to change when you change the name of a field or method
    - SQL queries have nothing to do with polymorphism or inheritance
    - SQL queries have nothing to do with OO encapsulation


    Just these three arguments make me think that you're a prevaylerhead that wants to be rid of databases altogether or something along those lines. If I'm mistaken I appologise, but you seem to be mixing up your facts. SQL is a less-than-ideal tool to express relational queries and facts. Your inheritance, polymorphism, encapsulation and OO can go take a hike.

    I will not even dignify the rest of the message with proper answers, because if clearly have got it all wrong.

    The Polepos thing is a sad joke, obviously. It's front page shows clear bias towards oo databases, and it goes on to say
    If you need a relational database for whatever reason, MySQL should certainly be on your evaluation list. We did test quite a few other proprietary products with the benchmark suite, products that may not be listed here because of their license agreements. These other products did not look good at all. No wonder MySQL has such a big and growing installed base.
    Riiight. Oracle, I want my money back now, these guys say MySQL is better !

    Keep on preaching. Flocks will gather.
  122. List list = database.list(new Query(){&nbsp;&nbsp;&nbsp;&nbsp;boolean select(Person person){&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;return person.getAge() > 21&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&amp;&amp; person.getName().startsWith("C");&nbsp;&nbsp;&nbsp;&nbsp;}&nbsp;&nbsp;&nbsp;&nbsp;});
    Why would I ever want to twist like that ?What's wrong withSELECT * FROM PERSON WHERE AGE > :1 and NAME LIKE 'C%'and some way of keeping this query out of the code (like iBATiS does) ?Why the need to reinvent the way we query? Why disregard the SQL standard? Why pretend it's code instead? What do you gain? The data model is where you start anyway, not the other way around, so there's no reason to use code to generate SQL. Why can't people accept that not everything has to be Java ?!

    While everything doesn't have to be java, the database is one piece of the application. There are other parts and they tend to get hard to maintain if you try to represent all parts of the application as SQL or as rows of data.

    Rows of data is a bad architectural decision. Why can't people accept that database are great for storing and retrieving data and pretty much bad for everything else?

    I worked on a project where the guy used PLSQL to generate HTML and hold business logic.

    What a nightmare.
  123. I worked on a project where the guy used PLSQL to generate HTML and hold business logic.What a nightmare.
    Been there, done that too, was the worse nightmare ever in my entire developer life indeed.
  124. I worked on a project where the guy used PLSQL to generate HTML and hold business logic.What a nightmare.
    Been there, done that too, was the worse nightmare ever in my entire developer life indeed.
    Yust use the rigth tool generate HTML to stop your nightmare, there are many ORM implemetations for JAVA, PHP is a very good tool to generate markup too.
  125. Yust use the rigth tool generate HTML to stop your nightmare, there are many ORM implemetations for JAVA, PHP is a very good tool to generate markup too.
    And what has ORM to do with it? Never mind, I did that years ago, and would never touch that code ever again, thanks god.
  126. And what has ORM to do with it? Never mind, I did that years ago, and would never touch that code ever again, thanks god.
    I found ORM is very good for web development. It is possible to use data access code in UI, but I prefer to have very simple templates to generate text and to transform logical model to linked UI model ("domain objects") in code before to farmat text. UI is the mater of taste and customers like to change it.
  127. Why do people tend to twist and tween an argument to it's extreme and then criticise THAT? Can you please read my post carefully.

    OBVIOUSLY not everything is SQL, rows and data.
    BUT there is a large and important part of the application that IS rows and data, the data model, that is. It's relational and we should be aware of that and use the relational way to access it, not invent query languages (except when you need portability). Nighmarish stories with respect to this abund, if you're in the right circles and meet the right people (that is, those DBAs that get paid the big bucks for keeping things sane and at SLA levels, as someone already pointed out).

    OBVIOUSLY PL/SQL to output HTML is at least weird if not nightmarish indeed, but this great insight has nothing to do with my point.

    I think you basically agree with what I'm saying, except you then went and said - out of the blue - that not everything should be represented as rows. Duh ! :)
    Also a correction there: databases are not great only for storing and retrieving data. They're also the best at data processing, partitioning, mining and what have you. PL/SQL or Java stored procedures for complex and critical operations live happily in the database and beat every other approach in terms of speed and if you're going Java (not PL/SQL) you may even claim maintainability.

    Cheers
  128. Agreed, these dumb java programmers that think they need to 'see' everything as an object are stupid.

    There is nothing wrong with getting data out of a ResultSet instead of a 'bean'.

    These bastard dialetcs of SQL that the Java croud is so proud of can't do half the stuff we do with SQL everyday.

    Then these java dumbasses think that their app *is the center of the universe* and that *they* are the only ones to access the data. Where I work, nothing could be further from the truth, and the database FAR outlives any particular language/environment. We've gone from VAX to foxpro to powerbuilder to java...and I'm sure we'll go beond that at some point.

    Java is NOT the center of the universe, nor should ORM be the center of your persistence stratagy.

    JDBC+SQL works beautifully.

    Sun should have quit trying to 'fix' something that was not broken.
  129. There is nothing wrong with getting data out of a ResultSet instead of a 'bean'.

    Actually, there is. If I am doing financial calculations, for example, I can write code using an ORM that expresses the calculations in terms of objects that are directly relevant to the calculation: Customer objects, Invoice objects, Payment objects etc. The logic of the calculation is clear. Why should I want to clutter my code with dozens of JDBC calls that have no relevance to the calculation. For example, what does 'executing a statement' have to do with my calculation?
    These bastard dialetcs of SQL that the Java croud is so proud of can't do half the stuff we do with SQL everyday.

    You will find that there is a lot that Java handle elegantly that SQL can't; inheritance relationships for example.
    Where I work, nothing could be further from the truth, and the database FAR outlives any particular language/environment. We've gone from VAX to foxpro to powerbuilder to java...and I'm sure we'll go beond that at some point.

    This is the classic 'where I work' argument. Your database may far outlive a particular language and environment but many don't.
    Java is NOT the center of the universe, nor should ORM be the center of your persistence stratagy.

    ORM is just a way that Java developers can access data. There is nothing to prevent others accessing the same data in other ways.
    JDBC+SQL works beautifully.Sun should have quit trying to 'fix' something that was not broken.

    JDBC+SQL is the worst possible combination. It combines a large number of boilerplate Java API calls with embedded SQL. You have neither the OOP-based expressiveness of Java or the power of raw SQL.
  130. Are you sure it is a good idea to use "domain objects" for financial calculations (example is http://finance.yahoo.com/q/ks?s=SUNW) ? This stuff is not trivial to calculate and I do not think objects can help.
  131. Are you sure it is a good idea to use "domain objects" for financial calculations (example is http://finance.yahoo.com/q/ks?s=SUNW) ? This stuff is not trivial to calculate and I do not think objects can help.

    You may personally not think objects can help, but plenty of developers think the opposite. OO has had a long and successful history of use in finance. Although it is not so widely used now, Smalltalk (a pure OO language) has been used by banks and financial companies for this kind of use for a very long time. OOP is a very good way of managing complexity. Using domain objects is semantically hugely preferable to a scattering of JDBC calls.
  132. Yes, it is possible to calculate this stuff on client using OOP and domain objects , but I think it is as silly as stored procedures to generate HTML.
  133. Yes, it is possible to calculate this stuff on client using OOP and domain objects , but I think it is as silly as stored procedures to generate HTML.
    I don't think he said that it would be done on the client.

    That is the great about doing it the way Steve says. The calc can be done in the database, on the database server, on the app server, on the "client", on a "service" server via Jini, ... .
  134. The calc can be done in the database, on the database server, on the app server, on the "client", on a "service" server via Jini, ... .

    Absolutely. Also, I think that the 'database-centric' attitude is far too simple an approach for the way that data is handled these days. Information is becoming increasingly mobile and portable. This is why I am so in favour of JDO as against any other persistence API - I may want to use the same code to query a relational store or, perhaps, XML files containing an extract of that store on a mobile computer. Being able to use only relational stores is far too limiting for me. Being restricted to vendor-dependent SQL is worse, and having that SQL embedded in Java code is worst of all - it restricts where code can be run and how data can be used.
  135. Just try to caclcutate this stuff with JDO persistence API and you will see reason for "the 'database-centric' attitude ". Any tool can be used in lame way, I see some of posters know it too (HTML in stored procedures).
  136. Just try to caclcutate this stuff with JDO persistence API and you will see reason for "the 'database-centric' attitude ". Any tool can be used in lame way, I see some of posters know it too (HTML in stored procedures).

    I think there's some confusion here. I don't think anyone is suggesting JDO or hibernate be used to do calculations. That would be inappropriate. All JDO and hibernate does is help you get data and save it. Once that data is loaded, it's up to the architect and developer to figure out the "best" way to do those calculations. Obviously, there are cases where a calculation requires all the rows of the table and that table might be 100 million rows. I don't think anyone is suggesting you load that all into one big server with 512Gb of RAM. Though it would be fun to do it, just to see what happens. In those kinds of extreme cases, you have to do it in the database, or you have to handle it incrementally.

    But both these cases have very little to do with embedding sql in a java class like SqlJ, or having sql like query support in a language. There's already OOQL, if someone really wants to have an object oriented query language.

    peter
  137. Yes, it is possible to calculate this stuff on client using OOP and domain objects , but I think it is as silly as stored procedures to generate HTML.

    I don't think it is helpful to make comments like this unless you are prepared to provide some kind of reasoning to back them up. It is very easy simply to say something is silly without providing justification.

    Also, there was no mention of calculating on the client. Almost all this sort of calculation is done server side.

    Using stored procedures to generate HTML is bad practice because it combines business and presentation logic in the same place. OOP, if used well, can be used to separate concerns. Using domain objects for calculation is separating storage logic from business logic, so your analogy is the precise opposite of what ORM does.
  138. How do you caclculete this stuff using portable JDO ?
  139. How do you caclculete this stuff using portable JDO ?

    You don't normally calculate using JDO. JDO is a transparent persistence API. You use JDO to specify the information you need for the calculation. Then you calculate using normal Java code. Any persistent objects that are modified during the calculation are stored when you indicate that you want to commit changes. Calculation is done with normal Java code. No need to keep referring to RecordSets etc.
  140. How do you caclculete this stuff using portable JDO ?
    You don't normally calculate using JDO. JDO is a transparent persistence API. You use JDO to specify the information you need for the calculation. Then you calculate using normal Java code. Any persistent objects that are modified during the calculation are stored when you indicate that you want to commit changes. Calculation is done with normal Java code. No need to keep referring to RecordSets etc.
    Doe's it mean you use stuff like this http://www.theserverside.com/news/thread.tss?thread_id=33576 for financial calculations on the real databases with a few GB of data ?
  141. Doe's it mean you use stuff like this http://www.theserverside.com/news/thread.tss?thread_id=33576 for financial calculations on the real databases with a few GB of data ?

    No, I have to be honest, not a few GB, but certainly for databases of hundreds of MB with hundreds of thousands of rows. I use a JDO product for this processing and it works fine. I also use this JDO product for 'batch processing' - transforming and loading large volumes of data. Again, it works fine, and is fast. I benchmarked some of this against postgresql stored procedure code and found little performance difference.
  142. Are you sure it is a good idea to use "domain objects" for financial calculations (example is http://finance.yahoo.com/q/ks?s=SUNW) ? This stuff is not trivial to calculate and I do not think objects can help.
    You may personally not think objects can help, but plenty of developers think the opposite. OO has had a long and successful history of use in finance. Although it is not so widely used now, Smalltalk (a pure OO language) has been used by banks and financial companies for this kind of use for a very long time. OOP is a very good way of managing complexity. Using domain objects is semantically hugely preferable to a scattering of JDBC calls.
    Steve,
    Just so you don't feel like you are alone in this (here),
    +1 to all your comments.
  143. Are you sure it is a good idea to use "domain objects" for financial calculations (example is http://finance.yahoo.com/q/ks?s=SUNW) ? This stuff is not trivial to calculate and I do not think objects can help.

    If the application isn't a real-time system, I see no problem using Java to do financial applications. On the client side it should be more than sufficient. System doing real-time financial calculations like duration, maturity, average price, p&e, etc use packaged solutions like tibco or they have domain experts in house to write that stuff. with my limited experience with financial systems, very few things are calculated in real-time outside of accounting systems and compliance systems.

    In both of these cases, I fail to see how having OO queries in Java makes any difference. If all the data is loaded in memory and the system really needs to be able to query it, I don't see a compelling reason to have query support in the language specification. I'm sure there are people who would benefit from such a feature, but I question whether that really should be a language feature or simple a tool/spec.

    peter
  144. We are talking about caclculation not about formating, It looks like some posters know how calcultate finacial stuff in portable way. I will hapy to know about it more, I will be very happy if you will help me to change my opinion.
  145. We are talking about caclculation not about formating,

    You mentioned stored procedures and HTML!
    It looks like some posters know how calcultate finacial stuff in portable way. I will hapy to know about it more, I will be very happy if you will help me to change my opinion.

    Excellent. There are plenty of ORMs you can download for free and try out. How about Hibernate? Then, for portable financial calculations I suggest you look at the java.math classes, such as BigDecimal (http://java.sun.com/j2se/1.5.0/docs/api/). They are designed to provide portable calculations of whatever precision you specify - ideal for finance, and will allow your code to run identically on any Java implementation.

    I'm sure you should be able to find out how to write portable financial code using this information; after all hundreds of thousands of developers already have.
  146. OK, I trust you. I will try it later.
  147. Peter,
     Do you have experience with processing large volumes of data with Java? ETL ish type processing?

     I am looking to leverage domain processing (translation: not having duplicate logic in different toolsets). The outstanding issue seems to be in the ETL area.
  148. ETL[ Go to top ]

    Peter,&nbsp;Do you have experience with processing large volumes of data with Java? ETL ish type processing?&nbsp;I am looking to leverage domain processing (translation: not having duplicate logic in different toolsets). The outstanding issue seems to be in the ETL area.

    This might sound bad, but in the past, we (as in the developers) wrote our own tools to extract data and push it to the proper destination. Most of this was integration between different systems.

    It's funny you ask about ETL, since I just accepted a fulltime position at Ascential Software. They focus on ETL stuff and IBM just bought Ascential. I don't know Ascential's product, but you the website has plenty of info. I'm guessing you need to transform data to a variety of models. A lot of systems still do this stuff with custom components to glue things together :)

    peter
  149. ETL[ Go to top ]

    This might sound bad, but in the past, we (as in the developers) wrote our own tools to extract data and push it to the proper destination. Most of this was integration between different systems.
    It isn't bad. I figured that was what I probably would need to do. And I figured you had. I just wanted to get your experiences on using Java to do it.

    It's funny you ask about ETL, since I just accepted a fulltime position at Ascential Software. They focus on ETL stuff and IBM just bought Ascential. I don't know Ascential's product, but you the website has plenty of info.
    I'll check their site out. I got an email from IBM the other day about them and the purchase.

    Anyone know if their is an ETL JSR? :)
  150. ETL[ Go to top ]

    This might sound bad, but in the past, we (as in the developers) wrote our own tools to extract data and push it to the proper destination. Most of this was integration between different systems.
    It isn't bad. I figured that was what I probably would need to do. And I figured you had. I just wanted to get your experiences on using Java to do it.
    It's funny you ask about ETL, since I just accepted a fulltime position at Ascential Software. They focus on ETL stuff and IBM just bought Ascential. I don't know Ascential's product, but you the website has plenty of info.
    I'll check their site out. I got an email from IBM the other day about them and the purchase. Anyone know if their is an ETL JSR? :)

    there's the new repository spec, but I wouldn't consider that a complete ETL specification. to cover ETL fully, the spec would need to provide a clean way of defining data sources, transformers and how to chain transformers together. A lot of the techniques seem to use XML and XSLT. In the past, I've used XML + XSLT to do the transform. It can be a bit painful, given XSLT is painful.

    peter
  151. ETL[ Go to top ]

    It can be a bit painful, given XSLT is painful.
    I would rather shove a fork in my eye. :)
  152. ETL[ Go to top ]

    In the past, I've used XML + XSLT to do the transform. It can be a bit painful, given XSLT is painful.peter

    Oh, I don't know. If you have had your brain mangled the right way in the past by using other declarative languages like Prolog, I think it can be very powerful. I have written some useful XSLT scripts. On the other hand I was never entirely sure why or how they worked :)
  153. Prolog is much nicer than XSLT[ Go to top ]

    In the past, I've used XML + XSLT to do the transform. It can be a bit painful, given XSLT is painful.peter
    Oh, I don't know. If you have had your brain mangled the right way in the past by using other declarative languages like Prolog, I think it can be very powerful. I have written some useful XSLT scripts. On the other hand I was never entirely sure why or how they worked :)

    every time I use XSLT, it feels like a drag. whereas when I use LISP, it's very nice :) Declarative languages are nice. XSLT is useful, it's just the syntax is a bit ugly and the XSLT spec is poorly written. I've that darn thing dozens of times and I still find it a pain to understand. sorry for being off topic.

    peter
  154. I have use this open source ETL tool for some simple use-cases, and it has been working fine: http://www.openadaptor.org/

    Regards,
    Henrique Steckelberg
  155. I have use this open source ETL tool for some simple use-cases, and it has been working fine: http://www.openadaptor.org/Regards,Henrique Steckelberg
    Thanks I had seen that in the past but had totally forgotten about it. I've sort of looked at Clover and Octopus.

    My goal is to find (or build) something that allows me to use domain objects in an ETL process. That layer will have the knowledge of what is valid and how to persist it. Duplicating this logic in an ETL tool is, well, duplicating effort. So any tool that takes flat file data and shoves it in the db isn't what I am looking for. Then again, if I can pull rules from a rules engine, maybe it is. At least partially.
  156. I have use this open source ETL tool for some simple use-cases, and it has been working fine: http://www.openadaptor.org/Regards,Henrique Steckelberg
    Thanks I had seen that in the past but had totally forgotten about it. I've sort of looked at Clover and Octopus. My goal is to find (or build) something that allows me to use domain objects in an ETL process. That layer will have the knowledge of what is valid and how to persist it. Duplicating this logic in an ETL tool is, well, duplicating effort. So any tool that takes flat file data and shoves it in the db isn't what I am looking for. Then again, if I can pull rules from a rules engine, maybe it is. At least partially.

    Sounds like you're looking for a tool to do ETL and transformation validation. A lot of the rule engine work I've done in the last few years deal with real-time transformation from a given format to the internal model and back out in the target model. In my case, the kind of transformations are very specific so we generally go with a mapping technique. Have you looked at Jibx? It provides a nice way to map from one XML format to another.

    peter
  157. Agreed, these dumb java programmers that think they need to 'see' everything as an object are stupid...We've gone from VAX to foxpro to powerbuilder to java...and I'm sure we'll go beond that at some point.

    Part of the reason is because the DB is the center of the universe with the tools you've described.

    I've done it many times and in many languages (COBOL, PL1, Powerbuilder, VB, FoxPro, C/C++, ... ) in the way you describe, and it is a pain. It makes the application brittle. No wonder the "app" is recreated in different languages. The wrong problem is being solved.
  158. Why do people tend to twist and tween an argument to it's extreme and then criticise THAT? Can you please read my post carefully.OBVIOUSLY not everything is SQL, rows and data.BUT there is a large and important part of the application that IS rows and data, the data model, that is. It's relational and we should be aware of that and use the relational way to access it, not invent query languages (except when you need portability). Nighmarish stories with respect to this abund, if you're in the right circles and meet the right people (that is, those DBAs that get paid the big bucks for keeping things sane and at SLA levels, as someone already pointed out).OBVIOUSLY PL/SQL to output HTML is at least weird if not nightmarish indeed, but this great insight has nothing to do with my point.I think you basically agree with what I'm saying, except you then went and said - out of the blue - that not everything should be represented as rows. Duh ! :)Also a correction there: databases are not great only for storing and retrieving data. They're also the best at data processing, partitioning, mining and what have you. PL/SQL or Java stored procedures for complex and critical operations live happily in the database and beat every other approach in terms of speed and if you're going Java (not PL/SQL) you may even claim maintainability.Cheers

    No sir, I don't basically agree with you. Sorry. :-). If you are advocating puting complex and critical operations in stored procedured, from a software development perspective we are as far apart as it is possible to get.

    I worked on a project where a DBA wanted me to do just that. I was a contractor and flat out refused. I will not write something that I know is inherently wrong. I would have left and told them as much. They relented and I was proven correct.

    I've worked with SLAs and meeting one is NOT dependent on having business logic in the database. That is utter nonsense.

    No sir, we are not in agreement.
  159. David,
    You sometimes need to do that. There's data processing that cannot be handled efficiently outside the database and that's common knowledge, not utter nonsense.
    You may have not run into it [yet], but that doesn't make it nonsense.
  160. David,You sometimes need to do that. There's data processing that cannot be handled efficiently outside the database and that's common knowledge, not utter nonsense.You may have not run into it [yet], but that doesn't make it nonsense.

    You don't understand. Nonsense is implying that you can only meet SLAs by binding the application in its entirety to the database.

    I don't believe that this is common knowledge at all.
  161. I first said there's some *critical* stuff that you can only do in the database to meet certain performance requirements.
    You went on to say that meeting SLAs can be achieved only by doing *business logic* in the database is nonsense.
    Now it's the *entire application* in the database that could meet SLAs.
    Oh ok I see...
  162. Also a correction there: databases are not great only for storing and retrieving data. They're also the best at data processing, partitioning, mining and what have you. PL/SQL or Java stored procedures for complex and critical operations live happily in the database and beat every other approach in terms of speed and if you're going Java (not PL/SQL) you may even claim maintainability.
    Cheers

    I think that assertion isn't true. It all depends on what kind of data processing you're talking about. Say the data processing is calculating p&e (price vs earning). Doing that in a stored procedure would be inappropriate in my opinion. Doing that with a database trigger would also be inappropriate in my opinion.

    In terms of partitioning, I would say it's a deployment and architectural decision. To me, Sql has very little to do with partitioning. Also, the paritioning support varies between Oracle, DB2, Sybase and Sql Server.

    for data mining, most of the setups I am aware have 1 database for transactions and a second for data mining. Actually, more like a cluster for transactions and a cluster for data mining. In the case of data mining, the trick is, what queries do you run? Are you going to code that in stored procedures? Many cases, data mining is done in an adhoc manner by a human, so what matters more is how you visualize the data. In other cases, a dedicated application is used to run automated mining queries nightly.

    In more complex situations, it requires doing pattern matching and filtering to figure out what those queries should be. So again, doing it all in the database may not be appropriate or even scalable. In all these cases, having embedded Sql doesn't buy you anything.

    peter
  163. So again, doing it all in the database may not be appropriate or even scalable. In all these cases, having embedded Sql doesn't buy you anything.peter
    I think it can. Gathering a large amount of data in a client application using JDBC and processing it in a tabular fashion will definitely be more acceptable than using an ORM that would create loads of domain or value objects for you, place them in lists or trees and starting from there.

    Also, regarding the assertion not being true. The idea, rather incorrectly expressed, was that databases are not just [dumb] data storage as the ante-poster suggested, but rather complex beasts. There's a trend that I find to be very wrong, where [Java] developers want to minimise the database's importance, preffering to ignore what and how it does the thing it does, dreaming of abstracting everything away. For a large category of applications that may be fine; for others it's definitely not; either way, 'ignorance is bliss' comes to mind.

    I've seen large business applications for banks, that used PL/SQL extensively and with great success. A lot of PL/SQL programmers that would code in PL/SQL just as you'd code in Java or C. I definitely see nothing wrong with that, it's a matter of approach and competencies. Why do you dismiss such an approach (procedures, triggers) as "innapropriate"? I've never actually done this myself but I think you could achieve a very clean business logic implementation in the database, using Java and/or PL/SQL stored procedures, rules and triggers.

    Regards
  164. So again, doing it all in the database may not be appropriate or even scalable. In all these cases, having embedded Sql doesn't buy you anything.peter
    I think it can. Gathering a large amount of data in a client application using JDBC and processing it in a tabular fashion will definitely be more acceptable than using an ORM that would create loads of domain or value objects for you, place them in lists or trees and starting from there.Also, regarding the assertion not being true. The idea, rather incorrectly expressed, was that databases are not just [dumb] data storage as the ante-poster suggested, but rather complex beasts. There's a trend that I find to be very wrong, where [Java] developers want to minimise the database's importance, preffering to ignore what and how it does the thing it does, dreaming of abstracting everything away. For a large category of applications that may be fine; for others it's definitely not; either way, 'ignorance is bliss' comes to mind.I've seen large business applications for banks, that used PL/SQL extensively and with great success. A lot of PL/SQL programmers that would code in PL/SQL just as you'd code in Java or C. I definitely see nothing wrong with that, it's a matter of approach and competencies. Why do you dismiss such an approach (procedures, triggers) as "innapropriate"? I've never actually done this myself but I think you could achieve a very clean business logic implementation in the database, using Java and/or PL/SQL stored procedures, rules and triggers.Regards

    The main reason in the specific cases I cited like calculating P&E or aggregations, using a trigger implies real-time calculations, which is not needed in the cases I am aware of. In many cases I've seen, things like P&E are calculated over night or on a weekly basis. The context I'm thinking of, anything that impacts transaction throughput is not acceptable. Therefore doing a real-time aggregation which may take upwards of 1 minute is not acceptable.

    it all depends on the requirements. If the database is small and only 2-3 people are using it, then I see no problem. where high throughput is needed, these kinds of processes are usually off-loaded to some other system.

    The overhead difference between data in an object graph or data in raw resultset isn't going to matter much. I would say it depends on the type of data and what kind of filtering you are doing. If you're loading the data into a rule engine to perform complex filtering, than it makes no sense to use resultset. I would only use tabular data like a resultset, if the filtering is simple and no aggregation is needed. that's my bias opinion.

    peter
  165. As always, it all depends. When dealing with millions of registrations at a time for whatever reason and whatever filtering and calculations, one byte of overhead per entry leads to megabytes of memory wasted. The data representation, transport and processing algorithms all matter - some more than overs :)
    My main point though, throughout this whole discussion, was to try to balance the panacean ORM-is-all-you-need approach/tone.
  166. As always, it all depends. When dealing with millions of registrations at a time for whatever reason and whatever filtering and calculations, one byte of overhead per entry leads to megabytes of memory wasted. The data representation, transport and processing algorithms all matter - some more than overs :) My main point though, throughout this whole discussion, was to try to balance the panacean ORM-is-all-you-need approach/tone.

    people get carried away at times. I sure have been known to do that in the past. Some people never have to aggregate 1-10 million rows of data for financial calculations, so for them it's more efficient to use ORM. I don't really see it as a case of ORM vs Sql, or even Sql vs embedded Sql vs OOQL. if the particular technique works well for a given application, then use it.

    I do see cases where real-time aggregates are needed, but those generally are not continuously re-calculated with every new order that is inserted into the order table. In cases like these for example, you need to perform the aggregation, do some business logic and then send the results through the message bus. Now obviously one could use Java triggers to do that and then send a JMS message, but in some cases, it's better to read the rows out and do that process. Atleast in my experience, the message sent out often needs the rows involved in the aggregation anyways, so doing it in a stored procedure just slows down your inserts.

    peter
  167. As always, it all depends. When dealing with millions of registrations at a time for whatever reason and whatever filtering and calculations, one byte of overhead per entry leads to megabytes of memory wasted. The data representation, transport and processing algorithms all matter - some more than overs :) My main point though, throughout this whole discussion, was to try to balance the panacean ORM-is-all-you-need approach/tone.
    people get carried away at times. I sure have been known to do that in the past. Some people never have to aggregate 1-10 million rows of data for financial calculations, so for them it's more efficient to use ORM. I don't really see it as a case of ORM vs Sql, or even Sql vs embedded Sql vs OOQL. if the particular technique works well for a given application, then use it.I do see cases where real-time aggregates are needed, but those generally are not continuously re-calculated with every new order that is inserted into the order table. In cases like these for example, you need to perform the aggregation, do some business logic and then send the results through the message bus. Now obviously one could use Java triggers to do that and then send a JMS message, but in some cases, it's better to read the rows out and do that process. Atleast in my experience, the message sent out often needs the rows involved in the aggregation anyways, so doing it in a stored procedure just slows down your inserts.peter

    I'm probably one of those who some would classify as having a tendency to 'get carried away'! This is because I do have an 'ORM is all you need' approach. It is my belief that much of the accepted belief about what it is and isn't appropriate to do with ORM isn't backed by evidence in all cases. I like ORM because having the business logic and model almost all in one place - the Java code - is far more maintainable and robust. For this reason I am prepared to sacrifice some performance. However, I often find that there aren't the performance problems with ORM that are often stated. I use ORM for processing very large data sets, and I have heard of it being used for millions of rows without a large memory requirement or performance penalty. My advice to any developer is to try the 'ORM is all you need' approach, and see if/how it works. I think many would be very surprised at the lack of problems.
  168. I'm probably one of those who some would classify as having a tendency to 'get carried away'! This is because I do have an 'ORM is all you need' approach. It is my belief that much of the accepted belief about what it is and isn't appropriate to do with ORM isn't backed by evidence in all cases. I like ORM because having the business logic and model almost all in one place - the Java code - is far more maintainable and robust. For this reason I am prepared to sacrifice some performance. However, I often find that there aren't the performance problems with ORM that are often stated. I use ORM for processing very large data sets, and I have heard of it being used for millions of rows without a large memory requirement or performance penalty. My advice to any developer is to try the 'ORM is all you need' approach, and see if/how it works. I think many would be very surprised at the lack of problems.

    I've tried to load a million rows into memory on a workstation with 1gb of RAM. It's definitely feasible. On a workstation with 512Mb RAM, it feasible to load 500K rows of data and not grind to a halt. For me it all depends on the overall process.

    If the data you're doing calculations on has to be sent to some destination at some point, it's better off to read that data out to an object graph or resultset. It's not cheap, but the other option is grinding your database to a halt and having to deal with lost transactions. Life would be boring if people didn't get carried away now and then :)

    peter
  169. I've tried to load a million rows into memory on a workstation with 1gb of RAM. It's definitely feasible. On a workstation with 512Mb RAM, it feasible to load 500K rows of data and not grind to a halt. For me it all depends on the overall process.If the data you're doing calculations on has to be sent to some destination at some point, it's better off to read that data out to an object graph or resultset. It's not cheap, but the other option is grinding your database to a halt and having to deal with lost transactions. Life would be boring if people didn't get carried away now and then :)peter

    The thing is that you don't need to load a million rows into memory, at least not all at once! With JDO (and, I believe, Hibernate) you can ask for only a fixed number of rows to be fetched at a time, and then the occasional 'flush' call will save modified objects to the store to prevent memory use growing. You can handle a very large number of rows without using much memory at all.
  170. Sounds horrible[ Go to top ]

    I've tried to load a million rows into memory on a workstation with 1gb of RAM. It's definitely feasible. On a workstation with 512Mb RAM, it feasible to load 500K rows of data and not grind to a halt. For me it all depends on the overall process.If the data you're doing calculations on has to be sent to some destination at some point, it's better off to read that data out to an object graph or resultset. It's not cheap, but the other option is grinding your database to a halt and having to deal with lost transactions. Life would be boring if people didn't get carried away now and then :)peter
    The thing is that you don't need to load a million rows into memory, at least not all at once! With JDO (and, I believe, Hibernate) you can ask for only a fixed number of rows to be fetched at a time, and then the occasional 'flush' call will save modified objects to the store to prevent memory use growing. You can handle a very large number of rows without using much memory at all.

    Most applications don't need to handle crazy stuff like this, but there are cases where a system has to. The common term for this is "As of analysis" It's called by all sorts of names actually. The basic idea is that you need to perform reconciliation, compliance validation, and balance the books starting from a specific time T in the past to a future date T2. It's basically replaying the events with some corrections to bad data in the past.

    So in these cases, it has nothing to do with ORM. Not that you can't use ORM to help load data into memory. the challenge is replaying the events accurately and efficiently. For example, say someone enters wrong price and quantity and the accounting system need to do "as of analysis." Well you can replay using LIFO or FIFO or both to figure out if you really have a problem. If it turns out your profit for Q1 was lower, it means you may have over paid taxes. So it all depends what you have to do :)

    stupidly loading too much data is bad obviously, but sometimes you really do need to load that much data.

    peter
  171. Sounds horrible[ Go to top ]

    So in these cases, it has nothing to do with ORM. Not that you can't use ORM to help load data into memory.
    There are many cases, many problems and there are the right tools to solve problems. I like ORM , but I do not think it is the right tool for everything and I am not going to participate in anti SQL propoganda just because I like ORM.
  172. Sounds horrible[ Go to top ]

    but I do not think it is the right tool for everything and I am ot going to participate in anti SQL propoganda just because I like ORM.
    Just be sure to stay way from SQL propaganda too. And anti-anti SQL propaganda.
  173. Sounds horrible[ Go to top ]

    but I do not think it is the right tool for everything and I am ot going to participate in anti SQL propoganda just because I like ORM.
    Just be sure to stay way from SQL propaganda too. And anti-anti SQL propaganda.

    it's all good. be it sql or orm, it's fun to see people's eyes bug out when you say, "the system has to an As of analysis of arbitrary time period." the usual response is, "but that could be millions of rows!" There's still plenty of reasons why mainframes are around and why Sun will keep selling their big E15K, E20K monsters. It's nice to have 10Gb of in memory cache :) of course in these edge cases, makes absolutely no difference if you're using sql or orm to load the data.

    peter
  174. Sounds horrible[ Go to top ]

    of course in these edge cases, makes absolutely no difference if you're using sql or orm to load the data.peter
    ORM has no problems with SQL, good products like hibernate support stored procedure calls too. I do not understand why ORM must be a tool to fight with SQL.
  175. Sounds horrible[ Go to top ]

    of course in these edge cases, makes absolutely no difference if you're using sql or orm to load the data.peter
    ORM has no problems with SQL, good products like hibernate support stored procedure calls too. I do not understand why ORM must be a tool to fight with SQL.

    Who says ORM is a tool to fight with SQL? On the contrary, ORM relies on SQL, and no-one who develops with ORM should be without a good understanding of SQL.
  176. Sounds horrible[ Go to top ]

    Who says ORM is a tool to fight with SQL? On the contrary, ORM relies on SQL, and no-one who develops with ORM should be without a good understanding of SQL.
    As I understand you are trieng to help OODBMS vendors to descredit SQL standards.
  177. Sounds horrible[ Go to top ]

    Who says ORM is a tool to fight with SQL? On the contrary, ORM relies on SQL, and no-one who develops with ORM should be without a good understanding of SQL.
    As I understand you are trieng to help OODBMS vendors to descredit SQL standards.

    You understand wrong. I LIKE standards! It is the RDBMS vendors who are helping to discredit SQL standards by not implementing them fully.
  178. Sounds horrible[ Go to top ]

    of course in these edge cases, makes absolutely no difference if you're using sql or orm to load the data.peter
    ORM has no problems with SQL, good products like hibernate support stored procedure calls too. I do not understand why ORM must be a tool to fight with SQL.
    Juozas, I can't understand how in one post you say you like ORM and use it to develop web apps, and then in another post you say ORM competes with SQL. Someone who really uses or knows what ORM is all about woundn't ever think such a thing. I think you are confusing ORM with something else, I have had this impression in every discussion involving ORM you participate. If you could describe in simple terms your definition of ORM, maybe many of these arguments would be made clear.

    Regards,
    Henrique Steckelberg
  179. Sounds horrible[ Go to top ]

    Probably my input is not very large, but I contribute myself for ORM. Just do not provoke me if you do not like silly jokes.
  180. I'm probably one of those who some would classify as having a tendency to 'get carried away'! ...
    I tend to agree. I would like to hedge my bets and say that there probably is some case where ORMs and objects might not be best. But I haven't seen it yet.

    What I see is that people try do use Java the same way they use COBOL. Don't. Things were done the way they were because:
    1. You had limited computing time
    2. COBOL is procedural
    3. Everything used to be stored and accessed via files - and going further back was not stored on DASD but tapes and cards.
    4. [Fill in the blank]

    We need to stop thinking like this. Now we have messaging and the web and tuple spaces and ... .

    For those of you who think IBM and Oracle think the relational model is best - I suggest you dig into what is available from both in the way of OOness. I just had a very interesting conversation with our DB2 DBA.
  181. Oracle OLAP a good example[ Go to top ]

    I'm probably one of those who some would classify as having a tendency to 'get carried away'! ...
    I tend to agree. I would like to hedge my bets and say that there probably is some case where ORMs and objects might not be best. But I haven't seen it yet.What I see is that people try do use Java the same way they use COBOL. Don't. Things were done the way they were because:1. You had limited computing time2. COBOL is procedural3. Everything used to be stored and accessed via files - and going further back was not stored on DASD but tapes and cards.4. [Fill in the blank]We need to stop thinking like this. Now we have messaging and the web and tuple spaces and ... .For those of you who think IBM and Oracle think the relational model is best - I suggest you dig into what is available from both in the way of OOness. I just had a very interesting conversation with our DB2 DBA.

    actually, if you look at Oracle's OLAP, it is influenced by OO. I'm too lazy to find a link right now. Google is your friend :)

    peter
  182. Oracle OLAP a good example[ Go to top ]

    actually, if you look at Oracle's OLAP, it is influenced by OO.
    Yup. And amongst other things.
  183. For those of you who think IBM and Oracle think the relational model is best - I suggest you dig into what is available from both in the way of OOness. I just had a very interesting conversation with our DB2 DBA.
    Indeed, how the big vendors go about promoting and developing their products is a different matter. The introduction of OO features in database products has a *very* long history. It has not gained much traction though.
    However, the relational model, apart from being the best model, is also the only model that has *any* foundation, moreover, it has a *very* solid algebraic foundation. There is no formal, solid and provable foundation for the OO model assuming one could actually agree on the mere description of such a model.
    Going back to the hierarchical paradigm (XML) is going back to the dark ages of network databases, which have failed even in front of a less-than-ideal implementation of the relational model (SQL databases).
    That is not to say that the OO model is not excellent for programming; I am firstly a programmer and I should know. The point is that the OO model (again, assuming there is one true OO model) is ill suited for data management.
  184. The point is that the OO model (again, assuming there is one true OO model) is ill suited for data management.
    True. So don't manage data. Manage attributes.
  185. The main reason in the specific cases I cited like calculating P&amp;E or aggregations, using a trigger implies real-time calculations, which is not needed in the cases I am aware of. In many cases I've seen, things like P&amp;E are calculated over night or on a weekly basis.
    Financial formulas have many parameters, some parameters are calculted "offline", some can be calculated at realtime to produce single number like P/E. I like to use ORM for everything too, but probably I am too lame to load database to memory and to map numbers to domain objects, I am not a finance expert and numbers are just numbers for me, I have no idea how formula maps to business logic, this business logic is just not a my business. But I trust ORM experts, probably it is very performat,portable and object oriented to calculte all this stuff using ORM in client memory.
  186. I am too lame to load database to memory and to map numbers to domain objects, I am not a finance expert and numbers are just numbers for me, I have no idea how formula maps to business logic

    ORM does not 'load database to memory'. It simply picks up the data required for a specific operation; it does not 'load the database' any more than stored procedures 'load the database'. You don't 'map numbers to domain objects'. Domain objects aren't numbers in the same way that a record in a table is not a number. The mapping of classes to tables can be handled automatically by good ORM products. Then you simply use the fields of the classes. No 'mapping of numbers' is involved at all.

    If you don't know how to map formulae to business logic, you are going to be unable to develop even pure SQL systems.
    But I trust ORM experts, probably it is very performat,portable and object oriented to calculte all this stuff using ORM in client memory.

    I You keep insisting that everything is in client memory. That is very rare indeed. Almost all ORM use is server side. I run ORM on the same machine as the server, so there is no network overhead at all.

    You seem to be an expert in manufacturing an ever-increasing number of Straw Men regarding ORM.
  187. If you don't know how to map formulae to business logic, you are going to be unable to develop even pure SQL systems.
    I do not need to care about finace to develop systems. Business logic is finace analyst business. This stuff is numbers,formulas, fields and tables for me. It looks like I must be a finace expert to use JDO, my domain is just data. Probaly I will stop to code after I become a finace analyst.
  188. It looks like I must be a finace expert to use JDO, my domain is just data.

    This is a very silly statement. You are setting up Straw Men again. JDO has, of course, nothing to do with the business logic or calculation. Your finance expert can write the business logic separately in Java, without needing to know about JDO. If you are interested in just data, then you will write the queries to retrieve the data and persist it. JDO (and transparent persistence) is about separation of concerns.
  189. " finance expert can write the business logic separately in Java "
    Finance experts prefer Excel and most of them do not have problems with SQL too, I am not going to make them JAVA developers.
  190. " finance expert can write the business logic separately in Java "Finance experts prefer Excel and most of them do not have problems with SQL too, I am not going to make them JAVA developers.

    I don't think any of us would want you to. :(

    (ok, that was a little mean. "Lord, I apologize and please be with the starving pyg..." :P For all of you who don't know - See "Larry the cable guy")
  191. " finance expert can write the business logic separately in Java "Finance experts prefer Excel and most of them do not have problems with SQL too, I am not going to make them JAVA developers.

    This is, of course, completely irrelevant to your claim that you need to be a finance expert to use JDO. And, to state that most finance experts can use SQL... (I would like to see my accountant cope with an Oracle prompt!)

    Companies hire people called 'developers'. These skilled workers sit with analysts and experts and discuss requirements with them. They then code up the algorithms and calculations in whatever language is considered suitable; C++, Java, C#... whatever.

    If you are seriously expecting people who are experts in finance and who usually use Excel to sit down with an editor and code up stored procedures for you, you are going to be in serious trouble.

    Of course, as I have already told you, finance code has been written in all sorts of languages (OOP and non-OOP) for decades. You are apparently implying that it should only be in SQL. This is, to be blunt, nuts.
  192. This is, of course, completely irrelevant to your claim that you need to be a finance expert to use JDO.
    You said it yourself:
    "finance expert can write the business logic separately in Java "
  193. This is, of course, completely irrelevant to your claim that you need to be a finance expert to use JDO.
    You said it yourself:"finance expert can write the business logic separately in Java "

    Exactly. This business logic can be written separately from JDO. Which proves my point.
  194. I worked on a project where the guy used PLSQL to generate HTML and hold business logic.What a nightmare.
    Yes, it is not a good idea to use stored procedures for UI, database objects like procedures and views are good for reusable stuff and optimizations. It makes sence in "systems" (many applications are connected to the same RDBMS). If this stuff is used in the right way (triggers,constraints, common business logic) then it saves a lot of time for integration, It is secure and performant.
    But I think this is too extreme to use this stuff for UI, it is as extreme as data aggregation on client using "domain objects".
  195. While everything doesn't have to be java, the database is one piece of the application.

    +1

    Well said. In my view, a major obstacle in trying to move much software development out of a stagnant 1980s attitude is to realise that the database is just a tool, and OOP languages, which became mainstream in all other areas of IT two decades ago, are the appropriate way to express business logic.
  196. It does happen[ Go to top ]

    I'm currently porting two database Web apps from MySQL to Oracle. One uses straight JDBC and honest-to-god embedded sql strings for PreparedStatements. The other uses Hibernate.

    Right now, I'm happy at least one of the two is based on Hibernate.
  197. It does happen[ Go to top ]

    I'm currently porting two database Web apps from MySQL to Oracle. One uses straight JDBC and honest-to-god embedded sql strings for PreparedStatements. The other uses Hibernate.Right now, I'm happy at least one of the two is based on Hibernate.
    With the Hibernate one, there is no "porting". It is generated DDL and go! :)
  198. Think outside the Box ![ Go to top ]

    The point wasn't just including SQL in C#, but solving the impedence fo SQL -> Objects -> XML delimma we are all facing.

    I'm sure the critics of Anders work were also the ones that said "Why do we need attributed classes and methods, enumerators, boxing/unboxing, and on and on ...".

    C# has a few tricks for Java to learn and Java has a few tricks for C# to learn.
  199. I have coding using esqlc and 4gl of informix for about 3 years, which is something like SQLJ. The code is more simple and easy to understand, easy to maintaince than other API based programming, such as ODBC, JDBC. so I think that native support for SQL is an interesting feature for compiler.

    Also, as an old java progammer(about 8 years), i realy like the "Simple Syntax" and the WriteOnceRunAnyWhere feature, and i would like to got a standand way for java source files. I dont want read java with a lot of dialects.

    So, is there any standard ways for SQLJ's precompiler? I think there should be a JSR in the domain, support extension such as SQLJ, Aspect, XML, and futher extension in a general way, unify the source file, compile process, and runtime, debug mode and more. So we can got the good things of a domain-dialect and still got the "KISS" and "WORA" features of java platform?
  200. Hello All,

    This may be a bit OT but I am looking for a library that does not replace JDBC resultsets, allows for a database independant version of SQL that is full featured and allows us to store the SQL externally. I need something like JDBC resultsets as we use our own implementation of DataSets to abstract the persistence from the persisted IE the data can come from a file, database query or other source and the processor only sees a collection of fields stored as a collection of rows. I need a database independant SQL as we regularly switch between Oracle9i and SQLServer 2000 (We have our reasons, so please don't bash me). We store our SQL externally so the program does not need to be changed when the business requirements change. We also have to rewrite some SQL queries when going from Oracle to SQL Server and vice-versa. The rewriting of SQL is my greatest concern so
    the library should provide something like HQL but not objectified.

    Thanks Ahead.
  201. Just implement wrapper for JDBC driver if you need custom SQL dialect, there are a few open source SQL parsers, just fork one of them (see http://jsqlparser.sourceforge.net/ for example). There are many libraries to make JDBC API more user friendly, see apache DBUtils or spring JDBC wrapper, fork one of them.
  202. Well, since I originated this thread, I would like to see if we could go back to where the thread started from, namely native support for SQL in Java.

    Let us go back to the history of Java, to the early days of AWT and then Swing. What Java did was to provide an abstraction of GUI regardless of the underlying operating system and desktop management system. It took some years before Swing was stablized, but eventually it provided a great way of dealing with GUI. And the support is native, meaning you have type-safe support for GUI development.

    Back then, Java started to support databases by adopting what was out there, namely ODBC. With a new face, JDBC was born. But what if the same abstraction as we saw with AWT and Swing could be done with databases?

    With all respect to the efforts out there to simplify database development, e.g. ORM, JDBC, XML, why cannot database programming be done with standard Java?

    Just imagine that database development become like something in the below:

    <code>
    SQLSelect select = new SQLSelect;
    select.setRule(new SomeQueryRule(lablablab));
    Columns columns = new Columns();
    columns.add(new Column("name"));
    Columns.add(new Column("lastname"));
    select.getColumns(columns);

    Query query = select.performQuery(select);
    </code>

    obs! Do not shoot me for the above code, it is just an illustration.
  203. Well, to some degree - and primarily with the exception of syntax - Java already has this in a number of ways:

    • iBatis and a host of other similar O/R mappers
    • Hibernate
    • Toplink
    • JDO implementations
    • EJB (CMP and BMP from 1.1 on, through 3.0)
    • Various DAO frameworks, too many to mention
    • Non-relational systems like Prevayler and other OODBMS systems
    • Btree implementations such as Berkeley DB for Java

    In various forms, all support stuff like that, and many of them can do so cross-database.
  204. Well, to some degree - and primarily with the exception of syntax - Java already has this in a number of ways:
    • iBatis and a host of other similar O/R mappers
    • Hibernate
    • Toplink
    • JDO implementations
    • EJB (CMP and BMP from 1.1 on, through 3.0)
    • Various DAO frameworks, too many to mention
    • Non-relational systems like Prevayler and other OODBMS systems
    • Btree implementations such as Berkeley DB for Java
    In various forms, all support stuff like that, and many of them can do so cross-database.

    Please add JDX O/R mapper to the list. JDX is lightweight and totally non-intrusive. It supports, among other things, bulk operations, streaming objects, and stored-procedure integration.

    -- Damodar Periwal
    Software Tree, Inc.
    Simplify Data Integration
  205. With all respect to the efforts out there to simplify database development, e.g. ORM, JDBC, XML, why cannot database programming be done with standard Java?

    Because for database programming you use SQL and stored procedures. It's a different programming domain, with it's own set of tools, languages, vendors and quirks. It requires expertise and you simply cannot pretend it's Java.

    For database _access_ you use APIs - "standard" Java, or "standard" C++ or "standard" PHP or whatever.
  206. Because for database programming you use SQL and stored procedures.
    And there you have your reason. We are not doing "database programming".

    Maybe you are right. We probably shouldn't hide the CLI behind JDBC or ODBC either. Or ... . :)
  207. Because for database programming you use SQL and stored procedures.
    And there you have your reason. We are not doing "database programming".Maybe you are right. We probably shouldn't hide the CLI behind JDBC or ODBC either. Or ... . :)

    I'm afraid I'm missing your point there.
    The original poster asked why isn't there a pure java way of doing database programming. I told him that SQL and stored procedures (standard, pl/sql, pl/pgsql, java, perl, php etc) are the way database programming is done. What you do in Java is /accessing/ the database, not /programming/ it. What was not clear enough ?
  208. What you do in Java is /accessing/ the database, not /programming/ it. What was not clear enough ?
    Yes I do. I hope you understand that what you said doesn't support your position. I don't think you will. :(
  209. What you do in Java is /accessing/ the database, not /programming/ it. What was not clear enough ?
    Yes I do. I hope you understand that what you said doesn't support your position. I don't think you will. :(

    Ok, so I'm thick right, and you're canadian. Please explain.
  210. I've been using SQLJ for the last 5 years. I think it would be great if it were native to java. Based on what has been posted so far, it seems like a lot of people have misconceptions of why SQLJ can be a good thing.

    1) It is static SQL. Which means that the query plan is known to the database at compile time. This means that it is pretty much guaranteed to execute the same way until a DBA does something explicit to the query plan or the underlying table statistics. In mission critical systems where performance is milliseconds, a runaway queiry can be disasterous. Guaranteed, static, query plans are a very "good thing". This to me is 90 percent of the benefit of SQLJ. Additionally, producing static SQL allows you to take advantage of the hundreds of non-java specific monitors, database utilities and tools that have existed for RDBMSs for decades.

    2) Everything else is gravy:
    a) The fact that you get compile time validation fo your SQL - no need to wait to run the app to get a SQL exception because of some bonehead error.
    b) The fact that security is verified at compile time as well. Performance saving, as well as avoiding run time security exceptions.
    c) the fact that you actually can get quite a big performance lift if you use the SELECT INTO feature, reducing the number of SQLs executed by 3:1
    d) the fact that your code is readable and succinct, compared to straight JDBC.

    Some of the posts so far have mentioned that the need for native SQL is obviated by ORM frameworks. To me that seems to be apples and oranges. You could just as easily build an ORM framework that is built on SQLJ as you can based on JDBC, especially if the SQLJ was native.

    IBM, (the vendor I know the most intimately) swears that SQLJ is the future of all its internal development and it does provide good support for it in its tooling. Despite this fact, it is clear that SQLJ has not caught on. I believe the reason for this is twofold: the fact that the preprocessor technology is more cumbersome than most developers want to deal with, and the fact that most developers are not familiar enough with RDBMSs to understand the benefits of using the technology.

    Andy Faibishenko
    ISTEK Consulting
    http://www.istekonline.com

    rants at:
    http://technomusings.blogspot.com
  211. SQL like all relational languages is designed for embedding ( "RELATIONAL COMPLETENESS OF DATA BASE SUBNGUAGES" by E. F. Codd ) so native support for SQL in PL is very important, but probably it is better to design new language and compile stuff to JAVA bytecode, there too many good things to support in single language.
  212. I've been using SQLJ for the last 5 years. I think it would be great if it were native to java. Based on what has been posted so far, it seems like a lot of people have misconceptions of why SQLJ can be a good thing.1) It is static SQL. Which means that the query plan is known to the database at compile time. This means that it is pretty much guaranteed to execute the same way until a DBA does something explicit to the query plan or the underlying table statistics. In mission critical systems where performance is milliseconds, a runaway queiry can be disasterous. Guaranteed, static, query plans are a very "good thing". This to me is 90 percent of the benefit of SQLJ. Additionally, producing static SQL allows you to take advantage of the hundreds of non-java specific monitors, database utilities and tools that have existed for RDBMSs for decades.2) Everything else is gravy:a) The fact that you get compile time validation fo your SQL - no need to wait to run the app to get a SQL exception because of some bonehead error.b) The fact that security is verified at compile time as well. Performance saving, as well as avoiding run time security exceptions.c) the fact that you actually can get quite a big performance lift if you use the SELECT INTO feature, reducing the number of SQLs executed by 3:1d) the fact that your code is readable and succinct, compared to straight JDBC.Some of the posts so far have mentioned that the need for native SQL is obviated by ORM frameworks. To me that seems to be apples and oranges. You could just as easily build an ORM framework that is built on SQLJ as you can based on JDBC, especially if the SQLJ was native.IBM, (the vendor I know the most intimately) swears that SQLJ is the future of all its internal development and it does provide good support for it in its tooling. Despite this fact, it is clear that SQLJ has not caught on. I believe the reason for this is twofold: the fact that the preprocessor technology is more cumbersome than most developers want to deal with, and the fact that most developers are not familiar enough with RDBMSs to understand the benefits of using the technology.Andy FaibishenkoISTEK Consultinghttp://www.istekonline.comrants at:http://technomusings.blogspot.com

    I had a choice to use SQLJ(vs. JDBC) on a project and did the research and saw items you mentioned. In fact, the DBA wanted me to use SQLJ for similar reasons. However, I just wasn't convinced that the performance benefits were enough to justify the hassle. I, like most people here I assumed, have used preprocessors before, so I don't think this was a deal breaker, but it wasn't attractive.

    I didn't find the code more readable, in fact the opposite, but I concede that this could be biased. One issue I had was the fact that the RDBMS companies didn't embrace the technology enough for me to be comfortable. I was a contractor on that job and the last thing I wanted was them upset years later because of maintanence. Since widespread adoption of SQLJ didn't happen, I feel justified in that decision.

    Also, I felt that performance could be optimized later IF there were performance problems or concerns.

    Basically, despite being familiar with the advantages of SQLJ, I still thought that JDBC was a better choice.
  213. I don't think I got my point across. The original question was whether there's any benefit to providing native SQL support in java. In my experience, having the ability to produce static SQL natively is a very useful feature for the reasons I outlined in my original post. Native would mean that the preprocessor step would not be needed. Furthermore, it doesn't prevent you from writing ORM frameworks on top of the native SQL support - they would probably be as powerful if not more powerful.

    Andy Faibishenko
    ISTEK Consulting, Inc
    http://www.istekonline.com

    rants at
    http://technomusings.blogspot.com
  214. summary:[ Go to top ]

    Sun has marketed that if you know Java, you won't need to know another lanagange or skill (like SQL, or relational) with some success in certain demographics.

    PHP and .NET can deal w/ tabular data as a part of their culture, so DBA's have easier communications there.

    People that know mutiple lanagages think this debate is waste of time.

    .V
  215. summary:[ Go to top ]

    Sun has marketed that if you know Java, you won't need to know another lanagange or skill (like SQL, or relational) with some success in certain demographics.

    This is totally mistaken. They have continued very strong support for C, C++ and Fortran. This is obvious if you go to their website and look under their development tool products (Sun Studio). They are also interested in extending the range of languages on the JVM. See JSRs 223 and 241.
    PHP and .NET can deal w/ tabular data as a part of their culture, so DBA's have easier communications there.

    There is little difference between the capabilities of PHP and JSP in this area, as illustrated by the JSTL SQL tags.
    People that know mutiple lanagages think this debate is waste of time..V

    I have used a large number of languages over 30 years and I think this debate is useful.
  216. summary:[ Go to top ]

    Sun has marketed that if you know Java, you won't need to know another lanagange or skill (like SQL, or relational) with some success in certain demographics. PHP and .NET can deal w/ tabular data as a part of their culture, so DBA's have easier communications there.People that know mutiple lanagages think this debate is waste of time..V

    I must have missed that marketing.
  217. See JoSQL (http://josql.sourceforge.net), forget all that RDBMS rubbish :)
  218. It seems that the assumption is all the embedded sql works against a backend database. However, in .Net, there are datasets, datatables that are collections within the language. It would be good to be able to filter and query those data structure using a familiar SQL syntax.

    At the moment, I am not aware of similar capabilities within Java.
  219. It seems that the assumption is all the embedded sql works against a backend database. However, in .Net, there are datasets, datatables that are collections within the language. It would be good to be able to filter and query those data structure using a familiar SQL syntax.At the moment, I am not aware of similar capabilities within Java.
    And with good reason.
  220. I thought gigaspaces supports sql[ Go to top ]

    It seems that the assumption is all the embedded sql works against a backend database. However, in .Net, there are datasets, datatables that are collections within the language. It would be good to be able to filter and query those data structure using a familiar SQL syntax.At the moment, I am not aware of similar capabilities within Java.
    And with good reason.

    I thought gigaspaces provides the ability to query the data in-memory using sql. Just to be clear, datasets and datatables are completely different than javaspaces and gigaspaces, but one need not go with the route MS has chosen.

    I've used datatables with MDX queries against analysis service. I wasn't aware that one could load data from Sql Server in a detached manner and then perform sql queries against the data in memory. By detached, I mean data read from sql server into memory just like a cache. The last time I checked, you can cache datasets in ASP and cache datasets in general, but it is not the same kind of caching as Hibernate, Coherence or gigaspaces.

    I still see embedded sql as a policy and development decision. in a small team, having embedded sql might not be bad at all. with the right team, it could be a great fit. For a large project with dozens of programmers and different divisions, embedding sql often is not acceptable. But being "unacceptable" from a company policy/development perspective doesn't mean embedded sql has no technical value. I would say these two things are separate issues.

    I don't agree with the assertion that Java doesn't have similar feature. It not be an "official Sun stamped" spec, but there are commercial solutions that provide the capability.

    peter
  221. It seems that the assumption is all the embedded sql works against a backend database. However, in .Net, there are datasets, datatables that are collections within the language. It would be good to be able to filter and query those data structure using a familiar SQL syntax.At the moment, I am not aware of similar capabilities within Java.
    Is this what you're looking for?
    "JoSQL (SQL for Java Objects) provides the ability for a developer to apply a SQL statement to a collection of Java Objects. JoSQL provides the ability to search, order and group ANY Java objects and should be applied when you want to perform SQL-like queries on a collection of Java Objects."

    Regards,
    Henrique Steckelberg