e-Sales Manager Data をMySQL にレプリケーションするPowerShell スクリプト

PowerShell でシンプルなスクリプトを記述し、e-Sales Manager data をMySQL データベースにレプリケーション(複製)。

CData Cmdlets for e-Sales Manager をつかって、PowerShell からリアルタイムe-Sales Manager data に連携できます。データ同期などのタスクの連携にぴったりの製品です。 本記事では、PowerShell からCData Cmdlets for e-Sales Manager およびCData Cmdlets for MySQL を使って、同期スクリプトを作成して実行します。

After obtaining the needed connection properties, accessing e-Sales Manager data in PowerShell and preparing for replication consists of four basic steps.

e セールスマネージャー Remix に接続するには、User、Passowrd、URL、TenantId パラメータが必要です。

  • User:API を実行するためのログインユーザーのユーザーID。
  • Password:API を実行するためのログインユーザーのユーザーパスワード
  • URL:e-Sales Manager Remix エンドポイントへのURL。例:https://XXX.softbrain.co.jp
  • TenantId:e-Sales Manager Remix テナント名のTenantd。例:cdata

Collecting e-Sales Manager Data

  1. Install the module:

    Install-Module ESalesManagerCmdlets
  2. Connect to e-Sales Manager:

    $esalesmanager = Connect-ESalesManager -User $User -Password $Password -URL $URL -TenantId $TenantId
  3. Retrieve the data from a specific resource:

    $data = Select-ESalesManager -Connection $esalesmanager -Table "Customer"

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

    $data = Invoke-ESalesManager -Connection $esalesmanager -Query 'SELECT * FROM Customer WHERE Address = @Address' -Params @{'@Address'='CustomerAddress'}
  4. Save a list of the column names from the returned data.

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

Inserting e-Sales Manager 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 e-Sales Manager 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 e-Sales Manager resource (Customer) and to exist in the database.

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

You have now replicated your e-Sales Manager data to a MySQL database. This gives you freedom to work with e-Sales Manager 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 e-Sales Manager and MySQL in PowerShell, you can pipe command results to perform the replication in a single line:

    Select-ESalesManager -Connection $esalesmanager -Table "Customer" | % { $row = $_ $values = @() $columns | % { $col = $_ $values += $row.$($col) } Add-MySQL -Connection $mysql -Table "Customer" -Columns $columns -Values $values }
  • If you wish to replicate the e-Sales Manager 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-ESalesManager 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')}
 
 
ダウンロード