☆Microsoft SQL Server 2005 Express Editionへの接続方法

検索が多い項目なので、ちょっとだけ丁寧に説明したいと思います。
データベースやテーブルの作成については、Delphiで接続する以前の問題なので、ここでは省略します。 SQLが苦手な人もSQL Server Management Studio Expressを使えば簡単に作成できますので、まずはデータベースを作っておいて下さい。又、接続にはADOを使っています。（私のはPro版なので、DBExpress用ドライバーがないからです）

(1) フォームにADOConnectionを配置し、それをダブルクリックする。
   （右クリックしてポップアップメニューから　ConnectionStringの編集を選択してもいいです。）

(2) 接続文字列を使うにチェックを入れて［ビルド］を押す。

(3) SQL Native Clientを選択して［次へ］を押す。

(4) 接続タブを以下のように設定します。



  1. データソースにlocalhost\sqlexpressと入力する。

  2. 「Windows NT の統合セキュリティを使用する」にチェックを入れる。
    （Microsoft SQL Server 2005 Express Editonを推奨設定でインストールした場合）

  3. 使用する初期カタログを入力します　コンボボックスから、データベースを選択する。

(5) ［接続のテスト］ボタンを押して、接続できるかどうかをチェックします。

(6) オブジェクトインスペクタのConnectedをTrueにしてみましょう。



パスワードを設定していない場合には、そのまま［OK］ボタンを押すだけです。
問題なくTRUEになれば、成功です。

フォームにADOTable、Datasource、DBGridを配置して、確認してみます。

  1. ADOTable1 の Connection に ADOConnection1 を設定する。
  2. ADOTable1 の TableName で Table を設定する。
  3. DataSource1 の DataSet に ADOTable1 を設定する。
  4. DBGrid1 の DataSource にDataSource1 を設定する。
  5. ADOTable1 の Active を True にする。

ちゃんと表示されたかな？

参考までに
Microsoft SQL Server 2005 Express Editionは、デフォルトのインストールでは、ローカル接続のみとなっているようです。 リモート接続したい場合には、SQL Server セキュリティ構成を実行して、SQLEXPRESS->Database Engine->リモート接続で、「ローカル接続のみ」から「ローカル接続およびリモート接続」に変更する必要があります。 データソースで、サーバー名を設定しても接続できない場合は、ファイヤーウォールよりも先に、この点をチェックしてみて下さい。
と書きつつも、私はまだローカル接続でしか使っていませんけど(笑)
あっ、XQueryを試す場合には、DataTypeCompatibility=80とする必要があるみたいです。Microsoftのホームページのどこかに書いていた記憶があります。

☆照合順序 COLLATE

これもDelphiの話ではなくSQL Serverの話です。
SQL Serverでは、文字列の照合時に COLLATE を使うと半角全角を無視したり、反対に厳格にしたりできます。

[Field1のデータ]
コンクリート
こんくりーと
ｺﾝｸﾘｰﾄ

（A）
SELECT FIELD1 FROM TABLE1 WHERE FIELD1 COLLATE
 Japanese_BIN LIKE '%こんくりーと%'

[表示結果]
こんくりーと

（B）
SELECT FIELD1 FROM TABLE1 WHERE FIELD1 COLLATE
 Japanese_CI_AS LIKE '%こんくりーと%'

[表示結果]
コンクリート
こんくりーと
ｺﾝｸﾘｰﾄ

上記の例については、いまさら説明は要らないかも知れませんね。
ただ、私がはまったのは次のようなものでした。

[FIELD1のデータ]
コンクリート
こんくりーと
ｺﾝｸﾘｰﾄ

[FIELD2のデータ]
null
null
fc=21

（A）
SELECT FIELD1 FROM TABLE1 WHERE (FIELD1+' '+FIELD2) COLLATE
 Japanese_BIN LIKE '%こんくりーと%'

[表示結果]
なし

（B）
SELECT FIELD1 FROM TABLE1 WHERE (FIELD1+' '+FIELD2) COLLATE
 Japanese_CI_AS LIKE '%こんくりーと%'

[表示結果]
ｺﾝｸﾘｰﾄ

FIELD2を足しただけなのになんで期待した結果にならないのだろう？とずっと悩んでしまいました。結局、文字列＋NULLになっていることに気づいたときには、なんでこんな初歩的なことで・・・とショックでした。
対応策としては、FIELD1とFIELD2を分けて、それぞれの照合をORで繋ぐか、テーブル作成時にNULLを許可しない等の工夫が必要になるようです。

☆XQuery 親ノードへのアクセス

再び、SQL Server 2005 Express Edition、XQueryの話です。
前回、XQUERYってややこしいっ！で使ったようなXMLを格納したフィールドで、あるノードの親ノードのIDを取得する処理を考えてみました。

