• [アーティクル]

Hey, Scripting Guy!帰ってきたデータベース スクリプト

The Microsoft Scripting Guys

この記事で使用しているコードのダウンロード: HeyScriptingGuy2008_04.exe (151KB)

ご存知のとおり、 もし Scripting Guys に欠点があるとすれば ("もし" ですよ、"もし")、それは、何かを行ったときの影響、または行わなかったときの影響を気にしすぎるということです。たいてい Scripting Guys が何かを行う場合は、自分たちの利益は考えず、他の人の利益を最優先にします。それが自分たちにどんな影響を与えるとしても。

たとえば、データベース スクリプトに関する記事を例に考えてみましょう。包み隠さず正直に言うと、データベース スクリプトに関する記事を執筆することは、Scripting Guys にとって大変な作業です。データベース スクリプトの作成が難しいというわけではありません、この作業は非常に簡単です。問題は、Scripting Guys がデータベースを扱う機会があまりないということです。このため、データベースに関する記事を執筆するときは、いつも少し立ち止まって、自分たちが何をしようとしているかを考えなくてはなりません。皆さんはずっと前からお気付きだと思いますが、Scripting Guy になるために、"考える" という行為はまったく必要ありません。

そもそも Scripting Guys のだれかが実際にそのようなことを考えていたら、Scripting Guys 自体が存在しなかったでしょう。

では、データベース スクリプトに関する記事の執筆がそこまで大変であるにもかかわらず、なぜ Scripting Guys は現在でもこの記事の執筆を続けているのでしょうか。答えは簡単です。それは、記事の執筆を止めたら世界中の皆さんがどうなってしまうかが心配だからです。かのアーサー コナン ドイル氏がシャーロック ホームズの命を絶つ (ホームズは「最後の事件」で崖から転落してその生涯を終えました) ことを決めた理由は、単にもうこの人物に関する話を執筆したくなかったからだそうです。しかしこの後、世界中から激しい抗議の声が上がりました。ある報告によると、ロンドンの人々は、彼の死を耳にしたとき、実際に帽子と腕に喪章を付けて街を歩いたそうです。「最後の事件」は 1893 年に Strand Magazine で発表されましたが、この後 20,000 人の読者が定期購読を中止したといいます。

何ということでしょう。

Scripting Guys は、データベース スクリプトに関する記事の執筆を止めたときに、同じようなことが起こるのではないかと心配しているのです。皆さんを辛い気持ちにさせたり落ち込ませたりしたくはありませんし、20,000 人もの TechNet Magazine の読者が定期購読を中止することなど考えたくもありません。**ですから、Scripting Guys はデータベース スクリプトに関する記事の執筆を続けていくことを、ここに宣言します。いつからですって。では今からにしましょう。

データベースにレコードを追加する

今月は、データベースの操作に関する気の利いたテクニックをいくつか紹介します。これらは、2007 年 9 月号 (technetmagazine.com/issues/2007/09/HeyScriptingGuy) で紹介したテクニックを改良したものです。Scripting Guys もまったく同じ記事を 2 回掲載するほど落ちぶれてはいません。

ばれないようであれば、そうしていたかもしれませんが。

まずは単純な方法でデータベースにレコードを追加してみましょう。C:\Scripts\Inventory.mdb というデータベースがあり、このデータベースに Computers というテーブルが含まれているとします。Computers テーブルには、次のフィールドが含まれています。

  • ComputerName
  • SerialNumber
  • UserName
  • Department

では、このデータベースに新しいコンピュータを追加するにはどうすればよいでしょうか。図 1 のコードをご覧ください。

Figure 1 Adding a record to the Computers table

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source = C:\Scripts\Inventory.mdb"

objRecordSet.Open "INSERT INTO Computers (ComputerName, SerialNumber, UserName, Department)" &  _
    "VALUES ('atl-ws-07', '11234', 'Ken Myer', 'Finance')", _
        objConnection, adOpenStatic, adLockOptimistic

このスクリプトの最初の数行については、詳しい説明を省略します。これらの情報が必要な場合は、スクリプト センター (microsoft.com/technet/scriptcenter) で提供されているリソースを参照してください。説明が必要なのは、定数 adOpenStatic と adLockOptimistic を使用して、レコードセットのカーソルの種類とレコードのロックを制御しているということだけでしょう (格好をつけているわけではなく、このコードが本当に単純であるということです)。ADODB.Connection オブジェクトと ADODB.Recordset オブジェクトのインスタンスを作成したら、次のコマンドを使用してデータベースを開きます。

objConnection.Open _
"Provider = Microsoft.Jet.OLEDB.4.0; " & _
"Data Source = C:\Scripts\Inventory.mdb"

ちなみに、上記のコマンドを実行すると、Microsoft® Access® 2003 データベースが開かれます。Access 2007 データベースを開くには、次のコマンドを使用します。

objConnection.Open _
"Provider = Microsoft.ACE.OLEDB.12.0; " & _
"Data Source = C:\Scripts\Inventory.accdb"

記事の内容から少し離れてしまいますが、これと似た次のコマンドを使用すると、SQL Server® データベースを開くことができます。

objConnection.Open _
"Provider=SQLOLEDB;" & _
"Data Source=atl-sql-01;" & _
"Trusted_Connection=Yes;" & _ "InitialCatalog=Inventory;" & _
"User ID=fabrikam\kenmyer;Password=34DE6t4G!;"

ここまでに紹介したのは、お決まりのコードです。すぐにわかると思いますが、データベース スクリプトの大半は、上記のようなコードから始まります。特に重要なのは、次のコードです。

objRecordSet.Open _
  "INSERT INTO Computers " & _
  "(ComputerName, SerialNumber, " & _
  "UserName, Department) " & _
    "VALUES ('atl-ws-07', 'A14B1234', " & _
      "'Ken Myer', 'Finance')", _
        objConnection, adOpenStatic, _
        adLockOptimistic

このコードでは、INSERT INTO クエリを使用して、Computers テーブルに新しいレコードを追加しています。テーブル名の後に、値を格納するすべてのフィールドの名前を、かっこ内にコンマで区切って記述していることに注意してください。

ここで、「"値を格納するすべてのフィールド" とはどういう意味だろう」という疑問が浮かぶかもしれません。良い質問です。では、この新しいコンピュータにまだ部署名が割り当てられていないとしましょう。このデータベースの Department フィールドには Null 値を指定できるので、単純に Department をリストから除外し、このフィールドに値を割り当てないことにします。つまり、次のようなクエリを記述します。

objRecordSet.Open _
  "INSERT INTO Computers " & _
  "(ComputerName, SerialNumber, " & _
  "UserName) " & _
    "VALUES ('atl-ws-07', 'A14B1234', " & _
      "'Ken Myer')", _
        objConnection, adOpenStatic, _
        adLockOptimistic

値の割り当てと言えば、フィールド名の後の文字列をご覧ください。VALUES キーワードの後に、各フィールドに割り当てる値が指定されています (これらもかっこで囲まれています)。フィールド名は、指定した順序どおりに配置されているわけではありません。たとえば、1 つ目に ComputerName を指定しても、それがデータベースの 1 つ目のフィールドではない場合があります。このことはフィールド名では特に問題ありませんが、値の場合は問題があります。値はフィールドとまったく同じ順序で指定する必要があります。つまり、1 つ目のフィールドに ComputerName を指定した場合、1 つ目の値にはコンピュータ名を指定する必要があります。これに従わなかった場合、問題が発生します (たとえば、ComputerName フィールドにユーザー名やシリアル番号が割り当てられることになります)。

ご覧のとおり、値の割り当てはそれほど複雑ではありませんが、データ型に応じて異なる形式で値を指定する必要があります。日付と文字列値は単一引用符で囲みますが、数値とブール値は単一引用符で囲まないようにします。

