Using multi model capabilities of SAP HANA in the new ABAP 1909 Developer Edition - Part I - Document Store

[UweFetzer_se38]

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

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

[mkemeter]

Really cool content! Cannot wait to see the Spatial and Graph parts coming up! 👍

[UweFetzer_se38]

Part II - Spatial functions

[former_member185932]

Hi Uwe,

Great blog, thank you. Using hdbsql I have connected to database SYSTEMDB to activate the document store on database HDB, however, I cannot connect to database HDB to create the schema using the SYSTEM user and default password. The hdbsql command gives a timeout. Do you have any ideas?

I have tried changing the system user password on database HDB using the following command but I still cannot connect to HDB.

ALTER SYSTEM STOP DATABASE HDB

ALTER DATABASE HDB SYSTEM USER PASSWORD <new_password>

Regards,
Jonathan

[UweFetzer_se38]

Hi Jonathan,

I would recomment to start all over again. Remove the container with "sudo docker rm a4a" and run the "RUN" command again. It should work exactly like I've described here. Did this multiple times while writing the blog post (initial SYSTEM password is "Ldtf5432").

[albanleong]

Love your series of tutorials and thanks for sharing!

[kyo_choi2]

Would you let us know how to fill the System parameters in ADT?

[kyo_choi2]

Nevermind, I used the 02 as instance number with System user.  PW is Ldtf5432