How to insert a binary file into a image field using stored procedures??

Question

How to insert a binary file into a image field using stored procedures??

Fredy Klammsteiner

SSC Veteran

Points: 278
More actions
June 23, 2004 at 12:19 am

#162777

Hi,
I should insert a binary file (blob) into an image field using a stored procedure.
Does anybody know, how this can be done??

Thanks and kind regards
Fredy

Viewing 11 posts - 1 through 10 (of 10 total)

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

mccork SSC-Insane Points: 22288 More actions · Answer 1

I've previously done this in a rather over-complicated way of using xp_cmdshell to determine where the "textcopy.exe" was located and then using xp_cmdshell again to invoke it to do the load.

If you want to go that way, let me know and I'll dig it up and post an example. It ain't pretty.

Cheers,
- Mark

Fredy Klammsteiner SSC Veteran Points: 278 More actions · Answer 2

Thanks for your response!

Is the xp_cmdshell the only way I could do in a stored procedure to load the file into the db? If yes, please give me an example and the textcopy.exe.

Thanks and regards

Fredy

mccork SSC-Insane Points: 22288 More actions · Answer 3

Fredy,

xp_cmdshell isn't the only way to go. Among a probable multitude of methods there's probably WRITETEXT and UPDATETEXT that could be used in conjunction with OLE automation and the filesystemobject methods. However - and remember, you asked for it - here is a method I use...

use tempdb

go

create table [mybinary] (

[tid] [int] identity (1, 1) not null primary key clustered,

[tname] [varchar] (128) not null ,

[timage] [image] null)

go

-- =======================================================================================================

-- text field handler:

--

-- - find the textcopy.exe utility (shipped with sql2k)

-- - loads/unloads specified text (eg. texthandler.sql) to/from mybinary table

-- to/from the file system

--

-- eg. exec texthandler @action='load', @tname='myphoto.jpg', @timagepath='c:\photos\myphoto.jpg'

--

-- =======================================================================================================

create procedure texthandler

@action varchar(10), -- unload (to file system) or load (to "mybinary" table)

@tname varchar(255), -- name of entry (stored in "mybinary" table)

@timagepath varchar(512), -- path and system filename to content (default directory is %temp%)

@verbose bit=1

as

set nocount on

if @action not in ('load','unload')

begin

raiserror('error: @action should be load or unload',16,1) with nowait

goto finish

end

-- -----------------------------------------------------------------

-- work out from the path variable where the textcopy.exe is

-- -----------------------------------------------------------------

if @verbose = 1 raiserror('texthandler debug: determining path to textcopy.exe',0,1) with nowait

declare @SQL_path varchar(1000), @regkey varchar(260)

declare @textcopypath varchar(1000), @workstr varchar(1000), @worknum int

declare @retcode integer, @error int

create table #cmdshell ([id] int identity, [txt] varchar(2500) null)

--

-- need to look up registry first.

-- registry key to look up differs if this is a named instance.

--

select @regkey = 'software\microsoft\'

if serverproperty('instancename') is null

select @regkey = @regkey + 'mssqlserver\setup'

else

select @regkey = @regkey + 'microsoft sql server\' + convert(sysname, serverproperty('instancename')) + '\setup'

--

-- read registry for sqlpath value

--

execute @retcode = master.dbo.xp_regread 'hkey_local_machine',

@regkey,

'sqlpath',

@param = @SQL_path output

select @error = @@error

if ( @retcode <> 0 ) or ( @error <> 0 )

begin

raiserror('error: running xp_regread to get sql path - retcode %d, error %d',0,1,@retcode,@error) with nowait

goto finish

end

--

-- tack \binn onto sqlpath.... textcopy.exe should be there

--

select @textcopypath = @SQL_path + '\binn'

if @textcopypath is null

begin

raiserror('error: cannot determine path to textcopy.exe',0,1) with nowait

goto finish

end

if @verbose = 1 raiserror('texthandler debug: found textcopy.exe path to be "%s"',0,1,@textcopypath) with nowait

-- -----------------------------------------------------------------

-- see if textcopy.exe is actually there

-- -----------------------------------------------------------------

if @verbose = 1 raiserror('texthandler debug: looking for textcopy.exe in "%s"',0,1,@textcopypath) with nowait

set @workstr = @textcopypath + '\textcopy.exe'

exec master.dbo.xp_fileexist @workstr, @retcode output

if @retcode <> 1

begin

raiserror('error: cannot find textcopy.exe. tried looking in "%s"',0,1,@textcopypath) with nowait

goto finish

end

if @verbose = 1 raiserror('texthandler debug: found %s\textcopy.exe',0,1,@textcopypath) with nowait

-- -----------------------------------------------------------------

-- create slot for text if loading and not already there

