Read Write Image Data Using T-SQL

Recently I need to display an image as logo on the SSRS reports. Since the logo needs to be replaced easily without modify the reports, I plan to store the image in a SQL table and use it as a database source image. The Image could be stored in either [VARBINARY](MAX) or [IMAGE] data type.

I use the OPENROWSET T-SQL script to upload the image file into the table. Then I could use the SELECT statement to retrieve it in SSRS.

CREATE TABLE [dbo].[Images](

[ImageID] [int] IDENTITY(1,1) NOT NULL,

[ImageName] [nvarchar](40) NOT NULL,

[ImageFile] [varbinary](max) NOT NULL,

) 

INSERT INTO [dbo].[Images]

([ImageName]

,[ImageFile])

SELECT 'Logo.png', * FROM OPENROWSET(BULK N'C:\MyPhoto.png', SINGLE_BLOB) as Logo

GO

To promote this image data to different systems, I need to create INSERT or UPDATE T-SQL statements to facilitate it.

For VARBINARY data type:

• Use DATALENGTH()  to determine the size of the data first.
  
• Then, use SUBSTRING to read in the data in 1024 bits
  
• Use PRINT function to generate the Insert/Update statement that using .WRITE(expression, NULL, 1024) to append the image data in small pieces.

DECLARE @ptrval varbinary(1024), @Len int, @start INt, @end INT

SELECT @Len = DATALENGTH(ImageFile) FROM dbo.Images WHERE ImageName = 'Logo.png'

SET @Len = @Len-1    

SET @start = 0

PRINT 'INSERT INTO [dbo].[Images]

           ([ImageName]

           ,[ImageFile])

     VALUES

           (''Logo''

           ,CAST('''' AS VARBINARY))'

While @start < @Len 

BEGIN

    SELECT @ptrval = SUBSTRING(ImageFile, @start, 1024)  FROM dbo.Images WHERE ImageName = 'Logo.png' 

    PRINT 'UPDATE [dbo].[Images] SET [ImageFile].Write('

    PRINT @ptrval

    PRINT ', NULL, 1024 )

    WHERE [ImageName] = ''Logo'''

    SET @start =@start + 1024 

    SET @end = @start + 1024-1

    IF @end > @Len

    BEGIN

        SET @end = @Len-@start+ 1

        SELECT @ptrval = SUBSTRING(ImageFile, @start, 1024)  FROM dbo.Images WHERE ImageName = 'Logo.png' 

        PRINT 'UPDATE [dbo].[Images] SET [ImageFile].Write('

        PRINT @ptrval

        PRINT ', NULL, 1024 )

        WHERE [ImageName] = ''Logo'''

        SET @start = @Len

    END

END

• After executing above query, the following result will be created and could be used as Insert/Update statements.

INSERT INTO [dbo].[Images]([ImageName] ,[ImageFile])VALUES ('Logo', CAST('' AS VARBINARY))

UPDATE [dbo].[Images] SET [ImageFile].Write(0xFF123462ABCE657033ED..........., NULL, 1024 )WHERE [ImageName] = 'Logo'

UPDATE [dbo].[Images] SET [ImageFile].Write(0x5C493D2522B62D1C894E3C..................., NULL, 1024 ) WHERE [ImageName] = 'Logo'.......

For IMAGE data type:

• Use DATALENGTH()  to determine the size of the data first.
  
• Use TEXTPTR() to retrieve a VARBINARY pointer.
  
• Then use READTEXT function to read in the data in small pieces.

DECLARE @ptrval varbinary(16), @Len int, @start INt, @end INT

SELECT @Len = DATALENGTH(ImageFile) FROM dbo.Images WHERE ImageName = 'Logo.png'

SET @Len = @Len-1 

SELECT @ptrval = TEXTPTR(ImageFile) FROM dbo.Images WHERE ImageName = 'Logo.png'

SET @start = 0

While @start < @Len 

BEGIN

    READTEXT dbo.Images.ImageFile @ptrval @start 1024;

    SET @start =@start + 1024 

    SET @end = @start + 1024-1

    IF @end > @Len

    BEGIN

        SET @end = @Len-@start+ 1

        READTEXT dbo.Images.ImageFile @ptrval @start @end;

        SET @start = @Len

    END

END

• After executing above query and storing the results in a text file,  Construct the Insert statement with a pointer and using UPDATETEXT statement to append the image data.

EXEC(N'INSERT INTO [dbo].[Images] ([ImageName], [ImageFile]) VALUES (''Logo'',0x89504E470...)')

EXEC(N'DECLARE @pv binary(16)

SELECT @pv=TEXTPTR([ImageFile]) FROM [dbo].[Images] WHERE [ImageName]=''Logo''

UPDATETEXT [Report].[Images].[ImageFile] @pv NULL NULL 0x493D2522B62D.......

....

');

Now, I could execute the T-SQL statement created against any new server to upload the image data.