- SAP Managed Tags
In various previous blogs, I described how to access ABAP CDS view entities that are exposed by an SQL service in SQL queries using the ODBC driver for ABAP. (If you are not familiar with this topic, I recommend that you read the blog post Consuming CDS View Entities Using ODBC-Based Client Tools | SAP Blogs first). It’s now also possible to expose and use ABAP-managed database procedures (AMDPs) using an SQL service. Compared to SQL queries, procedures support more types of input and output variables and allow to encapsulate more business logic in the procedure body.
In this blog post, I will show how such AMDPs need to be defined and exposed and will also describe how to use these AMDPs in the SQL dialect of the SQL service.
Prerequisites
ABAP release 2502 (Cloud) or the next corresponding onPrem version
ODBC driver for ABAP patch level 18 or higher
Defining and Implementing the AMDPs for the SQL Service as Class Methods
AMDPs that should be exposed as procedures in an SQL service need to be implemented as class methods. In addition, there are some restrictions for these class methods.
The class methods must be defined with clause “FOR SQL SERVICE” and you must specify AMDP options after this clause The allowed AMDP options are “AMDP OPTIONS SESSION CLIENT DEPENDENT READ-ONLY” or “AMDP OPTIONS CLIENT INDEPENDENT READ-ONLY”
Only IMPORTING and EXPORTING are allowed. In SQL, those will map to INPUT and OUTPUT parameters of the database procedures.
So, the complete CLASS METHODS block will look like:
CLASS-METHODS test_amdp_proc FOR SQL SERVICE AMDP OPTIONS ... IMPORTING .. EXPORTING ...Since the class methods define AMDPs, the implementation must have the clause “BY DATABASE PROCEDURE FOR HDB LANGUAGE SQL SCRIPT”, as in the following example:
METHOD test_amdp_proc BY DATABASE PROCEDURE FOR HDB LANGUAGE SQLSCRIPT.All parameters must be built from plain DDIC types.
The AMDPs can have scalar and table input and output parameters. If table input or output parameters are used, they must be standard tables without index.
Currently no optional input parameters are allowed because default values these parameters are not supported.
The accessed database objects in the AMDP code must be CDS view entities.
Since CDS view entities are currently read-only, the AMDP code itself will be read-only.
Let’s now discuss a simple AMDP that can be exposed in an SQL service. The test procedure will contain all supported parameter types and will be able to return multiple result sets.
Test Objects and Data for the AMDP Example
In a previous blog post, I defined and created the test CDS view entities ZORDERS, ZORDERSVIEW, and ZORDERITEMSVIEW filled them with some test data. Later these CDS view entities were exposed in a service definition as view ORDERS and ORDERITEMS. To avoid repetition, I will reuse these test objects in my AMDP sample. You can find the definitions in my previous blog post if you want to reproduce my test AMDP example shown here.
The contents of the exposed CDS view entities is as follows:
SQL> select * from ZORDERS.ORDERITEMS
+-----------+------------+---------------------+------------+
| OrderId | Pos | ITEM | Amount |
+-----------+------------+---------------------+------------+
| 0000000001| 1 | Apple | 5 |
| 0000000001| 2 | Banana | 5 |
| 0000000001| 3 | Orange Juice | 2 |
| 0000000002| 1 | Orange | 10 |
| 0000000002| 2 | Apple | 5 |
| 0000000003| 1 | Bottle Water | 5 |
+-----------+------------+---------------------+------------+
SQL> select * from ZORDERS.ORDERS
+-----------+-------------+
| Id | CreationDate|
+-----------+-------------+
| 0000000001| 20210801 |
| 0000000002| 20210802 |
| 0000000003| 20210803 |
+-----------+-------------+
The test procedure will have one INPUT parameter and two OUTPUT parameters of type table. To create the test procedure, I must first define structures and table types for all these parameters:
@EndUserText.label : 'Structure for tabular IN parameter of test procedure'
@AbapCatalog.enhancement.category : #NOT_EXTENSIBLE
define structure zfmh_items_in_struct { item : abap.char(100);
}
@EndUserText.label : 'Structure for first tabular OUT parameter of test procedure'
@AbapCatalog.enhancement.category : #NOT_EXTENSIBLE
define structure zfmh_id_struct {
id : abap.numc(10);
creation_date : abap.datn;
}
@EndUserText.label : 'Structure for second tabular OUT parameter of test procedure'
@AbapCatalog.enhancement.category : #NOT_EXTENSIBLE
define structure zfmh_items_struct {
pos : abap.int4;
item : abap.char(100);
amount : abap.int4;
}
Please note that all structures must be based on plain DDIC types, and all table types must be defined with empty key definition.
Using the table types and structures above, I can now define my test AMDP procedure in ABAP class ZFMHORDERS_PROCEDURE:
class ZFMHORDERS_PROCEDURE definition
public
final
create public .
public section.
interfaces if_amdp_marker_hdb.
"! <p class="shorttext synchronized">test AMDP with all param types</p>
"! @parameter min_creation_date | <p class="shorttext synchronized">scalar IN param</p>
"! @parameter items_tab_in | <p class="shorttext synchronized">table IN param</p>
"! @parameter num_items_to_return | <p class="shorttext synchronized">scalar OUT param</p>
"! @parameter id_tab_out | <p class="shorttext synchronized">1st table OUT param</p>
"! @parameter items_tab_out | <p class="shorttext synchronized">2nd table OUT param</p>
class-methods test_amdp_proc for sql service
amdp options cds session client dependent read-only
importing value(min_creation_date) type DATN
value(items_tab_in) type ZFMH_ITEMS_IN_TAB_TYPE
exporting value(num_items_to_return) type INT4
value(id_tab_out) type ZFMH_ID_TAB_TYPE
value(items_tab_out) type ZFMH_ITEMS_TAB_TYPE .
protected section.
private section.
ENDCLASS.
CLASS ZFMHORDERS_PROCEDURE IMPLEMENTATION.
method test_amdp_proc by database procedure for hdb language sqlscript
using ZORDERSVIEW ZORDERITEMSVIEW .
id_tab_out = select id as id, creationdate as creation_date
from ZORDERSVIEW
where creationdate >= :min_creation_date;
items_tab_out = select pos, item, amount from ZORDERITEMSVIEW I
inner join ZORDERSVIEW O on O.ID = I.ORDERID
where O.creationdate >= :min_creation_date
and I.item in ( select * from :items_tab_in );
select count(*) into num_items_to_return from :items_tab_out;
endmethod.
ENDCLASS.Exposing the AMDP in a Service Definition/Binding
In my previous blog post, I used a service definition, which I will now extend so that it also exposes the new AMDP procedure:
@EndUserText.label: 'SERVICE DEF'
define service ZORDERS provider contracts sql {
expose ZORDERSVIEW as Orders;
expose ZORDERITEMSVIEW as OrderItems;
expose method ZFMHORDERS_PROCEDURE=>test_amdp_proc as TestProcedure;
}Note that I must define the service with the clause “PROVIDER CONTRACTS SQL” because AMDPs can only be used in service bindings of type “SQL -Web API”.
In addition to the service definition, I now also change the service binding that I have used before. In addition to SELECT, I now also enabled operation “EXECUTE” :
Authorization Objects for Technical User Access
Now let’s take care of the authorizations for the SQL service. As described in one of my previous blog posts, a technical user needs authorization object S_SQL_VIEW with, for example, field settings ( “SQL_SCHEMA -> ZORDERS” ; “SQL_VIEW -> *” ; “SQL_VIEWOP -> SELECT” ) to run SQL queries on exposed CDS view entities.
To execute exposed procedures, the user needs the additional authorization object S_SQL_PROC with, for example, field settings ( “SQL_SCHEMA -> ZORDERS” ; “SQL_PROC -> *” ; “SQL_PROCOP -> EXECUTE” ).
System Metadata Tables for Procedures
Three new system tables (SYS.PROCEDURES, SYS.PROCEDURE_PARAMETERS and SYS.PROCEDURE_PARAMETER_COLUMNS) are now available that can be used to query the metadata of exposed procedures. SYS.PROCEDURES return the schema, the procedure name, and the short description from the ABAP class. SYS.PROCEDURE_PARAMETERS provides type information for all procedure parameters where all tabular parameters are shown as type ‘TTYP’. SYS.PROCEDURE_PARAMETER_COLUMNS shows the column types for all procedure table parameters.
After activating the new service definition and service binding, I can query the new system tables for the new test procedure:
SQL> select PROCEDURE_NAME, DESCRIPTION from SYS.PROCEDURES
WHERE SCHEMA_NAME = 'ZORDERS'
+-------------------------+---------------------------------+
| PROCEDURE_NAME | DESCRIPTION |
+-------------------------+---------------------------------+
| TestProcedure | test AMDP with all param types |
+-------------------------+---------------------------------+
SQL> select PARAMETER_NAME_UPPER,
PARAMETER_TYPE AS TYPE,
ODBC_TYPE_NAME AS TYPE_NAME,
ODBC_COLUMN_SIZE AS SIZE,
IS_TABLE, DESCRIPTION
from SYS.PROCEDURE_PARAMETERS
WHERE SCHEMA_NAME = 'ZORDERS'AND PROCEDURE_NAME = 'TestProcedure'
+-----------------------+-----+----------+-----+---------+------------------------+
| PARAMETER_NAME_UPPER | TYPE| TYPE_NAME| SIZE| IS_TABLE| DESCRIPTION |
+-----------------------+-----+----------+-----+---------+------------------------+
| MIN_CREATION_DATE | IN | DATN | 10 | FALSE | scalar IN param |
| NUM_ITEMS_TO_RETURN | OUT | INT4 | 10 | FALSE | scalar OUT param |
| ID_TAB_OUT | OUT | TTYP | | TRUE | 1st table OUT param |
| ITEMS_TAB_OUT | OUT | TTYP | | TRUE | 2nd table OUT param |
| ITEMS_TAB_IN | IN | TTYP | | TRUE | table IN param |
+-----------------------+-----+----------+-----+---------+------------------------+
SQL> select PARAMETER_NAME_UPPER,
COLUMN_POSITION AS POSITION,
ODBC_TYPE_NAME AS TYPE_NAME,
ODBC_COLUMN_SIZE AS SIZE,
DESCRIPTION
from SYS.PROCEDURE_PARAMETER_COLUMNS
WHERE SCHEMA_NAME = 'ZORDERS'
AND PROCEDURE_NAME = 'TestProcedure'
ORDER BY PARAMETER_NAME_UPPER, COLUMN_POSITION
+-----------------------+---------+----------+-----+--------------------------------+
| PARAMETER_NAME_UPPER | POSITION| TYPE_NAME| SIZE| DESCRIPTION |
+-----------------------+---------+----------+-----+--------------------------------+
| ID_TAB_OUT | 1 | NUMC | 10 | 1st table OUT param |
| ID_TAB_OUT | 2 | DATN | 10 | 1st table OUT param |
| ITEMS_TAB_IN | 1 | CHAR | 100 | table IN param |
| ITEMS_TAB_OUT | 1 | INT4 | 10 | 2nd table OUT param |
| ITEMS_TAB_OUT | 2 | CHAR | 100 | 2nd table OUT param |
| ITEMS_TAB_OUT | 3 | INT4 | 10 | 2nd table OUT param |
+-----------------------+---------+----------+-----+--------------------------------+
Local Temporary Tables for Table Type Input Parameters
Before I can finally discuss the procedure call itself, I must introduce the concept of local temporary tables in the SQL dialect of the SQL service. Since the ODBC driver for ABAP talks to the ABAP system like a database, it’s natural that this database has some kind of local temporary tables (LTTs). In the ABAP backend, these tables are ABAP tables bound to the ABAP session opened by the ODBC driver connection. In the SQL dialect of the SQL service, they can be created with a CREATE LOCAL TEMPORARY TABLE statement, filled with an INSERT statement, and later used by name for the of type table.
The CREATE LOCAL TEMPORARY TABLE DDL statement has the following form:
CREATE LOCAL TEMPORARY TABLE
SYS_TEMP.<name>
( <colname> <type> NOT NULL, … )
ON COMMIT PRESERVE ROWSAll LTTs are created in schema SYS_TEMP. Currently, NULL values in LTTs are not allowed . Therefore, all columns must have the NOT NULL clause. Since currently the ODBC driver for ABAP works in auto commit mode the clause ON COMMIT PRESERVE ROWS signals that the LTTs and their content survive auto commits.
To fill an LTT, a simple INSERT statement is needed.
INSERT INTO SYS_TEMP.<name> values ( ?, … )In this INSERT statement, only parameter markers are allowed, and no explicit column list is supplied, and the number of parameter markers needs to match the number of columns in the LTT definition. Currently no literal values are allowed.
If the LTT is filled with a larger number of rows, ODBC array insert should be used to minimize network roundtrips between the ODBC driver and the ABAP backend.
The LTT (ABAP table) will exist in the ABAP session opened by the ODBC connection until the LTT is explicitly dropped to free up resources in the ABAP backend or until the ODBC connection is closed.
DROP TABLE SYS_TEMP.<name>If you use LTTs in your ODBC application, you should not configure your ODBC DSN to use Connection Pooling . Otherwise connections with existing LTTs may get pooled and the LTT content may be visible to other applications that reactivate the pooled connection.
Our test procedure above has one input parameter of type table with a table with one CHAR(100) column. The corresponding CREATE LOCAL TEMPORARY TABLE DDL and INSERT statement could look as follows:
CREATE LOCAL TEMPORARY TABLE SYS_TEMP.ITEMS_IN
( ITEM CHAR(100) NOT NULL )
ON COMMIT PRESERVE ROWS
INSERT INTO SYS_TEMP.ITEMS_IN VALUES( ? )The CALL Procedure Statement
Like the SQL of other databases, the SQL dialect for the SQL service supports a CALL statement to execute procedures. The CALL statement syntax has the following characteristics:
To allow flexible extensibility of existing procedures, only the named parameter syntax is supported. The general form of the call statement is:
CALL <schema>.<procedure>( param_a => <value_a> , param_b => <value_b> , ... )
where the order of parameters is not defined. The procedure name is the exposed name from the service definition. It is interpreted like exposed view names in SELECT statements. This means that, if the procedure name is double-quoted, the case must match.
The parameter names in the CALL statement should not be double-quoted.
All input parameters are mandatory because there are no default values for input parameters.
All output parameters are optional.
For scalar input parameters, simple literals or parameter markers (‘?’) can be supplied as values.
For scalar output parameters, only parameter markers (‘?’) can used as values.
For table type input parameters, the value must be the name of an existing LTT.
For table type output parameters, currently only the word RESULT is allowed as value.
This key word signals that the output table will be returned as a result set.
The result sets will be returned in the order provided in the CALL statement.
Applied to my test procedure, for example, the following CALL statements are valid after LTT SYS_TEMP.ITEMS_IN has been defined:
CALL ZORDER.TESTPROCEDURE( MIN_CREATION_DATE => ? ,
NUM_ITEMS_TO_RETURN => ? ,
ITEMS_TAB_IN => SYS_TEMP.ITEMS_IN ,
ID_TAB_OUT => RESULT ,
ITEMS_TAB_OUT => RESULT )
-- The statement above returns two result sets.
CALL ZORDER.TESTPROCEDURE( ITEMS_TAB_OUT => RESULT ,
ITEMS_TAB_IN => SYS_TEMP.ITEMS_IN ,
MIN_CREATION_DATE => ? )
-- The statement above returns only one result set.Sample Code for a Procedure Execution
The ODBC driver for ABAP can be used to execute SQL queries in standard tools with an ODBC adapter, such as MS Excel or DBeaver, or in database federation features like SAP HANA SDA, SQL Server Linked Server, or Db2 Federation. However, it’s difficult to use such tools to demonstrate how procedures can be called via ODBC. Even though ODBC technically is a low-level C interface, I don’t want to provide lengthy C code examples to demonstrate how to call procedures via ODBC, either.
As a compromise, I decided to provide a Python code snippet as an example. The Python Blog Post listed below can be used as an introduction to using the ODBC driver for ABAP with Python. Some documentation about the pyodbc module can be found here. The Python code example isn’t overly complex, and you can easily translate it if you want to use the ODBC driver for ABAP in other tools or with ODBC extensions of other languages.
The pyodbc module contains most features that I need to demonstrate the execution of my TESTPROCEDURE. However, the pyodbc module does not provide an option to retrieve scalar OUT parameters. So, I left out the optional scalar OUT parameter in the procedure CALL statement. If you plan to use python to consume your exposed procedures, you should return your scalar output parameters wrapped into a tabular output parameter that returns a single row result set.
Here’s the sample code:
import pyodbc
import getpass
# some docu in
# https://code.google.com/archive/p/pyodbc/wikis/GettingStarted.wiki
# https://github.com/mkleehammer/pyodbc/wiki
#
# run with "python call_proc.py"
dsn = input("Your ODBC DSN? ")
user = input("Your Connect User? ")
pwd = getpass.getpass("Your Connect Password? ")
connection = pyodbc.connect('DSN=' + dsn + ';UID=' + user +
';PWD=' + pwd + ';')
ltt_curs = connection.cursor()
ltt_curs.execute("CREATE LOCAL TEMPORARY TABLE SYS_TEMP.ITEMS_IN "
"( ITEM CHAR(100) NOT NULL ) ON COMMIT PRESERVE ROWS")
ltt_curs.close()
items = [ ('Apple', ), ('Orange', ) ]
insert_curs = connection.cursor()
# switches on ODBC array insert
insert_curs.fast_executemany = True
insert_curs.executemany("INSERT INTO SYS_TEMP.ITEMS_IN VALUES( ? )", items)
insert_curs.close()
min_creation_date = "20210802"
call_curs = connection.cursor()
call_curs.execute("CALL ZFMHORDERS.TESTPROCEDURE"
"( MIN_CREATION_DATE => ? , "
" ITEMS_TAB_IN => SYS_TEMP.ITEMS_IN , "
" ID_TAB_OUT => RESULT , "
" ITEMS_TAB_OUT => RESULT )",
min_creation_date )
# print first result set
print("\nFirst result set containing (ID, CREATIONDATE):")
for row in call_curs.fetchall():
print (row)
# switch to second result set
call_curs.nextset ()
# print second result set
print("\nSecond result set containing (POS, ITEM, AMOUNT):")
for row in call_curs.fetchall():
print (row)
# clean up
call_curs.close()
connection.close()
Other blogs related to the ABAP SQL service or the ODBC driver for ABAP are:
Consuming CDS View Entities Using ODBC-Based Client Tools | SAP Blogs
Using the ODBC driver for ABAP on Linux | SAP Blogs
Access Control for ABAP’s SQL Service (1/3) – Set the Scene | SAP Blogs
SQL Queries on CDS objects Exposed as SQL Service | SAP Blogs
Access CDS Objects Exposed as SQL Services Using Microsoft SQL Server | SAP Blogs
Data Science with SAP S/4HANA – connect with Jupiter notebooks | SAP Blogs
HANA SDA. Accessing ABAP CDS View entities in HANA cloud - SAP Blogs
Build an Azure Data Factory Pipeline with the ODBC driver for ABAP - SAP Blogs
Links to SAP documentation:
Data Integration (in the ABAP Integration and Connectivity Guide)
ODBC - ABAP Platform, On-Premise
Conclusion
In this blog post, I showed you how to expose and call special AMDP procedures using the SQL service and the ODBC driver for ABAP. As a simple example, I presented a Python sample code that executes a procedure call.
I hope that the examples give you an idea how to use such exposed AMDP procedures. Please let me know if this new feature helps you. My colleagues and I would like to learn more about the use cases you come up with. Please let us also know if you encounter problems or if you feel that something is missing in my description.
Bluesky| User | Count |
|---|---|
| 48 | |
| 38 | |
| 23 | |
| 21 | |
| 21 | |
| 18 | |
| 16 | |
| 13 | |
| 12 | |
| 12 |
