Blob size from SQL
pretzel
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
pretzel
the provided functions ["lotofile()"]
pretzel
> P.S. I know that to actually retrieve the data, I need to use one of
> the provided functions ["lotofile()"]
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;
Art Kagel
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
IIUG Board of Directors (a...@iiug.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.
_______________________________________________
Informix-list mailing list
Inform...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
pretzel
> 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 (a...@iiug.org)
> Blog:http://informix-myview.blogspot.com/
>
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
Superboer
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
Fernando Nunes
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/data/zones/informix/library/techarticle/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.
Thank you.
DG
_______________________________________________
Informix-list mailing list
Inform...@iiug.org
http://www.iiug.org/mailman/listinfo/informix-list
--
Fernando Nunes
Portugal
http://informix-technology.blogspot.com
My email works... but I don't check it frequently...
Superboer
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...@gmail.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/data/zones/informix/library/techart...
Fernando Nunes
- 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.
pretzel
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
Fernando Nunes
Also, I think I'm missing something... You're looking for blob length()= 0, but you mentioned that SELECT ... FROM table WHERE blob_column IS NULL will not solve the problem.
At this moment I cannot see why?
Regards.
DG
_______________________________________________
Informix-list mailing list
pretzel
> Meanwhile, did you try the information I posted? It helped the person asking
> for this at the time...
Thank you, Mr. Nunes. I am in process of implementing it. I tried it
VERY quick and dirty, by attempting to use the precompiled blade, but
didn't work. I'm thinking, based on the error message (couldn't find
a library file) that it may be due to a dependency on a library object
from the Sun C compiler, which we don't have. So, now, I'm taking the
more scenic route and getting gcc (which wasn't on this machine), and
recompiling, etc.
> Also, I think I'm missing something... You're looking for blob length()= 0,
> but you mentioned that SELECT ... FROM table WHERE blob_column IS NULL will
> not solve the problem.
> At this moment I cannot see why?
I don't represent myself as an expert, so could be totally full of
beans. But, my hunch is that the blob column isn't empty, but, in
fact, has a proper blob handle (meaning, it is properly not NULL).
It's just that the blob to which the handle points has zero length.
I'm guessing that it could have gotten that way by execution of an SQL
"INSERT filetolo(<filename>... ) ..." with an empty file supplied as
the argument.
Anyway, I'm going to try the blade.
Thank you, once again.
DG
pretzel
"I was following up on your request to extend the LENGTH()
functionality to BLOBs and CLOBs, but is so happens that there is one
Feature Request for that matter already, and we hope to see that
implemented in future releases"
DG