SQL Server からPonparemall へストアドプロシージャで自動連携:SQL Gateway

製品をチェック

無償トライアル:

ダウンロードへ

製品の詳細情報へ:

Ponparemall ODBC Driver

Ponparemall ODBC ドライバーは、ODBC 接続をサポートする任意のアプリケーションからライブなPonparemall データに直接接続できる強力なツールです。標準のODBC ドライバーインタフェースを使用して、データベースのようにPonparemall にアクセスし、在庫、商品、受注などの読み出し、書き込み、および更新を実行できます。

データ連携でお困りですか?

お問い合わせ

SQL Server のCDC 機能とストアドプロシージャを使って、リンクサーバー経由でPonparemall のデータを定期的に更新する方法



トップ画像。SQL Gateway 経由でPonparemall をSQL Server へ連携。

シナリオ

SQL Serverにある既存テーブルの更新された値を、Ponparemall に自動的に取り込んでいきます。構成は、Ponparemall とSQL Server の間にSQL Gateway を配置し、SQL Gateway からデータソースへのアクセスはHTTP リクエスト、SQL Gateway からクライアントへはTDS プロトコル(SQL Server)でアクセスできるようにします。

SQL Gateway を使うことで、SQLServer のリンクサーバー経由やMySQL へ接続できるツール、サービスなどからもSaaS にアクセスすることが可能になります。もちろん参照だけでなく更新も可能です。

手順

やることは大枠でこちらの内容です。

  • CData ODBC Driver インストール & DSN 設定
  • SQL Gateway TDS プロトコルI/F を作成
  • SQL Server リンクサーバーを作成
  • SQL Server CDC 機能をオンにする
  • SQL Server 更新用ストアドプロシージャの作成
  • SQL Server エージェントでスケジュール設定

CData ODBC Driver のインストール & DSN 設定

CData ODBC Driver をインストールすると SQL Gateway もあわせてインストールされるようになっています。ではこちらのリンクからPonparemall をクリックして、インストーラーをダウンロードします。ダウンロードボタンをクリックしてPonparemall ODBC Driver をダウンロードします。

ダウンロードしたPonparemallODBCDriver.exe をダブルクリックしてインストールを開始します。
インストール自体はデフォルトのまま進んで構いません。インストール後にDSN 設定画面が開きます。

  1. 接続プロパティの指定がまだの場合は、DSN (データソース名)で行います。Microsoft ODBC データソースアドミニストレーターを使ってODBC DSN を作成および設定できます。一般的な接続プロパティは以下のとおりです:

    • InventoryUserId
    • InventoryPassword

    Microsoft ODBC データソースアドミニストレーターで必要なプロパティを設定する方法は、ヘルプドキュメントの「はじめに」をご参照ください。

    Ponparemall に接続するには、ShopUrl、InventoryUserId、InventoryPassword、OrderUserId、OrderPassword、PaymentUserId、およびPaymentPassword が必要です。

    Ponparemall へのアクセスの設定

    Ponparemall には各API のId とPassword が必要です。取得するには、以下の手順に従ってください。

    • Ponparemall Manager にログインし、メニューの「各種申請」の「WebAPI利用申請」をクリックします。
    • Web API メニューの「WebAPIサービス利用申請」をクリックし、利用申請を行います。
    • 後日、Ponparemall から必要なId とPassword が送られてきます。

    Ponparemall アカウントの認証

    次の接続プロパティを設定して接続します。

    • ShopUrl:接続先のPonparemall Shop URL を設定。「http://storetst4.ponparemall.com/sample/」に含まれる「sample」の部分を入力します。
    • InventoryUserId:「在庫API」のユーザID を設定。このプロパティは、Inventory テーブルにアクセスする場合に必要です。
    • InventoryPassword:「在庫API」のパスワードを設定。このプロパティは、Inventory テーブルにアクセスする場合に必要です。
    • OrderUserId:「受注API」のユーザID を設定。このプロパティは、Orders テーブルにアクセスする場合に必要です。
    • OrderPassword:「受注API」のパスワードを設定。このプロパティは、Orders テーブルにアクセスする場合に必要です。
    • PaymentUserId:「決済API」のユーザID を設定。このプロパティは、Payments テーブルにアクセスする場合に必要です。
    • PaymentPassword:「決済API」のパスワードを設定。このプロパティは、Payments テーブルにアクセスする場合に必要です。

ここまでで、CData Ponparemall ODBC Driver の設定が完了しました。続いてはSQL Gateway の設定に入っていきます。

SQL Gateway でTDS プロトコルI/F を作成

Windows メニューにある SQL Gateway クリックして起動します。

カバー

SQL Gateway が起動されたらこのようなコンソール画面が表示されます。最初に「サービス」タブから追加ボタンをクリックします。

カバー

以下の内容で設定します。

  • サービス名:任意
  • I/F:サービス名下のラジオボタンでTDS(SQL Server)を選択
  • データソース:先ほど設定したDSN
  • ポート:未使用のもの
カバー

外部からこの SQL Gateway にアクセスする際のユーザー情報を作成します。

カバー

作成後は「変更を保存」→「開始」の順でボタンをクリックします。サービスが正常に開始されると、このように緑色のランプが点灯します。

カバー

これで データソースからSQL Gateway 間の接続設定と、TDS プロトコルでのインタフェース設定が完了しました。

リンクサーバーの作成

次にSQL Server とSQL Gateway をつなぐために、リンクサーバーを作成していきます。
SSMS で対象の SQL Server に接続し、「サーバーオブジェクト」->「リンクサーバー」から右クリックでリンクサーバーの新規作成を行います。

  • リンクサーバー名:任意
  • サーバーの種類:その他
  • プロバイダー:SQL Server Native Client
  • データソース:SQL Gateway を起動しているインスタンスとポート
    「インスタンス,ポート」の書き方。
  • カタログ:SQL Gateway で指定したDSN
