Connect to Your SAP System – ERPL - Connect DuckDB to the SAP ecosystem

Introduction

Connecting DuckDB to your SAP system can be achieved in one of two ways. The first involves setting parameters directly within DuckDB, while the second utilizes the sapnwrfc.ini configuration file. Both methods have their unique advantages and depend on your specific circumstances and needs.

If you choose to set the parameters directly in DuckDB, this allows for a straightforward, code-based approach. It gives you the ability to clearly define and manipulate connection parameters within your SQL or scripting environment. This method is ideal for scenarios where you wish to maintain tighter control over the connection settings, or when changes to these parameters are expected frequently.

Alternatively, utilizing the sapnwrfc.ini configuration file is beneficial for environments where the SAP connection parameters are standardized across the system or when you desire to keep the connection parameters outside of your DuckDB scripts or applications. This method provides a consistent environment for connecting to SAP from multiple applications or scripts.

Both methods involve understanding the necessary parameters and the way they are used within both DuckDB and SAP. This section will provide step-by-step guidance for both approaches, helping you decide which method best suits your needs, and guiding you through the process of connecting DuckDB to your SAP system.

Using Connection Parameters directly in your Environment

import duckdb
con = duckdb.connect(':memory:', config={'allow_unsigned_extensions' : 'true'})

# Load ERPL extension
con.sql("LOAD erpl;")
# Alternative:
# con.sql("LOAD '[PATH TO ERPL EXTENSION]/erpl.duckdb_extension';")

# Setup connection parameters
con.sql("SET sap_ashost = '[HOST IP ADRESS]';")
con.sql("SET sap_sysnr = '00';")
con.sql("SET sap_user = '[USERNAME]';")
con.sql("SET sap_password = '[PASSWORD]';")
con.sql("SET sap_client = '001';")
con.sql("SET sap_lang = 'EN';")

# Test your connection by pinging the server
con.sql("PRAGMA sap_rfc_ping;")

library(duckdb)

# You need to set allow_unsigned_extensions to true for Loading the ERPL extension 
config <- list()
config["allow_unsigned_extensions"] <- "true"

drv <- duckdb(config=config)
con <- dbConnect(drv)

# Load ERPL extension
dbExecute(con, "LOAD erpl;")
# Alternative:
# dbExecute(con, "LOAD '[PATH TO ERPL EXTENSION]/erpl.duckdb_extension';")

# Setup connection parameters
dbExecute(con, "SET sap_ashost = '[HOST IP ADRESS]';")
dbExecute(con, "SET sap_sysnr = '00';")
dbExecute(con, "SET sap_user = '[USERNAME]';")
dbExecute(con, "SET sap_password = '[PASSWORD]';")
dbExecute(con, "SET sap_client = '001';")
dbExecute(con, "SET sap_lang = 'EN';")

# Test your connection by pinging the server
dbGetQuery(con, "PRAGMA sap_rfc_ping;")

# Output:
#    msg
# 1 PONG

using DuckDB

config = DuckDB.Config()
DuckDB.set_config(config, "allow_unsigned_extensions", "true")
con = DBInterface.connect(DuckDB.DB, ":memory:", config)

DBInterface.execute(con, "LOAD erpl;")
# Alternative:
# DBInterface.execute(con, "LOAD '[PATH TO ERPL EXTENSION]/erpl.duckdb_extension';")

# Setup connection parameters
DBInterface.execute(con, "SET sap_ashost = '[HOST IP ADRESS]'")
DBInterface.execute(con, "SET sap_sysnr = '00';")
DBInterface.execute(con, "SET sap_user = '[USERNAME]';")
DBInterface.execute(con, "SET sap_password = '[PASSWORD]';")
DBInterface.execute(con, "SET sap_client = '001';")
DBInterface.execute(con, "SET sap_lang = 'EN';")

DBInterface.execute(con, "PRAGMA sap_rfc_ping;")

# Output:
# 1×1 DataFrame
#  Row │ msg     
#      │ String? 
# ─────┼─────────
#    1 │ PONG

# Load ERPL extension
LOAD erpl;
# Alternative:
# LOAD '[PATH TO ERPL EXTENSION]/erpl.duckdb_extension';

# Setup connection parameters
SET sap_ashost = '[HOST IP ADRESS]';
SET sap_sysnr = '00';
SET sap_user = '[USERNAME]';
SET sap_password = '[PASSWORD]';
SET sap_client = '001';
SET sap_lang = 'EN';

# Ping your SAP server
PRAGMA sap_rfc_ping;

# Output:
# ┌─────────┐
# │   msg   │
# │ varchar │
# ├─────────┤
# │ PONG    │
# └─────────┘

Using `sapnwrfc.ini`

The sapnwrfc.ini file is used for SAP NW RFC (NetWeaver Remote Function Call) SDK configuration. It defines details of how your application - in this case ERPL - should connect to the SAP system. The actual content of the file would typically be based on the specifics of your environment and the SAP systems you are connecting to.

Here’s a basic example of what a sapnwrfc.ini file could look like:

# Global properties
DEST=mysapdest
TRACE=2
USE_SAPGUI=1

# Destination details
[mysapdest]
CLIENT=001
USER=[USERNAME]
PASSWD=[PASSWORD]
LANG=EN
ASHOST=[HOST IP ADRESS]
SYSNR=00

The above is just a basic example. In a real-world scenario, there would be various other parameters you could use, and you should ensure that this file remains confidential as it contains sensitive information.

Before deploying, consult SAP’s official documentation or your SAP team for the accurate configuration details, as mistakes or misconfigurations could impact connectivity and the operation of SAP applications.

SAP Connection Parameters

Currently, the following parameters must be set for a succesfull connection to your SAP system. Others may be included in a future version of this extension (SAPNWRFC.INI Parameters).

Parameter	Description
`sap_ashost`	Host name of a specific SAP application server.
`sap_sysnr`	SAP system number.
`sap_user`	User name in the SAP system or in the portal.
`sap_password`	User password.
`sap_client`	The Client to which to logon.
`sap_lang`	Logon Language.

Introduction

Using Connection Parameters directly in your Environment

Using sapnwrfc.ini

SAP Connection Parameters

SAP User Roles

Using `sapnwrfc.ini`