limit size of image column

I'm trying to limit the size of images that can be inserted into an Image
type column. The client side tools are not letting me do this so I thought I
could do it in a trigger or constraint in the backend. So I discovered that
I can't use Image datatypes in constraints. And also discovering that I
can't use it the way I thought I could in a trigger. HEre's what I was goign
to do:
IF UPDATE(ItemPic)
BEGIN
IF (SELECT DATALENGTH(ItemPic) AS ItemPicSize FROM INSERTED) > 100000
BEGIN
ROLLBACK TRAN
SET @.sMsg = 'Item Picture cannot exceed 100Kb.'
RAISERROR (@.sMsg, 16, 1)
RETURN
END
END
Which of course doesn't work because I get the error "Cannot use text,
ntext, or image columns in the 'inserted' and 'deleted' tables."
Is there a good way to do this? I won't be allowing very large images so
maybe a different data type would work?
Thanks,
KeithHow are they getting the image file to the database server? Presumably,
they are using a web app or a windows app (I don't know what "client side
tools" are, and why they don't let you do something), in which case surely
there is a place along the pipeline that you can validate the size BEFORE
you run the INSERT statement.
"Keith G Hicks" <krh@.comcast.net> wrote in message
news:OUJSYHTaGHA.3328@.TK2MSFTNGP02.phx.gbl...
> I'm trying to limit the size of images that can be inserted into an Image
> type column. The client side tools are not letting me do this so I thought
> I
> could do it in a trigger or constraint in the backend. So I discovered
> that
> I can't use Image datatypes in constraints. And also discovering that I
> can't use it the way I thought I could in a trigger. HEre's what I was
> goign
> to do:
> IF UPDATE(ItemPic)
> BEGIN
> IF (SELECT DATALENGTH(ItemPic) AS ItemPicSize FROM INSERTED) > 100000
> BEGIN
> ROLLBACK TRAN
> SET @.sMsg = 'Item Picture cannot exceed 100Kb.'
> RAISERROR (@.sMsg, 16, 1)
> RETURN
> END
> END
> Which of course doesn't work because I get the error "Cannot use text,
> ntext, or image columns in the 'inserted' and 'deleted' tables."
> Is there a good way to do this? I won't be allowing very large images so
> maybe a different data type would work?
> Thanks,
> Keith
>|||Read more about Image column
[url]http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part3/c1161.mspx[/url
]
Madhivanan|||I've already spoken to the developers of the client side tools I'm using
(3rd party windows app dev tools). I'm using one of their image controls
which has no ability to test or limit the size before the fact.
The only other part of the pipeline is in ado. I'd have to do it in the
OnBeforePost event of the ado dataset but accordign to the Delphi ado help,
there's no way to determine the data size of a blob control's data. The
following is from the help file (note the line "this does not work for BLOB
fields"):
with Field1 do
begin
if not IsBlob { this does not work for BLOB fields }
begin
{ Allocate space }
GetMem(MyBuffer, DataSize);
try
if not GetData(MyBuffer) then
MessageDlg(DisplayName + ' is NULL', mtInformation, [mbOK], 0)
else
{ Do something with the data };
finally
{ Free the space }
FreeMem(MyBuffer, DataSize);
end;
end;
end;
Which is why I came here as a last resort because I agree, the situation
should be handled before it ever gets to the db.
Keith
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OcB$30TaGHA.1200@.TK2MSFTNGP03.phx.gbl...
How are they getting the image file to the database server? Presumably,
they are using a web app or a windows app (I don't know what "client side
tools" are, and why they don't let you do something), in which case surely
there is a place along the pipeline that you can validate the size BEFORE
you run the INSERT statement.|||Try using an INSTEAD OF tigger. In pseudo code:
if no images larger than 10000 exist
insert rows
else
rollback
raiserror
ML
http://milambda.blogspot.com/|||That was actually the very first sql thing I tried. But I have foreign keys
with cascade updates/deletes between this and other tables that are not
allowing me to hae an INSTEAD OF UPDATE, INSERT trigger on this table.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:2FFA09F9-2D72-4AE9-8FE2-BC1FE6D42A76@.microsoft.com...
Try using an INSTEAD OF tigger. In pseudo code:
if no images larger than 10000 exist
insert rows
else
rollback
raiserror
ML
http://milambda.blogspot.com/|||Well, I think the answer for me at this point is to call a stored procedure
from the trigger and test teh size that way. That seems to be working sort
of. There are a couple of glitches but those are in a new post. I'm all set
in this one. Thanks for the answers folks. - keith|||Another approach may be to get the ADO call from Delphi to call a stored
procedure instead. Then insert into that stored procedure the code to
validate the size of the file.
If that's possible, then surely it would be possible to check the file size
before even making the call to a stored procedure. You know, what's the
point sending a 10Mb Graphic to the SQL Server cloging up the network, only
to have SQL say know and through the whole lot away.
Regards
Colin Dawson
www.cjdawson.com
"Keith G Hicks" <krh@.comcast.net> wrote in message
news:es9MQtUaGHA.428@.TK2MSFTNGP02.phx.gbl...
> Well, I think the answer for me at this point is to call a stored
> procedure
> from the trigger and test teh size that way. That seems to be working sort
> of. There are a couple of glitches but those are in a new post. I'm all
> set
> in this one. Thanks for the answers folks. - keith
>