There are several new important features in HANA 1.0 SPS7 with regard to SQLScript.  Over the last several support packages, we’ve seen the SQLScript tooling change quite a bit, and we will continue to enhance the editors, debuggers, and SQLScript language in future support packages as well.

New .hdbprocedure File Format


In SPS7, we introduce a new file format associated with design-time artifacts for procedures. Previously, the .procedure file format was used which was XML based, and is compatible with the procedure editor in the Modeler.  As of SPS7, we will also support the new file format called .hdbprocedure which is DDL based, or plain text based.  This will be the recommended file format to use moving forward.   Before we can completely deprecate the .procedure file format, we must first support procedure templates, and the R language when using the .hdbprocedure file format.  In the interim, we will support both file formats together in the “Stored Procedure” creation wizard.

/wp-content/uploads/2013/12/1_331259.png

/wp-content/uploads/2013/12/2_331270.png

You may notice some differences between the two file formats.  For example, the CREATE keyword has been removed from this syntax.  The procedure definition simply starts with the word PROCEDURE.  You will notice the ability to define the specific schema in which the runtime version is to be created, directly in-line with the name of the procedure.  The entire package hierarchy is also shown here. Also, the separator between the package hierarchy and the procedure name has changed from slash(/) to double colon(::). Finally, you will notice the “Local Table Types” tab is not present. In the .procedure editor, the local table types tab is used to define a table type which can then be referenced by the input/output parameters of the procedure.  As these generated table types were not really local anyway, we felt it better to not support it in the new format and instead recommend to create global table types via  CDS(.hdbdd files).  

/wp-content/uploads/2013/12/3_331265.png

See a demo video here.


User Defined Functions(UDF) Repository Artifacts


Another new feature in SPS7 is the support for scalar and table user defined functions(UDFs) as repository artifacts.  Previously, it was only possible to create these functions in the catalog via the SQLConsole.   You can now create the .hdbscalarfunction and .hdbtablefunction aritifacts via the associated wizards in the same way that we create procedures.


/wp-content/uploads/2013/12/4_331266.png


During activation, the run-time object is then created in the catalog.  This is an important feature as we can now leverage functions in our applications and deliver the code via delivery units.  Currently, the function editors are only text editors without any client side checking or formatting.  Of course, we plan to add code highlighting, and syntax checking in a future support package.

/wp-content/uploads/2013/12/5_331267.png

See a demo video here.

Debugging Catalog Procedures


Another new feature is the ability to debug catalog procedures. Previously if you wanted to debug a procedure you had to check out the project and have a local copy of the procedure.  This meant that if your procedures were created directly in the catalog with a CREATE PROCEDURE statement via the SQLConsole, you were not able to debug those procedures.  As of SPS7, you can now choose to debug procedures in the repository or ones in the catalog.  It does not matter how the catalog procedure was created, whether it was created during activation of a repository artifact (.hdbprocedure / .procedure ) or if it was created via CREATE PROCEDURE, the process of debugging the catalog procedure is the same.  In the debug configuration screen, there is a new drop down list box where you can choose between the two options.  Based on that list box, you can browse either the catalog or your local projects for the procedure in which you want to debug.  This is definitely an important feature because it allows you to debug procedures created directly in the catalog which have no design-time representation.  Also, it allows you to debug procedures without having to check out the project beforehand.

/wp-content/uploads/2013/12/6_331268.png

See a demo video here.

Debugging Authorization Concept


Finally, in SPS7 we have introduced a new authorization concept around debugging SQLScript procedures. Previously, you were only required to have the SYS.DEBUG authorization object, which allowed you to debug any procedures within the system, across all schemas.  In SPS7, we have modified the authorizations to be more restrictive in this regard.   The SYS.DEBUG is no longer required, and you will now need authorization for DEBUG on the specific procedure object itself, or DEBUG authorization at the schema level.  Procedures created directly in the catalog via the SQLConsole are owned by the user creating them, so no additional debug authorization is required for that user to also debug the procedure.  Procedures created in the repository are owned by _SYS_REPO and therefore the user must grant debug authorization to himself.   The following new privileges support this authorization concept.

grant DEBUG on <procedure> to <user>

grant DEBUG on schema <schema> to <user>


In most systems, users will not have authorization to grant these privileges directly using the GRANT statements, but can still do so by executing the repository wrapper procedures listed below.

Call _SYS_REPO.GRANT_PRIVILEGE_ON_ACTIVATED_CONTENT(

          ‘DEBUG’,'<procedure_name>’,'<username>’);

Call _SYS_REPO.GRANT_SCHEMA_PRIVILEGE_ON_ACTIVATED_CONTENT(

          ‘DEBUG’,'<schema_name>’,'<username>’);


