ExtractSap Function

From QPR ProcessAnalyzer Wiki
Revision as of 19:53, 1 December 2022 by Ollvihe (talk | contribs)
Jump to navigation Jump to search

ExtractSap function extracts data from an SAP R/3 system and returns it as a DataFlow.

If a column name contains illegal characters for table names, the illegal characters are converted to be underscore characters (e.g. "sap:Owner" -> "sap_Owner"). Columns are extracted as text data. Note that using this command requires installing SAP NetWeaver RFC Library.

Parameters:

  • User: SAP username used to connect to SAP. Mandatory. Corresponds to the "USER" constant on SAP side. See the SAP .NET Connector documentation for more info.
  • Password: Password of the SAP user used to connect to SAP. Mandatory. Corresponds to the "PASSWD" constant on SAP side. See the SAP .NET Connector documentation for more info.
  • PasswordKey: Secure string key for the stored SAP password. Alternative for the SapPW property.
  • Client: The SAP backend client. Mandatory. Corresponds to the "CLIENT" constant on SAP side. See the SAP .NET Connector documentation for more info.
  • AppServerHost: The hostname or IP of the specific SAP application server, to which all connections shall be opened. Mandatory if SapMessageServerHost is not defined. Corresponds to the "ASHOST" constant on SAP side. See the SAP .NET Connector documentation for more info.
  • MessageServerHost: The hostname or IP of the SAP system’s message server (central instance). Mandatory if SapAppServerHost is not defined. Corresponds to the "MSHOST" constant on SAP side. See the SAP .NET Connector documentation for more info.
  • SystemNumber: The SAP system’s system number. Mandatory if SapSystemID is not defined. Corresponds to the "SYSNR" constant on SAP side. See the SAP .NET Connector documentation for more info.
  • SystemID: The SAP system’s three-letter system ID. Mandatory if SapSystemNumber is not defined. Corresponds to the "SYSID" constant on SAP side. See the SAP .NET Connector documentation for more info.
  • Language: SAP language used. Default value is "EN". Optional. Corresponds to the "LANG" constant in SAP.
  • PoolSize: Maximum number of RFC connections that this destination will keep in its pool. Default value is "5". Optional. Corresponds to the "POOL_SIZE" constant in SAP.
  • PeakConnectionsLimit: In order to prevent an unlimited number of connections to be opened, you can use this parameter. Default value is "10". Optional. Corresponds to the "MAX_POOL_SIZE" constant on SAP side. See the SAP .NET Connector documentation for more info.
  • ConnectionIdleTimeout: If a connection has been idle for more than SapIdleTimeout seconds, it will be closed and removed from the connection pool upon checking for idle connections or pools. Default value is "600". Optional. Corresponds to the "IDLE_TIMEOUT" constant on SAP side. See the SAP .NET Connector documentation for more info.
  • Router: List of host names and service names or port numbers for the SAPRouter in the following format: /H/hostname/S/portnumber. Optional. Corresponds to the "SAPROUTER" constant in SAP.
  • LogonGroup: The logon group from which the message server shall select an application server. Optional. Corresponds to the "GROUP" constant on SAP side. See the SAP .NET Connector documentation for more info.
  • QueryMode: If this number is set to "1", then the query result will have the SAP Table field names as data table column names and actual data rows as rows. If this is set to "3", the query result will get the field descriptions from the SAP query using NO_DATA parameter, i.e. the returned columns are the following (in this order): Field, Type, Description, Length, Offset. Default value is "1". Optional. See the SAP .NET Connector documentation for more info.
  • QueryTable: Name of the SAP table to be extracted. Specifies the value for the parameter QUERY_TABLE in tab: 'Import' or function module 'rfc_read_table' in SAP. Mandatory. See the SAP .NET Connector documentation for more info. Note that if the query doesn't return any data, the target data table or temporary table is not created.
  • Rowcount: The maximum amount of rows to fetch. Specifies the value for parameter ROWCOUNT in tab: 'Import' or function module 'rfc_read_table' in SAP. Optional. See the SAP .NET Connector documentation for more info.
  • Rowskips: The number of rows to skip. Specifies the value for parameter ROWSKIPS in tab: 'Import' or function module 'rfc_read_table'. in SAP. Optional. See the SAP .NET Connector documentation for more info.
  • WhereClause: A comma separated list of WHERE clause elements passed for the SapQueryTable. Can be used with or without the SapWhereClauseSelect parameter. If used together with the SapWhereClauseSelect parameter, use the SapWhereClause parameter first. NOTE: The default maximum length for the Where Clause string is 72 characters in SAP, so the recommended maximum length of the SapWhereClause value is also 72 characters. In effect, specifies the value for parameter OPTIONS in tab: 'Import' or function module 'rfc_read_table' in SAP. Optional. See the SAP .NET Connector documentation for more info.
  • WhereClauseSelect: The SELECT query to be executed in QPR ProcessAnalyzer sandbox. Used with or without the SapWhereClause parameter to pass WHERE clauses to SapQueryTable. If used together with the SapWhereClause parameter, use the SapWhereClause parameter first. The query is expected to return a table with at least one column, as the contents from the rows in the first column of the table are concatenated together to form the WHERE clause in SAP RFC_ReadTable. Therefore, it's recommended to first create the table with the WHERE clauses into a temporary table. In addition, it's recommended to have an order number column in the table and use that in the SELECT query to make sure the WHERE clause elements are concatenated in the correct order. The default maximum length for Where Clause string is 72 characters in SAP, so the recommended maximum length for the WHERE clause string in each row of the table is also 72. In effect, specifies the value for parameter OPTIONS in tab: 'Import' or function module 'rfc_read_table' in SAP. Optional. The contents up to the first 10 rows in the first column of the SELECT query are shown in the QPR ProcessAnalyzer Script Log. See the SAP .NET Connector documentation for more info.
  • FieldNames: A comma separated list of field names for columns to be imported. Default value is empty, resulting in all columns being imported. Specifies the value for parameter FIELDNAME in tab: 'Tables' for table 'FIELDS' for function module 'rfc_read_table' in SAP. Optional. See the SAP .NET Connector documentation for more info.
  • Function: If you define a value for this parameter, then the new value specifies the SAP function that is called inside the #ImportSapQuery command. Optional. The default value is RFC_READ_TABLE. Another possible value is BBP_RFC_READ_TABLE. See the SAP .NET Connector documentation for more info.
  • ConvertDataTypes: List of SAP data types that are converted into respective data types. Defined by listing the data type identifier characters in any order. Available data type identifying characters are IFPCDTNX. If not defined, all data types are converted. Example: IFP (convert only numeric data types: Integer, Float, Packed number) (more information).

Project is needed for the secure strings.

If there is an error in the function, exception is given.

Examples: 

let sapConnection = #{ 
  "AppServerHost": "inf-sap01",
  "SystemNumber": "00",
  "User": "qpr_rfc",
  "Router": "",
  "SystemID": "QPR",
  "Client": "800",
  "Language": "EN",
  "PoolSize": 5,
  "PoolSizeMax": 10,
  "IdleTimeout": 600,
  "PasswordKey": "SapPW41" 
 };
let queryParameters = sapConnection.Extend({
  "FieldNames": "VBELN,ERDAT,ERZET,ERNAM,NETWR,WAERK", 
  "QueryTable": "VBAK",
  "Options": ["VBELN BETWEEN '0000017448'" ,"AND '0000017450'"]
});
let queryResults = ProjectById(1).ExtractSap(queryParameters);
Retrieved from "https://wiki.onqpr.com/pa/index.php?title=ExtractSap_Function&oldid=22071"