ExtractSap Function: Difference between revisions

From QPR ProcessAnalyzer Wiki
Jump to navigation Jump to search
No edit summary
No edit summary
Line 12: Line 12:
* '''SystemNumber''': SAP system’s system number. Mandatory if SystemID is not defined. Corresponds to the "SYSNR" constant in SAP.
* '''SystemNumber''': SAP system’s system number. Mandatory if SystemID is not defined. Corresponds to the "SYSNR" constant in SAP.
* '''SystemID''': SAP system’s three-letter system ID. Mandatory if SystemNumber is not defined. Corresponds to the "SYSID" constant in SAP.
* '''SystemID''': SAP system’s three-letter system ID. Mandatory if SystemNumber is not defined. Corresponds to the "SYSID" constant in SAP.
* '''Options''': Array of clause elements. Specifies the value for parameter OPTIONS in tab 'Import' or function module 'rfc_read_table' in SAP. NOTE: maximum length for one string is 72 characters(SAP limit). Optional. Example: ["VBELN BETWEEN '0000017448'" ,"AND '0000017450'"].
* '''OptionString''': String of clause elements. Specifies the value for parameter OPTIONS in tab 'Import' or function module 'rfc_read_table' in SAP. Maximum length for one string is 72 characters(SAP limit). Over 72 character length OptionString is splited by ' AND ' and ' OR ' to several OPTIONS parameter to avoid SAP 72 charachters limit. Optional. Example: "VBELN BETWEEN '0000017448' AND '0000017450'".
* '''FieldNames''': Comma separated list of field names for columns to be extracted. Default value is empty, which will extract all columns. Specifies the value for parameter FIELDNAME in tab 'Tables' for table 'FIELDS' for function module 'rfc_read_table' in SAP. Optional.
* '''Function''': SAP function name that is called in SAP. Optional. The default value is RFC_READ_TABLE. Another possible function name is BBP_RFC_READ_TABLE.
* '''Language''': SAP language used. Default value is "EN". Optional. Corresponds to the "LANG" constant in SAP.
* '''Language''': SAP language used. Default value is "EN". Optional. Corresponds to the "LANG" constant in SAP.
* '''Rowcount''': Maximum amount of rows to fetch. Specifies the value for parameter ROWCOUNT in tab 'Import' or function module 'rfc_read_table' in SAP. Optional.
* '''Rowskips''': Number of rows to skip. Specifies the value for parameter ROWSKIPS in tab 'Import' or function module 'rfc_read_table'. in SAP. Optional.
* '''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.
* '''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.
* '''IdleTimeout''': If a connection has been idle for more than the defined time in seconds, it will be closed and removed from the connection pool. Default value is "600". Optional. Corresponds to the "IDLE_TIMEOUT" constant in SAP.
* '''IdleTimeout''': If a connection has been idle for more than the defined time in seconds, it will be closed and removed from the connection pool. Default value is "600". Optional. Corresponds to the "IDLE_TIMEOUT" constant in SAP.
Line 19: Line 25:
* '''Mode''': 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.
* '''Mode''': 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.
* '''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. Note that if the query doesn't return any data, the target data table or temporary table is not created.
* '''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. Note that if the query doesn't return any data, the target data table or temporary table is not created.
* '''Rowcount''': Maximum amount of rows to fetch. Specifies the value for parameter ROWCOUNT in tab 'Import' or function module 'rfc_read_table' in SAP. Optional.
* '''Rowskips''': Number of rows to skip. Specifies the value for parameter ROWSKIPS in tab 'Import' or function module 'rfc_read_table'. in SAP. Optional.
* '''Options''': Array of clause elements. Specifies the value for parameter OPTIONS in tab 'Import' or function module 'rfc_read_table' in SAP. NOTE: maximum length for one string is 72 characters(SAP limit). Optional. Example: ["VBELN BETWEEN '0000017448'" ,"AND '0000017450'"].
* '''OptionString''': String of clause elements. Specifies the value for parameter OPTIONS in tab 'Import' or function module 'rfc_read_table' in SAP. Maximum length for one string is 72 characters(SAP limit). Over 72 character length OptionString is splited by ' AND ' and ' OR ' to several OPTIONS parameter to avoid SAP 72 charachters limit. Optional. Example: "VBELN BETWEEN '0000017448' AND '0000017450'".
* '''FieldNames''': Comma separated list of field names for columns to be extracted. Default value is empty, which will extract all columns. Specifies the value for parameter FIELDNAME in tab 'Tables' for table 'FIELDS' for function module 'rfc_read_table' in SAP. Optional.
* '''Function''': SAP function name that is called in SAP. Optional. The default value is RFC_READ_TABLE. Another possible function name is BBP_RFC_READ_TABLE.
* '''[[Importing_Data_from_SAP|ConvertDataTypes]]''': List of SAP data types that are converted into respective expression language 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).
* '''[[Importing_Data_from_SAP|ConvertDataTypes]]''': List of SAP data types that are converted into respective expression language 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).
* '''Ping''': If this is set to true, SAP server is pinged before SAP query execution. Purpose is help troubleshooting SAP connection issues. Optional.
* '''Ping''': If this is set to true, SAP server is pinged before SAP query execution. Purpose is help troubleshooting SAP connection issues. Optional.

