NULL or Empty String check on TEXT data type
Problem: in an SPL procedure on IDS 11.50 (Linux), how to tell whether a TEXT column is NULL or an empty string; TEXT can't be fetched into CHAR/LVARCHAR, and comparing it to "" raises error -615. Resolution: select the column into a variable declared REFERENCES TEXT and test it with IS NULL (no string comparison allowed), and for the empty-blob case use LENGTH(), e.g. LENGTH(blob_column) > 0, since the length is kept in the inline blob metadata. The poster confirmed this worked, avoiding the need for a custom UDR.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
Informix 11.50 FC8W2 - Linux Does anyone know how to test a TEXT column to determine if it's NULL or empty string from a stored procedure? Normal testing like it was a STRING doesn't work, and I can't select the data into a CHAR, or LVARCHAR variable. Thanks.
You select the TEXT column into a variable of type REFERENCES TEXT. Once you have selected the row you can test that variable for IS NULL in an IF statement. Art Art S. Kagel Advanced DataTools (www.advancedatatools.com) 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 Fri, Sep 28, 2012 at 1:49 PM, Jamie Gedye <jgedyedba@teleformix.com>wrote: > Informix 11.50 FC8W2 - Linux > > Does anyone know how to test a TEXT column to determine if it's NULL or > empty string from a stored procedure? > > Normal testing like it was a STRING doesn't work, and I can't select the > data into a CHAR, or LVARCHAR variable. > > Thanks. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --e89a8f3bac1d84b7fb04cac6d270
My variable are defined as
DEFINE l_var1 REFERENCES TEXT ;
DEFINE l_var2 REFERENCES TEXT ;
And my select to populate them is
SELECT text1,text2
INTO l_var1, l_var2
FROM mytable
WHERE col1 = p_1
AND col2 = p_2 ;
That all works with any errors, but when I try to compare.
IF (l_var1 IS NULL OR l_var1 = "") AND (l_var2 IS NULL OR l_var2 = "") THEN
RAISE EXCEPTION -746, 1, "my error message" ;
END IF
Doesn't work. It throws a -615 error.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art
Kagel
Sent: Friday, September 28, 2012 1:00 PM
To: ids@iiug.org
Subject: Re: NULL or Empty String check on TEXT data type [28392]
You select the TEXT column into a variable of type REFERENCES TEXT. Once you
have selected the row you can test that variable for IS NULL in an IF
statement.
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
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 Fri, Sep 28, 2012 at 1:49 PM, Jamie Gedye
<jgedyedba@teleformix.com>wrote:
> Informix 11.50 FC8W2 - Linux
>
> Does anyone know how to test a TEXT column to determine if it's NULL
> or empty string from a stored procedure?
>
> Normal testing like it was a STRING doesn't work, and I can't select
> the data into a CHAR, or LVARCHAR variable.
>
> Thanks.
>
>
>
>
****************************************************************************
***
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--e89a8f3bac1d84b7fb04cac6d270
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
Eliminate the comparison to "" that is what is generating the -615 error.
You cannot compare a TEXT or BYTE column to a string. Also, did you want
to test the condition when BOTH l_var1 AND l_var2 are NULL or when either
is NULL. The exception test should be:
IF (l_var1 IS NULL) AND (l_var2 IS NULL) THEN
RAISE EXCEPTION -746, 1, "my error message" ;
END IF
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
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 Fri, Sep 28, 2012 at 2:10 PM, Jamie Gedye <jgedyedba@teleformix.com>wrote:
> My variable are defined as
>
> DEFINE l_var1 REFERENCES TEXT ;
> DEFINE l_var2 REFERENCES TEXT ;
>
> And my select to populate them is
>
> SELECT text1,text2>
> INTO l_var1, l_var2
>
> FROM mytable
>
> WHERE col1 = p_1
>
> AND col2 = p_2 ;
>
> That all works with any errors, but when I try to compare.
>
> IF (l_var1 IS NULL OR l_var1 = "") AND (l_var2 IS NULL OR l_var2 = "") THEN
>
> RAISE EXCEPTION -746, 1, "my error message" ;
> END IF
>
> Doesn't work. It throws a -615 error.
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art
> Kagel
> Sent: Friday, September 28, 2012 1:00 PM
> To: ids@iiug.org
> Subject: Re: NULL or Empty String check on TEXT data type [28392]
>
> You select the TEXT column into a variable of type REFERENCES TEXT. Once
> you
> have selected the row you can test that variable for IS NULL in an IF
> statement.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> 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 Fri, Sep 28, 2012 at 1:49 PM, Jamie Gedye
> <jgedyedba@teleformix.com>wrote:
>
> > Informix 11.50 FC8W2 - Linux
> >
> > Does anyone know how to test a TEXT column to determine if it's NULL
> > or empty string from a stored procedure?
> >
> > Normal testing like it was a STRING doesn't work, and I can't select
> > the data into a CHAR, or LVARCHAR variable.
> >
> > Thanks.
> >
> >
> >
> >
>
> ****************************************************************************
> ***
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --e89a8f3bac1d84b7fb04cac6d270
>
>
> ****************************************************************************
> ***
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--14dae9340c893e56a504cac85d31
Thanks Art. My problem really isn't the NULL, it's the empty string. The
application that generates the data is storing an emptry string instead of
NULL. I found that after doing some more testing with the conditiion just
checking NULL.
Since I can't check if the TEXT actually contains an empty string from
within the procedure, I'm going to have to write a UDR to do the checking.
I was just trying to avoid that it possible.
Thanks for your help.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art
Kagel
Sent: Friday, September 28, 2012 2:50 PM
To: ids@iiug.org
Subject: Re: NULL or Empty String check on TEXT data type [28394]
Eliminate the comparison to "" that is what is generating the -615 error.
You cannot compare a TEXT or BYTE column to a string. Also, did you want to
test the condition when BOTH l_var1 AND l_var2 are NULL or when either is
NULL. The exception test should be:
IF (l_var1 IS NULL) AND (l_var2 IS NULL) THEN
RAISE EXCEPTION -746, 1, "my error message" ; END IF
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
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 Fri, Sep 28, 2012 at 2:10 PM, Jamie Gedye
<jgedyedba@teleformix.com>wrote:
> My variable are defined as
>
> DEFINE l_var1 REFERENCES TEXT ;
> DEFINE l_var2 REFERENCES TEXT ;
>
> And my select to populate them is
>
> SELECT text1,text2>
> INTO l_var1, l_var2
>
> FROM mytable
>
> WHERE col1 = p_1
>
> AND col2 = p_2 ;
>
> That all works with any errors, but when I try to compare.
>
> IF (l_var1 IS NULL OR l_var1 = "") AND (l_var2 IS NULL OR l_var2 = "")
> THEN
>
> RAISE EXCEPTION -746, 1, "my error message" ; END IF
>
> Doesn't work. It throws a -615 error.
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Art Kagel
> Sent: Friday, September 28, 2012 1:00 PM
> To: ids@iiug.org
> Subject: Re: NULL or Empty String check on TEXT data type [28392]
>
> You select the TEXT column into a variable of type REFERENCES TEXT.
> Once you have selected the row you can test that variable for IS NULL
> in an IF statement.
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> 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 Fri, Sep 28, 2012 at 1:49 PM, Jamie Gedye
> <jgedyedba@teleformix.com>wrote:
>
> > Informix 11.50 FC8W2 - Linux
> >
> > Does anyone know how to test a TEXT column to determine if it's NULL
> > or empty string from a stored procedure?
> >
> > Normal testing like it was a STRING doesn't work, and I can't select
> > the data into a CHAR, or LVARCHAR variable.
> >
> > Thanks.
> >
> >
> >
> >
>
> **********************************************************************
> ******
> ***
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --e89a8f3bac1d84b7fb04cac6d270
>
>
> **********************************************************************
> ******
> ***
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
****************************************************************************
***
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--14dae9340c893e56a504cac85d31
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
You CAN take the length( text_var ) and compare that to zero (0), that
should detect the empty but extent blob!
Art
Art S. Kagel
Advanced DataTools (www.advancedatatools.com)
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 Fri, Sep 28, 2012 at 3:56 PM, Jamie Gedye <jgedyedba@teleformix.com>wrote:
> Thanks Art. My problem really isn't the NULL, it's the empty string. The
> application that generates the data is storing an emptry string instead of
> NULL. I found that after doing some more testing with the conditiion just
> checking NULL.
>
> Since I can't check if the TEXT actually contains an empty string from
> within the procedure, I'm going to have to write a UDR to do the checking.
> I was just trying to avoid that it possible.
>
> Thanks for your help.
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art
> Kagel
> Sent: Friday, September 28, 2012 2:50 PM
> To: ids@iiug.org
> Subject: Re: NULL or Empty String check on TEXT data type [28394]
>
> Eliminate the comparison to "" that is what is generating the -615 error.
> You cannot compare a TEXT or BYTE column to a string. Also, did you want to
> test the condition when BOTH l_var1 AND l_var2 are NULL or when either is
> NULL. The exception test should be:
>
> IF (l_var1 IS NULL) AND (l_var2 IS NULL) THEN
>
> RAISE EXCEPTION -746, 1, "my error message" ; END IF
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> 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 Fri, Sep 28, 2012 at 2:10 PM, Jamie Gedye
> <jgedyedba@teleformix.com>wrote:
>
> > My variable are defined as
> >
> > DEFINE l_var1 REFERENCES TEXT ;
> > DEFINE l_var2 REFERENCES TEXT ;
> >
> > And my select to populate them is
> >
> > SELECT text1,text2> >
> > INTO l_var1, l_var2
> >
> > FROM mytable
> >
> > WHERE col1 = p_1
> >
> > AND col2 = p_2 ;
> >
> > That all works with any errors, but when I try to compare.
> >
> > IF (l_var1 IS NULL OR l_var1 = "") AND (l_var2 IS NULL OR l_var2 = "")
> > THEN
> >
> > RAISE EXCEPTION -746, 1, "my error message" ; END IF
> >
> > Doesn't work. It throws a -615 error.
> >
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> > Art Kagel
> > Sent: Friday, September 28, 2012 1:00 PM
> > To: ids@iiug.org
> > Subject: Re: NULL or Empty String check on TEXT data type [28392]
> >
> > You select the TEXT column into a variable of type REFERENCES TEXT.
> > Once you have selected the row you can test that variable for IS NULL
> > in an IF statement.
> >
> > Art
> >
> > Art S. Kagel
> > Advanced DataTools (www.advancedatatools.com)
> > 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 Fri, Sep 28, 2012 at 1:49 PM, Jamie Gedye
> > <jgedyedba@teleformix.com>wrote:
> >
> > > Informix 11.50 FC8W2 - Linux
> > >
> > > Does anyone know how to test a TEXT column to determine if it's NULL
> > > or empty string from a stored procedure?
> > >
> > > Normal testing like it was a STRING doesn't work, and I can't select
> > > the data into a CHAR, or LVARCHAR variable.
> > >
> > > Thanks.
> > >
> > >
> > >
> > >
> >
> > **********************************************************************
> > ******
> > ***
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --e89a8f3bac1d84b7fb04cac6d270
> >
> >
> > **********************************************************************
> > ******
> > ***
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
> >
> >
>
> ****************************************************************************
> ***
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --14dae9340c893e56a504cac85d31
>
>
> ****************************************************************************
> ***
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
--14dae9340af3381e5304cac8803f
You are correct you can not check the contents of the blob, but you
can check the length of the blob as this is stored in the in line blob
meta data. See the select below for an example.
select * from t1 where length(blob_column) > 0;
John F. Miller III
STSM, Embedability Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 09/28/2012 12:56:19 PM:
> From: "Jamie Gedye" <jgedyedba@teleformix.com>
> To: ids@iiug.org,
> Date: 09/28/2012 12:58 PM
> Subject: RE: NULL or Empty String check on TEXT data type [28395]
> Sent by: ids-bounces@iiug.org
>
> Thanks Art. My problem really isn't the NULL, it's the empty string. The
> application that generates the data is storing an emptry string instead
of
> NULL. I found that after doing some more testing with the conditiion just
> checking NULL.
>
> Since I can't check if the TEXT actually contains an empty string from
> within the procedure, I'm going to have to write a UDR to do the
checking.
> I was just trying to avoid that it possible.
>
> Thanks for your help.
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Art
> Kagel
> Sent: Friday, September 28, 2012 2:50 PM
> To: ids@iiug.org
> Subject: Re: NULL or Empty String check on TEXT data type [28394]
>
> Eliminate the comparison to "" that is what is generating the -615 error.
> You cannot compare a TEXT or BYTE column to a string. Also, did you want
to
> test the condition when BOTH l_var1 AND l_var2 are NULL or when either is
> NULL. The exception test should be:
>
> IF (l_var1 IS NULL) AND (l_var2 IS NULL) THEN
>
> RAISE EXCEPTION -746, 1, "my error message" ; END IF
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> 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 Fri, Sep 28, 2012 at 2:10 PM, Jamie Gedye
> <jgedyedba@teleformix.com>wrote:
>
> > My variable are defined as
> >
> > DEFINE l_var1 REFERENCES TEXT ;
> > DEFINE l_var2 REFERENCES TEXT ;
> >
> > And my select to populate them is
> >
> > SELECT text1,text2> >
> > INTO l_var1, l_var2
> >
> > FROM mytable
> >
> > WHERE col1 = p_1
> >
> > AND col2 = p_2 ;
> >
> > That all works with any errors, but when I try to compare.
> >
> > IF (l_var1 IS NULL OR l_var1 = "") AND (l_var2 IS NULL OR l_var2 = "")
> > THEN
> >
> > RAISE EXCEPTION -746, 1, "my error message" ; END IF
> >
> > Doesn't work. It throws a -615 error.
> >
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> > Art Kagel
> > Sent: Friday, September 28, 2012 1:00 PM
> > To: ids@iiug.org
> > Subject: Re: NULL or Empty String check on TEXT data type [28392]
> >
> > You select the TEXT column into a variable of type REFERENCES TEXT.
> > Once you have selected the row you can test that variable for IS NULL
> > in an IF statement.
> >
> > Art
> >
> > Art S. Kagel
> > Advanced DataTools (www.advancedatatools.com)
> > 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 Fri, Sep 28, 2012 at 1:49 PM, Jamie Gedye
> > <jgedyedba@teleformix.com>wrote:
> >
> > > Informix 11.50 FC8W2 - Linux
> > >
> > > Does anyone know how to test a TEXT column to determine if it's NULL
> > > or empty string from a stored procedure?
> > >
> > > Normal testing like it was a STRING doesn't work, and I can't select
> > > the data into a CHAR, or LVARCHAR variable.
> > >
> > > Thanks.
> > >
> > >
> > >
> > >
> >
> > **********************************************************************
> > ******
> > ***
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --e89a8f3bac1d84b7fb04cac6d270
> >
> >
> > **********************************************************************
> > ******
> > ***
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
> >
> >
>
****************************************************************************
> ***
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --14dae9340c893e56a504cac85d31
>
>
****************************************************************************
> ***
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
*******************************************************************************
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
Perfect... that would certainly be a lot easier. I'll give that a try.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of John
Miller iii
Sent: Friday, September 28, 2012 3:04 PM
To: ids@iiug.org
Subject: RE: NULL or Empty String check on TEXT data type [28397]
You are correct you can not check the contents of the blob, but you can
check the length of the blob as this is stored in the in line blob meta
data. See the select below for an example.
select * from t1 where length(blob_column) > 0;
John F. Miller III
STSM, Embedability Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 09/28/2012 12:56:19 PM:
> From: "Jamie Gedye" <jgedyedba@teleformix.com>
> To: ids@iiug.org,
> Date: 09/28/2012 12:58 PM
> Subject: RE: NULL or Empty String check on TEXT data type [28395] Sent
> by: ids-bounces@iiug.org
>
> Thanks Art. My problem really isn't the NULL, it's the empty string.
> The application that generates the data is storing an emptry string
> instead
of
> NULL. I found that after doing some more testing with the conditiion
> just
> checking NULL.
>
> Since I can't check if the TEXT actually contains an empty string from
> within the procedure, I'm going to have to write a UDR to do the
checking.
> I was just trying to avoid that it possible.
>
> Thanks for your help.
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Art
> Kagel
> Sent: Friday, September 28, 2012 2:50 PM
> To: ids@iiug.org
> Subject: Re: NULL or Empty String check on TEXT data type [28394]
>
> Eliminate the comparison to "" that is what is generating the -615 error.
> You cannot compare a TEXT or BYTE column to a string. Also, did you
> want
to
> test the condition when BOTH l_var1 AND l_var2 are NULL or when either
> is
> NULL. The exception test should be:
>
> IF (l_var1 IS NULL) AND (l_var2 IS NULL) THEN
>
> RAISE EXCEPTION -746, 1, "my error message" ; END IF
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> 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 Fri, Sep 28, 2012 at 2:10 PM, Jamie Gedye
> <jgedyedba@teleformix.com>wrote:
>
> > My variable are defined as
> >
> > DEFINE l_var1 REFERENCES TEXT ;
> > DEFINE l_var2 REFERENCES TEXT ;
> >
> > And my select to populate them is
> >
> > SELECT text1,text2> >
> > INTO l_var1, l_var2
> >
> > FROM mytable
> >
> > WHERE col1 = p_1
> >
> > AND col2 = p_2 ;
> >
> > That all works with any errors, but when I try to compare.
> >
> > IF (l_var1 IS NULL OR l_var1 = "") AND (l_var2 IS NULL OR l_var2 =
> > "") THEN
> >
> > RAISE EXCEPTION -746, 1, "my error message" ; END IF
> >
> > Doesn't work. It throws a -615 error.
> >
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf
> > Of Art Kagel
> > Sent: Friday, September 28, 2012 1:00 PM
> > To: ids@iiug.org
> > Subject: Re: NULL or Empty String check on TEXT data type [28392]
> >
> > You select the TEXT column into a variable of type REFERENCES TEXT.
> > Once you have selected the row you can test that variable for IS
> > NULL in an IF statement.
> >
> > Art
> >
> > Art S. Kagel
> > Advanced DataTools (www.advancedatatools.com)
> > 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 Fri, Sep 28, 2012 at 1:49 PM, Jamie Gedye
> > <jgedyedba@teleformix.com>wrote:
> >
> > > Informix 11.50 FC8W2 - Linux
> > >
> > > Does anyone know how to test a TEXT column to determine if it's
> > > NULL or empty string from a stored procedure?
> > >
> > > Normal testing like it was a STRING doesn't work, and I can't
> > > select the data into a CHAR, or LVARCHAR variable.
> > >
> > > Thanks.
> > >
> > >
> > >
> > >
> >
> > ********************************************************************
> > **
> > ******
> > ***
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --e89a8f3bac1d84b7fb04cac6d270
> >
> >
> > ********************************************************************
> > **
> > ******
> > ***
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
> >
> >
>
****************************************************************************
> ***
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --14dae9340c893e56a504cac85d31
>
>
****************************************************************************
> ***
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
****************************************************************************
***
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
Thanks Art & John!
That worked perfectly!
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Jamie
Gedye
Sent: Friday, September 28, 2012 3:06 PM
To: ids@iiug.org
Subject: RE: NULL or Empty String check on TEXT data type [28398]
Perfect... that would certainly be a lot easier. I'll give that a try.
-----Original Message-----
From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of John
Miller iii
Sent: Friday, September 28, 2012 3:04 PM
To: ids@iiug.org
Subject: RE: NULL or Empty String check on TEXT data type [28397]
You are correct you can not check the contents of the blob, but you can
check the length of the blob as this is stored in the in line blob meta
data. See the select below for an example.
select * from t1 where length(blob_column) > 0;
John F. Miller III
STSM, Embedability Architect
miller3@us.ibm.com
503-578-5645
IBM Informix Dynamic Server (IDS)
ids-bounces@iiug.org wrote on 09/28/2012 12:56:19 PM:
> From: "Jamie Gedye" <jgedyedba@teleformix.com>
> To: ids@iiug.org,
> Date: 09/28/2012 12:58 PM
> Subject: RE: NULL or Empty String check on TEXT data type [28395] Sent
> by: ids-bounces@iiug.org
>
> Thanks Art. My problem really isn't the NULL, it's the empty string.
> The application that generates the data is storing an emptry string
> instead
of
> NULL. I found that after doing some more testing with the conditiion
> just
> checking NULL.
>
> Since I can't check if the TEXT actually contains an empty string from
> within the procedure, I'm going to have to write a UDR to do the
checking.
> I was just trying to avoid that it possible.
>
> Thanks for your help.
>
> -----Original Message-----
> From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of
> Art
> Kagel
> Sent: Friday, September 28, 2012 2:50 PM
> To: ids@iiug.org
> Subject: Re: NULL or Empty String check on TEXT data type [28394]
>
> Eliminate the comparison to "" that is what is generating the -615 error.
> You cannot compare a TEXT or BYTE column to a string. Also, did you
> want
to
> test the condition when BOTH l_var1 AND l_var2 are NULL or when either
> is
> NULL. The exception test should be:
>
> IF (l_var1 IS NULL) AND (l_var2 IS NULL) THEN
>
> RAISE EXCEPTION -746, 1, "my error message" ; END IF
>
> Art
>
> Art S. Kagel
> Advanced DataTools (www.advancedatatools.com)
> 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 Fri, Sep 28, 2012 at 2:10 PM, Jamie Gedye
> <jgedyedba@teleformix.com>wrote:
>
> > My variable are defined as
> >
> > DEFINE l_var1 REFERENCES TEXT ;
> > DEFINE l_var2 REFERENCES TEXT ;
> >
> > And my select to populate them is
> >
> > SELECT text1,text2> >
> > INTO l_var1, l_var2
> >
> > FROM mytable
> >
> > WHERE col1 = p_1
> >
> > AND col2 = p_2 ;
> >
> > That all works with any errors, but when I try to compare.
> >
> > IF (l_var1 IS NULL OR l_var1 = "") AND (l_var2 IS NULL OR l_var2 =
> > "") THEN
> >
> > RAISE EXCEPTION -746, 1, "my error message" ; END IF
> >
> > Doesn't work. It throws a -615 error.
> >
> > -----Original Message-----
> > From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf
> > Of Art Kagel
> > Sent: Friday, September 28, 2012 1:00 PM
> > To: ids@iiug.org
> > Subject: Re: NULL or Empty String check on TEXT data type [28392]
> >
> > You select the TEXT column into a variable of type REFERENCES TEXT.
> > Once you have selected the row you can test that variable for IS
> > NULL in an IF statement.
> >
> > Art
> >
> > Art S. Kagel
> > Advanced DataTools (www.advancedatatools.com)
> > 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 Fri, Sep 28, 2012 at 1:49 PM, Jamie Gedye
> > <jgedyedba@teleformix.com>wrote:
> >
> > > Informix 11.50 FC8W2 - Linux
> > >
> > > Does anyone know how to test a TEXT column to determine if it's
> > > NULL or empty string from a stored procedure?
> > >
> > > Normal testing like it was a STRING doesn't work, and I can't
> > > select the data into a CHAR, or LVARCHAR variable.
> > >
> > > Thanks.
> > >
> > >
> > >
> > >
> >
> > ********************************************************************
> > **
> > ******
> > ***
> > > Forum Note: Use "Reply" to post a response in the discussion forum.
> > >
> > >
> >
> > --e89a8f3bac1d84b7fb04cac6d270
> >
> >
> > ********************************************************************
> > **
> > ******
> > ***
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
> >
> >
>
****************************************************************************
> ***
> > Forum Note: Use "Reply" to post a response in the discussion forum.
> >
> >
>
> --14dae9340c893e56a504cac85d31
>
>
****************************************************************************
> ***
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
>
>
****************************************************************************
***
> Forum Note: Use "Reply" to post a response in the discussion forum.
>
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.
****************************************************************************
***
Forum Note: Use "Reply" to post a response in the discussion forum.