//IDが456の親ノードのIDを取得する場合
// query, parent, returnは小文字でないと動きません。
SELECT CAST(TREEXML.query('
for $RESULT1 in //item[@id=456]/parent::item, 
$RESULT2 in (data($RESULT1/@id)) return $RESULT2
') as VARCHAR)
FROM dbo.KUBUN

上記の処理で親ノードのIDを取得できたので、これをストアドかユーザー定義関数内でループさせると、あるノードまでの パスを返すことができると思い試してみましたが、ここで問題が発生しました。というのもSQL Serverでの変数は、@node_idのように@を付ける必要があり、この仕様が、XMLのPATH式での属性を示す@(attribute)とかぶってしまうからです。結局、これを解決できずにADOQureyを使って次のように処理することにしました。

function TDataModule1.GetParentIDs(MyID: Integer): String;
const
  TopNodeID = 1; //一番上ノードのID
var
  SQL: String;
  I: Integer;
begin
  Result := '';
  while true do
  begin
    SQL := 'SELECT CAST(TREEXML.query(''for $RESULT1 in //item[@id=';
    SQL := SQL + IntToStr(MyID) + ']/parent::item, ';
    SQL := SQL + '$RESULT2 in (data($RESULT1/@id)) return $RESULT2'') ';
    SQL := SQL + 'AS VARCHAR) ';
    SQL := SQL + 'AS MyID FROM dbo.KUBUN';
    ADOQuery1.Close;
    ADOQuery1.SQL.Text := SQL;
    ADOQuery1.Open;

    // このSQLは指定IDを見つけてその親IDを返すため、
    // 一番上ノードのIDを与えるとエラーになります。
    // なので、そのIDになったときに処理を終了させます。
    if ADOQuery1.Eof then Break;
    I := ADOQuery1.FieldValues['MyID'];

    if I = TopNodeID then Break;

    Result := Result + '/' + IntToStr(I);
    MyID := ADOQuery1.FieldValues['MyID'];
  end;
  ADOQuery1.Close;
end;

☆ADOQueryを使ってImage型を読み書きする。

今までADOでImage型を扱ったことがなく、かなり試行錯誤した結果です(笑) ネット上でもサンプルがあまりないんですよね。 データベースは、例によって、SQL Server 2005 Express Editionです。

procedure TForm1.FormCreate(Sender: TObject);
begin
  ADOQuery1.Close;
  ADOQuery1.SQL.Text := 'SELECT IMG FROM dbo.DETAIL';
  ADOQuery1.Open;
end;

// Image型フィールドに書き込みます。
procedure TForm1.Button1Click(Sender: TObject);
var
  Stream : TADOBlobStream;
begin
  ADOQuery1.Edit;
  Stream := TADOBlobStream.Create(
    TBlobField(ADOQuery1.FieldByName('IMG')), bmWrite);
  Stream.LoadFromFile('c:\aa.jpg'); // BitmapかJpegしか保存しない仕様
  Stream.Free; //Postする前に開放しないとエラーになります。
  ADOQuery1.Post;
end;

// Image型フィールドから読み込みます。
procedure TForm1.Button2Click(Sender: TObject);

  // Bitmapかどうかを判定します。
  function IsBitmap(var Stream: TStream): Boolean;
  var
    Bmfh: TBitmapFileHeader;
  begin
    Stream.ReadBuffer(Bmfh, sizeof(Bmfh));
    Result := (Bmfh.bfType = $4D42);
    Stream.Position := 0; // 先頭に戻しておきます。
  end;

var
  Stream : TADOBlobStream;
  Pic : TPicture;
  Jpeg : TJpegImage;
begin
  Stream := TADOBlobStream.Create(
    TBlobField(ADOQuery1.FieldByName('IMG')), bmRead);
  Pic:=TPicture.Create;
  Jpeg := TJpegImage.Create;
  try
    Stream.Position := 0;
    if IsBitmap(TStream(Stream)) then
        Pic.Bitmap.LoadFromStream(Stream)
    else
      begin
        Jpeg.LoadFromStream(Stream);
        Pic.Assign(Jpeg);
      end;
    Image1.Picture.Assign(Pic);
  finally
    Jpeg.Free;
    Pic.Free;
    Stream.Free
  end;
end;

■XQUERYってややこしいっ！

XQUERYのサンプルって、階層がきちんと揃ったものは、結構あるのですが、 次のような階層が自由なものについては、なかなか見つけられないですね。
（こういうのは、XMLデータとしてふさわしくないのかな）

<?xml version="1.0" ?>
<koumoku>
  <item text="AAA" id="1">
    <item text="BBB" id="2">
      <item text="CCC" id="3" />
      <item text="DDD" id="4" />
    </item>
    <item text="EEE" id="5">
      <item text="FFF" id="6">
        <item text="GGG" id="7" />
        <item text="HHH" id="8" />
      </item>
    </item>
  </item>
</koumoku>

このXMLから、属性idを検索して、属性textの値を取得するのに丸一日悩んでました。 Delphiとはあまり関係ないのですが、せっかくなので書いておきますね。
(Microsoft SQL Server: SQL Server 2005 Express EditionのXML型に格納)

SELECT TREEXML.query('
for $RESULT1 in //item[@id=8], 
$RESULT2 in (data($RESULT1/@text)) return $RESULT2')
FROM dbo.KUBUN

結果として 属性ｉｄ 8を持つアイテムの属性text HHH を取得できます。

アプリケーションからデータベースを操作する上で、親ノードを調べたり、更新等を考えるとXML型のメリットってなんだろうと疑問に思ってしまいます。今までも、再帰処理により階層データを扱ってきたんだし、１００歩譲ってXML型にしても、文字型にそのまま出し入れするだけでもいいような気になってます。もっともっと、基本から勉強しないといけないですね。


XQUERYについて
XQuery 1.0: An XML Query Language
W3C Recommendation 23 January 2007
XQuery Update Facility
W3C Working Draft 11 July 2006

☆XML型フィールドとXQUERY

一つのフィールドにXMLを入れて処理するなんて・・・と古い考えの私ですが、Microsoft SQL Server 2005 Express Editonを使って試してみました。

※1200アイテム程のXMLをXQUERYで取り出し、TreeViewに設定しています。

procedure TForm1.Button1Click(Sender: TObject);
const
   Header = '<?xml version="1.0" encoding="shift_jis"?>';
var
  S: String;
begin
  ADOQuery1.Close;
  ADOQuery1.SQL.Clear;
  ADOQuery1.SQL.Add(
    'SELECT EST.query(''//koumoku'') AS XML_SAMPLE FROM SEKISAN.dbo.EST_KOU');
  ADOQuery1.Open;
  try
    S := Header + ADOQuery1.FieldValues['XML_SAMPLE'];
    XMLDocument1.XML.Text := S;
    XMLDocument1.Active := True;
    //TreeViewに設定
    XML2Tree(TreeView1,XMLDocument1);
  finally
    ADOQuery1.Close;
  end;
end;

※ADOの接続文字列に、DataTypeCompatibility=80を設定する必要があります。
※XMLの一部だけ取得するクエリの場合、XMLDocumentで
　「ドキュメント内では最上位の要素に限り、使用できます」というエラーが発生します。
　この場合には、<dummy>+取得した文字列+</dummy>とすれば、解決します。

テーブル内を再帰的に検索しながら、TreeViewに設定するよりもプログラムがすっきりします。 しかし、ノードの入れ替え等が発生した場合には、どうすればいいんでしょうね。 全部書き直すか、順番コードをつけるか・・・うーん、これってXMLじゃない場合と同じ悩みですね。 更新や削除処理は、まだ試していませんが、表示を中心につかう場合には、とても効果的だと感じました。

参考にしたサイト
about.com
Exporting a TreeView to XML. Populating a TreeView from XML

SQL Server 2005 Books Online
アプリケーションでの xml データ型の操作

☆Microsoft SQL Server 2005 Express Editionへの接続

仕事が忙しくなかなかプログラムする時間がとれず、このブログもほとんど更新できていません。
ところで昨日、Microsoft SQL Server 2005 Express Editionをインストールしたので、ちょっと使ってみました。私のは、Professional版なので、DBExpressはドライバーがないため、ADOで接続しました。接続文字列の設定がわからず、試行錯誤した結果、次の設定で使えるようになったので書いておきますね。

ADOTable1, DataSource1, DBGrid1をフォームに貼り付けます。
それぞれを設定しておきます。
ADOTable1のConnectionStringのボタンを押します。
データリンクプロパティのダイアログで下記の設定をして、OKボタンを押します。

[プロバイダ]タブ
「SQL Native Client」を選択します。

[接続]タブ
１．データソースとデータの場所を入力します。
　データソースに 「local\sqlexpress」　と入力します。→「localhost\sqlexpress」
２．サーバーへのログオンに必要な情報を入力します。
　「Windows NTの統合セキュリティを使用する。」を選択します。
３．使用する初期カタログを入力します。
　「MYDEMO」と入力します。
　　※使いたいデータベースがMYDEMO.mdfの場合

ConnectionStringの内容は以下のようになっていました。

Provider=SQLNCLI.1;
Integrated Security=SSPI;
Persist Security Info=False;
Initial Catalog=MYDEMO;
Data Source=localhost\sqlexpress;
Use Procedure for Prepare=1;
Auto Translate=True;
Packet Size=4096;
Workstation ID=<パソコン名>;
Use Encryption for Data=False;
Tag with column collation when possible=False;
MARS Connection=False;
DataTypeCompatibility=0;
Trust Server Certificate=False