☆ExcelをADOで操作する。

フォームにADOConnection1、ADOTable1、DataSource1、DBGrid1を配置し、いつものように？設定します。

ADOConnection1の設定です。

ADOConnection1.ConnectionString := 
 'Provider=Microsoft.Jet.OLEDB.4.0;' +
 'Data Source=C:\master.xls;' +
 'Extended Properties=EXCEL 8.0';
ADOConnection1.LoginPrompt := False;

次にADOTable1の設定です。ポイントは、TableNameでシート名を[ ]で囲うことです。これをしないと「FROM 句の構文エラーです。」と表示され、ファイルをオープンすることができません。TableNameをオブジェクトインスペクタで選択した場合には、[　]がないので要注意です。

ADOTable1.Connection := AdoConnection1;
ADOTable1.TableName := '[sheet1$]';
ADOTable1.Active := True;  

これでExcelの内容がDBGridに表示されるようになりました。

ADOConnection1、ADOTable1をクローズします。
そして、実行時にファイルをオープン／クローズする処理を書きます。

procedure TForm1.FormCreate(Sender: TObject);
begin
  ADOTable1.Open;
end;

procedure TForm1.FormDestroy(Sender: TObject);
begin
  ADOTable1.Close;
  ADOConnection1.Close; //念のため
end;  

---

ADOでの操作では追加や編集は可能ですが、行を削除をしようとすると次のようなエラーメッセージが表示されます。

ということで、行の削除はできないという仕様らしいです。 又、排他的に開かれているので、このプログラムを実行しているときにはExcel等で開くことはできないです。


[参考にした書籍]

Marco Cantu
Delphi6プログラミングバイブル

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

以前、ADOQueryを使ってImage型を読み書きする。 という記事を書きましたが、その中で「Image型フィールドに書き込みます。」部分は次のような単純な処理でも可能なようです。

procedure TForm1.Button1Click(Sender: TObject);
begin
  ADOQuery1.Edit;
  (ADOQuery1.FieldByName('IMG') as TBlobField).LoadFromFile('c:\aa.jpg');
  ADOQuery1.Post;
end;

[参考]
CodeGear NewsGroup
borland.public.delphi.database.ado
JPEG into BLOB field

☆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のホームページのどこかに書いていた記憶があります。

☆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;

☆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