Introduction

The tight integration of database access is a strong point of the ABAP language. But in many situations the Open SQL code that is to be executed is not known during coding time. ABAP now has all the features necessary to execute Open SQL queries dynamically. This article explains some of the features that are not entirely straightforward, including how complex Open SQL queries can be executed dynamically.

The SAP ABAP programming language provides a variety of ways to access business data residing in a SAP application, ranging from an abstract, object-oriented layer down to individual table fields of the underlying database. The following two options are available for the database level access:

• A logical interface that abstracts from the specifics of the underlying database (Open SQL)
• An interface that directly exposes the capabilities of the underlying database (Native SQL)

Often higher-level interfaces are preferred, including BAPI (business application programming interface) functions, IDocs (intermediate documents), or LDBs (logical databases). However, retrieving data at the database level is sometimes the only available option due to a lack of an appropriate representation at the business object level or due to the specific nature of the data. When accessing data at the database level, the Open SQL interface is usually preferred over native access because it is independent from the underlying database which makes it more tolerant of data model evolution and customization.

This article describes a generic way to access data using the Open SQL interface in which the actual data to be retrieved from a single table or a number of tables is specified at run time. This approach is illustrated using an example from the SAP Human Capital Management (HCM) module. The SAP HCM module stores master data in tables that represent Infotypes. These tables usually have a personnel number as part of their key. The tables contain information about a specific aspect of the corresponding employee's record, such as address, bank details, or information about working time and payroll. Customers typically customize the HCM data model by creating additional Infotypes or by extending existing Infotypes.

The example company in this article manages employee records with the SAP HCM module. The company wants to set up a project to make data from the HCM system available to a variety of proprietary applications that deliver employee-related information internally and externally. In the pilot phase of the project, the example company focused on its employee directory, which enables internal users to search for employee names and display information such as personnel numbers, the names of their organizations, and their contact information.

In the early phase of the pilot project, the project team investigated the IDocs, BAPIs, and LDBs interfaces and concluded that none of these matched the specific data extraction requirements. They need to access the data using Open SQL.

For the initial prototyping phase, the project team is focused on information from the following Infotypes:

• 0003: List of all employee IDs (for a given SAP client ID)
• 0000: Actions containing employee status, which is needed to filter active employees
• 0001: Organizational assignment for current positions of employees
• 0002: Personal data, including employee names and addresses

Infotype 0001 maps personnel numbers to the ID of the organization these individuals are working for when given a point in time and an SAP client ID. Because the information system needs to show the organization's names in a readable form, data needs to be extracted from a configuration table that maps organization IDs to organization names for the supported languages. Because the supported languages are encoded in an SAP-specific notion, another configuration table needs to be involved that maps the SAP-specific language code to a standard code for normalization purposes. The following two configuration tables are involved:

• T527X: Maps an organizational unit ID to its name in the supported languages
• T002: Maps the SAP internal language code to the ISO639 language code

The pseudo code in Listing 1 shows a simplified SQL query that retrieves employee names for all active employees and lists them with the English names of their organizations.

SELECT PA0003.PERNR, T527X.ORGTX, PA0002.VORNA, PA0002.NACHN
FROM
	PA0000 
	INNER JOIN PA0001 ON PA0000.PERNR = PA0001.PERNR
	INNER JOIN PA0002 ON PA0000.PERNR = PA0002.PERNR 
	INNER JOIN PA0003 ON PA0000.PERNR = PA0003.PERNR
	INNER JOIN T527X ON PA0001.ORGEH = T527X.ORGEH
	INNER JOIN T002 ON T527X.SPRSL  = T002.SPAS
WHERE
	PA0000.STAT2 = <active> AND
	T002.LAISO = <ISO639 lang>

Instead of creating individual ABAP routines that perform specific queries for the various proprietary applications, the company wants to minimize the ABAP programming effort. The company wants to implement a generic mechanism that takes the specification of an Open SQL query as input and performs the corresponding query upon request. With this objective in mind, the company implements a generic SAP bridge that is capable of executing application-specific Open SQL queries that are maintained in a repository that is external to the SAP system. The SAP bridge periodically does the following:

• Retrieves queries from its repository
• Submits the queries to the SAP HCM system
• Routes the results to the corresponding application

If requirements for the data to be retrieved change, only the corresponding application-specific queries need to be adapted within the SAP bridge's repository. There is no need to change ABAP code or deploy new modules on the SAP system by means of the SAP transport system. Though a similar mechanism could be implemented using generated ABAP code, this approach has its drawbacks in performance, maintenance, and monitoring.

The rest of this article contains the following sections that are focused on the ABAP side of the SAP bridge:

