A web service to read any SAP table via SQL - with JOINs, subqueries, GROUP BY, etc.

Most SAP-developers know about OData services - the state-of-the-art RESTful web API of SAP. Many know also, that it can be quite a pain to such a service for a flexible data query: every field, every filter, every option of your query must be programmed in ABAP in advance. This is a serious cost-driver for use cases like ad-hoc reporting, dashboarding or data exctraction. 

In this article, I'm going to demostrate, how to use the built-in ADT service called datapreview to run OpenSQL queries via HTTP. ADT stands for ABAP Development Tools and basically is the interface for the Eclipse IDE on SAP-side. It includes a collection of web services, that allow eclipse to interact with SAP. In particular, the Eclipse SQL Console utilizes the datapreview service, which we are going to use too. You can view and test all ADT services in the SICF transaction - just like any other NetWeaver web service.

Prerequisities

If your SAP installation supports the Eclipse SQL Console - you are good to go! Otherwise, refer to the SAP documentation on how to get up and running with Eclipse.

SQL query request

All you need to perform an OpenSQL SELECT statement, is send it as raw body of a POST request to http://your-sap-url/sap/bc/adt/datapreview/freestyle. By default, you will get the first 100 rows of the SELECT. If you wish to control pagination manually, add the parameter rowNumber to the URL (see the screenshot for an example).

Also note, that a single line in the request's body may not exceed 255 characters! Be sure to use \r\n for line breaks!

Perform OpenSQL queries via SAP ADT datapreview web service

Authentication and CSRF protection

You can use any authentication method available for SAP web services. However, on most systems you will need a CSRF-Token to perform a POST request - see my instructions to get one.

Querying a specific client (MANDT)

By default, the query will be performed for the default SAP client. To query a specific client, add the URL parameter sap-client to the first request (in most cases, it's the CSRF-fetch-request - see above): e.g. http://your-sap-url/sap/bc/adt/datapreview/freestyle?sap-client=100. 

SQL query response

The response from NetWeaver will contain the data as XML.

<?xml version="1.0" encoding="utf-8"?>
<dataPreview:tableData xmlns:dataPreview="http://www.sap.com/adt/dataPreview">
    <dataPreview:totalRows>8</dataPreview:totalRows>
    <dataPreview:isHanaAnalyticalView>false</dataPreview:isHanaAnalyticalView>
    <dataPreview:executedQueryString>SELECT SFLIGHT~CARRID, SFLIGHT~CONNID, SFLIGHT~FLDATE, SFLIGHT~PRICE, SPFLI~CITYFROM, SPFLI~CITYTO FROM SFLIGHT INNER JOIN SPFLI ON SFLIGHT~CARRID = SPFLI~CARRID AND SFLIGHT~CONNID = SPFLI~CONNID   INTO     TABLE @DATA(LT_RESULT)   UP TO 2  ROWS   .</dataPreview:executedQueryString>
    <dataPreview:queryExecutionTime>5.4370000</dataPreview:queryExecutionTime>
    <dataPreview:columns>
        <dataPreview:metadata dataPreview:name="CARRID" dataPreview:type="C" dataPreview:description="CARRID" dataPreview:keyAttribute="false" dataPreview:colType="" dataPreview:isKeyFigure="false"/>
        <dataPreview:dataSet>
            <dataPreview:data>AC</dataPreview:data>
            <dataPreview:data>AF</dataPreview:data>
        </dataPreview:dataSet>
    </dataPreview:columns>
    <dataPreview:columns>
        <dataPreview:metadata dataPreview:name="CONNID" dataPreview:type="N" dataPreview:description="CONNID" dataPreview:keyAttribute="false" dataPreview:colType="" dataPreview:isKeyFigure="false"/>
        <dataPreview:dataSet>
            <dataPreview:data>0820</dataPreview:data>
            <dataPreview:data>0820</dataPreview:data>
        </dataPreview:dataSet>
    </dataPreview:columns>
    <dataPreview:columns>
        <dataPreview:metadata dataPreview:name="FLDATE" dataPreview:type="D" dataPreview:description="FLDATE" dataPreview:keyAttribute="false" dataPreview:colType="" dataPreview:isKeyFigure="false"/>
        <dataPreview:dataSet>
            <dataPreview:data>20021220</dataPreview:data>
            <dataPreview:data>20021223</dataPreview:data>
        </dataPreview:dataSet>
    </dataPreview:columns>
    <dataPreview:columns>
        <dataPreview:metadata dataPreview:name="PRICE" dataPreview:type="P" dataPreview:description="PRICE" dataPreview:keyAttribute="false" dataPreview:colType="" dataPreview:isKeyFigure="false"/>
        <dataPreview:dataSet>
            <dataPreview:data>1222.00 </dataPreview:data>
            <dataPreview:data>2222.00 </dataPreview:data>
        </dataPreview:dataSet>
    </dataPreview:columns>
    <dataPreview:columns>
        <dataPreview:metadata dataPreview:name="CITYFROM" dataPreview:type="C" dataPreview:description="CITYFROM" dataPreview:keyAttribute="false" dataPreview:colType="" dataPreview:isKeyFigure="false"/>
        <dataPreview:dataSet>
            <dataPreview:data>FRANKFURT/MAIN</dataPreview:data>
            <dataPreview:data>FRANKFURT/MAIN</dataPreview:data>
        </dataPreview:dataSet>
    </dataPreview:columns>
    <dataPreview:columns>
        <dataPreview:metadata dataPreview:name="CITYTO" dataPreview:type="C" dataPreview:description="CITYTO" dataPreview:keyAttribute="false" dataPreview:colType="" dataPreview:isKeyFigure="false"/>
        <dataPreview:dataSet>
            <dataPreview:data>MONTREAL-DORVAL</dataPreview:data>
            <dataPreview:data>FORT DE FRANCE</dataPreview:data>
        </dataPreview:dataSet>
    </dataPreview:columns>
</dataPreview:tableData>

Getting started with OpenSQL

If you are familiar with ABAP, you will probably know OpenSQL too. Just skipt all the INTO TABLE-parts and variables. Skip ahead to the next section.

If you are new to ABAP and OpenSQL, you may have already noticed in the screenshot above, that the syntax of OpenSQL is a little different from other SQL dialects. OpenSQL is a special from of SQL, that can be embedded into SAP ABAP code. It follows the general SQL logic, but has numerous limitations. On the other hand, it works with any database, that SAP is compatible with!

Here are some of the most notable differences to general SQL:

• Table and field aliases are separated by ~ instead of . and MUST be declared with AS.
• Double quotes (") are not allowed - more precisely, they are used for inline comments.
• Subselects are not supported (no subqueries in the SELECT clause), but subqueries in the WHERE clause are OK.

Security Conciderations

Using such a generic SQL reader in production is, of course, questionable from the point of view of security. Surely, you can (and should!) limit the access to the web service to selected trusted users, but every one of them will gain access to ALL the data within the client (MANDT)! Of course, that's no different, than with other generic tools like the RFC_READ_TABLE function module, etc. It's a matter of trust in the application, that is going to use the web service and, in-fact, a matter of security policy of the company!

Alternatives

When it comes down to synchronous cross-plattform data queries on SAP systems, there are not many options if you do not want to build an API within SAP yourself. A fairly common alternative would be publishing RFC function modules like RFC_READ_TABLE (no JOINs or GROUP BY) or RFC_ABAP_INSTALL_AND_RUN (far more insecure as not limited to reading) as SOAP web service. You can get a good overview of these and other options in my article about connecting to SAP from PHP - the principles are the same for most web-related plattorms.