Re: Length of Blob Column
David Grove wanted a LENGTH()/SIZE() function for smart large objects (sblobs) in IDS 12.10 on Solaris, noting the old IBM developerWorks article on rolling your own blade was gone. Paul Watson suggested writing a C UDR using mi_lo_open/mi_lo_stat_size, based on the demo idschecksum.c. Andreas Legner then pointed out the bundled excompat DataBlade (registered via blademgr/sysbldprepare) provides a getlength function. The documented name failed with error -674; checking the .so symbols showed the real name is dbms_lob_getlength(), which worked. John Miller added that you can wrap it as LENGTH(BLOB) with a CREATE FUNCTION ... external name clause.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Informix 12.10 Solaris 10 (SPARC) The issue of someone needing to have a LENGTH() or SIZE() function that applies to sblobs has come up irregularly, but repeatedly, in this forum since at least 2006. I have participated in many of those discussions. I, and others, have made requests, over the years, for IBM to provide this functionality-- just like they do for CHAR data types. And blobs. But, not sblobs. At least one person with IBM connections has acknowledged that this has been requested for a long time, and stated that it would be forthcoming. To date, nada. Anyway, Mr. Nunes has helpfully posted this link a few times, in the past: http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/db_sbl ob.html It really is helpful-- it provides some kit and info to aid in "rolling your own" LENGTH function. Now, I find myself, again needing this functionality and information. I have used the information in the article above, successfully, years ago. But, I no longer have it. I recently attempted to download that article again, so I could use its valuable content to create (actually, I think it contained a binary version for SPARC Solaris) a new blade to provide the needed functionality. That is, to create a blade to implement a LENGTH() function for sblobs. The link above is dead. Does anyone know where that article, or comparable information, might currently reside? IBM declined to provide needed functionality, but did provide (unsupported) resources to enable a customer to build his own. But, now, even that seems to be non-existent. If it is no longer necessary because IBM has now implemented some form of LENGTH() function for sblobs in IDS, I would be happy to learn of it. Our version 12.10FC3 still doesn't have it. If that material is "out there", and anyone knows where it is, please let me know. I did note that, in one or two past threads on this topic, someone asked why this capability is needed. I would say for the same reason that one might want to retrieve (or use in WHERE clause) the LENGTH of a CHAR. Thank you. DG
Sorry. I should have mentioned... Although there are other similar threads, my post specifically refers to this one: http://members.iiug.org/forums/ids/index.cgi/read/19133 DG
David can't you just use mi_lo_open and mi_lo_stat_size in a simple C wrapper ? Cheers Paul > Informix 12.10 > Solaris 10 (SPARC) > > The issue of someone needing to have a LENGTH() or SIZE() function that > applies to sblobs has come up irregularly, but repeatedly, in this forum > since > at least 2006. I have participated in many of those discussions. I, and > others, have made requests, over the years, for IBM to provide this > functionality-- just like they do for CHAR data types. And blobs. But, not > sblobs. > > At least one person with IBM connections has acknowledged that this has > been > requested for a long time, and stated that it would be forthcoming. To > date, > nada. > > Anyway, Mr. Nunes has helpfully posted this link a few times, in the past: > > > http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/db_sbl ob.html > > It really is helpful-- it provides some kit and info to aid in "rolling > your > own" LENGTH function. > > Now, I find myself, again needing this functionality and information. I > have > used the information in the article above, successfully, years ago. But, I > no > longer have it. > > I recently attempted to download that article again, so I could use its > valuable content to create (actually, I think it contained a binary > version > for SPARC Solaris) a new blade to provide the needed functionality. That > is, > to create a blade to implement a LENGTH() function for sblobs. > > The link above is dead. Does anyone know where that article, or comparable > information, might currently reside? IBM declined to provide needed > functionality, but did provide (unsupported) resources to enable a > customer to > build his own. But, now, even that seems to be non-existent. > > If it is no longer necessary because IBM has now implemented some form of > LENGTH() function for sblobs in IDS, I would be happy to learn of it. Our > version 12.10FC3 still doesn't have it. > > If that material is "out there", and anyone knows where it is, please let > me > know. > > I did note that, in one or two past threads on this topic, someone asked > why > this capability is needed. I would say for the same reason that one might > want > to retrieve (or use in WHERE clause) the LENGTH of a CHAR. > > Thank you. > > DG > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > -- Paul Watson Tel: +1 913-674-0360 Mob: +1 913-387-7529 Web: www.oninit.com Oninit® is a registered trademark of Oninit LLC Failure is not as frightening as regret. If you want to improve, be content to be thought foolish and stupid. What this country needs are more unemployed politicians
Look at idschecksum.c in the demop area, you just need to tweak it to use ml_lo_stat_size Cheers Paul > Sorry. I should have mentioned... > > Although there are other similar threads, my post specifically refers to > this > one: > http://members.iiug.org/forums/ids/index.cgi/read/19133 > > DG > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > -- Paul Watson Tel: +1 913-674-0360 Mob: +1 913-387-7529 Web: www.oninit.com Oninit® is a registered trademark of Oninit LLC Failure is not as frightening as regret. If you want to improve, be content to be thought foolish and stupid. What this country needs are more unemployed politicians
Thank you, Paul. I have not previously implemented any extensions to Informix (except when once I followed the recipe [successfully] in the document that Mr. Nunes mentioned, and that I no longer have, and whose IBM link is broken, and I cannot find). Would your suggestion permit me to do an SQL command something like: "SELECT NEW_LENGTH_FUNCTION( sblob_column ) FROM my_table;" or "SELECT * FROM my_table WHERE NEW_LENGTH_FUNCTION( sblob_column ) < some_value;" Thank you. DG
Yep, using the idschecksum.c as a starting point should be easier enough Cheers Paul > Thank you, Paul. > > I have not previously implemented any extensions to Informix (except when > once > I followed the recipe [successfully] in the document that Mr. Nunes > mentioned, > and that I no longer have, and whose IBM link is broken, and I cannot > find). > > Would your suggestion permit me to do an SQL command something like: > > "SELECT NEW_LENGTH_FUNCTION( sblob_column ) FROM my_table;" > > or > > "SELECT * FROM my_table WHERE NEW_LENGTH_FUNCTION( sblob_column ) < > some_value;" > > Thank you. > > DG > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > -- Paul Watson Tel: +1 913-674-0360 Mob: +1 913-387-7529 Web: www.oninit.com Oninit® is a registered trademark of Oninit LLC Failure is not as frightening as regret. If you want to improve, be content to be thought foolish and stupid. What this country needs are more unemployed politicians
Just to provide support for my assertion that this exact functionality has been requested for many years, here is a thread from 2006. (There may very well be older ones.) http://members.iiug.org/forums/ids/index.cgi/noframes/read/6841 DG
We found a convenient solution. The developer just imported the table into MS SQL Server and did the work there. Quick and easy. DG
Hi David,
I just came over=20
http://www.ibm.com/support/knowledgecenter/SSGU8G=5F12.1.0/com.ibm.dbext.do=
c/ids=5Fr0055115.htm=20
-> GETLENGTH() function.
You'd get to this through the excompat blade bundled with the server:
dbaccess <your=5Fdatabase> <<!
execute function sysbldprepare("excompat.1.0", "create");!
-> real function name seems to be dbms=5Flob=5Fgetlength() which is defined=
=20
for blob and clob arguments.
Seems to do just what you wanted, and all bundled, obviously since=20
11.70.xC5 (might be able to just copy this blade to an older version=20
even?).
Second point:
the length of an sblob is not and cannot be known to the row "containing"=20
the sblob (should rather read "pointing to") as, by nature of sblobs, the=20
sblob is not owned by the row (can be pointed to by zero to many rows -=20
but nobody really owns it, and it doesn't have any notion of who/what is=20
pointing to it). You'd always be able to modify any sblob, so any length=20
information within a row having an sblob field would never be reliable.
As a consequence of this any attempt to determine any attribute of an=20
sblob, e.g. its length, would have to open the real blob ... which can be =
costly in larger queries, esp. when having this somewhere down in a=20
complex where clause.
HTH,
Andreas
From: "DAVID GROVE" <david.grove@alaska.gov>
To: ids@iiug.org
Date: 19.01.2017 18:58
Subject: Re: Length of Blob Column [38519]
Sent by: ids-bounces@iiug.org
Informix 12.10=20
Solaris 10 (SPARC)=20
The issue of someone needing to have a LENGTH() or SIZE() function that=20
applies to sblobs has come up irregularly, but repeatedly, in this forum=20
since=20
at least 2006. I have participated in many of those discussions. I, and=20
others, have made requests, over the years, for IBM to provide this=20
functionality-- just like they do for CHAR data types. And blobs. But, not =
sblobs.=20
At least one person with IBM connections has acknowledged that this has=20
been=20
requested for a long time, and stated that it would be forthcoming. To=20
date,=20
nada.=20
Anyway, Mr. Nunes has helpfully posted this link a few times, in the past: =
http://www.ibm.com/developerworks/data/zones/informix/library/techarticle/d=
b=5Fsblob.html=20
It really is helpful-- it provides some kit and info to aid in "rolling=20
your=20
own" LENGTH function.=20
Now, I find myself, again needing this functionality and information. I=20
have=20
used the information in the article above, successfully, years ago. But, I =
no=20
longer have it.=20
I recently attempted to download that article again, so I could use its=20
valuable content to create (actually, I think it contained a binary=20
version=20
for SPARC Solaris) a new blade to provide the needed functionality. That=20
is,=20
to create a blade to implement a LENGTH() function for sblobs.=20
The link above is dead. Does anyone know where that article, or comparable =
information, might currently reside? IBM declined to provide needed=20
functionality, but did provide (unsupported) resources to enable a=20
customer to=20
build his own. But, now, even that seems to be non-existent.=20
If it is no longer necessary because IBM has now implemented some form of=20
LENGTH() function for sblobs in IDS, I would be happy to learn of it. Our=20
version 12.10FC3 still doesn't have it.=20
If that material is "out there", and anyone knows where it is, please let=20
me=20
know.=20
I did note that, in one or two past threads on this topic, someone asked=20
why=20
this capability is needed. I would say for the same reason that one might=20
want=20
to retrieve (or use in WHERE clause) the LENGTH of a CHAR.=20
Thank you.=20
DG=20
***************************************************************************=
****=20
Forum Note: Use "Reply" to post a response in the discussion forum.=20
Hi David,
I just came over=20
http://www.ibm.com/support/knowledgecenter/SSGU8G=5F12.1.0/com.ibm.dbext.do=
c/ids=5Fr0055115.htm=20
-> GETLENGTH() function.
You'd get to this through the excompat blade bundled with the server:
dbaccess <your=5Fdatabase> <<!
execute function sysbldprepare("excompat.1.0", "create");!
-> real function name seems to be dbms=5Flob=5Fgetlength() which is defined=
=20
for blob and clob arguments.
Seems to do just what you wanted, and all bundled, obviously since=20
11.70.xC5 (might be able to just copy this blade to an older version=20
even?).
Second point:
the length of an sblob is not and cannot be known to the row "containing"=20
the sblob (should rather read "pointing to") as, by nature of sblobs, the=20
sblob is not owned by the row (can be pointed to by zero to many rows -=20
but nobody really owns it, and it doesn't have any notion of who/what is=20
pointing to it). You'd always be able to modify any sblob, so any length=20
information within a row having an sblob field would never be reliable.
As a consequence of this any attempt to determine any attribute of an=20
sblob, e.g. its length, would have to open the real blob ... which can be =
costly in larger queries, esp. when having this somewhere down in a=20
complex where clause.
HTH,
Andreas
-------------------------------------------------------------------
This is fantastic, if I can make it work. Exactly what we need.
I used 'blademgr' to successfully register the module, "excompat.1.0":
XTERM
informix@ifmx-prod-anc>pwd
/opt/informix/extend/excompat.1.0
informix@ifmx-prod-anc>blademgr
prodanctli>list acoms
DataBlade modules registered in database acoms:
excompat.1.0
prodanctli>
/XTERM
But, when I try to use the GETLENGTH() function, I get a -674, "getlength" can
not be resolved message.
SERVERSTUDIO
1/24/17 3:22 PM Executing statement:
Select FIRST 10 GETLENGTH(photo) FROM ofndr_photo;
SQL Error (-674): Routine (getlength) can not be resolved.
Error Position: Ln: 5 Col: 17
/SERVERSTUDIO
I tried "GETLENGTH" & "GET_LENGTH", with and without prepending "dbms_lob."
Obviously, I am doing something wrong. If anyone can offer a suggestion, I
would welcome it.
DG
How is the function defined ? Look the blade SQL files, Run nm on the bld/so
and look for function to make sure it is actually there ? Is onstage -m giving
the underpinning missing function ?
Cheers
Paul
Paul Watson
Oninit www.oninit.com
+1 913 387 7529
Oninit® is a Registered Trademark of Oninit LLC
> On Jan 24, 2017, at 18:34, DAVID GROVE <david.grove@alaska.gov> wrote:
>
> Hi David,
>
> I just came over=20
> http://www.ibm.com/support/knowledgecenter/SSGU8G=5F12.1.0/com.ibm.dbext.do=
> c/ids=5Fr0055115.htm=20
> -> GETLENGTH() function.
>
> You'd get to this through the excompat blade bundled with the server:
>
> dbaccess <your=5Fdatabase> <<!
> execute function sysbldprepare("excompat.1.0", "create");> !
>
> -> real function name seems to be dbms=5Flob=5Fgetlength() which is defined=
> =20
> for blob and clob arguments.
>
> Seems to do just what you wanted, and all bundled, obviously since=20
> 11.70.xC5 (might be able to just copy this blade to an older version=20
> even?).
>
> Second point:
> the length of an sblob is not and cannot be known to the row "containing"=20
> the sblob (should rather read "pointing to") as, by nature of sblobs, the=20
> sblob is not owned by the row (can be pointed to by zero to many rows -=20
> but nobody really owns it, and it doesn't have any notion of who/what is=20
> pointing to it). You'd always be able to modify any sblob, so any length=20
> information within a row having an sblob field would never be reliable.
> As a consequence of this any attempt to determine any attribute of an=20
> sblob, e.g. its length, would have to open the real blob ... which can be =
>
> costly in larger queries, esp. when having this somewhere down in a=20
> complex where clause.
>
> HTH,
> Andreas
>
> -------------------------------------------------------------------
>
> This is fantastic, if I can make it work. Exactly what we need.
>
> I used 'blademgr' to successfully register the module, "excompat.1.0":
>
> XTERM
> informix@ifmx-prod-anc>pwd
> /opt/informix/extend/excompat.1.0
>
> informix@ifmx-prod-anc>blademgr
>
> prodanctli>list acoms
> DataBlade modules registered in database acoms:
>
> excompat.1.0
> prodanctli>
> /XTERM
>
> But, when I try to use the GETLENGTH() function, I get a -674, "getlength"
can
> not be resolved message.
>
> SERVERSTUDIO
> 1/24/17 3:22 PM Executing statement:
>
> Select FIRST 10 GETLENGTH(photo) FROM ofndr_photo;>
> SQL Error (-674): Routine (getlength) can not be resolved.
> Error Position: Ln: 5 Col: 17
> /SERVERSTUDIO
>
> I tried "GETLENGTH" & "GET_LENGTH", with and without prepending "dbms_lob."
>
> Obviously, I am doing something wrong. If anyone can offer a suggestion, I
> would welcome it.
>
> DG
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
Perfect suggestion. Thank you. Checked the symbol table in the so. Lo and behold, the name differed slightly from the documentation. "dbms_lob_getlength()" is in the so, and it works perfectly! Thank you again for the nudge, Paul Watson; and thank you to Andreas Legner for the wonderful piece of knowledge. DG
I had a professor once, who was wont to say, "Ask big and don't apologize." Is it possible to take this nice, new functionality (having a built-in function to provide length of blobs), and expand on it a little? The function name is "dbms_lob_getlength()", which is a lot to type. (Whine, whine.) Can the "LENGTH()" function be overloaded so that "LENGTH()" can be used for BLOB, as well as CHAR types. In other words, LENGTH() (or maybe even "LEN()") would return the length of whatever the argument was, regardless of type? DG
You can call in Dave as long as it calls the same underpinning C code Cheers Paul Paul Watson Oninit www.oninit.com +1 913 387 7529 Oninit® is a Registered Trademark of Oninit LLC > On Jan 25, 2017, at 18:23, DAVID GROVE <david.grove@alaska.gov> wrote: > > I had a professor once, who was wont to say, "Ask big and don't apologize." > > Is it possible to take this nice, new functionality (having a built-in > function to provide length of blobs), and expand on it a little? > > The function name is "dbms_lob_getlength()", which is a lot to type. (Whine, > whine.) > > Can the "LENGTH()" function be overloaded so that "LENGTH()" can be used for > BLOB, as well as CHAR types. In other words, LENGTH() (or maybe even "LEN()") > would return the length of whatever the argument was, regardless of type? > > DG > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum.
I have not tried it, but you should try. I think the function manager
should make this work just fine.
create function length(BLOB) returns informix.integer
external name 'path=5Fto=5Flibrary(dbms=5Flob=5Fgetlength)'
language C
NOT VARIANT;
John F. Miller III
miller3@us.ibm.com
ids-bounces@iiug.org wrote on 01/25/2017 04:23:34 PM:
> From: "DAVID GROVE" <david.grove@alaska.gov>
> To: ids@iiug.org
> Date: 01/25/2017 04:24 PM
> Subject: Re: Length of Blob Column [38565]
> Sent by: ids-bounces@iiug.org
>
> I had a professor once, who was wont to say, "Ask big and don't
apologize."
>
> Is it possible to take this nice, new functionality (having a built-in
> function to provide length of blobs), and expand on it a little?
>
> The function name is "dbms=5Flob=5Fgetlength()", which is a lot to type.
(Whine,
> whine.)
>
> Can the "LENGTH()" function be overloaded so that "LENGTH()" can be used
for
> BLOB, as well as CHAR types. In other words, LENGTH() (or maybe even"LEN
()")
> would return the length of whatever the argument was, regardless of type?
>
> DG
>
>
>
***************************************************************************=
****
> Forum Note: Use "Reply" to post a response in the discussion forum.
>