PowerShell Scripting to Replicate SAP Netweaver Gateway Data to MySQL

The CData Cmdlets for SAP Netweaver Gateway offer live access to SAP Netweaver Gateway data from within PowerShell. Using PowerShell scripts, you can easily automate regular tasks like data replication. This article will walk through using the CData Cmdlets for SAP Netweaver Gateway and the CData Cmdlets for MySQL in PowerShell to replicate SAP Netweaver Gateway data to a MySQL database.

After obtaining the needed connection properties, accessing SAP Netweaver Gateway data in PowerShell and preparing for replication consists of four basic steps.

SAP Gateway allows both basic and OAuth 2.0 authentication. You can use basic authentication to connect to your own account, or you can use OAuth to enable other users to retrieve data from your service with their accounts. In addition to authenticating, set the following connection properties to access SAP Gateway tables.

• Url: Set this to the URL of your environment, or to the full URL of the service. For example, the full URL might appear as: https://sapes5.sapdevcenter.com/sap/opu/odata/IWBEP/GWSAMPLE_BASIC/. In this example, the environment URL would just be: https://sapes5.sapdevcenter.com.
• Namespace: Set the appropriate Service Namespace. In the example above, IWBEP is the namespace. It is optional if the full URL to the service is specified.
• Service: Set this to the service you want to retrieve data from. In the example above, the service is GWSAMPLE_BASIC. It is not required if the full URL is specified.

Authenticate via Basic Authentication

In basic authentication, you use your login credentials to connect. Set the following properties:

• User: This is the username you use to log in to SAP Gateway.
• Password: This is the password you use to log in to SAP Gateway.

Authenticate via OAuth Authentication

You can connect to SAP Gateway using the embedded OAuth connectivity (without setting any additional authentication connection properties). When you connect, the OAuth endpoint opens in your browser. Log in and grant permissions to complete the OAuth process. See the OAuth section in the online Help documentation for more information on other OAuth authentication flows.

Collecting SAP Netweaver Gateway Data

1. Install the module:

   Install-Module SAPGatewayCmdlets

2. Connect to SAP Netweaver Gateway:

   $sapgateway = Connect-SAPGateway -User $User -Password $Password -URL $URL

3. Retrieve the data from a specific resource:

   $data = Select-SAPGateway -Connection $sapgateway -Table "SalesOrderLineItems"

   You can also use the Invoke-SAPGateway cmdlet to execute pure SQL-92 statements:

   $data = Invoke-SAPGateway -Connection $sapgateway -Query 'SELECT * FROM SalesOrderLineItems WHERE Quantity = @Quantity' -Params @{'@Quantity'='15'}

4. Save a list of the column names from the returned data.

   $columns = ($data | Get-Member -MemberType NoteProperty | Select-Object -Property Name).Name

Inserting SAP Netweaver Gateway Data into the MySQL Database

With the data and column names collected, you are ready to replicate the data into a MySQL database.

1. Install the module:

   Install-Module MySQLCmdlets

2. Connect to MySQL, using the server address and port of the MySQL server, valid user credentials, and a specific database with the table in which the data will be replicated:

   $mysql = Connect-MySQL -User $User -Password $Password -Database $Database -Server $Server -Port $Port

3. Loop through the SAP Netweaver Gateway data, store the values, and use the Add-MySQL cmdlet to insert the data into the MySQL database, one row at a time. In this example, the table will need to have the same name as the SAP Netweaver Gateway resource (SalesOrderLineItems) and to exist in the database.

   $data | % { $row = $_ $values = @() $columns | % { $col = $_ $values += $row.$($col) } Add-MySQL -Connection $mysql -Table "SalesOrderLineItems" -Columns $columns -Values $values }

You have now replicated your SAP Netweaver Gateway data to a MySQL database. This gives you freedom to work with SAP Netweaver Gateway data in the same way that you work with other MySQL tables, whether that is performing analytics, building reports, or other business functions.

Notes

• Once you have connected to SAP Netweaver Gateway and MySQL in PowerShell, you can pipe command results to perform the replication in a single line:

  Select-SAPGateway -Connection $sapgateway -Table "SalesOrderLineItems" | % { $row = $_ $values = @() $columns | % { $col = $_ $values += $row.$($col) } Add-MySQL -Connection $mysql -Table "SalesOrderLineItems" -Columns $columns -Values $values }

• If you wish to replicate the SAP Netweaver Gateway data to another database using another PowerShell module, you will want to exclude the Columns, Connection, and Table columns from the data returned by the Select-SAPGateway cmdlet since those columns are used to help pipe data from one CData cmdlet to another:

  $columns = ($data | Get-Member -MemberType NoteProperty | Select-Object -Property Name).Name | ? {$_ -NotIn @('Columns','Connection','Table')}