Revision as of 12:22, 2 December 2022

ExtractSap function extracts data from an SAP R/3 system and returns it as a DataFlow. The function is in the generic context and also in the project context. When using the secure strings, the function need to be called in the project context as the secure strings are project specific.

Using the ExtractSap function requires installing SAP NetWeaver RFC Library.

Parameters:

  • User: SAP username used to connect to SAP. Mandatory. Corresponds to the "USER" constant in SAP.
  • Password: Password for the SAP user used to connect to SAP. Mandatory. Corresponds to the "PASSWD" constant in SAP.
  • PasswordKey: Secure string key for the stored SAP password. Alternative for the Password property.
  • Client: The SAP backend client. Mandatory. Corresponds to the "CLIENT" constant in SAP.
  • AppServerHost: Hostname or IP of the specific SAP application server, to which all connections shall be opened. Mandatory if MessageServerHost is not defined. Corresponds to the "ASHOST" constant in SAP.
  • MessageServerHost: Hostname or IP of the SAP system’s message server (central instance). Mandatory if AppServerHost is not defined. Corresponds to the "MSHOST" constant in SAP.
  • SystemNumber: SAP system’s system number. Mandatory if SystemID is not defined. Corresponds to the "SYSNR" constant in SAP.
  • SystemID: SAP system’s three-letter system ID. Mandatory if SystemNumber is not defined. Corresponds to the "SYSID" constant in SAP.
  • Options: Array of clause elements. Specifies the value for parameter OPTIONS in tab 'Import' or function module 'rfc_read_table' in SAP. NOTE: maximum length for one string is 72 characters(SAP limit). Optional. Example: ["VBELN BETWEEN '0000017448'" ,"AND '0000017450'"].
  • OptionString: String of clause elements. Specifies the value for parameter OPTIONS in tab 'Import' or function module 'rfc_read_table' in SAP. Maximum length for one string is 72 characters(SAP limit). Over 72 character length OptionString is splited by ' AND ' and ' OR ' to several OPTIONS parameter to avoid SAP 72 charachters limit. Optional. Example: "VBELN BETWEEN '0000017448' AND '0000017450'".
  • FieldNames: Comma separated list of field names for columns to be extracted. Default value is empty, which will extract all columns. Specifies the value for parameter FIELDNAME in tab 'Tables' for table 'FIELDS' for function module 'rfc_read_table' in SAP. Optional.
  • Function: SAP function name that is called in SAP. Optional. The default value is RFC_READ_TABLE. Another possible function name is BBP_RFC_READ_TABLE.
  • Language: SAP language used. Default value is "EN". Optional. Corresponds to the "LANG" constant in SAP.
  • Rowcount: Maximum amount of rows to fetch. Specifies the value for parameter ROWCOUNT in tab 'Import' or function module 'rfc_read_table' in SAP. Optional.
  • Rowskips: Number of rows to skip. Specifies the value for parameter ROWSKIPS in tab 'Import' or function module 'rfc_read_table'. in SAP. Optional.
  • 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.
  • IdleTimeout: If a connection has been idle for more than the defined time in seconds, it will be closed and removed from the connection pool. Default value is "600". Optional. Corresponds to the "IDLE_TIMEOUT" constant in SAP.
  • 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 in SAP.
  • Mode: 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.
  • 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. Note that if the query doesn't return any data, the target data table or temporary table is not created.
  • ConvertDataTypes: List of SAP data types that are converted into respective expression language 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).
  • Ping: If this is set to true, SAP server is pinged before SAP query execution. Purpose is help troubleshooting SAP connection issues. Optional.
  • TraceLevel: Sets SAP trace level, which can be 0, 1, 2, 3 or 4. Setting higher trace level helps to troubleshoot SAP connection issues. sapnco.dll writes log file to the current working folder with for example name'nco_rfc_5484_2.trc'. Optional.

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);