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 パラメータが必要です。
Install the module:
Install-Module ESalesManagerCmdlets
Connect to e-Sales Manager:
$esalesmanager = Connect-ESalesManager -User $User -Password $Password -URL $URL -TenantId $TenantId
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'}
Save a list of the column names from the returned data.
$columns = ($data | Get-Member -MemberType NoteProperty | Select-Object -Property Name).Name
With the data and column names collected, you are ready to replicate the data into a MySQL database.
Install the module:
Install-Module MySQLCmdlets
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
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.
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')}