Skip to Content
Having read the paper “Java Persistence in SAP Web Application Server” by Karl Kessler, I couldn’t stop myself from making a little comment.

SQLJ Karl seems to promote SQLJ. I have a strong feeling against this technology, because it’s a wild and dreaded mix of SQL and Java code. So, to back up this statement, let’s have a look at advantages and disadvantages:
Advantages – compile time checks.
Disadvantages

  1. you have to learn SQLJ.
  2. you embed your SQL code(which is not exactly sql) directly into the application.
  3. you intermingle mapping logic with connectivity logic.

CMP and JDO don’t that appeal to me also. Although these both technologies are proven in the battlefield and showed their maturity, they also have a certain disadvantages. Of course, it’s also a learning curve. Then, it can be very tricky to track WHAT exactly is happening behind the scenes: what sql queries are getting issued to the database, when, how often. Unfortunately I had no chance to play with CMP and JDO in WebAS, so I’d ask other people who did to speak their mind.

JDBC. Last in the list, but not last in developers’ arsenal. Combination JDBC + Open SQL seems to me the most powerful today. Unfortunately here we have to introduce JDBC connectivity code into our applications. J2EE zealots solve it with DAOs(Data Access Objects), home-built frameworks and! with open source community frameworks. An example of such community framework is Spring framework. It does a lot of different interesting things and one of them is JDBC abstraction.

I specially omitted stored procedures here, because it’s currently not supported on the Open SQL level. Though, this mechanism is extremely powerful and fast and I really miss it.

So, to make a conclusion…I guess it’d be an interesting undertaking to make a component, linking Open SQL with Spring JDBC abstraction layer.

To report this post you need to login first.

15 Comments