• The Open SQL queries construction format with special focus on joined queries over multiple tables
• An introduction to the dynamic aspects of Open SQL.
• How the individual elements of a dynamic Open SQL query are created based on information in the SAP DDIC
• An example of how a simple version of the SAP bridge can use the ABAP routine to execute a query, such as the one in Listing 1, on an SAP HCM system.

Back to top

Extracting data with Open SQL

Syntactically Open SQL looks very familiar to developers with basic knowledge of the SQL standard. An Open SQL statement basically consists of the elements in Listing 2.

SELECT <select clause> <from clause> [<where clause>] [<group by clause>] 
     [<having clause>] [<order by clause>]

The ABAP code in Listing 3 below is an example of a workable SAP function module that retrieves personnel numbers, first and last names as well as an English description of the organization these employees are working for from an SAP HCM system and stores the result in an internal table. For the purpose of illustration the contents of this internal table are printed to the standard output device using an ABAP write statement. The user executing the function module needs to have the appropriate rights to access data at the logical table level. Note that no special treatment of the SAP client ID is required since it is implicitly given by the underlying session parameter.

FUNCTION z_retrieve_employee_info.

  DATA: BEGIN OF wa,
          pernr TYPE pakey-pernr,
          orgtx TYPE t527x-orgtx,
          vorna TYPE pa0002-vorna,
          nachn TYPE pa0002-nachn,
        END OF wa.

  DATA itab LIKE TABLE OF wa.


  SELECT employees~pernr adr~vorna adr~nachn orgnam~orgtx
  INTO CORRESPONDING FIELDS OF TABLE itab
  FROM
    pa0003 AS employees
      INNER JOIN pa0000 AS status ON employees~pernr = status~pernr
      INNER JOIN pa0001 AS org    ON employees~pernr = org~pernr
      INNER JOIN pa0002 AS adr    ON employees~pernr = adr~pernr
      INNER JOIN t527x  AS orgnam ON org~orgeh = orgnam~orgeh
      INNER JOIN t002   AS lang   ON orgnam~sprsl = lang~spras
  WHERE
    status~stat2 >= '2' AND   status~endda > sy-datum AND    " active employees
    org~endda > sy-datum AND                                 " validity period
    adr~endda > sy-datum AND                                 " validity period
    lang~laiso = 'EN'                                        " fixed interface language
  ORDER BY
    employees~pernr.

  LOOP AT itab INTO wa.
    WRITE: / wa-pernr, wa-vorna, wa-nachn, wa-orgtx.
  ENDLOOP.

ENDFUNCTION.

Open SQL in ABAP 6.1 supports joins among tables, subqueries, and most other SQL features of modern databases. Join predicates need to be specified in the FROM clause. The results are saved in the data structure that is specified in the INTO clause. This can be an internal table for bulk selects from the database, which has performance advantages. It can also be a data structure of a type that is fitting to the SELECT clause. In that case an ENDSELECT keyword is needed. This makes the SELECT clause a code loop. Each returned row is saved in the data structure and can be processed between the SELECT and ENDSELECT keywords until no more rows are returned.

Back to top

Using dynamic Open SQL

Sometimes the SQL code that needs to be executed is not known during programming time. You can specify static queries to use variables in the WHERE clause, as shown in Listing 4.

SELECT NACHN FROM PA0002 WHERE VORNA = firstname

In Listing 4, firstname is a string that is specified at runtime. The static variant does not allow the VORNA field to be filled dynamically. To solve this problem, ABAP enables specifying Open SQL clauses dynamically. In this case, the clause is parsed at runtime, and the call against the database is built up from the static and dynamic parts of the Open SQL statements. You can choose to specify some Open SQL clauses dynamically and leave others static. With ABAP 6.1 all clauses of the SELECT expression can be specified dynamically. You can dynamically specify simple fields, such as the WHERE condition, or you can create SQL SELECTS with complex joins at runtime. Using dynamic SQL results in a minor performance hit, but it is much faster than running the same query using a generated ABAP function.

ABAP uses dynamic token specification to specify an Open SQL clause dynamically. Do this by putting an ABAP string, character variable, or table variable in parentheses. The variable in parentheses must contain a valid SQL clause, which is parsed at runtime. A string variable needs to contain the SQL clause in the correct syntax. But the dynamic token can also be an internal table with one column of type string or CHAR. The column cannot exceed a length of 72. The condition is not case-sensitive. If a table is used, the condition can span an arbitrary number of lines as long as identifiers, such as field names, are not word-wrapped. The rules in this case are similar to the way static SQL is written in ABAP. Although using a string variable is easier, you might need to use an internal table if a remote function call sends the dynamic SQL code.

