I have a table with a blob column (that is stored in a sbspace). Is
there a way to do something like:

SELECT * FROM <table> WHERE SIZE(<blob_col>) = 12345;


I have tried that and also using "LENGTH()" instead of size. No go.
Is there a way to do this using SQL?

Thank you.

DG

P.S. I know that to actually retrieve the data, I need to use one of
the provided functions ["lotofile()"]



On Feb 28, 10:19*am, pretzel <davidegr... (AT) gmail (DOT) com> wrote:

On Feb 28, 10:21*am, pretzel <davidegr... (AT) gmail (DOT) com> wrote:
which rows contain a zero-length blob. Hence, the query would more
accurately be described as:

SELECT row_pk FROM <table> WHERE SIZE(<blob_column>) = 0;

IB that you can use the LENGTH() function with a BLOB/CLOB type column, I
know you can use it with a TEXT/DATA type column.

Art

Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (art (AT) iiug (DOT) org)
Blog: http://informix-myview.blogspot.com/

Disclaimer: Please keep in mind that my own opinions are my own opinions and
do not reflect on my employer, Advanced DataTools, the IIUG, nor any other
organization with which I am associated either explicitly, implicitly, or by
inference. Neither do those opinions reflect those of other individuals
affiliated with any entity with which I am affiliated nor those of the
entities themselves.



On Mon, Feb 28, 2011 at 4:29 PM, pretzel <davidegrove (AT) gmail (DOT) com> wrote:

On Feb 28, 1:23*pm, Art Kagel <art.ka... (AT) gmail (DOT) com> wrote:
Thank you, Art. I always appreciate your posts.

I tried the following in dbaccess:

"SELECT <row_pk_ FROM <table> WHERE LENGTH(blob)=0;"

and got:

"674: Routine (length) can not be resolved."


Same thing when trying SIZE().

I see that this has been asked a couple of times in the past 10 years,
with no definitive answer, so I suspect Informix does not provide the
capability. Just to be sure, I have opened a tech support case to get
the real skinny.

Thank you.

DG

You are right to open a case, i would too in case i needed it.... i
guess this is a missing feature.
and loooooooooooonnnnnnnnnggggggggg overdue;
(some oil on the fire: dumb blobs do have a length function and they
are afaik unsupported
using HDR in dumb blob spaces!!! so...... sblobs eq blob and clob in
sbspaces are supported
using hdr and miss functions like length...)

In order to get the functions you want you need to write your own udr
See informix datablade api programmers guide (v11.50.xC5)
around pages 6.50
the status info has the size of a lo.

Sorry i have no working example
when time allows i may hack one together or maybe someone else has
one??


Superboer

On 1 mrt, 01:06, pretzel <davidegr... (AT) gmail (DOT) com> wrote:

If you consider a definitive answer the development of the feature I believe
you're right...
In any case please check this earlier thread:

http://www.iiug.org/forums/ids/index.cgi/read/19143

and this article:

http://www.ibm.com/developerworks/da.../db_sblob.html

I think it will solve your problem...
In any case I think we all agree that length(blob) would be better... It's
just a question of priorities... Please ask for the feature in your PMR even
if this answer solves your problem.
As some important person once told me: "we will not implement things that
are not asked for... unless we had nothing more to do, which obviously is
not the case".
The words were not exactly these, I don't remember who is was, but I must
agree it makes sense. So, asking for things is a requirement for future
complaints

Regards.


On Tue, Mar 1, 2011 at 12:06 AM, pretzel <davidegrove (AT) gmail (DOT) com> wrote:



--
Fernando Nunes
Portugal

http://informix-technology.blogspot.com
My email works... but I don't check it frequently...

--> As some important person once told me: "we will not implement
things that
--> are not asked for... unless we had nothing more to do, which
obviously is
--> not the case".

obstacle has it... and you can tell that fellow that it is more or
less the same discussion as having upper in 9.1 and pre 7.3

Superboer

On 1 mrt, 12:51, Fernando Nunes <domusonl... (AT) gmail (DOT) com> wrote:

This is a side discussion and I personally could agree with you but:

- The idea that person gave to me, was kind of generic, and in no way
related to this
- The facts are that IBM has a number of documented requests. And there are
several non documented. The people who has to prioritize the features
implementation must evaluate them under certain criteria.
These criteria will never be consensual, but that's a fact of life. Point
is: Please document the requests. After that it's easier to complain
Regards.


On Tue, Mar 1, 2011 at 1:37 PM, Superboer <superboer7 (AT) t-online (DOT) de> wrote:



--
Fernando Nunes
Portugal

http://informix-technology.blogspot.com
My email works... but I don't check it frequently...

As expected, tech support confirms the inability of Informix to learn
the size of a blob from SQL.

The thing that surprised me, though, was that we went back-and-forth a
few times trying stuff. This issue seems like something tech support
should have just immediately known, without me having to try stuff and
report back.

I did make a request for enhancement. But, after 10 years of these
kinds of questions (searching cdi), I gotta think IDS developers have
long decided to disregard the issue.

DG