In one of my previous blog posts “SAP HANA Express and ABAP Developer Edition: new BFFs 🙂”  I’ve described how to connect a HANA Express Edition as sidecar to the ABAP Developer Edition.

Now that we finally have a HANA based ABAP developer edition (see SAP ABAP Platform 1909, Developer Edition: AVAILABLE NOW) we don’t need this sidecar approach anymore and can work with the additional HANA functions from ABAP right on the underlaying database.

This is part 1 of a three part series:

• Part I: Document Store aka NoSQL database
• Part II: Spatial functions
• Part III: Graph functions

Document Store (Disclaimer)

Don’t use this approach with your companies S/4HANA installation before you have contacted your license department. See also “What type of SAP HANA (License) do you need ?” by Owen Pettiford

Preparation

For the preparations on the database we are using the HANA SQL Command line (see SAP HANA HDBSQL (Command-Line Reference))

But you could also use the HANA Tools in Eclipse or (better) DBeaver.

Assuming we are logged on as user a4hadm on the system (“docker exec -it a4h bash”)

Change to the DB user

>> su hdbadm

Enable Document Store on HANA

(must be processed on the system database)

Login in System DB
(hint: passwords of all users in the ABAP Developer Editions are initialy the same: “Ldtf5432”)

/usr/sap/HDB/HDB02/exe/hdbsql -i 2 -u SYSTEM -p PasswordOfUserSYSTEM -d SYSTEMDB

Check whether we are on the right tenant:

hdbsql SYSTEMDB=> \s
host : localhost:30213
sid : HDB
dbname : SYSTEMDB <--------------
user : SYSTEM
kernel version: 2.00.044.00.1571081837
SQLDBC version: libSQLDBCHDB 2.04.162.1568407618
autocommit : ON
locale : C
input encoding: UTF8
sql port : vhcala4hci:30213

Now activate the Document Store

hdbsql SYSTEMDB=> alter database hdb add 'docstore'

This will take a while (around 50secs on my computer)

Quit the CLI

hdbsql SYSTEMDB=> \q

Creating a new playground (schema) for us

Login in tenant database HDB

/usr/sap/HDB/HDB02/exe/hdbsql -i 2 -u SYSTEM -p PasswordOfUserSYSTEM

Check again that we are on the right tenant:

hdbsql HDB=> \s
host : localhost:30213
sid : HDB
dbname : HDB <--------------
user : SYSTEM
kernel version: 2.00.044.00.1571081837
SQLDBC version: libSQLDBCHDB 2.04.162.1568407618
autocommit : ON
locale : C
input encoding: UTF8
sql port : vhcala4hci:30215

Create new schema, where we can play around

hdbsql HDB=> create schema abap

Grand access rights for user SAPA4H, so we can do everything in this schema from ABAP

hdbsql HDB=> GRANT CREATE ANY ON SCHEMA abap TO sapa4h;
hdbsql HDB=> GRANT INSERT ON SCHEMA abap TO sapa4h;
hdbsql HDB=> GRANT SELECT ON SCHEMA abap TO sapa4h;

Now we are done on database level and we can quit the HANA SQL command line tool

hdbsql HDB=> \q

Test connection to the database

(this example should have worked already even without the document store enablement)

Open the ABAP Development Tools in Eclipse, login to system A4H and create a new report for our first test.

DATA results TYPE string_table.

TRY.
	DATA(sql) = NEW cl_sql_statement( ).
	DATA(statement) = |select 'Just a test' as test from dummy;|.

	DATA(result) = sql->execute_query( statement ).

	result->set_param_table( REF #( results ) ).
	result->next_package( ).

	cl_demo_output=>display( results ).

  CATCH cx_sql_exception INTO DATA(lcx).
	cl_demo_output=>display( lcx->get_text( ) ).

  CATCH cx_parameter_invalid INTO DATA(lcx_parameter).    "
	cl_demo_output=>display( lcx_parameter->get_text( ) ).

ENDTRY.

Execute with <F9>

We should see the following result in the ABAP console

Just a test

Test Document Store

Now let’s test the JSON collections in the SAP HANA document store (aka NoSQL database)

• create a JSON collection (method execute_ddl)
• insert some data (method execute_update)
• select the JSON strings (method execute_query)
• select just one value (method execute_query)
• delete the collection (method execute_ddl)

DATA results TYPE string_table.

TRY.
	DATA(sql) = NEW cl_sql_statement( ).

	DATA(statement) = |create collection abap.persons;|.
	sql->execute_ddl( statement ).

	"important here: use ` instead of ' at the beginning and the end of the string
	statement = `insert into abap.persons values( { firstname : 'Nyota', lastname : 'Uhura' } );`.
	sql->execute_update( statement ).
	statement = `insert into abap.persons values( { firstname : 'James', middlename : 'Tiberius', lastname : 'Kirk' } );`.
	sql->execute_update( statement ).

	statement = |select * from abap.persons;|.
	DATA(result) = sql->execute_query( statement ).
	result->set_param_table( REF #( results ) ).
	result->next_package( ).

	statement = |select firstname from abap.persons where lastname = 'Uhura';|.
	result = sql->execute_query( statement ).
	result->set_param_table( REF #( results ) ).
	result->next_package( ).

	statement = |drop collection abap.persons;|.
	sql->execute_ddl( statement ).

	INSERT |Collection deleted| INTO TABLE results.
	cl_demo_output=>display( results ).

  CATCH cx_sql_exception INTO DATA(lcx).
	cl_demo_output=>display( lcx->get_text( ) ).

  CATCH cx_parameter_invalid INTO DATA(lcx_parameter).    "
	cl_demo_output=>display( lcx_parameter->get_text( ) ).

ENDTRY.

You should get the following output in the ABAP console

{"FIRSTNAME": "Nyota", "LASTNAME": "Uhura"} 
{"FIRSTNAME": "James", "MIDDLENAME": "Tiberius", "LASTNAME": "Kirk"} 
Nyota 
Collection deleted

Looks like you now have the full control of the HANA database 🙂
(but: ‘With great power there must also come great responsibility“)

Have fun playing around with JSON Collections, Graph Database and Spatial data within ABAP.

Cheers, Uwe