You must be Logged on to comment or reply to a post.

  1. Markus Kohler
    Hi,

    Opinions expressed do not represent the views of anyone other than myself.

    I fully agree with you, that the choice SQLJ  might look strange, because it’s main idea is to mix SQL with java code, which we should have learned should be avoided.

    On the other side it has the advantage that it is very close to the ABAP development model. ABAP is embedding (or should be we say extending) SQL statements as well, and people are used to it.

    One argument also seems to be compile time checking of the SQL statements. IMHO compile time checking has been overrated for a long time, and if you write unit tests for your DAO ( data access object), which you should do anyway, compile time checking doesn’t buy you much.

    I’m also wondering whether refactring will still work for SQLJ (I have not tried that yet).

    Regards,
    Markus

    (0) 
    1. Stepan Samarin Post author
      Hello Markus,
      Of course you’re right, people are used to developing software in the unique ABAP way…I see this Open SQL in ABAP not as a benefit. I was trained and got used to always make a strict separation between different systems/layers/components. What ABAP makes with Open SQL is a mixture of middleware logic with database access logic. For some applications it’s a benefit. For other it’s not. I guess it’d be a theme for a separate religious blog dispute ;).
      (0) 
    2. Anonymous
      Hi,

      1. One missed feature noticed using OpenSql and DataDictionary is lack of support for “Views”. As a developer, in most of the cases, writing select SQL against View is much easier and cleaner than using Joins.
      (Of late, heard that it is planned for NW05)

      2. Regarding SQLJ, yes compile time checks are good, but so far we found that, with proper Unit Tests for database access classes, same thing can be accomplieshed.

      3. Regarding programming with SQLJ, 10 year back or so I remember using pro*c, where Oracle SQL code and C code are mixed together. It was a big project with about 10000 source files and certainly maintaining the code wasnt that easy.

      4. Some comments on StoredProcedures: Using stored procedures is as good/bad as using NativeSqlAccess thro OpenSql which sort of goes against the philosophy of portable SQL code. ( However Agree it could be more performant than dynamically compiled SQL, but….)

      Rgds
      Kiran

      (0) 
  2. Benny Schaich-Lebek
    As a basis developer I thought you should be familliar with embedded SQL aka OpenSQL in ABAP, cause it is absolutely the same with SQLJ.

    Counting deployment cycles in J2EE I really would appreciate compile time checks of tables, field names and SQL syntax!

    CMP and JDO:
    You may have missed the fact that Java persistency delivers SQL trace which makes it really easy to check what actually happens on the DB, no matter what you are using.

    JDBC:
    Well, what you are suggesting is exactly what you get right after writing your SQLJ file: After saving it, the SQLJ directly gets generated into a complete JDBC .java file…

    Stored procedures:
    STORED PROCEDURES?!? Are you really working for basis? I thought even thinking about this is the fastest way to get nailed on a cross directly in front of building 1… (At least that was the impression I got years ago on my first contact with SAP and didn’t mention it since working here)

    Regards,
    Benny

    (0) 
    1. Stepan Samarin Post author
      Hello Benny,
      it was very interesting to read your reply. Yes, I’m a basis developer. Yes, I did mention stored procedures, because I believe in the power of this technology and successfully used it before coming to SAP. We are not in jail here, right?

      CMP and JDO: sorry but I think you missed that I said that I didn’t play with this technologies in WebAS. I’ll defintely do in the future.

      JDBC. I was talking about abstracting JDBC connectivity code. I’ve seen lots of mistakes in JDBC connectivity code, because it’s tricky. I see the same mistakes in SQLJ examples now. I’ll post code examples soon to backup this statement.

      (0) 
      1. Benny Schaich-Lebek
        At least it reliefed me to hear that you used stored procedures BEFORE you came to SAP. You are right, this is a powerful technology and as such uses the power of the database you’re accessing – which we need desparately to concentrate on the DB functionality of our three-tier-environment (hear a bell ringing?).
        OpenSQL misses this function for a reason and it misses it long time now, as it is just ported from the ABAP world to Java.
        Of course you can use stored procedures for nice programs running in a 5 user environment, or maybe even ten. But this is not typical for SAP systems.
        I’m awaiting your examples of error prone code eagerly, as I’m sure the guys sitting next to me (some of them wrote that stuff), would really like to know what they missed.
        (0) 
  3. JIM SANDERS
    First let me say that I have been a fan of SQL stored procedures since before most of you guys were kissing your first girl in school. You SQL embedders are jokers! I just ran a test insert statement in a loop inside a stored procedure that added 1,000,000 rows to a MaxDB (SapDB 7.5) on a lame box (1 gigahertz Celeron 1 CPU 512 Mb RAM Linux ES 3.0) in a minute or two. But when I tried embedding the SQL loop in a VB 6 app that connected remotely from the  WAN it took hours.

    Secondly Java stands for Job Auto-extension Virtual Application – it is just an extra layer of coding that keeps you hired forever as it is mounds of speghetti code branded as OOPS that no developer in his right mind would ever entrust to run a web server or mission critical app. It even has the warning that it is not to be used in life support or rocket science.

    Now you are gonna say that the Java layer is fast since it runs on the SQL server. That doesn’t account for the speghetti code effect though, eh?

    The man is right SQL embedded into anything, even into your excalibur Java language that Sun was supposed to slay the dragon (MS) with is stuuuuuuuuuuuuuuuupid. Get a life and write stored procs or change careers please. I am tired of rewriting you guys speghetti!

    (0) 
    1. Benny Schaich-Lebek
      Hi Jim,
      it should have been a hard task for you to do this as at the time I grew up in school because then there was no such thing like any database that could handle something named “stored procedure”, except you tell me how to do this with VSAM, IMS, or other strange old programs we used to call database at that time.

      However, you didn’t run an R/3 SD test on top of that stored procedure action you’re talking about, did you?

      Otherwise I bet you would have seen a significant stop of action during that time.

      And also, you did not coincidentally run your VB app in auto commit mode so that you started a transaction for every single record writing to the database?

      Your arguments are reading a little bit like the ones of my desktop database customers back in 1991 changing to a real SQL DB. Most of them were really astonished why their stuff suddenly was so much slower, never realizing that this was not the purpose of changing to remote DB.
      And this is exactly what you’re proving here: Yes, your scenario is best done with Paradox, dBASE, Access, and you name the others in that class.

      And here I completely agree with you: A single task is always best performing in a single process. But this is not the purpose of software that is written for many concurrent users in high performance, mission critical environments.

      By the way, this has nothing to do with Java.

      Regards,
      Benny

      (0) 
      1. JIM SANDERS
        OK me so-sorry I make fun of your dating habits. And Java makes no diff. to me since me-no-use-java.

        But my point was erraneously submerged in java-kissing comments that embedded SQL is a back-peddeling from all what the gurus in the n-tier world had been teaching: that you have a DB layer run by stored procs, then a b’ness layer run by java or whatever code-fiasco you like, then a UI layer of yet another of your favorite code-flavors. Then you use OOP to make nice sweet looking API’s for the 3 tiers to call each other with. And voila! you have n-tier (3-tier) done right. But now you just chuck wrench in gearbox moving SQL statements out of DB layer? As a long time DBA on real DB’s like Orafice, I find most folks should be banned from writing SQL let alone designing normalized data structures. And finally, if one makes a change to the data structures and all the SQL is in stored procs, its easy to alter the SQL to address the DB changes. Not so easy if the SQL is mixed up all over n-tier layers and maintained by who knows how many in house and external contractors, eh?

        (0) 
        1. Benny Schaich-Lebek
          Jim,
          you’re talking about a completely different world here, do you? SAP applications are usually designed to run on different DB’s without any maintenance to the DB. Therefore OpenSQL was written. Try this with any other technology.

          The gurus you’re talking about  are not those I met(outside SAP) and they are definitely not from SAP, as no SAP program contains a single line of stored procedure. But may I mention that SAP’s three tier environment runs the single largest standard ERP system on this planet? Where some ‘gurus’ may not be able to proof that they ever ran something bigger then up to twenty users.

          I read enough of those books that defined the business level of apps into a servlet engine – good enough to move some peanuts around, but no real application.

          By the way: Changing data structures is something that (nearly) never happens in an SAP program. The impact is such deep that your SQL mix into the code is really your least problem. You’re not talking about LARGE applications here. Otherwise you would know.

          Regards,
          Benny

          (0) 
  4. Katarzyna Fecht
    Hi Stephan,

    Let’s concentrate on the relational persistence, where SAP Web AS supports both: JDBC and SQLJ APIs. Please note that Karl discusses the JDBC API first (the last position just in your list…). Indeed, we highly appreciate the proven and powerful JDBC technology, implicitly, we appreciate and support the JDBC based components.

    Further, keep in mind that SAP’s strategy for Java is to comply with established Java standards – like JDBC – ensuring platform independence and application portability. This is one of the reasons why SAP does not rely on any – still proprietary – JDBC framework. Our customers should be not required to install, get licenses etc. for any additional third part software to run SAP applications. Inwardly, you can adopt any JDBC compliant code to run on SAP Web AS Java easily.
    Now let’s turn on to the crucial point: SAP’s core strategy is to enable Java-based real life complex enterprise solutions. Writing long-lived systems in Java, featuring, for example, the system reliability, efficient maintainability and extendibility. Running possibly on hundreds or even thousands customer installations. And still obeying the Java standards –  a  challenging aim, right? Obviously, you can build a perfectly tailored JDBC-based system for a specific customer project, where, among others, you can react quick in the case any JDBC errors caused in the productive system (I keep my fingers crossed for finding out the cause of fault quick…). And you can benefit from SAP’s long lasting experience with database centric development and the resulting recommendations. The important one is: Static SQL checks significantly improve the system reliability and maintainability and enormously speed the whole development process, first of all the testing phase. That’s why, yes, you are right, Karl (together with other SAP database specialists) is promoting the usage of SQLJ, especially in internal SAP projects. Namely, SQLJ combines the expressiveness of SQL with the early SQL statement check. If you save your SQLJ source file, SAP Java IDE immediately checks firstly the SQL syntax and secondly the accordance of the embedded names and types of database objects (tables, columns, indexes) with the definitions in the database catalog. For example, you get an error message immediately if you type a column name wrong. And SQLJ is a (newer) standard driven by major database system manufacturers thus it perfectly meets the SAP’s strategy for Java. (As you can see, not only SAP has noticed the need to bypass some shortcomings of JDBC.)

    Finally, let’s investigate your list of advantages and shortcomings of SQLJ:

    1. You have to learn SQLJ. 
    Yes, it is true: Nevertheless, SQL skilled programmers get accustomed to the little SQLJ syntax
    fast. It also holds for ABAP programmers. And the SQL beginners are not required to learn any JDBC objects and methods.

    2. You embed your SQL code (which is not exactly sql) directly into the application.
    I cannot guess what you mean. Firstly, mixing SQL and Java is actually the core JDBC feature, where SQL statements are just String parameters of JDBC methods and they can be placed inside any .java file. In contrast, files containing SQLJ clauses still have the extension .sqlj what forces the clear separation between the database access (DAO) classes (.sqlj) and the business process (.java). I also wonder about the statement “not exactly sql” as the main idea of SQLJ part 0 – also called “embedded SQL in Java” – is that you embed the original SQL statements directly into Java. Possibly you mean the useful extension allowing embedding the Java variables directly into SQL statements (I guess the most JDBC programmes would highly appreciate such a possibility).

    3. You intermingle mapping logic with connectivity logic.    
    You are right, although you get rid of managing (JDBC) statements, you still deal with an abstraction of a database connection called ‘connection context’ and there is an ‘SQLJ iterator’ corresponding to (JDBC) result sets – even having the advantage that you can rely on the type mapping etc. works fine right away after saving your code. The further advantage with SAP Web AS is: You can straightforwardly convert the SQLJ connection context to the JDBC connection and vice versa. The same holds for JDBC result sets and SQLJ iterators. You can run SQLJ- and JDBC based code even in the same database transaction. Both Open SQL/SQLJ and Open SQL/JDBC support the identical portable SQL set.
                
    4. I guess it’d be an interesting undertaking to make a component, linking Open SQL with Spring JDBC abstraction layer.
    I guess you are totally right, as there are many ways promising the reliable persistence layer. Try out SQLJ combined with Java Dictionary and admire the power of the following scenario with SAP Web AS:
    1. Database: After server installation, already installed and configured
    2. Connection pool (caching open database connections): Ready to use as well
    3. Define portable database objects and application wide database types once: In SAP’s Java Dictionary
    4. Write portable DAO classes and shorten the development cycle by the immediately SQL check: use SQLJ. If needed, combine SQLJ and JDBC code easily.

    To make a conclusion … The benefits of SQLJ outweigh the barrier of the need to get accustomised to. Try the related “Getting started” chapter of the Java Persistence documentation: You will manage to have your first SQLJ code running on SAP Web AS fast.

    (0) 
    1. Stepan Samarin Post author
      Hello Katarzyna,

      thanks for the detailed reply. I’m preparing an article, bashing SQLJ from both programmers’ and designers’ point of views, just to backup my statements.

      By the way, what do you want to say by “try getting started”? 😉 I wouldn’t have posted SUCH an opinion without a certain confidence.

      And, SQLJ is definitely not a new standard…

      (0) 
    2. Stepan Samarin Post author
      >Possibly you mean the useful extension allowing embedding the Java variables directly into SQL statements

      Yes, that’s exactly what I meant.

      (0) 

Leave a Reply