שמירת קבצים בתוך טבלה

SQL Server מאפשר לשמור קבצים בינאריים בתוך טבלה. למשל- טבלת עובדים בה יש עמודה המכילה את התמונה של כל עובד.
כדאי כבר להקדים ולציין שהשימוש בכלי הזה בעייתי- קבצים גראפיים ואחרים נוטים להיות גדולים מאוד, וכשמדובר בטבלאות גדולות – נפח האחסון שלהן גדל למימדים מעוררי חלחלה.. לא לנסות בבית, ובטח שלא במשרד!
החל מגרסת 2008 R2 יש פתרון חלופי – Filestream – בו הקובץ נשמר בנפרד על הדיסק והדטבייס שומר על גודל שפוי (כתבתי על כך פוסט בעבר), אבל אני אעסוק כאן דווקא בפתרון הישן.

ניצור בכונן C: מחיצה בשם Tmp (סביר להניח שלרובנו יש כבר אחת כזו), ונעתיק אליה מספר קבצים.
אני העתקתי קובץ טקסט בשם MyFile.txt,
קובץ Word בשם MyFile2.doc,
וקובץ Excel בשם MyFile3.xlsx.
ניצור טבלה מתאימה לאחסון הקבצים:

Use tempdb;

Go

If Object_Id('MyTbl','U') Is Not Null Drop Table MyTbl;

Go

Create Table MyTbl(ID Int Identity,

                   Teur Varchar(Max),

                   MyFile Varbinary(max));

Go

כעת נקלוט את הקבצים (עם תיאור מתאים) לטבלה:

Insert

Into    MyTbl(Teur,MyFile)

Select  'My text file',

        *

From    Openrowset(Bulk N'C:\Tmp\MyFile1.txt',Single_BLOB) T;

Insert

Into    MyTbl(Teur,MyFile)

Select  'My Word file',

        *

From    Openrowset(Bulk N'C:\Tmp\MyFile2.doc',Single_BLOB) T;

Insert

Into    MyTbl(Teur,MyFile)

Select  'My Excel file',

        *

From    Openrowset(Bulk N'C:\Tmp\MyFile3.xlsx',Single_BLOB) T;

Go

כדאי לזכור שכונן C: הוא זה של השרת (למי שלא מתרגל על התקנה מקומית אלא על שרת הייצור..).

הטבלה נראית כרגע כך-

Select  *

From    MyTbl;

כיצד מחלצים את הקובץ חזרה למחיצה?

קודם כל יש ליצור קובץ פורמט: בלעדיו המערכת מוסיפה באופן אוטומטי מספר תווי בקרה בתחילת כל קובץ, תווים שבמקרה הטוב ניתן למחוק אותם (במקרה של קובץ טקסט) ובמקרה הפחות טוב (קובץ בינארי) – לא ניתן לפתוח אותו.

יש ליצור בעזרת ה-notepad קובץ בשם C:\Tmp\BCP.fmt שכולל את ארבע השורות הבאות:

10.0

1

1 SQLBINARY 0 0 "" 1 MyFile ""

יש לשים לב: הקובץ אינו מסתיים בסוף השורה השלישית, אלא צריך להיות שם מעבר לשורה הבאה.

וכעת נייצא בעזרת קובץ הפורמט את שלושת הקבצים למחיצה בשמות חדשים (MyNewFile במקום MyFile):

Exec XP_CmdShell 'BCP "Select MyFile From tempdb.dbo.MyTbl Where Teur=''My text file''" Queryout "C:\Tmp\MyNewFile1.txt" -T -S localhost -f "C:\Tmp\BCP.fmt"';

Exec XP_CmdShell 'BCP "Select MyFile From tempdb.dbo.MyTbl Where Teur=''My Word file''" Queryout "C:\Tmp\MyNewFile2.doc" -T -S localhost -f "C:\Tmp\BCP.fmt"';

Exec XP_CmdShell 'BCP "Select MyFile From tempdb.dbo.MyTbl Where Teur=''My Excel file''" Queryout "C:\Tmp\MyNewFile3.xlsx" -T -S localhost -f "C:\Tmp\BCP.fmt"';

Go

הערה טכנית- מי שפקודת XP_CmdShell אינה מאופשרת אצלו שיאפשר אותה לפי ההנחיות כאן.

לחילופין ניתן להריץ ממסך ה-Cmd את פקודת ה-BCP שבין הגרשיים, ולהשאיר גרש בודד סביב התיאור של הקובץ ('My text file' למשל).