To specify the WHERE clause in Listing 4 dynamically, assume that you have a function module that has a string input parameter whereClause that contains the filter condition you want to apply for the select call. During execution, the contents of the whereClause variable are parsed and executed if the syntax is valid. If the variable doe not contain a valid whereClause, an exception is raised. Listing 5 shows the syntax of the Open SQL query.

SELECT NACHN FROM PA0002 WHERE (whereClause)

An Open SQL select clause always needs a data structure where it can put the results of the call, such as a structure or an internal table for bulk calls. This structure needs to have fields that have data types compatible with the selected fields. If this is not the case, there will be data conversion errors during runtime. In static SQL, this is not an issue, because the expected data types are known during programming time. In most cases only fields from one table are selected, and a structure of the database table structure type can be used as a target structure. This no longer works if you use arbitrary tables and fields to specify the from clause or the result clause dynamically, though, because the result set types cannot be determined at programming time. In this case, a target structure needs to be created dynamically at runtime so that it fits the datatypes of the dynamically specified result set, as described in the following section.

Back to top

Creating target rows dynamically

This section describes how to create a target structure dynamically at runtime using the Run Time Type Services (RTTS API of ABAP). All SELECT queries require a row structure to store the data of the retrieved row. The types of this row structure need to be compatible with the types of the corresponding fields that are selected. If you use the standard INTO clause, the row is filled in the order of the selected fields. If only character-based fields are selected, you can use a row structure of strings that has a sufficient number of fields. This does not work for all field types, including DATS.

If fields of arbitrary types need to be selected and the field types are not known in advance, dynamically build a row structure with fields of the needed types. You can do this in a number of ways. If only fields of one database table are selected and the table name is known at execution time, it is possible to retrieve a row structure of the database table from the data dictionary and to use the INTO CORRESPONDING FIELDS clause. However, this is not possible if database tables are joined and fields of more than one table are returned. In this scenario, a row structure with the fitting fields needs to be constructed dynamically at runtime.

ABAP provides an extensive API for runtime analysis and creation of data types. The core of these is a set of ABAP objects classes that can be used to describe types, to analyze existing objects, and to instantiate type descriptions. These are part of the Run Time Type Services (RTTS) class library. The library contains all information needed to create a type at runtime. In addition the library provides class methods to create objects of their type in different ways. Listing 6 shows an example library for an existing ABAP object.

CL_ABAP_TYPEDESCR

The classes are as follows:

CL_ABAP_ELEMDESCR

A class that defines the description of basic ABAP data types, such as c (character), i (integer), and f (floating point).

CL_ABAP_TYPEDESCR

A type description for which the child class CL_ABAP_DATADESCR is needed to dynamically create a type. CL_ABAP_TYPEDESCR also provides helper methods to create a description of the type of an existing object. The class can also create a type of the value of a database field, given the field name of the database table.

CL_ABAP_STRUCTDESCR

An analog to CL_ABAP_TYPEDESCR. CL_ABAP_STRUCTDESCR is the description of a structure type. For example, it provides methods to create a structure type from a list of CL_ABAP_TYPEDESCR objects, which define the types of the fields of the structure.

CL_ABAP_TABLEDESCR

The description of an internal ABAP table. CL_ABAP_TABLEDESCR needs a CL_ABAP_STRUCTDESCR to define the row type of the internal table.

For the example, create a row structure that corresponds to the selected fields of your dynamic SQL clause. You know the names of the fields that are specified in the dynamic SELECT clause. The dynamic SELECT clause can be created from a string object or from a table with one column that contains the field names. For the example, you have a table FIELDS with entries such as [PA0003~MANDT PA0003~PERNR PA0002~PERNR]. You need to create a type description of each requested field from the SELECT clause and put the type descriptions into a structure description. You will then use this structure description to instantiate the target structure type and then a target structure instance.

The structure description is an object of type CL_ABAP_STRUCTDESCR that you need for instantiating the actual structure type. To create this object, use the CL_ABAP_STRUCTDESCR=>CREATE method. This method needs an object of type ABAP_COMPONENT_TAB as input. ABAP_COMPONENT_TAB is a table of ABAP_COMPONENTDESCR objects, which itself is a structure that combines a field type of CL_ABAP_DATADESCR and a name field. This indirection is needed because a structure field needs both a type and a unique name, similar to the way a structure type is normally specified statically in ABAP code.

Next create a component description for each field with a unique name and a correct type, and add the descriptions to the internal table of type ABAP_COMPONENT_TAB.

One option to create the unique name for the component is to use the row index from SY-TABIX. Note that a field name must start with a character, and it cannot include any special characters. This means that you need to prefix the row index with a character. To use the string representation of SY-TABIX as part of the field name, use the CONDENSE function to remove the last empty character that optionally contains the minus sign.

There are multiple ways to create a type description for an ABAP type. If you have an ABAP data object, you can use the function CL_ABAP_TYPEDESCR=>DESCRIBE_BY_DATA to create a type description of this type. However, you do not have a data object yet. You have only the name of the field in Open SQL notation. In this case, use the function CL_ABAP_TYPEDESCR=>DESCRIBE_BY_NAME. This function can take the name of an object. You want to have the description of the type of a database table column. Similar to the way data objects of a database field type can be instantiated in ABAP, Listing 7 shows the input of this function.

PA0003-MANDT

or

SFLIGHT-CARRID

This is similar to the Open SQL fieldnames you used as input for the dynamic SELECT clause. You now need to take the fieldname in Open SQL notation PA0003~MANDT... and replace the ~ character in these field names with - to provide the correct input for the DESCRIBE_BY_NAME function, as shown in Listing 8.

DATA:
    columnName TYPE SO_TEXT,
    fieldsRow TYPE ZALBUS_STRUCT_WHERECLAUSE, TODO change type to example
    fieldname TYPE string,
    fieldDescr TYPE abap_componentdescr,
    fieldDescrTab TYPE abap_component_tab,
    rowStructDescr TYPE REF TO cl_abap_structdescr.
FIELD-SYMBOLS:
    <datarow> TYPE ANY, 
    <datafield> TYPE ANY.

LOOP AT FIELDS INTO fieldsRow. 
    fieldname = SY-TABIX.
    CONCATENATE 'f' fieldname INTO fieldname.
    CONDENSE fieldname. 
    fieldDescr-name = fieldname.

    columnName = fieldsRow-TEXT.
    REPLACE FIRST OCCURRENCE OF SUBSTRING '~' 
            IN columnName WITH '-' RESPECTING CASE.  

    fieldDescr-type ?= cl_abap_typedescr=>describe_by_name( columnName ).
    APPEND fieldDescr TO fieldDescrTab.
ENDLOOP.

After creating the ABAP_COMPONENT_TAB table that contains all component definitions, you can use CL_ABAP_STRUCTDESCR=>CREATE to create the structure definition for your object. The method creates the description object in memory and returns a reference to the object.

The second step creates an actual structure of this type using the CREATE DATA ... TYPE HANDLE command. The HANDLE keyword specifies that you use a reference to an RTTS class to instantiate the object.

Because the CREATE DATA command creates an object in memory and returns a reference to this, you still cannot use this object in the INTO clause of the SELECT command because this requires a data object and not a reference. Instead, ABAP provides field symbols of any type that take the place of a data object. So you need to dereference the reference with the ->* expression and assign this to a field symbol of TYPE ANY, as shown in Listing 9.

rowStructDescr = cl_abap_structdescr=>create( fieldDescrTab ).	 

CREATE DATA rowReference TYPE HANDLE rowStructDescr.

ASSIGN rowReference->* TO <datarow>.

This field symbol can now be used in the INTO clause of the SELECT statement. You now have a data object of a structure that has a field of the correct type for each field you want to select dynamically. This gives you all parts needed for dynamic joins that you can integrate into a working function module.

Back to top

Building the RFM

This section describes how to create a Remote Function Module using the information from the previous sections to specify the SELECT, FROM, and WHERE clauses of an Open SQL statement dynamically. Remote Function Modules (RFMs) are SAP function modules that can be called from other SAP systems and programs using the SAP Java Connector (JCo) or other methods. As a first step, an RFM needs to be created based on the attached ABAP code using the ABAP workbench. After activating the RFM, perform a simple test from the ABAP workbench to verify the proper operation of the code. This article does not provide step-by-step instructions to create RFMs because there is ample documentation available for this (see Resources). However the parameters of the module and the source code are provided in the following sections.

Parameters

The example RFM will contain four table parameters. Three parameters provide the dynamic SQL clauses, and one parameter returns the selected data. Before activating the code, create two structures representing the data type of the function-module's input and output parameters. Each of the following structures is based on an existing data element:

• Structure ZSQL_CLAUSE_ELEMENTS represents the dynamic SELECT, FROM, and WHERE clauses of an Open SQL query. ZSQL_CLAUSE_ELEMENTS consists of a single column based on the data element SO_TEXT.
• Structure ZTABLEROWS is based on the data element CHAR2000, which allows a maximum row length of 2000 characters including the separators. ZTABLEROWS is used to represent the query result.

The example concatenates the FROM and WHERE clauses into a string variable in the ABAP code to simplify the calls. Word wrapping is not needed if the table rows in the ABAP code are concatenated. If you use the original database table in the dynamic ABAP clause, there would be some restrictions, such as field names not permitted to be split between rows. Clauses that are longer than 72 characters need to be split into pieces of 72 characters and added to the corresponding INPUT table. You need the information from the FIELDS clause to assemble the dynamic target structure. To simplify this in the ABAP code, use one fully qualified field name in Open SQL notation, including the table name, in each row of the FIELDS input table, such as PA0003~PERNR. The table name needs to be added even if the FROM clause doesn't contain joins so you can instantiate the correct field types.

To keep the example simple, assume that the data return code has the following restrictions:

• The return table has one column of type CHAR2000.
• The RFM adds a string representation of the selected fields to this return table.
• The RFM adds one row to the return table for each returned row of the SELECT expression.
• In each row, the values of the fields are separated by a separation character | (vertical bar).
• No special treatment is available to cover tables with arbitrary row length or fields that contain the | character. There are a number of standard ways to deal with this, such as fixed length fields, wraparound mechanisms, and escaping. Because this article focuses on illustrating the use of enabling dynamic query techniques, the example doesn't dive into making the behavior more fool proof.

The tables used for input are:

FIELDS

The table that contains the fields that shall be returned in valid Open SQL notation. One field per row, completely specified, including table name. For example, PA0002~PERNR.

FROMCLAUSE

The FROM clause of the dynamic SQL query, split into 72 character pieces.

WHERECLAUSE

The WHERE clause of the dynamic SQL query, split into 72 character pieces.

The table used for output is:

DATA

The table containing the string representation of the selected fields divided by the separation character |.

Source

Listing 10 shows the ABAP code of the function module. The code works in three basic steps:

1. The dynamic target structure is created using the values of the FIELDS table as input, as described under Creating target rows dynamically.
2. Two string variables containing the FROM and the WHERE clauses are concatenated from the corresponding input tables.
3. The Open SQL expression is executed.

For each returned row, the field values of the dynamically created target structure are cast into a string and concatenated with the separation character. The complete row representation is then added to the output DATA table.

FUNCTION ZEXTRACT_TABLE_DATA.
*"----------------------------------------------------------------------
*"*"Local Interface:
*"  TABLES
*"      FIELDS STRUCTURE  ZSQL_CLAUSE_ELEMENTS
*"      FROMCLAUSE STRUCTURE  ZSQL_CLAUSE_ELEMENTS
*"      WHERECLAUSE STRUCTURE  ZSQL_CLAUSE_ELEMENTS
*"      DATA STRUCTURE  ZTABLEROWS
*"----------------------------------------------------------------------


*"----------------------------------------------------------------------
*" Copy selected fields from QUERY_TABLE to DATA_STRUCTURE
*"----------------------------------------------------------------------
  TYPE-POOLS: abap.
  DATA:
        columnName TYPE SO_TEXT, 
        fieldDataDescrRef  TYPE REF TO abap_componentdescr, 
        numberFields TYPE i, 
        fieldDescr TYPE abap_componentdescr, 
        fieldname TYPE string, 
        fieldDescrTab TYPE abap_component_tab,
        rowStructDescr TYPE REF TO cl_abap_structdescr, 
        rowReference TYPE REF TO data, 
        returnRowString TYPE string, 
        dataFieldString TYPE string, 
        dataline LIKE data, 
        fromClauseRow TYPE ZALBUS_STRUCT_WHERECLAUSE,
        fromClauseString TYPE string,
        whereClauseRow TYPE ZALBUS_STRUCT_WHERECLAUSE,
        whereClauseString TYPE string,
        fieldsRow TYPE ZALBUS_STRUCT_WHERECLAUSE.
  FIELD-SYMBOLS:
          <datarow> TYPE ANY, 
          <datafield> TYPE ANY. 


* CREATE DataStructure with field names 
* Datatypes are read from fieldnames of FIELDS input table
  DESCRIBE TABLE FIELDS LINES numberFields.
  LOOP AT FIELDS INTO fieldsRow.
    fieldname = SY-TABIX.
* names need to be unique and must start with a char
    CONCATENATE 'string' fieldname INTO fieldname.
    CONDENSE fieldname.
    fieldDescr-name = fieldname.
* for dictionary lookup we need to change columnnames from Open SQL
* to dictionary notation
    columnName = fieldsRow-TEXT.
    REPLACE FIRST OCCURRENCE OF SUBSTRING '~' IN columnName WITH '-' RESPECTING CASE.

    fieldDescr-type ?= cl_abap_typedescr=>describe_by_name( columnName ).
    APPEND fieldDescr TO fieldDescrTab.
  ENDLOOP.

  rowStructDescr = cl_abap_structdescr=>create( fieldDescrTab ).