Of course it is recommended to grant these authorizations via a repository role(.hdbrole), in which the code might look something similar to this.  


role <my_package>::<role_name> {

— Grant EXECUTE and DEBUG at Schema Level

  schema <my_package>:<schema_name>.hdbschema: DEBUG, EXECUTE;  

— Grant EXECUTE and DEBUG at Procedure Level

  sql object <my_package>:<procedure_name>.hdbprocedure: DEBUG, EXECUTE;

}


Additionally, in order to debug an external session, the session’s owner must grant “ATTACH DEBUGGER” authorization to the user who is debugging.  The authorization for granting “ATTACH DEBUGGER” to a user cannot be done via a role and must be done in user management on via the SQLConsole using the following statement.

grant ATTACH DEBUGGER to <user>


For example, you want to debug from ABAP directly into your SQLScript procedure.  ABAP connects to the database via the SAP<SID> user id. So the SAP<SID> user must grant the “ATTACH DEBUGGER” privilege to the user whom wants to debug the procedure, in this case SYSTEM user id.  As shown below, I have logged on as the SAP<SID> user, and I have modified the SYSTEM user id, adding the authorization for SYSTEM to attach to a session of SAP<SID>.  The SAP<SID> user id would need DEBUG authorization for the schema or procedure as well.

/wp-content/uploads/2013/12/7_331269.png

For more information about the new debugging authorization concepts, please refer to SAP Note 1942471.  For more information about all the HANA native development topics, check out the HANA Developer Guide.


To report this post you need to login first.

16 Comments

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

  1. Jody Hesch

    Thanks, Rich!

    Any new/updated features, not tooling-related, specific to SQL or SQLScript (such as the oft-mentioned concatenation(aggregation) function)? 🙂

    (0) 
  2. varada santosh

    Hi Rich

        Thanks for sharing the information on new features, is there any possibility to debug scripted & CE based calculation views also along with SPS7 or it is expected in next version.

    Thanks

    Santosh Varada

    (0) 
    1. Rich Heilman Post author

      Yes, you should be able to debug scripted calculation views with no problem since a script calculation view generates a procedure underneath anyway.  You would simply debug this generated procedure which resides in _SYS_BIC schema.

      Cheers,

      Rich Heilman

      (0) 
  3. Phoebe Zhang

    hi Rich,

    thanks for your sharing. I have a question here:

    how can we grant privilege on table function  to a role?

    I can grant privilege on a scalar function but cannot do the same thing on the table function.

    E.g:

    Here is role definition:

    role APP.function.roles::User {

      catalog schema “SYSTEM” : SELECT;

      catalog sql object  “SYSTEM”.”APP.function.functions::get_employees_by_name_filter” : EXECUTE;

      }

    funtion : get_employees_by_name_filter is a hdbtablefunction. Its definition is :

    FUNCTION “SYSTEM”.”APP.function.functions::get_employees_by_name_filter” (RETURNSTR NVARCHAR(40))

      RETURNS table ( EMPLOYEEID NVARCHAR(10))

      LANGUAGE SQLSCRIPT

      SQL SECURITY INVOKER AS

    BEGIN

    RETURN

      SELECT ‘HELLO’ AS EMPLOYEEID FROM DUMMY;

    END;

    when I activating this role, I get following error:

    function.PNG

    Could you help to have a look at this? thanks in advance.

    regards,

    Phoebe

    (0) 
    1. Siva Kumar

      Hi,

      I am facing issue in editing .HDB Procedure in dev ,Kindly help me on this ,I am getting error you can create read only procedure on this server

      /wp-content/uploads/2015/05/sdn_1_697103.png

      /wp-content/uploads/2015/05/sdn_2_697106.png

      Regards,

      Siva

      (0) 
  4. Hagen Dittmer

    Hiya Rich,

    just wondering if you have any information as to when R will be supported in .hdbprocedure files? I’m keen to start writing some inline R.

    Thanks in advance,

    Hagen

    (0) 
    1. Rich Heilman Post author

      This has been reported to development, and I”m told that the fix is in a pre-SP10 branch, so I am hoping it will be there for SP10 release at the end of June.

      Cheers,

      Rich

      (0) 
  5. Valery Anusevich

    Hi Rich,

    In Modeler mode – file format old .procedure

    In Development mode – new .hdbprocedure


    which one is the right way for create procedures in HANA – in Modeler Mode or in Development mode?

    (0) 
    1. Rich Heilman Post author

      You can actually create a .procedure file in the development perspective as well.  But to answer your question, the way forward is to create a .hdbprocedure file, .procedure is being deprecated.

      Cheers,

      Rich

      (0) 

Leave a Reply