User Defined Libraries (UDL) in SQLScript

Veröffentlicht am 19. November 2019 von

Jörg Brandeis

SAP HANA 2.0 SPS03 introduced the concept of User Defined Libraries (UDL) in SQLScript. It enables us as developers to bundle several functions, procedures and variables in one object. All components of a UDL are defined in one piece of source code and only one metadata object is created.

In some respects, a library is similar to a static class in ABAP, that is, a class that contains only class methods and variables. The visibility of individual components of a library can be controlled using the keywords PUBLIC and PRIVATE. The PRIVATE property specifies that this component can only be accessed within the same library. Using PUBLIC, on the other hand, you can allow free access from outside.

The lifetime of the data of a variable of a UDL is limited to one session. Public variables can also be accessed from outside the library.

Direct access to components

Within the same library, all components can be used directly. The variables of the library are also directly visible in all procedures and functions of the library. Outside the library, the component must be preceded by the name of the UDL:

<UDL>:<Component> 

Access with alias

Up to SAP HANA 2.0 SPS03, access from outside the library is only possible within a logical container. To do this, an alias must be created there for each library, which can then be used to access the components. Only one alias name is possible for each library. With SPS04, direct access was allowed, which also allows the use of components outside logical containers.

The example in the following listing shows the different ways of accessing the components of the UDL:

• within the same library
• outside the library with the library name
• in a different logical container with alias name

CREATE LIBRARY testlib
AS BEGIN
  PUBLIC VARIABLE cv_42 CONSTANT INTEGER DEFAULT 42;
  
  PUBLIC PROCEDURE proc1 (OUT ov_result INTEGER)
  AS BEGIN
    ov_result = :cv_42;
  END;
    
  PUBLIC PROCEDURE call_proc1(OUT ov_result INTEGER)
  AS BEGIN
   CALL proc1(ov_result=>ov_result);
  END;
END;

CALL testlib:call_proc1(?);
SELECT testlib:cv_42 FROM DUMMY;

DO BEGIN 
  USING testlib AS lib; --Definition des ALIAS  
  SELECT lib:cv_42 FROM DUMMY;
END;

Listing 1: Definintion of a UDL with a constant

In the next listing you will see how you can use these constants in a query elsewhere. A prerequisite for this is of course the declaration with the keyword PUBLIC.

SELECT * 
  FROM aufgaben
 WHERE id < const:cv_max
   AND id > const:cv_min;

Listing: Uage of the constant of the previous listing

Unfortunately, there are restrictions when using variables and constants outside the library, so it may be necessary to copy a variable from a library to a local variable first. This is the case, for example, if you want to use a value in the TOP clause of a query.

The other way around, direct assignment of a UDL variable with the SELECT ... INTO statement is not possible. Instead, a local variable must first be filled, which is then assigned to the variable from the UDL.

Bottom Line

Overall, concept of user-defined libraries does not yet seem 100 percent mature. Nevertheless, it already offers many useful features. It remains to be hoped that the next service pack will further round out the concept. An integration of UDL into the AMDP framework would also be desirable.