-- -----------------------------------------------------------------

if @action = 'load' and not exists

(select * from mybinary where tname = @tname)

begin

if @verbose = 1 raiserror('texthandler debug: new text %s - adding a row to mybinary table',0,1, @tname) with nowait

insert mybinary (tname, timage) values (@tname, '')

if @@error <> 0 or @@rowcount <> 1

goto finish

end

-- -----------------------------------------------------------------

-- load/unload text

-- -----------------------------------------------------------------

if @verbose = 1 raiserror('texthandler debug: using textcopy.exe to %s %s',0,1, @action, @timagepath) with nowait

truncate table #cmdshell

set @workStr = 'cd /d "' + @TextCopyPath + '" & textcopy.exe'

+ ' /S(local)'

+ ' /UMyLogin'

+ ' /PMyBigSecret'

+ ' /D' + db_name()

+ ' /TMyBinary'

+ ' /' + case when @action = 'unload' then 'O' else 'I' end

+ ' /F"' + @timagePath + '"'

+ ' /Ctimage'

+ ' /W"where tname = ''' + @tname + '''"'

+ ' /Z'

insert #cmdshell exec master..xp_cmdshell @workstr

select @worknum = count(*)

from #cmdshell

where [txt] like

case

when @action = 'unload' then '%wrote % bytes to file%'

else '%read % bytes from file%'

end

if @worknum < 1

begin

raiserror('error: problem using textcopy.exe to %s %s. no read/write activity reported:',0,1,@action, @timagePath) with nowait

select isnull([txt],'') as " " from #cmdshell order by [id]

goto finish

end

select @workstr = [txt] from #cmdshell where [txt] like 'data copied % sql server image column % file%'

if @@rowcount < 1

begin

raiserror('error: problem using textcopy.exe to %s %s. debug info follows:',0,1,@action, @timagePath) with nowait

select isnull([txt],'') as " " from #cmdshell order by [id]

goto finish

end

if @verbose = 1 raiserror('texthandler debug: %s',0,1, @workstr) with nowait

-- -----------------------------------------------------------------

-- All finished.

-- -----------------------------------------------------------------

Finish:

go

exec texthandler

@action='load',

@tname='myphoto.jpg',

@timagepath='c:\photos\myphoto.jpg'

go

exec texthandler

@action='unload',

@tname='myphoto.jpg',

@timagepath='c:\photos\stillmyphoto (I hope).jpg'

Cheers,
- Mark

Fredy Klammsteiner SSC Veteran Points: 278 More actions · Answer 4

Great!! Thanks alot for your response. That seems really to help me!

regards Fredy

paras_98 SSCrazy Points: 2994 More actions · Answer 5

Hi... Isn't there any simpler method. May be by using some other stored procedure or function to do the same task?

Because most of the shared servers do not allow use of xp_cmdshell for security reasons.

Paras Shah
Evision Technologies
Mumbai, India

jeremyr Old Hand Points: 306 More actions · Answer 6

DONT do it !! It's bad database design and will produce a vfery slow database.

It's far more efficient to store the binary file in a subdirectory and store the path\filename in the database

You cannot do point in time recovery for the tables if they contain binary data

Paolo-138492 SSC Enthusiast Points: 109 More actions · Answer 7

jeremyr,

what does it mean that i cannot do point in time recovery if they contain binary data? explain more please...

Paolo

Frank Kalis SSC Guru Points: 111183 More actions · Answer 8

Mark,

you didn't mention that xp_regread is undocumented and therefore the use should be considered carefully

Rather than doing this from within SQL Server, I would write some small app in VB and use the ADO Stream Object. That's a LOT easier!

If it needs to be done with T-SQL what about textcopy.exe as Mark mentioned also and which is explained in BOL?

As for jeremyr:

There is no black or white solution and no such simple answer as "Do it" or "Don't do it". This is a "It depends" (hehe, stolen your slogan, Kenneth ) situation. You might consider reading this and decide then which way to go.

--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 9

in SQL 7 you can use the writetext WITH LOG, in SQL 2000 this is ignored and is the same as the database recovery model.

so don't worry about point in time restore being affected.

point in time restore is basically your last backup + all the transaction log backups you take can be used to get back to any point in time. ... not just to when you tooke th backup.

for this to work your database needs to be in full recovery mode and transaction log backup jobs set up.

if you don't need to do this then just put the database in simple mode.

MVDBA

MVDBA (Mike Vessey) SSC-Insane Points: 21797 More actions · Answer 10

if you want a small VB app that can insert binary data into a field then email me at michaelv@quantix-uk.com

i'ev got a small app where you choose your database, table , selection criteria, target field and source data and hey presto- data is loaded.

it's alll done using ado getchunk, so it's quick and painless.

MVDBA