select/insert/update blob fields
-
Is possible to select, insert, update blob fields (just using SQL statements)? Im working w/ a project about storing image file (WMF files) to a database.
Do anyone know of some samples I can test and play with?
I will appreciate any help! Thanks!
AUXilliary COMmunication 1
-
Hi newbie,
Use ADODB.Stream for retrieving/updating blob fields.
Please go to this link http://support.microsoft.com/default.aspx?scid=kb;en-us;258038
Good luck.
-
uhm, yes you can use the ado objects and methods via extended stored procs in Sql.
I have provided a specific real life example, but it is not well documented. Let me know if you would like further information on any of the objects, methods, properties etc. The best source I have found for this stuff is at http://www.w3schools.com/ado/ado_connect.asp The problem is that you have to translate the vb method for invoking the ADO objects/methods into the extended proc method. . .
declare @error int , @object int ,@record int,@fstream int, @con int,@recordset int,@eof int
declare @fields int,@field1 int,@field2 int,@value int
declare @file varchar(256),@ptr binary(8000)
select @value = 24, @file = 'c:\test.txt'--'\\iatdevdc1\dba\downloads\microsoft\sql2k\sqlbolsetup.cab'
exec @error = sp_oacreate 'adodb.recordset',@recordset out
exec @error = sp_oacreate 'adodb.stream',@fstream out
exec @error = sp_oacreate 'adodb.connection',@con out
exec @error = sp_oasetproperty @con,'connectionstring','Provider=SQLOLEDB;Integrated Security=SSPI;Initial catalog=SDv2C;Server=tjames\twhite;'
exec @error = sp_oasetproperty @fstream,'type',1
exec @error = sp_oamethod @fstream,'open'
exec @error = sp_oamethod @fstream,'LoadFromFile',null,@file
exec @error = sp_oamethod @con,'open'
exec @error = sp_oasetproperty @recordset,'source','select top 1 PropertyID,Value from propertytext'
exec @error = sp_oasetproperty @recordset,'cursortype',1
exec @error = sp_oasetproperty @recordset,'locktype',3
exec @error = sp_oasetproperty @recordset,'ActiveConnection',@con
exec @error = sp_oamethod @recordset,'open'
exec @error = sp_oamethod @recordset,'addnew'
exec @error = sp_oagetproperty @recordset,'Fields',@fields out--this is the fields collection not a field object
exec @error = sp_oagetproperty @fields,'item',@field1 out,0
exec @error = sp_oagetproperty @fields,'item',@field2 out,1
exec @error = sp_oasetproperty @field1,'value',@value
exec @error = sp_oagetproperty @fstream,'EOS',@eof out
while @eof = 0
begin
exec @error = sp_oamethod @fstream,'read',@ptr out,4000
exec @error = sp_oamethod @field2,'AppendChunk',null,@ptr
exec @error = sp_oamethod @recordset,'update'
exec @error = sp_oagetproperty @fstream,'EOS',@eof out
--exec @error = sp_oagetproperty @fstream,'position'
-
Thanks to this extended sproc technique. I just learned that it is possible to create objects inside sqlserver, what a nice feature!
Many thanks for the script Tobi. I will just study it!
AUXilliary COMmunication 1
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply