Access + Node.js + MySQLでリモート接続

Microsoft Accessは閉じたLAN内で使う分には申し分無い開発環境なのですが、リモートのDBサーバに接続したり、クラウドのSQLに接続して使ったシステムを構築する場合、いくつか気を遣う場面が存在します。とりわけ

  1. ODBC経由でのリンクテーブルだと数万件のテーブルを見るだけでも遅くてカクカク
  2. 単純なリンクテーブルの場合、そのテーブル内のデータが全部丸見えで、ケースによっては困る。
  3. ADOを使ったインメモリレコードセットは接続時は遅いがスクロールは早い。ADOを使いこなせないといけない
  4. MySQLなどの場合、Accessでクライアントを作る場合、クライアント側にODBCドライバと接続設定を追加する手間がある。DSN設定も面倒ですね。
  5. Electronでクライアントを作る場合、ODBCドライバは不要ですが
  6. VBAのようにクライアント側をあれこれ流用できない(Outlook起動してメール送り付けるなど)
  7. セキュリティ面から、MySQLサーバへ直接クライアントから接続させたくない(localhostからの接続だけ受け付けたい)
  8. VBAからは資格情報マネージャに直接アクセスできないので、パスワードの保存に気を使う

などなど。3.は割とこれまで業務用のAccessでは使われてきた手だと思います。ODBC、インメモリに加えて今回、Node.jsを間に加えてMySQLへREST API的に接続する手法をやってみたいと思います。もちろん、ODBCドライバもリンクテーブルも利用しません。

今回使用するファイルやライブラリ

今回はクライアント側およびサーバー側にいくつかのロジックを追加している為、追加のモジュールが必要となっています。

※MySQLのサーバ管理はHeidiSQLを利用すると簡単に管理ができるようになります。オススメ。

サーバー側(Node.js)

事前準備

Ubuntu Linux 18.04 LTSに対して、MySQL Server 5.7をインストールしてあります。また、Node.jsもインストール済みとします。サーバ側での実質追加モジュールはexpressなので、以下の手順で今回の環境を構築しましょう。この辺のくだりはこちらで詳しい作業を記録しておきましたので、参考にしてみてください。

  1. 適当にホームディレクトリ内にフォルダを作っておく(例:mysqlapi
  2. ターミナルを起動し、1.のディレクトリ内に移動し、npm init -yを実行する。package.jsonが作成されます。
  3. 続けて、ターミナルにて npm install express --save にて、expressを導入する
  4. 続けて、ターミナルにて npm install promise-mysql にて、promise-mysqlを導入する
  5. フォルダ内にindex.jsという空のファイルを作っておく。ここにコードを記述しておきます。
  6. 予め、MySQLにtestdbというデータベースといくつかのテーブルを適当に作っておきます。
  7. コードを記述したら、node index.jsにてスクリプトを起動します。Port:3200にて待機し始めます。

図:HeidiSQLでDB内を見てみた様子

図:スクリプト起動中の様子

ソースコード

基本パート

  • Cryptoモジュール読み込み時のkeyおよびiveについては、Access側で使ってるものと同じものを指定しています。
  • keyは文字通り暗号化する為のキー。iveは暗号化の際に用いられる初期化ベクトルと呼ばれるもの。鍵と同じ文字数でランダムなものを使います。
  • 今回はクライアント側からDB接続用パスワードはAES128bitで暗号化された状態で送っているので、Node.js側でデコードしないと、DB接続ができません。その為にこのモジュールを利用しています。
  • Web API定義をする為にexpressを読み込み、別途body-parserも読み込みます(express4からの仕様)
  • body-parserの初期化もセットで行う。初期化をしておかないと、Access側からのリクエストを取得する事ができなくなります(POST通信の場合)。
  • 今回はPort:3200にて待ち受けするようにしました。デーモン化する為にforeverを使うと尚良いでしょう。

MySQL接続パート

今回通常のリクエスト(SELECT文など)の場合にはGET通信とし、レコードの追加リクエスト(INSERT)などの場合にはPOST通信として受けるようにいくつかのエンドポイントを用意しました。エンドポイントに対してAccess側から様々な引数を渡して、Node.js側で受け取ってあげる事でエンドポイント毎に違う処理を実装し、Web APIのような作りを実現しています。

GET通信

  • /api/v1/userinfoとしてエンドポイントを設定しています。これに文字列を繋げてGET通信として受信します。
  • 引数は、queryとして受け取れ自動的にJSON形式になって取得が可能です。
  • passwordはAES128bitで暗号後、Base64でエンコードされた形で送られてくるので、Cryptoの復号化する時のパラメータとしてBase64を指定しています。
  • uidの値でフィルタしたデータをSELECT文で取得し、クライアント側に返しています。
  • get通信でアクセスURLを組み立ててブラウザで見ると、JSON形式で対象のレコードが返ってくるのを確認する事が可能です。
  • 通常、パスワードなどのセンシティブな情報を送る場合には、GETではなくPOSTで通信するのが普通です。
  • GETのURLは、http://10.0.0.1/api/v1/userinfo?uid=12345&pw=abudarakataburaといった組み立てになります。
POST通信

  • /api/v1/insertmanとしてエンドポイントを設定し、JSON形式でPOST通信で送られてきたデータを引数から受け取ります。
  • クライアント側からのデータを送信し、Insert文等を発行するPOST通信で受け取るエンドポイントです。
  • POST通信なのでbody-parserを介して、URLデコードされた形でリクエストパラメータが取得できるようになります。
  • passwordはAES128bitで暗号後、Base64でエンコードされた形で送られてくるので、Cryptoの復号化する時のパラメータとしてBase64を指定しています。
  • クライアント側から送る情報は最小限なので、社員IDをもとにuseridテーブルから追加の情報を取得しレコードを追加する仕組みにしています(ユーザに毎回同じような情報を入れてもらう必要がなくなるため) - uidgetsql関数が担当。
  • その後、insdatasql関数にて、Insertするレコードを生成して、実行。その後、resultからinsertIdとして「オートインクリメントで発行されたレコードのID」を取得して返しています。
  • 最後に返されたIDをもってseledatasql関数を持ってして追加されたレコードを取得し、Access側へとres.jsonにてデータの塊を返しています。

クライアント側(Access)

サーバ設定登録画面

今回のプログラムはサーバ設定を保存するダイアログにて、パスワードをAES128bitで暗号化するとともに、レジストリに保存するようにしています。データ送信時は暗号化したまま、パスワードパラメータを送って、Node.js側で復号化する仕組みです。ここでは、データ保存時に暗号化しレジストリに保存する所までを記載しています。

パスワードのテキストボックスのみ定型入力プロパティは「パスワード」として設定しているので、*で表示するようにしています。

  • aes128encodeおよびaes128decodeの2つは、今回利用させていただいたライブラリを使って暗号化、復号化する関数です。
  • Form_loadにて暗号化パスワードは復号化されてボックスに入れています。
  • 保存時にパスワードを暗号化してレジストリに保存しています。
  • サーバアドレス、ID、パスワードの3つをレジストリにエントリーとして追加しています。

図:こんな感じで表示、登録できるダイアログです

データ送信側設定

ユーザ側からは、例えばワークフローシステムのように何かの申請を送ったり、現在の登録データの取得リクエストなどを送りますが、なるべく個人データはサーバ側に登録してあるデータを流用し、ユーザの入力負担を減らすように作っています。

  • Generalプロシージャにて、Node.js側で用意したエンドポイントを定数で宣言追加しています。実際のURLはサーバアドレスやパラメータを繋げて動的にコード内で組んでいます。
  • Node.js側から送られてくるデータはJSON形式であるため、CallByName関数でパース処理をしています。
  • 会社利用を想定しているため、WinHttpオブジェクトにプロキシ利用の設定を入れてあります。
  • GET通信の場合、データはURLにつなげて送り込むので、URL組み立て時につなげています。
  • POST通信の場合、JSON形式でNode.js側に送る必要があるので、Dictionaryオブジェクトを利用してくみ上げ、VBA-JSONのConvertToJsonにて変換し、送り付けています。
  • WinHttpで送信時にURLエンコードはなされているので、個別データを別途URLエンコードする処理は加えていません。

複数レコードを一度に送る

前項では1レコードをJSON化して送っていますが、複数レコードをまとめて送りたい時もあります。この時のCollectionとDictionaryの使い方次第で、連想配列を組み上げて送る事が可能です。

この時のJSONデータの構築結果は以下の通り。

Node.js側で、req.body.shipToで値を取り出せば、連想配列データとして取得する事が可能です。以下のようなコードをAPIとして用意しておきます。

関連リンク

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です

日本語が含まれない投稿は無視されますのでご注意ください。(スパム対策)