Anybody knows how to insert binary data into mssql ?

  • I have been working upon sql server 2005 and trying to insert binary data into

    database but without successful and I just wonder if anybody could give me

    some advice, thank you.

    1, I created a table with varbinary(max) field for storing binary data

    CREATE TABLE [dbo].[img_item](

    [id] [int] IDENTITY(1,1) NOT NULL,

    [img_data] [varbinary](max) NOT NULL,

    CONSTRAINT [PK_img_item] 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]

    GO

    SET ANSI_PADDING OFF

    2, I got a bulk of data and sprintf trying to insert into database

    sprintf ((char*)binary_query_str, "INSERT INTO [dbo].[img_item] (id, img_data) VALUES ('%d', '%s')", newId, binary_escape_str);

    BYTE *binary_escape_str;

    The data I am trying to insert is:

    \0\b\b\t\t\b \r

     $.\' \",#(7),01444\'9=82<.342踈0?郳0JFIF\0\0\0\0\0\00*Intel(R) JPEG Library, version 1,5,4,36\0踈0CC\t\t\t

    \r\r2!!22222222222222222222222222222222222222222222222222?0\0\0\0\0\0\0\0\0\0\b\t

    \0\0\0}\0!1AQa\"q2亼b#B绷R佯$3br俓t\%&\'()*456789:CDEFGHIJSTUVWXYZcdefghijstuvwxy\z儎厗噲墛挀敃枟槞殺¥ウЖ┆渤吹斗腹郝媚牌侨墒矣哉肿刭卺忏溴骁栝犟蝮趱鲼?\0\0\0\0\0\0\0\b\t

    \0\0w\0!1AQaq\"2乗bB憽绷\t#3R?br裓n$4醆%?&\'()*56789:CDEFGHIJSTUVWXYZcdefghijstuvwxy\z們剠唶垑姃摂晼棙櫄ⅲぅΗī炒刀犯购旅呐魄壬室釉罩棕仝忏溴骁栝牝篝貊鼬繺0\b\0d\0d\"\0赲0 \0\0?\0?x3路^瘐徘喆iL秠$捖&gc扞+扞颷燄倄?M\0?\0?x蘒鐔快k\0篭n\0?\0??齖n???\0鈎\0??\0?h??\0壆倞\0?\0??齖n???\0鈎\0??\0?h??\0壆倞\0?\0??齖n???\0鈎\0??\0?h??\0壆倞\0?\0??齖n???\0鈎\0??\0?h??\0壆倞\0?\0??齖n???\0鈎\0??\0?h??\0壆倞\0鵖沔摝柁:狈音鸎L嵹;XV\%-媸2B€3€9?鍀?磜鼣;?G\0??伏\t\0$竺\_鯸n?\0袺]p?眖4鹈勐\%a仁Q庝X損柔NH钖cG*磏2?0霢iM騖%\'元q鏽+“为9`*紖O呦也NI?歮s猴?ufD?缇*誤兊?…熔\z苉JSr芪?F薻\z啤i狵凳?aLa彬凖k殯?癧Ms6?qCpm潔?恥\0卥鴱\\恥鑬耱瓁志s岘OG坒\0衆rtR+?#;?磨樐]剻d1榥a??巍璡b\0憥!\\伏价WIS抗峏蒶┹N? 韝蝴O醁:y艝t6?觚??*閄Xwds驰屮屟]蟡u?伶鶓?嗥\tq摿?矿?]陹?涗秌?l姤U蕇瀪??萅Ж祟诚Z椓鈂rj?钮1N鸔?\0旧黬*徙nI弧┗?\0扑榈/h譿2嗓\0?惴壹谓鉢0频忴?鲴=y誶瓓>埔釂\_匎{剠釓N磄Ib?僜b\\?剥]\t9螝坁h?+*?@n\z蹼?詾€茳0?V陧[n~V騑?灑臣7)昻娙?敋0b(鸜F\t蒹2悾 ??SǒE??鉣y珇︽趎=?\0QHq焅z??娬眗著=?U?邦煃iI贔U曘s楍\b垡裎?輁_笸rrx7A笧鈟lwKp扭c+騅n$s?樸#‘穃_\_鵀Nq纤?鱁TU=v淲L[M劁g沰0E巅賛mt窻蜠tW暐\%芒?呦a炚.嫟鴇Zy狠ゥ溑稤弎\"噺\r唖怤}?汁3阇#箃益絺\b氄|?傎?尪8??A沧}\_蘡c\t來?根?-憣 ?T\'\z彏N签墹?-A]Z锂恐タ\'?=宗-^驺腆鞮wy€嗐v9 \zt??:腛umh?厄I Id\0^O錦\啗釢/Z?骚亟緭f豬|苚窗=塡'邈蔽=\t鬕懚謈鑷鵙鴶涆╀GS羱2葁C驱\0J\'?疐s環$g兀樽蟐sX瓓>唣/鼡蟎r??\0E-S2Og琝%A寤J泵*3終/Rq笘儒€趡鮘\餦'鼡蟎r??\0E-f牮汷夆筴e戔歽$怽b?腟hbF<.7q摑i菤耦塷暕?3灤?)阋C{T謲嫊<笈G帵湽WV嘌?b褏??2?5煫X蒈i峔r澚唙U+\"筣紓y佴g揦慶簴?丫7T洙闔\'├陠眎v哱rZ鼏噇?;暩羘?譩k[渏椹&?

    xoR?2$笽-?<槤奔曺癋3亴謂结絉株?g槯偬穡?艕yo‰廽朦\%\i?on镤濧?栢酣莭\_[浴x/,RI<駆司9v`?熄?\0氕)‥Y猹苫谨锈-婈贘i?煣}奤y

    眿瓹g怚鐑撝簕?濫c縞`鱓6塷o╆(?觨s€XdG-幑\0c姚氁嬉?\_呷\b9\0I/?\\駛~O\_cNs媞N如鰊=O#\0!??\0g?疍s|??甄u??\0?鹱齻?鬜?U厒FQ?妑虡\\卜伾;FP\0?鹱齻?鬜諒?絼w0\tH逼Py泿凸~?弻懭Cp鴷墯鱄鈶\%xZu br2欑庛垡瀄zUM畁舾$欻\'旑磉|d a粚蟸氮?~?齥扨|蚗韰H颚賁方9Q茚T撿+E-\"N鳗駧斦浜擘<灼$:?}║!H,0\z翮帣fウ\\Z]GxCn^#T9蕦幗?綷"跪髶盭ch麳х\\?E璿,j\0剎?汶盈|\z盐's]*塍,?v?皩:?縍?:螙驸鈌杋I\0?躙z齖t李;饈j绳ヽ???1霃诗KtmXf@c?侫S?詂?&4?iA筡b徧#+?岤帲鵥燊硋!\_.F?,樔U\0摓=?\z琝%骕C!]蠃P\0\0镓騸圍MЩGh蹎椰?l?熕穃z?搮?戞?\0????魘蹒U锜\0????魘蹒u躣}苦O鵟'?\0癠奪輺(鎵5?eq怗?呧O鵟'?\0癠奪?\00牓\0(\0图?紹v氷蠯#c$籿靔缠CAA呌恦巅黭n奬0萛_

    枞r禶:HC醈醢S邨o癍??Q??0}櫩茣|)"lK?限秇>Zm〕裰沷n沑"M&=珤q廫z騔??﨡圐偅\0巡讖??煆?汛k.逴褮

    +x磴i!敱TP牅Hp=\\妄铪铪铪'

    Or say, anybody knows something about how to excute statements that contain binary data, especially bulk binary data, into mssql ? Um....

  • Take a look at "bcp" in Books Online. That might have what you need.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • From SSMS a binary file can be inserted using OPENROWSET.

    INSERT INTO img_item(img_data)

    SELECT * FROM OPENROWSET(BULK N'C:\myfile.jpg', SINGLE_BLOB) AS img_data

    In a .NET application a binary file can be loaded into a byte array, then inserted.

    ' VB.NET example

    ' Load file into a byte array

    Dim fi As New System.IO.FileInfo("c:\myfile.jpg")

    Dim fs As System.IO.FileStream = fi.OpenRead

    Dim lBytes As Long = fs.Length

    Dim myImage(lBytes) As Byte

    fs.Read(myImage, 0, lBytes)

    fs.Close()

    ' Insert binary data into database

    Using myConn as New Data.SqlClient.SqlConnection(My.Settings.myConnectionString)

    Dim myCommand as new Data.SqlClient.SqlCommand("INSERT INTO img_item VALUES (@img_data)", myConn)

    myCommand.Parameters.AddWithValue("@img_data",myImage)

    myConn.open()

    myCommand.ExecuteNonQuery()

    myConn.Close()

    End Using

  • Maybe I didnt mention cleary basically I got the binary data which you can see above

    and trying to insert into database but without success.

    And if just directly insert into db it would be much easier but with the requirements we

    have and we have to decode the image, encrypte it and then format into string before

    inserting into microsoft database, Um. It isnot a .net app, it is win32 app in vs2005

    with sql server2005,ummmmm

  • GSquared (5/12/2008)


    Take a look at "bcp" in Books Online. That might have what you need.

    Okay, I will have a look at "bcp", cheers

  • Or you can connect with a stream file object.


    N 56°04'39.16"
    E 12°55'05.25"

  • Peso (5/13/2008)


    Or you can connect with a stream file object.

    Okay, i will have a try... um

  • Dear Roger,

    Please refer to the example in SET ANSI_PADDING (Transact-SQL),

    http://msdn.microsoft.com/en-us/library/ms187403.aspx

    Hope it is useful.

    Best Regards, Yong Seng

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply