コマンド一発で、指定したフォルダ内のすべての画像ファイルを SQL Server へ バイナリ（FILESTREAMとして） で登録する
到達点
- コマンド一発で、指定したフォルダ内のすべての画像ファイルを SQL Server へ バイナリ（FILESTREAMとして） で登録する。
- コマンド一発だが、実際には 1.指定したフォルダのファイル一覧をテキストファイルに出力する VBScript、2.テキストファイル上のファイル一覧にある画像を SQL Server に登録する SQLCMD の2つの処理が実行される。
仕様
- フォルダは複数指定可能にする。
- フォルダは UNCパス、ローカルパスどちらでも指定できるものとする。
- 指定したフォルダのサブフォルダを再帰的に検索する。
- カメラモデル、シャッタースピード、絞りなど、画像の属性も画像と同様に登録する。
作成の流れ
- ImageInsertDB.cmd を実行する。
- VBスクリプト「FilePropertyRecurse.vbs」が実行され、Shell.Application の Namespace でフォルダが持つ属性情報を取得し、フォルダ内のファイルの一覧とその属性を、コマンド実行ユーザのデスクトップにタブ区切りテキストファイル「ResultFile.log」として出力する。
- SQLCMD で ストアドプロシージャ「insertBulkImage」を起動される。
- 1.1のテキストファイルを テーブル「PhotoProterty」へ Bulkinsert する。
- ２.１で登録した画像ファイルのフルパスをもとに OPENROWSETで画像をバイナリ（FILESTREAM）で登録する SQL文 が動的に生成される。
- 2.2で生成された SQL文が FETCH で繰り返し実行され、画像がテーブル「IMAGEBULK」へ登録される。
- 1.1のテキストファイルを テーブル「PhotoProterty」へ Bulkinsert する。
- VBスクリプト「FilePropertyRecurse.vbs」が実行され、Shell.Application の Namespace でフォルダが持つ属性情報を取得し、フォルダ内のファイルの一覧とその属性を、コマンド実行ユーザのデスクトップにタブ区切りテキストファイル「ResultFile.log」として出力する。
特記事項
- Shell.Application の Namespace でフォルダが持つ属性情報を、写真に関係しそうな48個取得するので、これに時間がかかる。時間を短縮するには、必要な項目に絞り込んで、取得する項目を減らす。
- 実行すると、テーブル「IMAGEBULK」「PhotoProterty」は最初にTRUNCATEする。追加登録で登録する場合は、ファイル一覧作成で、ファイル作成日が指定日以降のファイルのみに絞り込むとか、Insertするときにファイル作成日が指定日以降のデータのみに絞り込むとかの改変が必要。
参考：GetDetailsOf で得られる情報
| i | 属性名 | データサンプル |
| 0 | 名前 | IMG_9317.CR2 |
| 1 | サイズ | 7.19 MB |
| 2 | 項目の種類 | CR2 ファイル |
| 3 | 更新日時 | 2006/04/28 19:12 |
| 4 | 作成日時 | 2013/01/05 17:07 |
| 5 | アクセス日時 | 2013/01/05 17:07 |
| 6 | 属性 | A |
| 7 | オフラインの状態 | オンライン |
| 8 | 利用可能性 | オンラインのみ |
| 9 | 認識された種類 | イメージ |
| 10 | 所有者 | S-1-5-21-1732841086-1304064800-1295771338-1001 |
| 11 | 分類 | ピクチャ |
| 12 | 撮影日時 | 2006/04/28 19:12 |
| 13 | 参加アーティスト | |
| 14 | アルバム | |
| 15 | 年 | |
| 16 | ジャンル | |
| 17 | 指揮者 | |
| 18 | タグ | |
| 19 | 評価 | 評価なし |
| 20 | 作成者 | Yunogotoshi |
| 21 | タイトル | |
| 22 | 件名 | |
| 23 | 分類項目 | |
| 24 | コメント | |
| 25 | 著作権 | |
| 26 | トラック番号 | |
| 27 | 長さ | |
| 28 | ビット レート | |
| 29 | 保護 | |
| 30 | カメラのモデル | Canon EOS 20D |
| 31 | 大きさ | 3504 x 2336 |
| 32 | カメラの製造元 | Canon |
| 33 | 会社 | |
| 34 | ファイルの説明 | |
| 35 | プログラム名 | |
| 36 | 継続時間 | |
| 37 | オンライン | |
| 38 | 再帰 | |
| 39 | 場所 | |
| 40 | 任意出席者アドレス | |
| 41 | 任意出席者 | |
| 42 | 開催者住所 | |
| 43 | 開催者名 | |
| 44 | アラーム時刻 | |
| 45 | 必須出席者アドレス | |
| 46 | 必須出席者 | |
| 47 | リソース | |
| 48 | 会議の状態 | |
| 49 | 空き時間情報 | |
| 50 | 合計サイズ | 2.72 TB |
| 51 | アカウント名 | |
| 52 | 進捗状況 | |
| 53 | コンピューター | MEDIASV |
| 54 | 記念日 | |
| 55 | 秘書の名前 | |
| 56 | 秘書の電話 | |
| 57 | 誕生日 | |
| 58 | 住所 (会社) | |
| 59 | 市 (会社) | |
| 60 | 国/地域 (会社) | |
| 61 | 私書箱 (会社) | |
| 62 | 郵便番号 (会社) | |
| 63 | 都道府県 (会社) | |
| 64 | 番地 (会社) | |
| 65 | FAX 番号 (会社) | |
| 66 | 会社のホーム ページ | |
| 67 | 会社電話 | |
| 68 | コールバック番号 | |
| 69 | 自動車電話 | |
| 70 | 子供 | |
| 71 | 会社代表電話 | |
| 72 | 部署 | |
| 73 | 電子メール アドレス | |
| 74 | 電子メール2 | |
| 75 | 電子メール3 | |
| 76 | 電子メールの一覧 | |
| 77 | 電子メール表示名 | |
| 78 | 表題2 | |
| 79 | 名 | |
| 80 | 氏名 | |
| 81 | 性別 | |
| 82 | 名2 | |
| 83 | 趣味 | |
| 84 | 住所 (自宅) | |
| 85 | 市 (自宅) | |
| 86 | 国/地域 (自宅) | |
| 87 | 私書箱 (自宅) | |
| 88 | 郵便番号 (自宅) | |
| 89 | 都道府県 (自宅) | |
| 90 | 番地 (自宅) | |
| 91 | 自宅 FAX | |
| 92 | 自宅電話 | |
| 93 | IM アドレス | |
| 94 | イニシャル | |
| 95 | 役職 | |
| 96 | ラベル | |
| 97 | 姓 | |
| 98 | 住所 (郵送先) | |
| 99 | ミドル ネーム | |
| 100 | 携帯電話 | |
| 101 | ニックネーム | |
| 102 | 勤務先所在地 | |
| 103 | 住所 (その他) | |
| 104 | ほかの市区町村 | |
| 105 | 他の国/地域 | |
| 106 | 他の私書箱 | |
| 107 | 他の郵便番号 | |
| 108 | 他の都道府県 | |
| 109 | 他の番地 | |
| 110 | ポケットベル | |
| 111 | 肩書き | |
| 112 | 市区町村 (郵送先) | |
| 113 | 国/地域 | |
| 114 | 私書箱 (郵送先) | |
| 115 | 郵便番号 (郵送先) | |
| 116 | 都道府県 | |
| 117 | 番地 (郵送先) | |
| 118 | プライマリ電子メール | |
| 119 | 通常の電話 | |
| 120 | 職業 | |
| 121 | 配偶者 | |
| 122 | サフィックス | |
| 123 | TTY/TTD 電話 | |
| 124 | テレックス | |
| 125 | Web ページ | |
| 126 | 内容の状態 | |
| 127 | 内容の種類 | |
| 128 | 取得日時 | |
| 129 | アーカイブ日時 | |
| 130 | 完了日 | |
| 131 | デバイス カテゴリ | |
| 132 | 接続済み | |
| 133 | 探索方法 | |
| 134 | フレンドリ名 | |
| 135 | ローカル コンピューター | |
| 136 | 製造元 | |
| 137 | モデル | |
| 138 | ペアリング済み | |
| 139 | クラス | |
| 140 | 状態 | |
| 141 | 状態 | |
| 142 | クライアント ID | |
| 143 | 共同作成者 | |
| 144 | コンテンツの作成日時 | |
| 145 | 前回印刷日 | |
| 146 | 前回保存日時 | |
| 147 | 事業部 | |
| 148 | ドキュメント ID | |
| 149 | ページ数 | |
| 150 | スライド | |
| 151 | 総編集時間 | |
| 152 | 単語数 | |
| 153 | 期限 | |
| 154 | 終了日 | |
| 155 | ファイル数 | |
| 156 | ファイル拡張子 | .CR2 |
| 157 | ファイル名 | IMG_9317.CR2 |
| 158 | ファイル バージョン | |
| 159 | フラグの色 | |
| 160 | フラグの状態 | |
| 161 | 空き領域 | 1.05 TB |
| 162 | ||
| 163 | ||
| 164 | 共有の種類 | |
| 165 | ビットの深さ | |
| 166 | 水平方向の解像度 | |
| 167 | 幅 | 3504 ピクセル |
| 168 | 垂直方向の解像度 | |
| 169 | 高さ | 2336 ピクセル |
| 170 | 重要度 | |
| 171 | 添付 | |
| 172 | 削除 | |
| 173 | 暗号化の状態 | |
| 174 | フラグの有無 | |
| 175 | 終了済 | |
| 176 | 未完了 | |
| 177 | 開封の状態 | |
| 178 | 共有 | |
| 179 | 製作者 | |
| 180 | 日付時刻 | |
| 181 | フォルダー名 | 20060428 |
| 182 | フォルダーのパス | \\MEDIASV\Family\Picture\200604\20060428 |
| 183 | フォルダー | 20060428 (\\MEDIASV\Family\Picture\200604) |
| 184 | 参加者 | |
| 185 | パス | \\MEDIASV\Family\Picture\200604\20060428\IMG_9317.CR2 |
| 186 | 場所ごと | |
| 187 | 種類 | CR2 ファイル |
| 188 | 連絡先の名 | |
| 189 | 履歴の種類 | |
| 190 | 言語 | |
| 191 | 最終表示日 | |
| 192 | 説明 | |
| 193 | リンクの状態 | 未解決 |
| 194 | リンク先 | |
| 195 | URL | |
| 196 | ||
| 197 | メディアの作成日時 | |
| 198 | リリース日 | |
| 199 | エンコード方式 | |
| 200 | エピソード番号 | |
| 201 | プロデューサー | |
| 202 | 発行元 | |
| 203 | シーズン番号 | |
| 204 | サブタイトル | |
| 205 | ユーザー Web URL | |
| 206 | 作者 | |
| 207 | ||
| 208 | 添付ファイル | |
| 209 | BCC アドレス | |
| 210 | BCC | |
| 211 | CC アドレス | |
| 212 | CC | |
| 213 | 会話 ID | |
| 214 | 受信日時 | |
| 215 | 送信日時 | |
| 216 | 送信元アドレス | |
| 217 | 差出人 | |
| 218 | 添付ファイルの有無 | |
| 219 | 送信者アドレス | |
| 220 | 送信者名 | |
| 221 | ストア | |
| 222 | 送信先アドレス | |
| 223 | To do タイトル | |
| 224 | 宛先 | |
| 225 | 経費情報 | |
| 226 | アルバムのアーティスト | |
| 227 | アルバム アーティストで並べ替え | |
| 228 | アルバム ID | |
| 229 | アルバムで並べ替え | |
| 230 | 参加アーティストで並べ替え | |
| 231 | ビート数/分 | |
| 232 | 作曲者 | |
| 233 | 作曲者で並べ替え | |
| 234 | イニシャル キー | |
| 235 | コンパイルの一部 | |
| 236 | 雰囲気 | |
| 237 | セットのパート | |
| 238 | 期間 | |
| 239 | 色 | |
| 240 | 保護者による制限 | |
| 241 | 保護者による制限の理由 | |
| 242 | 使用領域 | 61% |
| 243 | EXIF バージョン | |
| 244 | イベント | |
| 245 | 露出補正 | 0 ステップ |
| 246 | 露出プログラム | 絞り優先 |
| 247 | 露出時間 | 1/20 秒 |
| 248 | 絞り値 | f/5.6 |
| 249 | フラッシュ モード | フラッシュなし (強制) |
| 250 | 焦点距離 | 81 mm |
| 251 | 35mm 焦点距離 | |
| 252 | ISO 速度 | ISO-800 |
| 253 | レンズ メーカー | |
| 254 | レンズ モデル | |
| 255 | 光源 | |
| 256 | 最大絞り | |
| 257 | 測光モード | パターン |
| 258 | 向き | |
| 259 | 人物 | |
| 260 | プログラムのモード | |
| 261 | 彩度 | |
| 262 | 対象の距離 | |
| 263 | ホワイト バランス | |
| 264 | 優先度 | |
| 265 | プロジェクト | |
| 266 | チャンネル番号 | |
| 267 | この回のタイトル | |
| 268 | 字幕 | |
| 269 | 再放映 | |
| 270 | SAP | |
| 271 | 放送日 | |
| 272 | プログラムの説明 | |
| 273 | 記録時間 | |
| 274 | ステーション コール サイン | |
| 275 | 局名 | |
| 276 | 概要 | |
| 277 | 抜粋 | |
| 278 | 自動要約 | |
| 279 | 検索のランキング | |
| 280 | 秘密度 | |
| 281 | 共有ユーザー | |
| 282 | 共有状態 | 非共有 |
| 283 | 利用可能 | |
| 284 | 製品名 | |
| 285 | 製品バージョン | |
| 286 | サポートのリンク | |
| 287 | ソース | |
| 288 | 開始日 | |
| 289 | 共有中 | |
| 290 | 課金情報 | |
| 291 | 完了 | |
| 292 | 仕事の所有者 | |
| 293 | タイトルで並べ替え | |
| 294 | 総ファイル サイズ | |
| 295 | 商標 | |
| 296 | ビデオ圧縮 | |
| 297 | ディレクター | |
| 298 | データ速度 | |
| 299 | フレーム高 | |
| 300 | フレーム率 |
ソース
FilePropertyRecurse.vbs
日本語コメントが消えてるけど・・・
'()
'-----------------------------------------------
Option Explicit
Dim args
Dim RESULT
Dim SEARCH
Set args = Wscript.Arguments
RESULT = args.Item(0) '
SEARCH = args.Item(1) '
Dim objFso
Dim objResult
Dim objSearch
Dim objShell
Dim iLine
Dim searchFol
Dim objFol
Dim swHeader
swHeader = 0
'
Set objFso = CreateObject("Scripting.FileSystemObject")
'
If objFso.FileExists(RESULT) Then
Call objFso.DeleteFile(RESULT)
End If
'
Set objResult = objFso.OpenTextFile(RESULT, 8, True, -1)
'
Set objsearch = objFso.OpenTextFile(SEARCH, 1, False)
If Err.Number > 0 Then
WScript.Echo "Open Error"
Else
Do Until objSearch.AtEndOfStream
searchFol = objSearch.ReadLine
iLine = searchFol
Set objFol = objFso.GetFolder(iLine)
If objFso.FolderExists(searchFol) Then
Call MainProc(SearchFol,objFol)
end if
Loop
End If
objResult.Close
objSearch.Close
'-----------------------------------------------
'
'-----------------------------------------------
Sub MainProc(paramFol, paramObjFol)
Dim arrHeaders(300)
Dim objShell
Dim objFolder
Dim i
Dim strLine
Dim strFileName
Dim fsoSubFolder
Dim fsoFile
objShell = CreateObject("Shell.Application")
objFolder = objShell.Namespace(paramFol)
For i = 0 To 300
Select Case i
Case 0, 1, 2, 3, 4, 5, 9, 10, 11, 12, 30, 31, 32, 35, 156, 157, 165, 166, 167, 168, 169, 181, 182, 183, 185, 187, 243, 244, 245, 246, 247, 248, 249, 250, 251, 252, 253, 254, 255, 256, 257, 258, 259, 260, 261, 262, 263
arrHeaders(i) = objFolder.GetDetailsOf(objFolder.Items, i)
strLine = strLine & arrHeaders(i) & vbTab
End Select
Next
If swHeader <> 1 Then
objResult.WriteLine strLine
swHeader = 1
End If
For Each strFileName In objFolder.Items
If objFolder.GetDetailsOf(strFileName, 156) <> "" Then
strLine = 1 & vbTab
For i = 0 To 300
Select Case i
Case 0, 1, 2, 3, 4, 5, 9, 10, 11, 12, 30, 31, 32, 35, 156, 157, 165, 166, 167, 168, 169, 181, 182, 183, 185, 187, 243, 244, 245, 246, 247, 248, 249, 250, 251, 252, 253, 254, 255, 256, 257, 258, 259, 260, 261, 262, 263
strLine = strLine & objFolder.GetDetailsOf(strFileName, i) & vbTab
End Select
Next
objResult.WriteLine strLine
End If
Next
'
For Each fsoSubFolder In paramObjFol.SubFolders
Call MainProc(fsoSubFolder.path, fsoSubFolder)
Next
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[insertBulkImage]
SELECT 'Return Value' = @return_value
ImageInsertDB.cmd
SET OUT="C:\Users\%USERNAME%\Desktop\ResultFile.log"
SET IN="C:\Users\%USERNAME%\Desktop\searchFile.txt"
SET SQL="C:\Users\%USERNAME%\Desktop\execPROC.sql"
FilePropertyRecurse.vbs %OUT% %IN%
SQLCMD -i %SQL%
FilePropertyRecurse.vbs の引数説明
引数1：検索結果ログ（タブ区切りテキストファイル）
引数2：検索対象フォルダ
※ストアドプロシージャの「insertBulkImage」の Bulk Insert 参照するテキストファイルのパスを、実行ユーザのデスクトップにある「ResultFile.log」で固定しているので、引数1は実行ユーザのデスクトップに固定。
テーブル「PhotoProterty」作成
CREATE TABLE [dbo].[PhotoProterty](
[ID] [int] IDENTITY(1,1) NOT NULL,
[名前] [nvarchar](255) NULL,
[サイズ] [nvarchar](50) NULL,
[項目の種類] [nvarchar](50) NULL,
[更新日時] [nvarchar](50) NULL,
[作成日時] [nvarchar](50) NULL,
[アクセス日時] [nvarchar](50) NULL,
[認識された種類] [nvarchar](50) NULL,
[所有者] [nvarchar](255) NULL,
[分類] [nvarchar](50) NULL,
[撮影日時] [nvarchar](50) NULL,
[カメラのモデル] [nvarchar](50) NULL,
[大きさ] [nvarchar](50) NULL,
[カメラの製造元] [nvarchar](50) NULL,
[プログラム名] [nvarchar](50) NULL,
[ファイル拡張子] [nvarchar](50) NULL,
[ファイル名] [nvarchar](50) NULL,
[ビットの深さ] [nvarchar](50) NULL,
[水平方向の解像度] [nvarchar](50) NULL,
[幅] [nvarchar](50) NULL,
[垂直方向の解像度] [nvarchar](50) NULL,
[高さ] [nvarchar](50) NULL,
[フォルダー名] [nvarchar](255) NULL,
[フォルダーのパス] [nvarchar](255) NULL,
[フォルダー] [nvarchar](255) NULL,
[パス] [nvarchar](255) NULL,
[種類] [nvarchar](50) NULL,
[EXIF バージョン] [nvarchar](50) NULL,
[イベント] [nvarchar](50) NULL,
[露出補正] [nvarchar](50) NULL,
[露出プログラム] [nvarchar](50) NULL,
[露出時間] [nvarchar](50) NULL,
[絞り値] [nvarchar](50) NULL,
[フラッシュ モード] [nvarchar](50) NULL,
[焦点距離] [nvarchar](50) NULL,
[35mm 焦点距離] [nvarchar](50) NULL,
[ISO 速度] [nvarchar](50) NULL,
[レンズ メーカー] [nvarchar](50) NULL,
[レンズ モデル] [nvarchar](50) NULL,
[光源] [nvarchar](50) NULL,
[最大絞り] [nvarchar](50) NULL,
[測光モード] [nvarchar](50) NULL,
[向き] [nvarchar](50) NULL,
[人物] [nvarchar](50) NULL,
[プログラムのモード] [nvarchar](50) NULL,
[彩度] [nvarchar](50) NULL,
[対象の距離] [nvarchar](50) NULL,
[ホワイト バランス] [nvarchar](50) NULL,
CONSTRAINT [PK_PhotoProterty] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
テーブル「IMAGEBULK」作成
CREATE TABLE [dbo].[IMAGEBULK](
[ID] [int] IDENTITY(1,1) NOT NULL,
[STREAMID] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[FilePath] [nvarchar](255) NULL,
[IMAGE] [varbinary](max) FILESTREAM NULL,
CONSTRAINT [PK_IMAGEBULK] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] FILESTREAM_ON [FileStreamGroup1],
UNIQUE NONCLUSTERED
(
[STREAMID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] FILESTREAM_ON [FileStreamGroup1]
ストアドプロシージャ「insertBulkImage」作成
CREATE PROCEDURE [dbo].[insertBulkImage]
AS
BEGIN
SET NOCOUNT ON;
TRUNCATE TABLE [dbo].[PhotoProterty]
TRUNCATE TABLE [dbo].[IMAGEBULK]
DECLARE @LOGFILE NVARCHAR(255)
DECLARE @USER NVARCHAR(255)
SET @USER = SUBSTRING(SYSTEM_USER,CHARINDEX('\',SYSTEM_USER) + 1,LEN(SYSTEM_USER) - CHARINDEX('\',SYSTEM_USER))
SET @LOGFILE = 'C:\Users\' + @USER + '\Desktop\ResultFile.log'
DECLARE @BULKSQL NVARCHAR(MAX)
SET @BULKSQL ='BULK INSERT [dbo].[PhotoProterty] FROM ' + '''' + @LOGFILE + '''' + 'WITH(FIRSTROW = 2,DATAFILETYPE = ''widechar'')'
EXEC (@BULKSQL)
CREATE TABLE #TEMPSQL
(
[SqlCommand] NVARCHAR(MAX)
)
INSERT INTO #TEMPSQL
SELECT 'INSERT INTO [TEST].[dbo].[IMAGEBULK] SELECT NEWID(),' + '''' + [パス] + '''' + ',[IMAGEDATA].* FROM OPENROWSET(BULK ' + '''' + [パス] + '''' + ', SINGLE_BLOB) AS [IMAGEDATA]' as SqlCommand FROM [dbo].[PhotoProterty]
WHERE [認識された種類] = 'イメージ'
DECLARE @SQL NVARCHAR(MAX)
DECLARE CursorSQL CURSOR FOR
SELECT SqlCommand FROM #TEMPSQL
OPEN CursorSQL
FETCH NEXT FROM CursorSQL
INTO @SQL
WHILE @@FETCH_STATUS = 0
BEGIN
EXEC (@SQL)
FETCH NEXT FROM CursorSQL
INTO @SQL
END
CLOSE CursorSQL
DEALLOCATE CursorSQL
END
コメント
コメントを投稿