Skip to Content
Technical Articles
Author's profile photo Uwe Fetzer

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

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:

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

Assigned Tags

      8 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Mathias Kemeter
      Mathias Kemeter

      Really cool content! Cannot wait to see the Spatial and Graph parts coming up! πŸ‘

      Author's profile photo Uwe Fetzer
      Uwe Fetzer
      Blog Post Author

      Part II - Spatial functions

      Author's profile photo sandra green
      sandra green

      Really cool content!

      such a nice blog and so much information I got through your blog, thanks for sharing!!!

      Author's profile photo Jonathan Bourne
      Jonathan Bourne

      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

      Author's profile photo Uwe Fetzer
      Uwe Fetzer
      Blog Post Author

      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").

      Author's profile photo Alban Leong Bang
      Alban Leong Bang

      Love your series of tutorials and thanks for sharing!

      Author's profile photo kyo choi
      kyo choi

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

      Author's profile photo kyo choi
      kyo choi

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