How can I tell if an "image" data type column contains a value?

Question

How can I tell if an "image" data type column contains a value?

Michael Chang-204743

SSC Enthusiast

Points: 105
More actions
January 27, 2005 at 3:19 pm

#163711

I'm working on an ASP.NET application in C# and after weeks of trying I've finally gotten a form to upload some values into a SQL table.
One of the uploaded values is a file attachment that is being sent to an "Image" column.
When I browse the table, I can see the text values of the other columns inserted but the image column just says <binary>. Of course I know it's a binary type column, wouldn't a "Null" server me better (if it was indeed a null value)?
What should I see if the file is actually contained in the column and if it is how can I tell?

Not sure how clear this post is but I appreciate any help,

-MC

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

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

dmbaker SSCertifiable Points: 5139 More actions · Answer 1

What query tool are you using? Query Analyzer shows NULL if there's no data. Can you use the datalength() function to determine if there is any data in the field?

Quick test:

create table tmp_table

(

some_number int,

some_data image

)

insert into tmp_table (some_number) values (1)

select some_number, some_data, datalength(some_data)

Query Analyzer shows NULL for values of both some_data and datalength(some_data).

So, maybe try datalength()?

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 2

If the table allows NULL for the image then:

drop table t

go

create table t (c1 int, i1 image null)

go

insert into t values (1,'111')

go

insert into t values (2,NULL)

go

select * from t

go

select c1 from t where i1 is null

go

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Michael Chang-204743 SSC Enthusiast Points: 105 More actions · Answer 3

Holy cow, you mean to tell me I have to write a (mini) program just to determine if the record holds a value in a particular column?

I do appreciate the suggestions. Coming from the Notes/Domino world this is done via a single click and eyeballing of the document's properties.

Isn't there some sort of query I can write like

Select * from TABLENAME where IMAGECOLUMNNAME != Null

?

If something like that is doable, could someone tip me off as to the appropriate syntax,

I greatly appreciate any assistance,

TKS/MC

Rudyx - the Doctor SSC-Forever Points: 43695 More actions · Answer 4

Select count(*) from TABLENAME where IMAGECOLUMNNAME IS Null

RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."

Michael Chang-204743 SSC Enthusiast Points: 105 More actions · Answer 5

Hey thanks Rudy, that worked.

FYI I modified your query to be:

Select * From TABLENAME where (IMAGECOLUMNNAME Is Not Null)

and this gives me exactly what I want, I get returned any record(s) where the image type column contains a value.

Thanks again,

-MC