The CData MCP Server for SAP ERP dynamically retrieves schemas for SAP tables, queries, and function modules at run time. However, since it can be inefficient to retrieve the metadata for the entire SAP system, the server also applies default filters to represent only a slice of your SAP system.

Below is an explanation of how the server models each type of SAP object. How the server determines what to display is highly customizable; see Accessing SAP Entities for more information on selecting the SAP objects you want.

SAP Tables

SAP tables are listed as views. This is because SAP strongly discourages directly making changes to SAP tables. Instead, function modules such as BAPIs should be used for making changes to table data. See Function Modules below for more on that.

Table data is retrieved from SAP using either the RFC_READ_TABLE or Z_CUSTOM_READ_TABLE function module if you have it installed. These give access to basic SQL support, but more complex queries such as joins and group by must be handled locally. More complex queries are handled automatically by the server.

See Using a Custom Read Table Function for more information on circumventing limitations with RFC_READ_TABLE.

SAP Queries

Just like tables, you can also work with SAP queries as relational views. By default they are turned off to try and avoid cluttering the server with too many options. They can be turned on by using the QueryMode connection property.

Unlike tables, SAP queries might require certain values to be specified as inputs. You can specify these query inputs as part of the predicate in the SQL. For instance:

SELECT * FROM Z_SAP_QUERY WHERE InputName='x'

All SAP query views include a Variant column that you can also input as part of the projection.

Be aware that values that are only inputs in an SAP query are reflected back in the results as the same value that was specified. This does not actually indicate that SAP returned the value that way. It is just something that is required to keep the SQL statement valid.

Function Modules

Function modules in SAP can be used for almost any task. These include the very RFC calls that the server makes such as RFC_READ_TABLE and function modules that modify data such as BAPI_PO_CREATE.

You can execute a function module directly as a stored procedure. SAP function modules accept and return primitive values and complex structures; the corresponding stored procedures accept and return SQL types and tables. For instance:

EXEC STFC_CONNECTION

For a more complex example, try RFC_READ_TABLE:

  EXEC RFC_READ_TABLE @QUERY_TABLE='MARA', @FIELDS='FIELDS#TEMP', @ROWCOUNT=1

In the above example, the RFC_READ_TABLE function module is executed to return the MARA table. The QUERY_TABLE and ROWCOUNT parameters are passed in as primitive values. In this function module, FIELDS is an input table. To provide this input, you can populate a temporary table with the #TEMP syntax and INSERT statements. For example:

  INSERT INTO FIELDS#TEMP (FIELDNAME) VALUES ('MANDT');
  INSERT INTO FIELDS#TEMP (FIELDNAME) VALUES ('MATNR');
  INSERT INTO FIELDS#TEMP (FIELDNAME) VALUES ('ERSDA');
  INSERT INTO FIELDS#TEMP (FIELDNAME) VALUES ('ERNAM');

Alternatively, JSON or XML can be used to submit tabular data for environments where the #TEMP tables would be difficult to use. For example:

  EXEC RFC_READ_TABLE OPTIONS='{ \"TEXT\": \"TABNAME LIKE ''MARA%''\" }', QUERY_TABLE='DD02L', ROWCOUNT='1', ReturnTables='DATA'

Customizing Stored Procedure Schemas

Instead of using the dynamic, in-memory schemas, you can use GenerateSchemaFiles to save static schema files that are easy to customize. A common use case is to remove extra unwanted inputs and outputs that the server may report for a stored procedure.

In SAP, there is nothing in the metadata that distinguishes tables in function modules as either inputs or outputs. Since there is no way to know which tables are actually used for inputs and which are used for outputs, the server reports the tables as being available for both inputs and outputs.

To generate the schema file, set GenerateSchemaFiles to "OnUse" and call the stored procedure. Also, set the Location property to the folder where you want the schema to be output.

To modify the schema file, open the generated schema file in the Location folder with any text editor. Then find the <input> or <output> tags that are not needed and remove them. Refresh the connection in the server to pick up the schema and your changes.