Embedded Objects in an image column

Question

Embedded Objects in an image column

Wayne Gipters

Valued Member

Points: 63
More actions
November 14, 2006 at 4:57 pm

#117616

Does any one have any ideas on how I can stop people embedding photos into an image type column and only allow them to 'link' the photo. I can't do this via the applications front end, so could I do this via a trigger?
Also is there some t-sql to wrap in a cursor to identify any embedded images so I can fix them. The offending Table in my database (maintained by a 3rd party vendor) is growing way big.
Any quick way of highlighting this data would be great as I can put the query into a cursor. A brief tsql statemet would be great...
TA
Wayne

Viewing 6 posts - 1 through 6 (of 6 total)

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

Sergiy SSC Guru Points: 110209 More actions · Answer 1

Actually embedding photos is what image datatype is about.

If you don't want them to embed photos then just remove image column and replace it with nvarchar(255) one. They gonna store links there, not photos.

To identify embedded image is really easy: WHERE image_column IS NOT NULL.

No other kind of data may occur in this column.

_____________
Code for TallyGenerator

Wayne Gipters Valued Member Points: 63 More actions · Answer 2

Thanks for that, But I am contsrained as the database is maintained by a vendor & I can not change the type of the column not their front end app but I can add triggers etc. So I still need to identify linked objects as opposed to embedded objects. Identifying Null data is no help but thanks any rate for the suggestion.

TA

Wayne

Sergiy SSC Guru Points: 110209 More actions · Answer 3

If it's an image it's not a link by definition. Everything going into image_column is an image, not link. Even if that image is just displaying picture of a link.

Easiest thing you can do is to separate images from data.

Create new filegroup (on another disk, if possible) and set it as "Text Filegroup" for that table.

All your photos will go there.

_____________
Code for TallyGenerator

Wayne Gipters Valued Member Points: 63 More actions · Answer 4

Thanks for clarifying that. I will create the new file group as suggested.

Problem solved : ta &

Regards

Wayne

Carl Federl One Orange Chip Points: 25384 More actions · Answer 5

Using the Northwind demo database and the Employee table, below is a trigger that will only allow the Photo column to contain a value that begins with 'http'. Change or add the criteria as needed for your particular specifieciation, such as the photo must begin with a UNC or Drive letter.

A few SQL Server restrictions on the image datatype:

Image datatypes cannot be referenced in check constraints so

alter table Employees

add constraint Employees_C_Photo_HTTPOnly

CHECK ( cast( substring( photo, 1, 255 ) as varchar(255) ) like 'http%')

Server: Msg 1760, Level 16, State 1, Line 2

Constraints of type CHECK cannot be created on columns of type IMAGE.

In a trigger, image data in the inserted and deleted virtual table cannot be reference, so a join to the target table must be performed to see the image data.

CREATE TRIGGER Employees_tia001 ON dbo.Employees FOR insert AS

set nocount on

set xact_abort on

if 0 = (select count(*) frominserted) return

IF 0

(select count(*)

from inserted

joinEmployees

on Employees.EmployeeID= inserted.EmployeeID

wherecast( substring( Employees.photo, 1, 255 ) as varchar(255) ) NOT like 'http%')

begin

raiserror('Employee photos may only contain file names on web servers',16,1)

rollback

return

end

return

GO

-- Test Cases

-- Success expected

INSERT INTO Employees

(Photo, LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, Address, City, Region, PostalCode, Country, HomePhone, Extension, Notes, ReportsTo, PhotoPath)

select 'http://myfile.jpg'

, LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, Address, City, Region, PostalCode, Country, HomePhone, Extension, Notes, ReportsTo, PhotoPath

from employees

whereEmployeeID = 1

-- failure expected

INSERT INTO Employees

(Photo, LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, Address, City, Region, PostalCode, Country, HomePhone, Extension, Notes, ReportsTo, PhotoPath)

go

select '0x151C2F00020000000D000E'

, LastName, FirstName, Title, TitleOfCourtesy, BirthDate, HireDate, Address, City, Region, PostalCode, Country, HomePhone, Extension, Notes, ReportsTo, PhotoPath

from employees

whereEmployeeID = 1

SQL = Scarcely Qualifies as a Language