* now we create the actual data structure in memory
  create data rowReference type HANDLE rowStructDescr.
* finally we assign it to the Field-symbol used by the select statement
  ASSIGN rowReference->* TO <datarow>.
* End Create DataStructure

* to simplify calls we concatenate from and whereclause into strings
* this way caller doesn't need to check word wrappings
  fromClauseString = ''.
  LOOP AT FROMCLAUSE INTO fromClauseRow.
    CONCATENATE fromClauseString fromClauseRow-TEXT INTO fromClauseString.
  ENDLOOP.

  whereClauseString = ''.
  LOOP AT WHERECLAUSE INTO whereClauseRow.
    CONCATENATE whereClauseString whereClauseRow-TEXT INTO whereClauseString.
  ENDLOOP.

* Now start actual select operation
  SELECT (FIELDS) FROM (fromClauseString) INTO <datarow> WHERE (whereClauseString).
* we read all fields of the current row, cast it to string and
* concatenate it into a dataline with division chars.
    CLEAR: returnRowString.
    DO numberFields TIMES.
      ASSIGN component sy-index of structure <datarow> to <datafield>.
      dataFieldString = <datafield>.
      CONCATENATE returnRowString '|' datafieldstring INTO returnRowString.
    ENDDO.
    dataline = returnRowString.
* finally dataline is added to the return table.
    INSERT dataline INTO TABLE data.
  ENDSELECT.

ENDFUNCTION

You have now created an ABAP RFM that enables the execution of pretty much every Open SQL SELECT statement. You can add the ORDER BY and HAVING clauses if needed. The next section describes how to call this remote function module from a Java™ program, replicating the static Open SQL call you created in Extracting data with Open SQL without any business logic in the ABAP code.

Back to top

Invoking the generic query routine from a Java program

The ABAP routine described in the preceding sections can be invoked from a Java program using the SAP Java Connector (JCo). This section briefly outlines a simple Java program that feeds the generic SAP bridge with runtime query data and prints the result to STDOUT.

For the appropriate level of dynamic Open SQL support, the ABAP environment must be release 6.1 or later. The SAP Java Connector for a variety of operating systems is available for download from the connectors page of the SAP marketplace (see Resources). The files in the Download section provide everything else needed to run the example query on a SAP ERP system, including the complete Java class implementation. It requires a proper installation of the SAP Java Connector version 3, which requires a Java runtime environment (JRE) of at least version 1.5.

Complete the following steps to enable remote invocation of an ABAP routine from a Java program:

1. Create a JCo Destination to connect to a SAP system and retrieve the JCo repository object, which represents the metadata of a SAP system.
2. Retrieve the function template from the repository based on the function module name.
3. Instantiate function parameters.
4. Invoke function on based on the JCo Destination object.

Invoke the method populateTable() for each of the three parameters that represent SQL SELECT, FROM, and WHERE clauses, as shown in Listing 11. These parameters are passed in the form of an internal table that is basically a two dimensional, in-memory structure with fixed row length. Note that although the use of table parameters is discouraged by SAP in recent system versions of the ABAP development environment, it is a common mechanism for passing structured objects to ABAP routines.

   /**
    * Instantiate a table parameter with values of an array 
    * @param tables table parameter list of an ABAP function module
    * @param tableName name of table to be instantiated
    * @param tableValues array of string values to be stored in this table
    */

   static void populateTable(JCoParameterList tables,
                             String   tableName,
                             String[] tableValues) {
      JCoTable table = tables.getTable(tableName);
      table.appendRows(tableValues.length); // create a new row
      int i = 0;
      do {                                  // and fill it with data
         table.setValue(0, tableValues[i]); // 1st column holds FIELDNAME
         i++;
      } while (table.nextRow());
   }

