条件に該当するデータが存在しない時だけ登録するSQL

条件に該当するデータが存在しない時だけ登録するSQL

insert into [テーブル名] (フィールド名)
select 登録する値
where NOT EXISTS (select 1 from [テーブル名] where 条件)

例1:テーブル「tbl1」のフィールド「f1」に「テストデータ」がなければ、「テストデータ」を登録する

insert into tbl1 (f1)
select 'テストデータ'
where NOT EXISTS (select 1 from tbl1 where f1='テストデータ')

例2:テーブル「tbl1」のフィールド「f1」に「テストデータ」がなければ、フィールド「f2」に「ダミーデータ」を登録する

insert into tbl1 (f2)
select 'ダミーデータ'
where NOT EXISTS (select 1 from tbl1 where f1='テストデータ')

SQLのCOUNT(*)とCOUNT(列名)では結果が異なる

NULLを含むとき、COUNT(*)とCOUNT(列名)では結果が異なります。

Col1
10
(NULL)
50
(NULL)
40

このようなTBL1テーブルを例にします。

COUNT(*)はNULLを含めてカウントします。

SELECT COUNT(*) FROM TBL1

count
-----
    5

COUNT(列名)はNULLを含めません。

SELECT COUNT(COL1) FROM TBL1

count
-----
    3

SQL ゼロからはじめるデータベース操作』によると、

集約関数はNULLを除外する。ただし「COUNT(*)」は例外的にNULLを除外しない。

SQL ゼロからはじめるデータベース操作

ということでした。

FireDACのTFDIBBackupとTFDIBRestoreでFirebirdのバックアップや復元をする

FierDACのTFDIBBackupコンポーネントでFirebirdデータベースのバックアップ、
TFDIBRestoreコンポーネントでバックアップしたデータベースの復元ができます。

TFDFBNBackupTFDFBNRestoreがnbackupツールを呼び出すのに対して、
TFDIBBackupTFDIBRestoreはgbackツールを呼び出します。

サンプルアプリケーション

フォームに次のコンポーネントを配置します。

  • TButtonコンポーネントを2つ
  • TFDPhysFBDriverLink
  • TFDGUIxWaitCursor
  • TFDIBBackup
  • TFDIBRestore

FireDAC_gbak

TFDIBBackupでバックアップする

// Firebirdライバの場所
FDPhysFBDriverLink1.VendorLib := 'C:\driver\fbembed.dll';
FDIBBackup1.DriverLink := FDPhysFBDriverLink1;

// ユーザー名
FDIBBackup1.UserName := 'sysdba';
// パスワード
FDIBBackup1.Password := 'masterkey';
// データベースサーバーのホスト名
FDIBBackup1.Host := 'localhost';
// データベースサーバーに接続するプロトコル(ipLocal/ipTCPIP/ipNetBEUI)
FDIBBackup1.Protocol := ipLocal;
// バックアップ対象のデータベースファイル
FDIBBackup1.Database := 'C:\test\SAMPLE.FDB';
// バックアップ先のファイル名
FDIBBackup1.BackupFiles.Add('C:\test\SAMPLE.FDB.backup');
// バックアップタスクを開始する
FDIBBackup1.Backup;

TFDIBRestoreで復元する

// Firebirdライバの場所
FDPhysFBDriverLink1.VendorLib := 'C:\driver\fbembed.dll';
FDIBRestore1.DriverLink := FDPhysFBDriverLink1;

// ユーザー名
FDIBRestore1.UserName := 'sysdba';
// パスワード
FDIBRestore1.Password := 'masterkey';
// データベースサーバーのホスト名
FDIBRestore1.Host := 'localhost';
// データベースサーバーに接続するプロトコル(ipLocal/ipTCPIP/ipNetBEUI)
FDIBRestore1.Protocol := ipLocal;
// 復元対象のデータベースファイル
FDIBRestore1.Database := 'C:\test\NEW.FDB';
// 復元先のファイル名
FDIBRestore1.BackupFiles.Add('C:\test\SAMPLE.FDB.backup');
// 復元タスクを開始する
FDIBRestore1.Restore;

DelphiでFirebirdのバイナリデータの読み書き

DelphiでFireDACを使ってFirebirdのバイナリデータの読み書きに挑戦した。

使用したアプリケーションのバージョンは下記の通り。

  • Delphiのバージョンは、Delphi XE6(FireMonkeyデスクトップアプリケーション)
  • Firebirdのバージョンは、Firebird 2.5.2

テーブルの作成

バイナリデータを保存するテーブルを作成する。
Firebirdではバイナリ列はBLOBデータ型に保存する。

BLOBデータ型にはバイナリデータ用と長い文字列用の2種類がある。
バイナリデータ用は「SUB_TYPE BINARY」または「SUB_TYPE 0」を指定する。
長い文字列用は「SUB_TYPE TEXT」または「SUB_TYPE 1」を指定する。

/* バイナリデータを保存するテーブルを作成する */
CREATE TABLE MYIMAGE (JPEGBYTE BLOB SUB_TYPE BINARY);