値自体に単一引用符が含まれている (O'Brien という名前など) 場合は、単一引用符を 2 つ続けて記述することによって、この文字を "エスケープ" する必要があります。

'O''Brien'

妙な感じがしますが、正しい処理です。

データベースからレコードを削除する

すばらしいスクリプトでしたね。1 つの SQL クエリを実行するだけでデータベースにレコードを追加できるのですから、これ以上のものはないでしょう。私たちの知る限りでは。

でも、1 つのクエリを使用してデータベースから複数のレコードを削除できるスクリプトがあれば、話は別です。

たとえば、人事部を廃部することにしたとします (冗談ではありません。Scripting Guys はいつもマイクロソフトの人事部に非常に悩まされています)。人事部のコンピュータを会社の資産データベースから削除するにはどうすればよいでしょうか。この操作を行うには、図 2 のようなスクリプトを使用します。

Figure 2 Deleting multiple records

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source = C:\Scripts\Inventory.mdb"

objRecordSet.Open "DELETE * FROM Computers Where Department='Human Resources'", _
    objConnection, adOpenStatic, adLockOptimistic

ご覧のとおり、このスクリプトは先ほど新しいレコードを追加するときに使用したスクリプトとまったく同じコードで始まっています。つまり、定数を定義し、オブジェクトを作成した後、Inventory.mdb データベースに接続しています。その後、単純に DELETE クエリを使用して Computers テーブルからすべてのレコード (*) を削除します。少なくとも、Department フィールドの値が "Human Resources" になっているレコードはすべて削除します。

objRecordSet.Open _
  "DELETE * FROM Computers " & _
   "Where Department='Human Resources'", _
    objConnection, adOpenStatic, _
    adLockOptimistic

崖から転落するくらい簡単ですね。

悪気はありませんよ、シャーロック ホームズ。

データベース内のレコードを更新する

人事部の存在を跡形もなく消し去ることができたら、どんなにすばらしいことでしょう (マイクロソフト人事部の皆さんへ : 読者の皆さんと世間一般の人事部について面白おかしく説明しているだけで、マイクロソフトの人事部のことを言っているわけではありませんよ。楽しむつもりなどまったくありません)。そうは言っても、人事部のコンピュータを資産データベースからすべて削除することが最善ではない場合もあります。なぜでしょうか。それは、人事部の従業員がすべてのハードウェアを持ち逃げしない限り (実際にその可能性があるかのように聞こえますが、気にしないでください、冗談は言っていませんから)、それらのコンピュータは引き続き会社で使用されるからです。つまり、データベース内には存在している必要があるということです。そのことを踏まえて、データベースからすべてのレコードを削除するのではなく、単純にそれぞれのレコードを更新しましょう。コンピュータの部署名を "Human Resources" から "None" に変更します。つまり、図 3 のようなスクリプトを実行します。

Figure 3 Updating records

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source = C:\Scripts\Inventory.mdb"

objRecordSet.Open "UPDATE Computers Set Department = 'None' " & _
    "Where Department = 'Human Resources'", _
    objConnection, adOpenStatic, adLockOptimistic

先ほどと同じように、変更した部分は SQL クエリだけです。この場合は、UPDATE クエリを使用して Computers テーブル内のレコードを更新しています。また、UPDATE キーワードの他に次の 2 つのパラメータを追加して、どのようにレコードを更新するか、およびどのレコードを更新するかを指示する必要があります。

  • Set Department = 'None': この部分では、Department フィールドに新しい値を指定しています。また、Set Department = 'None', UserName = 'None' という構文を使用すれば、一度に複数のフィールドに値を指定できます。おわかりのとおり、この構文では Department フィールドと UserName フィールドに None という値を設定しています。
  • Where Department = 'Human Resources': これは、どのレコードを更新するかを指定するための標準的な Where 句です。この場合は、Department フィールドの値が "Human Resources" になっているすべてのレコードを指定しています。この Where 句を記述した理由は単純です。データベース内のすべてのレコードを変更するのではなく、以前人事部に所属していたレコード (コンピュータ) のみを削除する必要があるからです。

ここで、Update クエリを使用したすばらしいテクニックをご紹介しましょう。たとえば、全従業員の給料が 10% アップすることになったとします (Scripting Guys とアーサー コナン ドイル氏との間で少なくとも 1 つ共通していることは、どちらも架空の話を扱っているということです)。次のスクリプトでは、Employees というテーブルを開き、各従業員の Salary フィールドを変更しています。では Salary フィールドには何が設定されるのでしょうか。このフィールドには、従業員の現在の給料からの増加率である 1.1 を設定します。つまり、各従業員の給料を 10% アップするということです (こんな風にして簡単に給料がアップすればいいのですが)。このクエリを次に示します。

objRecordSet.Open _
  "Update Employees " & _
  "Set Salary = (Salary * 1.1)", _
    objConnection, adOpenStatic, _
    adLockOptimistic

すばらしいですね。ご覧のとおり、Update クエリでは計算を実行できます。たとえば、すべての臨時社員の契約を 60 日間延長することになったとしましょう。このような場合は、次のクエリを使用すると便利です。

objRecordSet.Open _
  "Update TempEmployees " & _
  "Set ContractExpirationDate = " & _
  "(ContractExpirationDate + 60)", _
    objConnection, adOpenStatic, _
    adLockOptimistic

簡単にデータを取得する

便利なクエリという話が出たところで、最後にまとめとしてデータベースから簡単にデータを取得する方法をいくつかご紹介します。まずは、単純ですが非常に便利なスクリプトをご紹介します。資産データベースに Price というフィールドが含まれているとします。ある程度予想できると思いますが、このフィールドはコンピュータの価格を表しています。では、組織内で最も高額な 5 台のコンピュータを確認するにはどうすればよいでしょうか。図 4 のサンプル スクリプトをご覧ください。

Figure 4 Sorting the recordset

Const adOpenStatic = 3
Const adLockOptimistic = 3

Set objConnection = CreateObject("ADODB.Connection")
Set objRecordSet = CreateObject("ADODB.Recordset")

objConnection.Open _
    "Provider = Microsoft.Jet.OLEDB.4.0; " & _
        "Data Source = C:\Scripts\Test.mdb" 

objRecordSet.Open "SELECT Top 5 * FROM Computers Order By Price", _
     objConnection, adOpenStatic, adLockOptimistic

objRecordSet.MoveFirst

Do Until objRecordset.EOF
    Wscript.Echo objRecordset.Fields.Item("ComputerName")
    objRecordset.MoveNext
Loop

objRecordSet.Close
objConnection.Close

ご覧のとおり、この SQL クエリでは 2 つの操作を行っています。Price を基準にレコードセットを並べ替え (Order By Price)、上位 5 台 (つまり価格のトップ 5) のコンピュータを取得 (SELECT Top 5) しています。上位 10 台のコンピュータを取得する場合は、次のクエリを使用します。

objRecordSet.Open _
  "SELECT Top 10 * FROM Computers " & _
  "Order By Price", _
    objConnection, adOpenStatic, _
    adLockOptimistic

注 : 今回、ループ処理によってレコードセット内のレコードを 1 つずつ処理して、各コンピュータの名前を表示するコードを追加しました。以前のスクリプトではデータを返して表示する必要がなかったので、このコードは使用しませんでした。

次のクエリを使用すると、価格が上位 10% のコンピュータを取得することができます。

objRecordSet.Open _
  "SELECT Top 10 PERCENT * " & _
  "FROM Computers Order By Price", _
    objConnection, adOpenStatic, _
    adLockOptimistic

では、最も低額なコンピュータを取得するにはどうすればよいでしょうか。同じ方法を使用すれば問題ありませんが、今回はレコードセットを降順 (最低額から最高額の順) に並べ替えます。つまり、次のように DESC を使用して、レコードセットを降順に並べ替えるよう指示します。

objRecordSet.Open _
  "SELECT Top 5 * FROM Computers " & _
  "Order By Price DESC", _
    objConnection, adOpenStatic, _
    adLockOptimistic

どうですか。便利なスクリプトですよね。

さらに 2 つ面白いものをご紹介します。たとえば、データベースに Budgeted というフィールドが含まれているとします。このフィールドには、特定のコンピュータ用に計上された予算が値として格納されます。では、この予算と実際にかかった金額を比較するにはどうすればよいでしょうか。次のクエリを使用すると、予算を上回っているコンピュータの一覧を返すことができます。

objRecordSet.Open _
  "SELECT * FROM Computers " & _
  "Where (Budgeted < Price)", _
    objConnection, adOpenStatic, _
    adLockOptimistic

一方、次のクエリを使用すると、すべてのコンピュータの平均価格を計算し (SELECT AVG(Price) FROM Computers)、平均価格を下回っているコンピュータの一覧を返すことができます。

objRecordSet.Open _
  "SELECT * FROM Computers " & _
  "WHERE Price < " & _
  "(SELECT AVG(Price) FROM Computers)", _
    objConnection, adOpenStatic, _
    adLockOptimistic

そうです、Where 句の中で SELECT クエリを使用しています。このしくみですか。その話はまた別の機会に譲ります。しかし、ご覧のとおり、SQL クエリを使用してさまざまな役立つ情報を取得できることはおわかりいただけたのではないでしょうか。

今回の教訓

冒頭でお話したように、データベース スクリプトに関する記事をそこまで執筆したかったかと言われれば自信がありませんが、執筆してよかったと思います。理由の 1 つは、皆さんに便利なクエリを使用していただけるからですが、もう 1 つの理由は、今回でなくてもいずれは執筆しなければならなかっただろうと思うからです。あのアーサー コナン ドイル氏でさえ、シャーロック ホームズをよみがえらせることを余儀なくされました。アーサー氏が最初に取った一時的な措置は、シャーロック ホームズが最後を迎える直前の話として、新たな物語を執筆することでした。しかしその後、彼は読者からの重圧に屈して、シャーロック ホームズが命を絶ったふりをしたという少々あやふやな話を作り上げました。結局それによってすべての読者が満足したので、事態は丸く収まりました。

面白いことに、最新のシャーロック ホームズの作品が発表されると、Strand Magazine に 30,000 件の新規購読申し込みがありました。英国と米国の両出版社は、アーサー氏に感謝の意を表して、世界で最も高額な執筆料を支払いました。

アーサー コナン ドイル氏は、非常に愛された登場人物をよみがえらせ、世界で最も高額な執筆料を受け取りました。そして今回 Scripting Guys は、非常に愛されたトピックをよみがえらせました。しかもその死をごまかす必要なく。すばらしい TechNet Magazine の読者の皆さんであれば、気付いていただけましたよね。**

Dr. Scripto のスクリプト パズル

パズルを解くスキルだけでなく、スクリプト作成スキルもテストする月に一度の課題です。

2008 年 4 月 : 文字選び

今月号のパズルでは、青色のマス目に A から O までのいずれかの文字を当てはめて VBScript 関数の名前を作成します。それぞれの文字 (A から O まで) は 1 回しか使用できません。また、これらの文字はアルファベット順どおりのマス目には当てはまりません。当てはめる文字が関数名の先頭の文字になる場合もあれば、末尾や中間の文字になる場合もあります。たとえば、次の行に D を当てはめると、関数名 IsDate が出来上がります。

  (画像を拡大するには、ここをクリックします)

それではいざ挑戦です。A から O までのいずれかの文字を当てはめて、VBScript 関数の名前を作成してください。

**** (画像を拡大するには、ここをクリックします)

ANSWER:

Dr. Scripto のスクリプト パズル

解答 : 文字選び (2008 年 4 月)

  (画像を拡大するには、ここをクリックします)

The Microsoft Scripting Guys は、マイクロソフトの仕事をしています、というよりもマイクロソフトにより雇われています。野球をプレイしたり監督したり観戦したり (または他のさまざまな活動を) しているのでない限り、彼らは TechNet スクリプト センターを運営しています。詳細については、www.scriptingguys.com を参照してください。

© 2008 Microsoft Corporation and CMP Media, LLC. All rights reserved; 許可なしに一部または全体を複製することは禁止されています.