The method extractTableData(...) takes the three SQL clauses as input, instantiates the corresponding table parameters, passes them to the remote function module, and returns the result as a two-dimensional array. For the example scenario, the ABAP function module passes rows in the form of strings where fields are concatenated into a string and separated with the character |, as shown in Listing 12.

   /**
    * Run a dynamic Open SQL query using the remove function module ZEXTRACT_TABLE_DATA
    * on an SAP system. Select, from, and where clauses are string arrays the elements
    * of which may not exceed a length limitation of 72 characters.
    * @param selectClause String array containing elements of the query's select clause
    * @param fromClause String array containing elements of the query's from clause
    * @param whereClause String array containing elements of the query's where clause
    * @return Two dimensional String array the columns of which correspond to the fields
    * of the selectClause and the rows of which correspond to individual hits.
    * @throws JCoException Any exception raised by the ABAP routine or the Java Connector
    */
   
   final static String SAP_QUERY_DELIMITER  = "|"; 
   final static String DATA_EXTRACTION_RFM = "ZEXTRACT_TABLE_DATA";
      
   static String[][] extractTableData(String[] selectClause,
                                      String[] fromClause,
                                      String[] whereClause) throws JCoException{

      String[][] table = null;

        if (selectClause == null || selectClause.length == 0 ||
            fromClause == null || fromClause.length == 0) {
            System.err.println("From or select clause may not be empty.");
            return null;
        }
        
      JCoFunctionTemplate ftemplate =
                   jcoRepository.getFunctionTemplate(DATA_EXTRACTION_RFM);
      if (ftemplate == null) {
         System.err.println("Custom RFM '" + DATA_EXTRACTION_RFM + "' not found.");
         return null;
      }

      JCoFunction function = ftemplate.getFunction();
      JCoParameterList tables = function.getTableParameterList();

      populateTable(tables, "FIELDS", selectClause);
      populateTable(tables, "FROMCLAUSE", fromClause);
      if (whereClause != null && whereClause.length > 0) {
         populateTable(tables, "WHERECLAUSE", whereClause);
      }

      // execute the remote function  module
      function.execute(jcoDestination);               

      JCoTable data = tables.getTable("DATA");

      // numRows is the actual number of rows that have been retrieved
      int numRows = (data == null) ? 0 : data.getNumRows();

      // if any rows have been read: retrieve data values and store in 'table'
      if (numRows > 0) {
         table = new String[numRows][];
         ArrayList<String> row = new ArrayList<String>();
         int posFrom, posTo, rowCount = 0;
         do {
            JCoRecordFieldIterator fieldIter = data.getRecordFieldIterator();
            JCoRecordField field = fieldIter.nextRecordField();
            String line = field.getString();

            // Rows in the result table ('DATA') are 72char strings where
            // field values are separated with the '|' character. The
            // first character of a row is always a '|'. The following
            // lines extract field values and store them as individual
            // strings in the two dimensional result table.
            String s;
            posFrom = 1;
            do {
               posTo = line.indexOf(SAP_QUERY_DELIMITER, posFrom);
               if (posTo < 0)
                  s = line.substring(posFrom);
               else {
                  s = line.substring(posFrom, posTo);
                  posFrom = posTo + 1;
               }
               s = s.trim();
               row.add(s);
            }
            while (posTo > 0);
            
            table[rowCount++] = row.toArray(new String[]{});
            row.clear();
         } while (data.nextRow());
         
      } // numRows > 0  
      
      return table;
   }

To complete the example, Listing 13 shows the main method that takes the name of a file holding the SAP connection parameters as input, establishes a connection to the SAP system, invokes the remote function module, and displays its result on the screen. The Open SQL clauses are provided as constants.

   static String[] selectClause = {
      "pa0003~pernr",      // master record: personnel number
      "pa0002~vorna",      // address information: first name
      "pa0002~nachn",      // address information: last name
      "t527x~orgtx"        // name of organizational unit
   };
   
   static String[] fromClause   = {
      "pa0003 AS employees",
         " INNER JOIN pa0000 ON pa0003~pernr = pa0000~pernr",
         " INNER JOIN pa0001 ON pa0003~pernr = pa0001~pernr",
         " INNER JOIN pa0002 ON pa0003~pernr = pa0002~pernr",
         " INNER JOIN t527x  ON pa0001~orgeh = t527x~orgeh",
         " INNER JOIN t002   ON t527x~sprsl = t002~spras"
   };
   
   static String[] whereClause  = {
       "pa0000~stat2 >= '2' AND pa0000~endda > sy-datum", // active employees
       " AND pa0001~endda > sy-datum",
       " AND pa0002~endda > sy-datum",
       " AND pa0003~mandt = '800'",
       " AND pa0003~pernr >= '1000' AND pa0003~pernr <= '1100'",
       " AND t002~laiso = 'EN'"          // ISO639 language code
   };
   
    /**
     * The main method takes the name of a file containing SAP connection parameters
     * as input and invokes the function that performs the dynamic Open SQL query.
     * The results of this query are written to STDOUT.
     * @param args Name of file containing SAP connection parameters
     * (not including mandatory extension '.jcoDestination')
     */
       
   public static void main(String[] args) {
      try {
         System.out.println("Connecting");
         jcoDestination = JCoDestinationManager.getDestination(args[0]);
         jcoRepository = jcoDestination.getRepository();
           
         System.out.print("Running query: ");
         String[][] employeeData =
                         extractTableData(selectClause, fromClause, whereClause);
         System.out.println(employeeData.length + " hits.");
         if (employeeData != null) {
            for (int i = 0; i < employeeData.length; i++) {
               String[] employeeRecord = employeeData[i];
               for (int j = 0; j < employeeRecord.length; j++) {
                  System.out.print(employeeRecord[j] + "\t");
               }
               System.out.println();
            }                           
         }
         System.out.println("Done.");
      }
      catch (JCoException jcoe) {
         jcoe.printStackTrace();
      }
  }

