Click here to Skip to main content
65,938 articles
CodeProject is changing. Read more.
Articles / database / SQL-Server / SQL-Server-2008

How to Set Default Value or Binding for BLOB DataTypes on SQL Server

5.00/5 (83 votes)
15 Jan 2013CPOL2 min read 42K  
This tip/trick helps to set a default value for blob data type such as Image, VarBinary.

Introduction

This tip/trick helps to set a default value for BLOB data type such as Image and VarBinary.

Background and Problem

There is a time you need to have image/file on a database table. The table field may be required after a certain time or it must contain default value if its value wasn't available at the time of data insertion. The existing data should also be updated from null to some real data.

There are a number of ways to achieve such situations. I'll present a couple of ways as follows. Before the solution, let's closely see the values of an Image/VarBinary column . A simple SQL Select statement will show us that the value consists of hexadecimals which start with an 0x. Example could be 0x89504E47... From this, we can observe that the value should start with a 0x (a zero and an x, say Zerox/Zerobyte) and a hexadecimal value(0-9 and A-F) values. The table below summarizes the observation.

Hexadecimal value

0x

89504E47

Mandatory value

Optional value<span style="FONT-SIZE: 10pt"></span>

To make the solution easy, let's assume we have:

  1. A table Employee with columns ID, Name, Photo with data types int, nvarchar and image respectively. Note: For simplicity, I omitted lots of columns that can represent an Employee.
    SQL
    CREATE TABLE [dbo].[Employee](
        [ID] [int] IDENTITY(1,1) NOT NULL,
        [Name] [nvarchar](50) NULL,
        [Photo] [image] NULL,
     CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED 
    (
        [ID] ASC
    ))
  2. An image file which is located in a specific directory say (c:\) with a name nophoto.png. A sample image can be downloaded from here.

How to Solve the Problem

  1. Simplest but Clumsy way (Zerox/Zerobyte): The simplest way is to use Zerox/Zerobyte(0x) or set this value to the desired column default value property.
    SQL
    -- Insert new Employee
    INSERT INTO 
    Employee(Name,Photo) 
    VALUES('Wonde',0x);
    
    -- Update Employees where photo is unavailable
    UPDATE Employee 
    SET Photo = 0x 
    WHERE Photo IS NULL

    And to set as default value.

    SQL
    ALTER TABLE [dbo].[Employee] 
    ADD  CONSTRAINT [DF_Employee_Photo]  
    DEFAULT (0x) FOR [Photo]

    Although this solves the problem, Zerox/Zerobyte(0x) may not represent a true image/file content value for future use.

  2. Graceful way: The handy way is to create a function that read an image/file from a specific directory and returns it as Varbinary(MAX) datatype so it can be used directly or can be set directly to desired column default value property.
SQL
CREATE FUNCTION [dbo].[GetBlobData]()
RETURNS VARBINARY(MAX) 
AS
BEGIN
    DECLARE @IsFileExists INT,
        @BinaryData VARBINARY(MAX)
    -- Set minimum binary representation Hexadecimal number as default
    SET @BinaryData =(0x) 
    -- First check if the file exists on the directory 
    EXEC Master.dbo.xp_fileexist N'C:\nophoto.png', @IsFileExists OUT 
    IF @IsFileExists = 1
    
        -- If exists then read the file content
        SET @BinaryData = (SELECT * FROM OPENROWSET(BULK N'C:\nophoto.png',SINGLE_BLOB) _
	AS BLOBData)

    RETURN @BinaryData
END  

Notice the code, if the file does not exist, it will send a Zerox/Zerobyte(0x). Let's modify the previous example using this function.

SQL
-- Insert new Employee
INSERT INTO 
Employee(Name,Photo) 
VALUES('Wonde',[dbo].[GetBlobData]());

-- Update Employees where photo is unavailable
UPDATE Employee 
SET Photo = [dbo].[GetBlobData]() 
WHERE Photo IS NULL

And to set as default value.

SQL
ALTER TABLE [dbo].[Employee] 
ADD  CONSTRAINT [DF_Employee_Photo]  
DEFAULT ([dbo].[GetBlobData]()) FOR [Photo]
Things to note here are that the SQL Server should have an access to the image/file and user who will use the function should have permission to use it.

Points of Interest

I found the tip is useful and wanted to share it with the community. I hope you will find it useful as well.

History

  • January 01, 2013 First version
  • January 05, 2013 Updated version
  • January 15, 2013 Updated version

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)