At 04:34 PM 05-12-01 -0500, you wrote:

>Hello,
>
>I have a Stored Procedure that I use to check and return some info
>about records in a DB. Below is the relevant parts. I am trying to
>check and see if some data is in a blob. If so I want to return a
>flag with that knowledge. The user doesn't need to see the note at that
>time and to conserve bandwidth I don't want to transmit the note text.
>
>The SP below compiles fine, but causes an error on execution.
>
>If I remove the (Notes <> '') line the procedure returns '*'
>even if the Notes field is empty. So what do I need to do
>to get this info?
>
>CREATE PROCEDURE SPPARTSNOTES (
> XPART VARCHAR (16))
>RETURNS (NOTESFLAG CHAR (1))
>AS
>Declare variable Notes BLOB SUB_TYPE TEXT SEGMENT SIZE 8192;
>begin
> xPart = Upper(:xPart);
>
> /* The Inv Records */
> for Select Inv_Notes
> from inv
> where inv_Part = :XPart
> into :Notes
> do begin
> if (Notes is not null) then
> begin
> if (Notes <> '') then /* <==== Error Here */
> NotesFlag = '*';
> end
> suspend;
> end
> exit;
>end

Michael,
Unless you are purposely storing empty string in "empty" blobs, an empty blob will be NULL. So, the first test for null will establish whether the blob is empty or not. After that, to examine the contents of the blob, e.g. to check whether the contents of a non-null blob evaluate to empty string, you will need to use a UDF to read it.

do begin
if (Notes is not null) then
begin <----- we are now looking at a non-null blob
if (Notes <> '') then /* <==== Error Here */ <-- because Notes is a blob, not a string
NotesFlag = '*';

Of course, removing that second test achieves nothing but to make the NotesFlag '*' for all blobs that contain something!

Helen

All for Open and Open for All
InterBase Developer Initiative · http://www.interbase2000.org
_______________________________________________________