March 29, 2004 at 9:29 pm
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
March 31, 2004 at 2:29 am
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.
March 31, 2004 at 9:18 am
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'
March 31, 2004 at 2:42 pm
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy