Skip to Content

For those who have been working with SQLScript for awhile, you know that it is quite cumbersome to try and figure out what has gone wrong when a procedure does not return the correct results. Of course we have the TRACE statement which can be inserted into the SQLScript procedure which then writes out the intermediate results to a trace table during execution. But this approach is flawed. Firstly, the inspection of the results has to be done after the entire procedure execution is complete.  You can’t see the results in real-time as the code is being executed.  Secondly, and maybe most importantly, what customer will allow you to insert TRACE statements into a procedure directly in a productive environment?  We need the ability to trace variables during execution without having to modify the procedure in any way. Would it not be great for developers, and support personnel to be able to debug SQLScript procedures directly using a real integrated debugger? Well, good news!  With the release of SAP HANA 1.0 SPS5, I am pleased to announce the availability of the new SQLScript Debugger!

The new debugger is tightly integrated into SAP HANA Studio.   It uses the standard eclipse “Debug” perspective, so there is basically zero learning curve for those who are familiar with debugging in eclipse.   You can set/remove breakpoints, resume to the next breakpoint, and terminate the debug session. Other step features are still in the planning phases.

12-5-2012 10-43-37 AM.png

The most powerful feature of the debugger is of course the variable evaluation feature.  When the procedure is executed in the debugger, all of the variables are listed in the “Variables” tab including table variables. You can evaluate the values of the variables as you execute each statement of the SQLScript code.   For table variables, you can right-click and choose “Open Preview”.  This will open a tab at the bottom showing the data in table format.

12-5-2012 10-37-43 AM.png

As you probably have already realized, the new SQLScript debugger is a huge deal for HANA developers. We can now debug SQLScript procedures on the fly and see exactly what is going on during execution of each statement.  Currently, the debugger does have some restrictions which we are working to resolve over the next several revisions.  A list of these restrictions is documented in SAP Note 1779803. Finally, look for new videos coming soon to  SAP HANA Academy page showing how to use the new development tools delivered with SAP HANA 1.0 SPS5.

To report this post you need to login first.

13 Comments

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

  1. Joseph Chavadiyil

    Thanks for the overview

    I followed the steps as mentioned in the HANA sps5 guide to debug a sqlscript proc. But somehow I cant get to do it.

    What the guide says is that any <dot> procedure file open at the time when the debug perspective is selected, can be used to set breakpoints and executed.

    Now my question is shouldn’t we be executing the call procedure statement to debug the execution of the procedure with input values rather than the declaration of the procedure.

    I tried this approach though , but in the Debug window where I can see call stacks, I get a message “<terminated>xyz.PROCEDURE “.

    I tried a sql trace and it looks like the execution of the procedure is as follows,

    the below JSON executes the query in jmj\JMJPROC in debug mode i guess


    ## ERROR QUERY: cursor_140426255794688_c34804.execute(”’ { CALL SYS.DEBUG(‘[{

    “arguments”: {  },

    “seq”: 3,

    “command”: “init”,

    “type”: “request”

    }]’,?) } ”’) # con info [con-id 234804, APPLICATIONVERSION: 1.0.47.201212210450, APPLICATION: HDBStudio]

    the below JSON executes the query with breakpoints set in jmj\JMJPROC


    ## ERROR QUERY: cursor_140426255756288_c34804.execute(”’ { CALL SYS.DEBUG(‘[{

      “arguments”: { “breakpoints”: [

        {

    “handle”: 3,

    “location”: {

    “resourceIdentifier”: {

    “name”: “jmj\/JMJPROC“,

    “namespace”: “_SYS_BIC”

            },

    “line”: 3

          },

    “attributes”: {  },

    “language”: “sql”,

    “type”: “line”

        }

      ]  },

      “seq”: 4,

      “command”: “setBreakpoints”,

      “type”: “request”

    }]’,?) } ”’) # con info [con-id 234804, tx-id 1310,  user: schema:, APPLICATIONVERSION: 1.0.47.201212210450, APPLICATION: HDBStudio]

    Could this due to missing auth ?

    (0) 
    1. Rich Heilman Post author

      Currently there is a restriction with the debugger where you can only initiate a debug session for a procedure without input parameters.  So you you need to debug a procedure with input parameters, you must first create a wrapper procedure(without input parameters) which calls the procedure(the one with input parameters) and pass the input values to the CALL statement.  Then you can place a breakpoint in the wrapper procedure and initiate your debugger session from there.  Again, we are working to make this more straight forward in future SPs where you will be able to define the input parameters in the debug configuration, and hence debug the procedure with input parameters straight away.

      Cheers,

      Rich Heilman

      (0) 
      1. Peter Murphy

        Hi, I am also receiving “Terminated” message when debugging. I don’t have any input params:

        CREATE

        PROCEDURE TEST_1 ( )

        LANGUAGE SQLSCRIPT

        SQL SECURITY INVOKER

        READS SQL DATA AS


        BEGIN


        /*****************************

        Write your procedure logic

        *****************************/

        t_vbak = select count(*) from xxxxxxxx.VBAK;


        t_vbap = select count(*) from xxxxxxxx.VBAP;


        END

        ;

        <terminated>SQLScriptDebugConfig [SQLScript Procedure]

        <terminated>TEST_1.procedure

        Please advise,

        Thanks

        Peter

        (0) 
            1. Rich Heilman Post author

              Hi Peter. Yes, in fact you will need to add the authorization for DEBUG to your user profile. Go to your user id, and click SQL privileges tab.  Then add the DEBUG object to your profile. 

              Cheers,

              Rich

              (0) 
              1. Peter Murphy

                Thanks Rich, I’m all set now, I got the DEBUG privilage added to my profile and works fine now.

                Do you know if there are plans to be able to debug procedures with CE functions in an upcoming revision?

                (0) 
                1. Rich Heilman Post author

                  Yes, actually you can debug procedures with CE functions today, but there is a chance that in certain situations, it may not work.  This is why it is listed as a restriction in the note.  I will ask development about this.

                  Cheers,

                  Rich Heilman

                  (0) 
  2. Thomas Jung

    Which IDE – the Studio or the Web-based Development Workbench?  The Studio has had the ability to debug design time procedures since SPS 05. However the Web-based Development Workbench only gained that feature very recently with SPS 10. For the Web-based Development Workbench in SPS 09, you had to set the breakpoint via the catalog in the runtime object even for procedures created with hdbprocedure.

    (0) 
  3. Pavan Kumar Nayakanti

    Hi,

    Am a new bee in the SAP world.

    Somehow, am unable to understand to find how to associate the soruce code with the debugging session. I could not understand how should i be able to work with the break points while debugging. Please see the screenshot – hope this helps you to understand my problem statement. Thanks.Capture.PNG

    (0) 

Leave a Reply