Saturday, January 25, 2014

ASE, HANA and much more

After a long time I am back to writing this blog again. Much has happened in SAP ecosystem since my last post. SAP HANA has gone to cloud, ODATA has become the standard way of communicating between different layers in SAP products and SAP business suite customers are increasingly using Adaptive Server (ASE) as the underlying database.

As I continue to work on ODATA and ASE, new things continue to surprise me. These things, I call them quirks - may not be that surprising for an ASE veteran, but they are for users of some other databases in the market. These quirks/peculiarities or special features, whatever they are, have been discussed in various forums. One particular helpful resource I found is a creation of one of my co-workers. Check it out  (  http://www.sypron.nl/main.html ) to find wealth of interesting ASE facts compiled over the years.

Today, I will discuss a feature of ASE that an average user may not have come across. It's an sql command -SET CHAINED ON/OFF that controls the transaction behavior. If your code is not explicitly starting a database transaction for series of sql commands, then SET CHAINED OFF command tells ASE to start a transaction. In the jdbc code you can do connection.setAutoCommit(true) to get similar functionality. The chained mode is particularly important if you are trying to execute a stored procedure on ASE. The successful execution of ASE stored procedure is dependent on using the correct chained mode. What bugs me is that, as a user of the stored procedure you need to know which mode was used when that stored procedure was created in the database.

If you happen to use autocommit(true) while executing a stored procedure that was created with 'set chained on' setting, then you will get a nasty error that looks like this "this procedure may be run only in chained transaction mode, SET CHAINED ON will cause the current session to run in chained transaction mode" . I have seen countless posts on internet and recently in SAP ecosystem discussion forums about this error, highlighting the frustration experienced by developers new to ASE. Thankfully ASE provides a system stored procedure that can set the mode of a stored procedure to "ANY", which allows that stored procedure to be executed in both settings - autocommit true and false. However it's not used widely judging from the number of people that experience this problem. If you do not want to get bitten by this nasty problem in runtime you can choose to do either of these two.

1. If you have the access to the underlying database (you need to be the owner of the procedure or have an SA role) you can run sp_procxmode command to make the stored procedure in question run in any mode. Assuming the name of the stored procedure is sp_proc_1 the command would be
execute sp_procxmode sp_proc_1, "anymode"

2. You can do this at run-time in your JDBC code. Execute sp_sprocxmode procedure with only one argument - the name of the stored procedure. The result set of this statement will give you what mode this procedure needs. Assuming, it requires 'unchained', you can execute conn.setAutoCommit(true) and then execute the sp_proc_1 procedure.

The second option is the better one that will avoid certain headaches later on, if you ever want to execute a stored procedure in  ASE. Regardless of the mode in which the stored procedure was created, I recommend you implement this strategy. There are other interesting facts about stored procedures and why stored procedures are important again is something I would to write in my next post.


10 comments:

  1. Thank you for your post. This is excellent information. It is amazing and wonderful to visit your blog.
    iPhone training courses in bangalore
    ios app development course

    ReplyDelete
  2. Hmm, it seems like your site ate my first comment (it was extremely long) so I guess I’ll just sum it up what I had written and say, I’m thoroughly enjoying your blog. I as well as an aspiring blog writer, but I’m still new to the whole thing. Do you have any recommendations for newbie blog writers? I’d appreciate it.
    Best Selenium Training in Chennai | Selenium Training Institute in Chennai | Besant Technologies
    Best AWS Training in Chennai | Amazon Web Services Training in Chennai

    ReplyDelete
  3. Hmm, it seems like your site ate my first comment (it was extremely long) so I guess I’ll just sum it up what I had written and say, I’m thoroughly enjoying your blog. I as well as an aspiring blog writer, but I’m still new to the whole thing. Do you have any recommendations for newbie blog writers? I’d appreciate it.
    Best Selenium Training in Chennai | Selenium Training Institute in Chennai | Besant Technologies
    Best AWS Training in Chennai | Amazon Web Services Training in Chennai

    ReplyDelete
  4. Really great post, I simply unearthed your site and needed to say that I have truly appreciated perusing your blog entries.


    angularjs Training in bangalore

    angularjs Training in electronic-city

    angularjs Training in online

    angularjs Training in marathahalli

    ReplyDelete
  5. Thank you for taking the time and sharing this information with us. It was indeed very helpful and insightful while being straight forward and to the point.
    python training in velachery | python training institute in chennai

    ReplyDelete
  6. A very nice guide. I will definitely follow these tips. Thank you for sharing such detailed article. I am learning a lot from you.
    Devops training in marathahalli
    Devops training in rajajinagar

    ReplyDelete
  7. Wow it is really wonderful and awesome thus it is very much useful for me to understand many concepts and helped me a lot. it is really explainable very well and i got more information from your blog.

    rpa training in velachery| rpa training in tambaram |rpa training in sholinganallur | rpa training in annanagar| rpa training in kalyannagar

    ReplyDelete
  8. Nice tutorial. Thanks for sharing the valuable information. it’s really helpful. Who want to learn this blog most helpful. Keep sharing on updated tutorials…
    Java training in Chennai | Java training in Bangalore

    Java online training | Java training in Pune

    ReplyDelete