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:
- 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
.
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
))
- 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
- 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.
INSERT INTO
Employee(Name,Photo)
VALUES('Wonde',0x);
UPDATE Employee
SET Photo = 0x
WHERE Photo IS NULL
And to set as default value.
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.
- 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.
CREATE FUNCTION [dbo].[GetBlobData]()
RETURNS VARBINARY(MAX)
AS
BEGIN
DECLARE @IsFileExists INT,
@BinaryData VARBINARY(MAX)
SET @BinaryData =(0x)
EXEC Master.dbo.xp_fileexist N'C:\nophoto.png', @IsFileExists OUT
IF @IsFileExists = 1
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.
INSERT INTO
Employee(Name,Photo)
VALUES('Wonde',[dbo].[GetBlobData]());
UPDATE Employee
SET Photo = [dbo].[GetBlobData]()
WHERE Photo IS NULL
And to set as default value.
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