New SQLScript Features in SAP HANA 1.0 SPS7

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.

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).  

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.

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.

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.

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.

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.