The main method has a single vm argument, which must be the name of a file that contains connection parameters of the SAP system in the format required by the SAP Java Connector (for details, see Resources to review the definition of class DestinationDataProvider in the JCo user guide). The file should have the extension .jcoDestination, which is not to be specified as part of the parameter. Listing 14 shows content of the .jcoDestination file..

jco.client.ashost=erp6host
jco.client.sysnr=00
jco.client.client=800
jco.client.user=data
jco.client.passwd=...

Note that the user ID used to connect to the SAP system needs to have appropriate authorizations to be able to read the tables that are used. See Resources for details on how to set up appropriate authorizations in SAP. In a test or demo environment, an admin user is usually sufficient. Running 'java SAPTableDataExtractor <name of file with SAP connection data> prints a table to standard output, listing information such as personnel numbers, employee names, and the names of the organizations these employees are working for, as shown in Listing 15.

00001009   Herbert Braunstein  Exec.directory - Germany    
00001021    Werner  Stadler Exec.directory - Germany    
00001020    Beate   Müller-Hinze    Exec.directory - Germany    
00001019    Keith   Thomson Exec.directory - Germany    
00001018    Bill    Fischer Exec.directory - Germany    
00001017    Annette Sturm   Exec.directory - Germany    
00001000    Anja    Müller  Human Resources (D) 
...

Back to top

Conclusion

This article describes how to build a program that performs Open SQL queries based on dynamically specified clauses. You can see the value of such an approach in the context of the various existing APIs and in the steps needed to create such a program. An end-to-end example offered specifics on how the logical data layer invokes a routine from a remote Java program. Special attention has been given to the way in which the ABAP logic treats the joins and how the appropriate resulting structures are built. This article shed some light on a powerful way to extract information so that you can adapt queries to changing requirements without the need to redeploy functions on the SAP stack.

Back to top

Downloads

Information about download methods

Resources

Learn

• Consult SAP help for more detailed information.
  
  
• Use the ABAP Programming Reference (SAP help) for more reference information.
  
  
• Use the Open SQL Programming Reference (SAP help) for more reference information.
  
  
• Read the Open SQL Grammar document (SAP help) for more reference information.
  
  
• Learn how to Implement IBM DB2 and SAP in a Microsoft cluster environment (developerWorks, Jul 2005).
  
  
• Learn how to Enable database high availability using DB2 HADR and Tivoli SA MP in an SAP environment (developerWorks, Aug 2007).
  
  
• Learn more about Information Management at the developerWorks Information Management zone. Find technical documentation, how-to articles, education, downloads, product information, and more.
  
  
• Stay current with developerWorks technical events and webcasts.
  
  
• Follow developerWorks on Twitter.
  
  

Get products and technologies

• Go to the SAP marketplace (requires authentication) to get related technology.
  
  
• Refer to SAP connectors in the SAP marketplace (requires authentication) for more information about connectors.
  
  
• Build your next development project with IBM trial software, available for download directly from developerWorks.
  
  

Discuss

• Check out the developerWorks blogs and get involved in the developerWorks community.
  
  

About the authors

Florian Funke is a Software Engineer at the IBM Development Lab in Boeblingen, Germany. He is working in Information Management developing SAP content management and data management products.

Peter Gerstl is a Software Architect at the IBM Development Lab in Boeblingen, Germany, with over 15 years of experience in text analytics, enterprise content management, and business process management. Peter has held various technical leadership positions both inside IBM and externally. His current focus area is information lifecycle management and data governance in heterogeneous IT landscapes.

Benjamin Leonhardi is a Software Engineer working on SAP solutions at the IBM Development Lab in Boeblingen, Germany. Before that he worked for four years in the business intelligence and warehousing area.

Rate this article

Comments

Back to top

Table of contents

• Introduction
• Extracting data with Open SQL
• Using dynamic Open SQL
• Creating target rows dynamically
• Building the RFM
• Invoking the generic query routine from a Java program
• Conclusion
• Downloads
• Resources
• About the authors
• Comments

Dig deeper into Information Management on developerWorks

IBM SmartCloud trial. No charge.

Unleash the power of hybrid cloud computing today!

---

Public and private solutions in one trial.

Special offers