データベースの接続

TFDConnectionを配置して接続できるようにする。
(具体的な手順は省略した)

TFDTableの設定

FTTable1のConnectionプロパティを「FDConnection1」、TableNameプロパティを「MYIMAGE」に設定する。

ActiveプロパティをTrueにして接続できることを確認する。

FDTable1のフィールドエディタを開き、「すべてのフィールドを追加」する。
(JPEGBYTEが追加される。)

01

TBlobFieldのLoadFromFileメソッドでバイナリデータを保存する

TBlobFieldのLoadFromFileメソッドを使ってバイナリデータを保存してみる。

ボタンを押したときのイベントを記述する。
このコードでは、あらかじめ用意しておいたテスト用のJEPGファイル(C:\test\test.jpg)をデータベースに保存している。

procedure TForm1.Button1Click(Sender: TObject);
begin
  if not FDTable1.Active then
    FDTable1.Active := True;

  FDTable1.Append;
  FDTable1JPEGBYTE.LoadFromFile('C:\test\test.jpg');
  FDTable1.Post;

  FDTable1.Active := False;
end;

TBlogFieldのLoadFromStreamメソッドでバイナリデータを保存する

TBlobFieldのLoadFromStreamメソッドを使ってバイナリデータを保存してみる。

ボタンを押したときのイベントを記述する。
このコードでは、あらかじめ用意しておいたテスト用のJEPGファイル(C:\test\test.jpg)をストリームに読み込んでから、ストリームをLoadFromStreamメソッドでデータベースに保存している。

procedure TForm1.Button4Click(Sender: TObject);
var
  MS: TMemoryStream;
begin
  if not FDTable1.Active then
    FDTable1.Active := True;

  FDTable1.Append;
  MS := TMemoryStream.Create;
  MS.LoadFromFile('C:\test\test.jpg');
  FDTable1JPEGBYTE.LoadFromStream(MS);
  MS.Free;
  FDTable1.Post;

  FDTable1.Active := False;
end;

TBlobFieldのSaveToFileメソッドでバイナリデータをファイルに保存する

TBlobFieldのSaveToFileメソッドでバイナリデータをファイルに保存してみる。

コードの最後では確認のためTImageコンポーネントに保存した画像を表示している。

procedure TForm1.Button3Click(Sender: TObject);
const
  PATH = 'C:\test\blob.jpg';
begin
  if not FDTable1.Active then
    FDTable1.Active := True;

  FDTable1JPEGBYTE.SaveToFile(PATH);
  FDTable1.Active := False;
  Image1.Bitmap.LoadFromFile(PATH);
end;

TBlobFieldのSaveToStreamメソッドでバイナリデータにストリームに保存する

TBlobFieldのSaveToFileメソッドでバイナリデータをストリームに保存してみる。

ストリームに保存したバイナリデータは、ファイルに保存してからTImageコンポーネントに表示している。

procedure TForm1.Button2Click(Sender: TObject);
const
  PATH = 'C:\test\blob.jpg';
var
  MS: TMemoryStream;
begin
  if not FDTable1.Active then
    FDTable1.Active := True;

  if TFile.Exists(PATH) then
    TFile.Delete(PATH);
  MS := TMemoryStream.Create;
  FDTable1JPEGBYTE.SaveToStream(MS);
  FDTable1.Active := False;

  MS.SaveToFile(PATH);
  MS.Free;
  Image1.Bitmap.LoadFromFile(PATH);
end;

TFDQueryでバイナリデータを保存する

バイナリデータを保存するには、TFDParamのLoadFromStreamメソッドを使用する。

procedure TForm1.Button5Click(Sender: TObject);
var
  MS: TMemoryStream;
begin
  MS := TMemoryStream.Create;
  MS.LoadFromFile('C:\test\test.jpg');
  MS.Seek(0, 0);
  FDQuery1.SQL.Text := 'insert into myImage (jpegByte) values (:bData)';
  FDQuery1.ParamByName('bData').LoadFromStream(MS, ftBlob);
  FDQuery1.ExecSQL;
  MS.Free;
end;

TFDQueryでバイナリデータを読み込む

TFDQueryのCreateBlobStreamメソッドを使用して、バイナリデータをTStreamで取得する。

ストリームのデータは、ファイルに保存してからTImageコンポーネントに表示している。

procedure TForm1.Button6Click(Sender: TObject);
const
  PATH = 'C:\test\blob.jpg';
var
  Stream: TStream;
  MS: TMemoryStream;
begin
  FDQuery1.Open('select jpegByte from myImage');
  FDQuery1.First;
  Stream := FDQuery1.CreateBlobStream(FDQuery1.FieldByName('jpegByte'),
    TBlobStreamMode.bmRead);
  MS := TMemoryStream.Create;
  MS.LoadFromStream(Stream);
  MS.SaveToFile(PATH);
  MS.Free;
  FDQuery1.Close;
  Image1.Bitmap.LoadFromFile(PATH);
e