Inserting images via T-SQL into MSSQL tables.

[Wade Wegner]

Hi,

Awhile ago I asked some questions in the private SQL groups on the datatype
Image for MSSQL 2000 (Standard Edition on Windows NT 5.0, Build 2195:
Service Pack 4), and received some great feedback. As a result, I decided
to pursue a table schema with the images loaded into the Image data type
fields -- my tests found that it surprisingly works well with Crystal
Reports, so I was happy.

The difficulty has been finding a reliable way to insert images into the
image data type field. I need to do this via T-SQL, and to date the
following procedure is the only thing I have found to work -- except that it
only works with TIFFs, JPGs, and BMPs, and it doesn't seem to work with
GIFs.

How does Microsoft recommend the insertion of images into an Image column,
if they simply want to use T-SQL and not .NET or any VB application? The
goal is to have an image located on the server, and pass the path for this
image to the T-SQL procedure.

Here is the procedure (note -- this is run after the record already exists,
effectively updating the record).

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE dbo.prcUpd_ImageColumn

@strFileName varchar(128),
@uidImage uniqueidentifier

AS
DECLARE @i int,
@size int,
@blocks int,
@ptrval binary(16),
@fso int,
@file int,
@j int,
@hr int,
@buffer varbinary(8000)

-- determine the size (in blocks) of the image
ALTER TABLE #filedetails(altname varchar(30),size int,createdate
varchar(32),createtime varchar(32),lastwrittendt varchar(30),lastwrittentime
varchar(32),lastaccessdt varchar(30),lastaccesstime varchar(32),attributes
int)
INSERT INTO #filedetails exec master..xp_getfiledetails @strFileName

set @size=(select size from #filedetails)

if @size=null
begin
RETURN
end

drop table #filedetails

SET @blocks = @size / 8000+1

-- without making sure that the field is first null, I have received
problems -- not exactly sure why
UPDATE tblImages SET Image = null WHERE uid_Image = @uidImage

EXEC @hr = sp_OACreate 'ADODB.Stream', @file OUT
EXEC @hr = sp_OAMethod @file, 'Open'
EXEC @hr = sp_oasetproperty @file, 'Type', 1

EXEC @hr = sp_oasetproperty @file, 'LoadFromFile', @strFileName

SELECT @ptrval = TEXTPTR(Image) FROM tblImages WHERE uid_Image = @uidImage

exec @hr = sp_oamethod @file, 'read', @buffer out, 8000

if @blocks = 1
begin
-- this seems to work for BMPs
WRITETEXT tblImages.Image @ptrval @buffer
end
else
begin
-- this seems to work for the JPGs and TIFFs
WRITETEXT tblImages.Image @ptrval @buffer
set @j=@blocks-1
while @j>0
begin
exec @hr = sp_oamethod @file, 'read', @buffer out , 8000
set @i=(select DATALENGTH(Image) from tblImages WHERE uid_Image =
@uidImage)
UPDATETEXT tblImages.Image @ptrval @i 0 @buffer set @j=@j-1
end
end

EXEC @hr = sp_OAMethod @file, 'Close'
EXEC @hr = sp_OADestroy @file
EXEC @hr = sp_OADestroy @fso

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

--
http://www.rimrockgroup.com/

--
http://www.rimrockgroup.com/

[Wade Wegner]

Greg,

Thank you for your suggestion, but I do need a T-SQL solution -- if, in this
instance, I could use .NET or another environment, I would.

Please, if anyone has a T-SQL solution to this problem I would greatly
appreciate their help!

Thanks,

Wade

--
http://www.rimrockgroup.com/
"Greg H" <greg.hyza...@trade-ranger.com> wrote in message
news:5267BB3C-B02C-4632...@microsoft.com...
> Might I suggest writing a simple .net app to insert the images. It would
be very simple to code and much simpler. Not to mention that you wouldn't
have to use com and your procedure would only have to take an image data
type and do the insert. This looks like an example of trying to do too much
in T-SQL. The .net app would also be easier to read and allow you to have
better error-handling, etc.
>
> Thanks,
> Greg H

[Wade Wegner]

Turns out there is a pretty simple solution -- only you won't find it
discussed in SQL Books Online (my bad!).

The TextCopy Utility is a command-line utility for importing and exporting
text or images files with SQL Server. Here is the syntax:

TEXTCOPY [/S [sqlserver]] [/U [login]] [/P [password]]
[/D [database]] [/T table] [/C column] [/W "where clause"]
[/F file] [{/I | /O}] [/K chunksize] [/Z] [/?]

A great discussion of this can be found at:

http://www.mssqlcity.com/Articles/KnowHow/Textcopy.htm

So, there is a very simple solution -- and, while it's not 100% T-SQL -- I
can still utilize it as such.

Thanks for your help!