Enhancements to SQLScript Editor & Debugger in the SAP Web-Based Development Workbench

In SPS 10, the SQLScript editor and debugger in the SAP Web-based Development Workbench has been enhanced in several ways to help the developer be more productive.  We introduced the editor in SPS 9 with basic keyword hints, but in SPS 10, we’ve expanded this to include code snippets and semantic code completion very similar to what we introduced in the SAP HANA Studio in SPS 9.  Basically, if you want to construct a SELECT statement, you simply type the word SELECT and hit CTRL+SPACE.  You will then get a list of possible code snippets to choose from.

/wp-content/uploads/2015/06/1_735834.png

Select the snippet you wish to insert and hit ENTER, the code snippet is inserted into the procedure.  You can then adjust as needed.


/wp-content/uploads/2015/06/2_735835.png


Another feature that we’ve added to the SQLScript editor in the web-based development workbench is semantic code completion.  For example, if you need to call a procedure, you can simply type the word CALL and hit CTRL+SPACE, and you will get a drop down list of procedures. Simply double click on the object you want to insert.  This is context sensitive, so it works quite well in other statements as well.

/wp-content/uploads/2015/06/3_735863.png

With SPS 9, we introduced the ability to debug procedures within the web-based development workbench, but only from the catalog.  As of SPS 10, you can now debug design-time artifacts(.hdbprocedure files) as well.  You simply open the .hdbprocedure file and set your breakpoints.  You can then, right click and choose “Invoke Procedure” to run it from the SQL console.  The debugging pane is show and execution stops at your breakpoint.  You can then of course single step through the code and evaluate values.


/wp-content/uploads/2015/06/4_735865.png

Commit/Rollback


One of the many stored procedure language features that a developer expects in any database is the concept of COMMIT & ROLLBACK.  Up until now we did not support COMMIT/ROLLBACK in SQLScript.  As of SP10, we now support the use of COMMIT/ROLLBACK within procedures only, not for scalar or table User Defined Functions(UDFs). The COMMIT statement commits the current transaction and all changes before the COMMIT statement.  The ROLLBACK statement rolls back, the current transaction and undoes all changes since the last COMMIT. The transaction boundary is not tied to the procedure block, so if there are nested procedures that contain COMMIT/ROLLBACK then all statements in the top-level procedure are affected. For those who have used dynamic SQL in the past to get around the fact that we did not support COMMIT/ROLLBACK natively in SQLScript, we recommend that you replace all occurrences with the native statements because they are more secure.  For more information, please see the section on Commit & Rollback in the SQLScript Reference Guide.


Header Only Procedures/Functions


We’ve also introduced the concept of “Header Only” procedures/functions in SPS 10.  This is to address a problem when creating procedures/functions that are dependent on one another.  You can’t create the one procedure/function before the other. Basically this allows you to create procedures/functions with minimum metadata first using the HEADER ONLY extension.  You can then go back and inject the body of the procedure/function by using the ALTER PROCEDURE statement.  The CREATE PROCEDURE AS HEADER ONLY and ALTER PROCEDURE statements are only used in the SQL Console, not in design-time artifacts. Below is a sample of the basic syntax, for more information, please see the section on Procedure & Function Headers in the SQLScript Reference Guide.


CREATE PROCEDURE test_procedure_header( in im_var integer,

                                out ex_var integer ) as header only;

ALTER PROCEDURE test_procedure_header( in im_var integer,

                                out ex_var integer )

LANGUAGE SQLSCRIPT

SQL SECURITY INVOKER

READS SQL DATA AS

BEGIN

   ex_var = im_var;

END;

SQL Inlining Hints


The SQLScript compiler combines statements in order to optimize code.  SQL Inlining hints allows you to explicitly enforce or block the inlining of SQL statements within SQLScript.  Depending on the scenario, execution performance could be improved by either enforcing or blocking inlining. We can use the syntax, WITH HINT(NO_INLINE) or WITH HINT(INLINE).  For more information, please see the section on Hints: NO_INLINE & INLINE in the SQLScript Reference Guide.

Multiple Outputs from Scalar UDFs


In SPS 8, we released the ability to call scalar functions in an assignment statement. But there was a limitation which only allowed you to return one output parameter per call.  In SPS 10, you can now retrieve multiple output parameters from a single call.

The following function output_random_numbers has two return parameters called ex_rand1 and ex_rand2.

CREATE FUNCTION output_random_number( )

        RETURNS ex_rand1 integer,

                 ex_rand2 integer

    LANGUAGE SQLSCRIPT

    SQL SECURITY INVOKER AS

BEGIN

ex_rand1 = ROUND(TO_DECIMAL(1 + (999999-1)*RAND()),2);

ex_rand2 = ROUND(TO_DECIMAL(1 + (999999-1)*RAND()),2);

END;

In this procedure, we will call the function and retrieve both return parameters in one call.

