May 9, 2008 at 6:16 am
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\00*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路^瘐徘喆iL秠$捖&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損柔NH钖cG*磏2?0霢iM騖%\'元q鏽+“为9`*紖O呦也NI?歮s猴?ufD?缇*誤兊?…熔\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\\?剥]\t9螝坁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\_cNs媞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禶:HC醈醢S邨o癍??Q??0}櫩茣|)"lK?限秇>Zm〕裰沷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....
May 12, 2008 at 1:29 pm
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
May 12, 2008 at 3:44 pm
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
May 13, 2008 at 2:46 am
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
May 13, 2008 at 2:47 am
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
May 13, 2008 at 4:18 am
Or you can connect with a stream file object.
N 56°04'39.16"
E 12°55'05.25"
May 13, 2008 at 4:24 am
Peso (5/13/2008)
Or you can connect with a stream file object.
Okay, i will have a try... um
May 29, 2008 at 1:25 am
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