הבלוג של גרי רשף

18/09/2011

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

Filed under: Uncategorized — גרי רשף @ 18:42

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;

clip_image002

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

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

יש ליצור בעזרת ה-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' למשל).

מודעות פרסומת

7 תגובות »

  1. […] גרסת 2008 ניתן היה לשמור קבצים בטבלאות בעמודות BLOB כמתואר בפוסט הזה. הפתרון מאוד בעייתי משתי סיבות עיקריות: 1. הדבר גורם […]

    פינגבאק של FileTable ב-SQL Server 2012 - SQLServer.co.il — 22/12/2011 @ 21:55

  2. […] גרסת 2008 ניתן היה לשמור קבצים בטבלאות בעמודות BLOB כמתואר בפוסט הזה. הפתרון מאוד בעייתי משתי סיבות עיקריות: 1. הדבר גורם […]

    פינגבאק של FileTable ב-SQL Server 2012 - גרי רשף — 22/12/2011 @ 20:56

  3. […] גרסת 2008 ניתן היה לשמור קבצים בטבלאות בעמודות BLOB כמתואר בפוסט הזה. הפתרון מאוד בעייתי משתי סיבות עיקריות: 1. הדבר גורם […]

    פינגבאק של FileTable ב-SQL Server 2012 « הבלוג של גרי רשף — 22/12/2011 @ 20:54

  4. הבעיה שלך נעוצה ב XP_CmdShell (ולכן אני חוזר כל פעם ואומר שאין מספיק התייחסות לנושא הזה, בעיקר מצד אנשים שהם DBA שלא תמיד מתריעים מפני השלכות של חורי אבטחה)
    אם תחפש בגוגל אתה תראה סרטונים מעניינים, איך מנצלים את זה שפתחת את האופציה לעבוד עם
    XP_CmdShell, ודרך שאילתות SQL לדוגמא פותחים לך על השרת משתמשים חדשים של מערכת ההפעלה, וכל מה שנשאר לך זה להתחבר ב RDP ואתה בעל הבית:)
    תעבור על זה באיזה לילה שאתה לא נרדם
    http://www.google.co.il/#hl=iw&q=youtube.com+:+sql+server+hacking&oq=youtube.com+:+sql+server+hacking&aq=f&aqi=&aql=&gs_sm=e&gs_upl=4282l5188l2l5360l7l7l0l3l0l1l235l422l0.1.1l2l0&bav=on.2,or.r_gc.r_pw.&fp=7732078a1dd70fa5&biw=819&bih=488

    תגובה של pelegk1 — 18/09/2011 @ 20:48

    • הבעייתיות של XP_CmdShell ידועה.
      שאלתי אם יש בעיית אבטחה באחסון קבצים בתוך טבלה.

      תגובה של גרי רשף — 19/09/2011 @ 07:57

  5. 1) זה פתח לבעיית אבטחה
    2) עדיף להשתמש באופציה של שמירת קבצים ל DB רק למקרה קיצוני של אין ברירה, מאחר וזו לא צורת עבודה נכונה וזה לא ייעודו.

    תגובה של pelegk1 — 18/09/2011 @ 19:18

    • לגבי 2 – מסכּים.
      לגבי 1 – תוכל להסביר בקצרה?

      תגובה של גרי רשף — 18/09/2011 @ 19:30


RSS feed for comments on this post. TrackBack URI

להשאיר תגובה

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

הלוגו של WordPress.com

אתה מגיב באמצעות חשבון WordPress.com שלך. לצאת מהמערכת / לשנות )

תמונת Twitter

אתה מגיב באמצעות חשבון Twitter שלך. לצאת מהמערכת / לשנות )

תמונת Facebook

אתה מגיב באמצעות חשבון Facebook שלך. לצאת מהמערכת / לשנות )

תמונת גוגל פלוס

אתה מגיב באמצעות חשבון Google+ שלך. לצאת מהמערכת / לשנות )

מתחבר ל-%s

בלוג בוורדפרס.קום.

%d בלוגרים אהבו את זה: