Blob size from SQL

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...@gmail.com> wrote: > 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

On Feb 28, 10:21 am, pretzel <davidegr...@gmail.com> wrote: > P.S.  I know that to actually retrieve the data, I need to use one of > the provided functions ["lotofile()"] P.P.S. Let me be more clear. I'm trying to identify rows in a table, 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; > On Feb 28, 10:19 am, pretzel <davidegr...@gmail.com> wrote: > > > 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

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@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. On Mon, Feb 28, 2011 at 4:29 PM, pretzel <davidegrove@gmail.com> wrote: > On Feb 28, 10:21 am, pretzel <davidegr...@gmail.com> wrote: > > P.S. I know that to actually retrieve the data, I need to use one of > > the provided functions ["lotofile()"] > P.P.S. Let me be more clear. I'm trying to identify rows in a table, > 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;> > > > > > On Feb 28, 10:19 am, pretzel <davidegr...@gmail.com> wrote: > > > > > 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 > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >

On Feb 28, 1:23 pm, Art Kagel <art.ka...@gmail.com> wrote: > 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