Technology Blogs by Members
Explore a vibrant mix of technical expertise, industry insights, and tech buzz in member blogs covering SAP products, technology, and events. Get in the mix!
cancel
Showing results for 
Search instead for 
Did you mean: 
UweFetzer_se38
Active Contributor
3,391
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.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
7 Comments
Labels in this area