Using SQLScript Libraries

A library is a group of variables, procedures, and functions that are related. SAP HANA has some built-in libraries, but you can also create custom libraries.

The members of the library can be re-used in other procedures or functions.

The library members functions and procedures can be defined as PRIVATE or PUBLIC, by defining their access mode. There may be some specialist library members that only makes sense to be used with some projects, and these can be defined as private. These will not be available for other procedures or functions. Generally useful functions and procedures can be defined as public for any users who have the EXECUTE permission on the library.

Types of library

• User defined libraries
• Built-in libraries

Library members

• Variables
• Functions
• Procedures

For functions and procedures

• Private functions and procedures
  • For internal use within the library
  • Cannot be called from outside the library
• Public functions and procedures
  • Can be used / called from outside the library like a non-library function or procedure
  • Need the EXECUTE privilege on the library

Views to display all libraries and members

• (SYS schema)
• LIBRARIES
• LIBRARY_MEMBERS

Although libraries and their members can be defined directly in the SQL Console using the CREATE LIBRARY statement, this is not recommended for development projects. Source artifacts should be defined instead using the Development view of SAP Web IDE.

The source file type is .hdblibrary. There is no dedicated menu option for this artifact as there is for procedures and functions. You should use menu option New → File and be sure to specify the file extension after the file name.

You can use supplied system views to list all possible libraries and their members they contain once they are built:

• SYS.LIBRARIES

• SYS.LIBRARY_MEMBERS

Each member in a library does not have its own individual metadata object. Only the entire library is a metadata object and all members in the library are considered part of that single metadata object. There is very often a dependency on the individual members of a library. If one member becomes invalid, it might have an effect on the other members in the library object.

Create a library and some members

Code Snippet

Copy codeSwitch to dark mode

123456789101112
create library mylib as begin 

	public variable maxval constant int = 100; 

	public function bound_with_maxval(i int) returns x int as begin 
		x = case when :i > :maxval then :maxval else :I end; 
	end; 

	public procedure get_data2(in size int, out result table(col1 int)) as begin 
		result = select top :size col1 from data_table; 
	end; 
end; 

Use system view LIBRARY_MEMBERS to list available members

Because library members do not have their own metadata object, it is possible to have library members that share the same name. SAP recommends that you always use fully qualified names when defining the library members.

To reference a library and its contents in SQLScript, you must first identify the library, then you may access an item held in the library. Libraries are identified with the USING <library_name>... AS <reference_name> command. Items in a library are accessed using the <reference_name>:<item_name> naming scheme.

1234567891011121314
create procedure myproc (in inval int) as begin 

	using mylib as mylib; 
	
	declare var1 int = mylib:bound_with_maxval(:inval); 

	if :var1 > mylib:maxval then 
		select 'unexpected' from dummy; 
	else 
		declare tv table (col1 int); 
		call mylib:get_data2(:var1, tv); 
		select count(*) from :tv; 
	end if; 
end;

LIbraries are deleted with the DROP LIBRARY statement if you have created this directly with the SQL Console using the CREATE LIBRARY statement. If you have created this library using the source file .hdblibrary, then delete the source file and rebuild the parent folder to remove the runtime object.

SAP HANA comes with a few built-in libraries for handling a few of the special functions that can be handled with efficient performance.

• SQLSCRIPT_SYNC: Offers functions for sleeping and waking up with performance efficiency
• SQLSCRIPT_STRING: Offers simple ways for manipulating strings
• SQLSCRIPT_PRINT: Makes it possible to print strings or even whole tables

For example, you might want to let certain processes wait for a while. By different user’s design, implementing such waiting manually may lead to "busy waiting" and to the CPU performing unnecessary work during this waiting time. To avoid this, SQLScript offers a built-in library SYS.SQLSCRIPT_SYNC containing the procedures SLEEP_SECONDS and WAKEUP_CONNECTION.

Similar to this library, there are libraries for manipulating strings (SYS.SQLSCRIPT_STRING) and for some special PRINT functions including table printing (SYS.SQLSCRIPT_PRINT).

Beware of the current limitation when using libraries:

• The usage of library variables is currently limited. For example, it is not possible to use library variables in the INTO clause of a SELECT INTO statement or in the INTO clause of dynamic SQL. This limitation can be easily circumvented by using a normal scalar variable as an intermediate value.

• It is not possible to call library procedures with hints.

• Since session variables are used for library variables, it is possible (provided you have the necessary privileges) to read and modify arbitrary library variables of (other) sessions.

• Variables cannot be declared by using LIKE for specifying the type.

• Non-constant variables cannot have a default value yet.

• Table type variables are not supported in libraries.

• A library member function cannot be used in queries.