CREATE PROCEDURE test_scalar_function(

          OUT ex_x integer, OUT ex_y integer)

  LANGUAGE SQLSCRIPT

  READS SQL DATA AS

BEGIN

    (ex_x,ex_y) = output_random_number( );

END;

You can also, retrieve both values separately with two different calls, referencing the name of the return parameter.

CREATE PROCEDURE test_scalar_function(

         OUT ex_x integer, OUT ex_y integer)

  LANGUAGE SQLSCRIPT

  READS SQL DATA AS

BEGIN

    ex_x = output_random_number( ).ex_rand1;

    ex_y = output_random_number( ).ex_rand2;

END;

Table Type for Table Variable Declarations


In SPS 9, we introduced the ability to declare a table variable using the DECLARE statement.  At that point, you could only define the structure explicitly inline, and could not reference a table type from the catalog or from the repository. In SPS 10, you can now do so.  In the below example, LT_TAB is declared referencing a table type in a CDS(.hdbdd) file. 


CREATE PROCEDURE get_products( )

    LANGUAGE SQLSCRIPT

    SQL SECURITY INVOKER

    DEFAULT SCHEMA SAP_HANA_EPM_NEXT

    READS SQL DATA AS

BEGIN

declare lt_tab “sap.hana.democontent.epmNext.data::MD.Products”;

lt_tab = select * from “sap.hana.democontent.epmNext.data::MD.Products”;

select * from :lt_tab;

END;

Anonymous Blocks


Finally,  the last feature I would like to introduce is the concept of Anonymous Blocks.  This allows the developer to quickly write and execute SQLScript code in the SQL Console without having to create a stored procedure.  This is very useful for trying out small chucks of code during development.  You can execute DML statements which contain imperative and declarative statements. Again there is no lifecycle handling(CREATE/DROP statements), and no catalog object.  You can also not use any parameters or container specific properties such as language, or security mode.  The syntax is very simple, you basically use the word DO, followed by a BEGIN/END block.  Then you simply put your SQLScript code in the BEGIN/END block and execute it.  For more information, please see the section on Anonymous Blocks in the SQLScript Reference Guide.


/wp-content/uploads/2015/06/5_735864.png

To report this post you need to login first.

18 Comments

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

  1. Shilpik Balpande

    This is really helpful.

    Specially the Anonymous block will reduce  lot of extra effort when deploying scripts using variables.

    Thanks and cheers,

    Shilpik

    (0) 
  2. Krishna Tangudu

    Hi Rich Heilman

    Is the .ini parameter under SQL, sql_granulize_enabled is the one which will make the optimization guideline in SQLScript that dependent statements are combined if possible?

    if not can you help me understand which parameter makes this setting as default?

    Regards,

    Krishna Tangudu

    (0) 
    1. Lars Breddemann

      HI Krishna

      as far as I can tell, the parameter

      “Enables pushdown of rounding functions from SQL queries into the OLAP engine.”

      This is apparently used for e.g. the rounding function of the series-handling commands.

      The parameter is set to TRUE in current revisions. No need to do that manually.

      It does not have anything to do with how you code SQLScript or how that is processed.

      Cheers,

      Lars

      (0) 
      1. Krishna Tangudu

        Hi Lars Breddemann

        Thanks for your time in replying to this.

        After SP9 , Upgrade I saw there is a change in how the Table Variables are processed in Calculation View ( Script ).

        Example:

        If I have below 2 Table Variables in my CV:

        Tab = select * from TableA;

        Tab2 = select * from :Tab where A = ‘2’

        I see that it is doing below:

        Tab2 = select * from (select * tableA) where A = ‘2’

        I understand from SP10, we have HINTS like INLINE or NO_INLINE as mentioned hereNew SQLScript Features in SAP HANA 1.0 SPS 10 under “SQL Inlining Hints”

        in the blog.

        Want to know if there is any such system parameter or anyother work around which can help to achieve NO_INLINE functionality in SP9?

        Regards,

        Krishna Tangudu

        (0) 
          1. Krishna Tangudu

            Hi Lars,

            Some of the CV’s which were written currently have many LOJ’s in the code on the Table Variable and after upgrading to SP9, these CV’s are taking longer time than before and the query which is being executed in M_ACTIVE_STATEMENTS while running the CV shows it is trying to keep all the variables INLINE like Tab2 = select * from (select * from tableA) where A= ‘2’.

            So wanted to check if there is any parameter at system level which will help to proceed in the same way as it was before upgrading which will help to solve the performance issues meanwhile while we will get sometime to re-write the code properly.

            Regards,

            Krishna Tangudu

            (0) 
            1. Lars Breddemann

              I see – look, for this kind of problem (a performance degradation after a SAP HANA software update), I highly recommend to open a support incident.

              Using hints for production code is a maintenance problem and shouldn’t be used to fix something going wrong.

              (0) 

Leave a Reply