カバー

セキュリティに移動して、SQL Gateway で作成したユーザー情報を入力して設定は完了です。

カバー

これでPonparemall のオブジェクトがリンクサーバーにテーブルとして表示されるようになりました。

カバー

SQL Server の設定(CDC)

まずは CDC(Change Data Capture) 機能をオンにしていきます。ちなみにこの CDC とは、各テーブルのデータの変更情報を保持してくれる機能です。
※CDC はStandard Edition 以上、もしくはDeveloper Edition で利用可能です。

今回はこのCDC 機能を使って変更データを抽出し、Ponparemall に連携していきます。

CDC 機能を利用するデータベースを指定して以下のSQL を実行します。

USE cdata; GO EXECUTE sys.sp_cdc_enable_db; GO

CDC の対象とするテーブルを設定します。

EXEC sys.sp_cdc_enable_table @source_schema = N'dbo', @source_name = N'顧客データ_東北支店', @role_name = N'cdc_role', @supports_net_changes = 1 GO カバー


各種パラメータの説明はMicrosoft の公式ドキュメントをご参照ください。上記以外にも設定できるようです。

これで対象 DB のシステムテーブルに、変更情報を保持してくれるテーブルが表示されました。

カバー

何も変更していない状態では、dbo_顧客データ_東北支店_CT テーブルのレコードはない状態です。

カバー

CDC 対象の顧客データ_東北支店 テーブルにあるメールアドレス列の値を変更してみます。

カバー

もう一度、dbo_顧客データ_東北支店_CT テーブルを参照すると変更前後のレコードが格納されました。ちなみに$operation=3 で更新前、4で更新後となります。

カバー

これで SQL Server で変更したデータを確認することが出来るようになりました!

ストアドプロシージャの作成

シンプルに CDC テーブルの Name という項目だけを使用して、それをリンクサーバーの [Ponparemall].[顧客管理(営業支援パック)] テーブルに Insert、Delete、Update で連携するということをやってみます。

CREATE PROCEDURE PonparemallSP AS BEGIN SET ANSI_NULLS ON SET ANSI_WARNINGS ON --カーソルの値を取得する変数宣言 DECLARE @Id varchar(50) DECLARE @email varchar(50) DECLARE @Operation int --カーソル定義 DECLARE CUR_1 CURSOR FOR SELECT a.[RecordId], a.[メールアドレス],a.[__$operation] FROM [cdata].[cdc].[dbo_顧客データ_東北支店_CT] AS a, (SELECT[RecordId] , MAX([__$seqval]) AS seqval FROM [cdata].[cdc].[dbo_顧客データ_東北支店_CT] GROUP BY [RecordId]) AS b WHERE a.[RecordId] = b.[RecordId] AND a.[__$seqval] = b.seqval --'3'は更新前レコード AND a.[__$operation] <> '3' --カーソルオープン OPEN CUR_1; FETCH NEXT FROM CUR_1 INTO @Id,@email,@Operation; WHILE @@FETCH_STATUS = 0 BEGIN -- 削除レコードの場合 IF @Operation = 1 BEGIN DELETE FROM [PONPAREMALL_GATEWAY].[CData Ponparemall Sys].[Ponparemall].[顧客管理(営業支援パック)] WHERE RecordId=@Id; END -- 新規レコードの場合 ELSE IF @Operation = 2 BEGIN INSERT INTO [PONPAREMALL_GATEWAY].[CData Ponparemall Sys].[Ponparemall].[顧客管理(営業支援パック)] ([メールアドレス]) Values(@email); END -- 更新レコードの場合 ELSE IF @Operation = 4 BEGIN UPDATE [PONPAREMALL_GATEWAY].[CData Ponparemall Sys].[Ponparemall].[顧客管理(営業支援パック)] SET [メールアドレス] = @email WHERE RecordId=@Id; END --次のレコード FETCH NEXT FROM CUR_1 INTO @Id,@email,@Operation; END --カーソルクローズ CLOSE CUR_1; DEALLOCATE CUR_1; --CDC変更テーブルのレコード削除 Truncate table [cdc].[dbo_顧客データ_東北支店_CT] END

スケジュール設定

最後にSQL Server エージェントでストアドプロシージャのスケジュール実行を設定します。

カバー

ステップで新規作成から、先ほど作成したストアドプロシージャを実行するよう設定します。

カバー

実行タイミングを設定します。今回は夜中に毎日実行されるようスケジューリングしました。

カバー

設定後はこのように次にジョブが実行される時間やステータスなどが表示されています。

カバー

これで設定が完了しました!

SQL Server のデータを更新

以下は変更テーブルの中身です。赤枠内のレコードが リンクサーバーを介してPonparemall に反映される想定です。
赤枠レコードの内訳は、

  • 1行目:削除レコード(__$operation:1)
  • 2行目:更新前レコード(__$operation:3)
  • 3行目:更新後レコード(__$operation:4)
  • 4行目:追加レコード (__$operation:2)
カバー

ではこれでAM 3:00に実行されるのを待ってみると、ジョブが実行されました。

カバー

リンクサーバーの[Ponparemall].[顧客管理(営業支援パック)] テーブルを参照すると、先ほど確認したレコードが削除も含めて反映されていました。

カバー

これであとはこのまま起動しておくだけで、SQL Server で更新したレコードをもとにPonparemall が更新されるようになりました。

おわりに

いかがでしたでしょうか。SQL Server の CDC 機能とストアドプロシージャ、リンクサーバーでPonparemall に自動連携できるようになりました。

CData ODBC Driver for Ponparemall は 30日間の無償評価版がご利用可能です。こちらから是非お試しください。