Sage US Data をMySQL にレプリケーションするPowerShell スクリプト

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

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

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

The Application Id and Company Name connection string options are required to connect to Sage as a data source. You can obtain an Application Id by contacting Sage directly to request access to the Sage 50 SDK.

Sage must be installed on the machine. The Sage.Peachtree.API.dll and Sage.Peachtree.API.Resolver.dll assemblies are required. These assemblies are installed with Sage in C:\Program Files\Sage\Peachtree\API\. Additionally, the Sage SDK requires .NET Framework 4.0 and is only compatible with 32-bit applications. To use the Sage SDK in Visual Studio, set the Platform Target property to "x86" in Project -> Properties -> Build.

You must authorize the application to access company data: To authorize your application to access Sage, restart the Sage application, open the company you want to access, and connect with your application. You will then be prompted to set access permissions for the application in the resulting dialog.

While the compiled executable will require authorization only once, during development you may need to follow this process to reauthorize a new build. To avoid restarting the Sage application when developing with Visual Studio, click Build -> Configuration Manager and uncheck "Build" for your project.

Collecting Sage US Data

  1. Install the module:

    Install-Module Sage50USCmdlets
  2. Connect to Sage US:

    $sage50us = Connect-Sage50US -ApplicationId $ApplicationId -CompanyName $CompanyName
  3. Retrieve the data from a specific resource:

    $data = Select-Sage50US -Connection $sage50us -Table "Customer"

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

    $data = Invoke-Sage50US -Connection $sage50us -Query 'SELECT * FROM Customer WHERE Name = @Name' -Params @{'@Name'='ALDRED'}
  4. Save a list of the column names from the returned data.

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

Inserting Sage US 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 Sage US 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 Sage US 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 Sage US data to a MySQL database. This gives you freedom to work with Sage US 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 Sage US and MySQL in PowerShell, you can pipe command results to perform the replication in a single line:

    Select-Sage50US -Connection $sage50us -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 Sage US 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-Sage50US 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')}
 
 
ダウンロード