User Defined Libraries (UDL) in SQLScript
19. November 2019
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:
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.
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.