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.


31 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. 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
  8. This comment has been removed by the author.

    ReplyDelete
  9. Wow! This is the perfect blog I am looking this type of blog its awesome blog here , share great information about this topic. This informative blog helps many readers with their decision-making regarding the situation. Great articles and will look forward for more!opleiding medium

    ReplyDelete



  10. The strategy you have posted on this technology helped me to get into the next level and had lot of information in it. The angular js programming language is very popular which are most widely used.



    Dot Net Training in Chennai | Dot Net Training in anna nagar | Dot Net Training in omr | Dot Net Training in porur | Dot Net Training in tambaram | Dot Net Training in velachery







    ReplyDelete

  11. The post is written in very a good manner and it entails many useful information for us. I am happy to find your distinguished way of writing the post. Now you make it easy for me to understand and implement the concept. All the best for your blog.
    Java training in Chennai

    Java training in Bangalore

    Java training in Hyderabad

    Java Training in Coimbatore

    Java Online Training

    ReplyDelete
  12. 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. Please, continue to give me such valuable posts.
    hadoop training in chennai

    hadoop training in tambaram

    salesforce training in chennai

    salesforce training in tambaram

    c and c plus plus course in chennai

    c and c plus plus course in tambaram

    machine learning training in chennai

    machine learning training in tambaram

    ReplyDelete
  13. Really great post, Thank you for sharing This knowledge.Excellently written article, if only all bloggers offered the same level of content as you, the internet would be a much better place. Please keep it up!
    sap training in chennai

    sap training in omr

    azure training in chennai

    azure training in omr

    cyber security course in chennai

    cyber security course in omr

    ethical hacking course in chennai

    ethical hacking course in omr

    ReplyDelete