Re: INITIALIZE vs LET
On Tue, 19 Dec 2000, William Rice wrote:
>Jonathan Leffler <jleffler@informix.com> wrote:
>> "ART KAGEL, BLOOMBERG/ NEW YORK" wrote:
>> > ----- Original Message -----
>> > To: mdstock@mydas.freeserve.co.uk
>> > At: 12/18 17:13
>> >
>> > What likely happened is that you FETCHED the date into a GLOBAL
>> > which is initialized to zero (ie 12/31/1899) (or a local in R4GL).
>> > Since the column was NULL the FETCH did not alter the current
>> > contents of the variable so it remained zero!
>>
>> Surely not? If you fetch a null value, then the previous value in
>> the variable must be overwritten to indicate that the fetched value
>> is now null. Only if there was no data to fetch or an error occurred
>> would the variable be unaltered (and it might have been altered if an
>> error occurred).
>
>Why _must_ the previous value in the variable be overwritten?
>
>>From 9.2 Syntax guide p. 2-426
>--
>You cannot select a null value from a table column and place that value
>into an output variable. If you know in advance that a table column
>contains a null value, after you select the data, check the indicator
>variable that is associated with the column to determine if the value
>is null.
>--
>
>To me this implies that if a null value is selected, what is contained
>in the variable holder is undefined, only what is contained in the
>indicator holder is defined.
>
>What is actually the case is not something I can't test seeing I do not
>have 4gl. If what is in the variable defines whether or not something
>is NULL, why have the INDICATOR at all?
Art Kagel countered with a similar comment, and I've replied to him and
c.d.i with one counter-example; William Rice gave a page number to
check. Sure enough, there on p2-246 is the comment. What intrigues me
is that the comment is in the section on EXECUTE, and not in the FETCH
section as I would have expected. Further, the FETCH section does not
seem to have the same restriction.
I postulate that this is some sort of documentation error. I've adapted
the example I sent to Art to use EXECUTE instead of a simple SELECT, and
it shows the same result as the simple SELECT:
$ cat xx2.ec
#include <stdio.h>
int main(void)
{ EXEC SQL BEGIN DECLARE SECTION;
int j = 0;
EXEC SQL END DECLARE SECTION;
EXEC SQL WHENEVER ERROR STOP;
EXEC SQL CONNECT TO 'stores';
EXEC SQL CREATE TEMP TABLE T(I INTEGER);
EXEC SQL INSERT INTO T VALUES(NULL);
EXEC SQL PREPARE p FROM "SELECT I FROM T";
printf("Before EXECUTE: %d\\n", j);
EXEC SQL EXECUTE p INTO :j;
printf("After EXECUTE: %d\\n", j);
EXEC SQL DISCONNECT ALL;
return 0;
}
$ rmk -u xx2
INFORMIXC="gcc" esql -O xx2.ec -o xx2
rm -f xx2.[co]
$ ./xx2Before EXECUTE: 0
After EXECUTE: -2147483648
$
(As before, this is ESQL/C 9.40.UC2, from CSDK 2.50.UC2, on Sun
UltraSparc running Solaris 7 against Foundation 2000 9.21.UC2).
Since this appears to be a documentation problem, I've done what the
manuals ask you to do -- sent email to doc@informix.com with the issue.
As before, I would not dispute for a second that indicator variables are
the better way to go, nor that integers are easier to deal with than,
say, VARCHAR variables. However, empirically, unless I'm
misunderstanding something worse than usual, the documentation is not in
alignment with reality.
--
Yours,
Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h>
Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN
"I don't suffer from insanity; I enjoy every minute of it!"
I have verified that the behavior Jonathan describes for INTEGER holds for
CHAR also. Looks like I'm holding onto some old behavior or my own
prejudice from the past. I will note that when handling CHAR and VARCHAR
columns, if you use 'string' host variables there is no way except for
indicator variables to distinquish between an empty or all blank string and
a NULL one. As Jonathan says, indicators are still the correct way to handle
NULLs.
Art S. Kagel
Jonathan Leffler wrote:
>
> On Tue, 19 Dec 2000, William Rice wrote:
> >Jonathan Leffler <jleffler@informix.com> wrote:
> >> "ART KAGEL, BLOOMBERG/ NEW YORK" wrote:
> >> > ----- Original Message -----
> >> > To: mdstock@mydas.freeserve.co.uk
> >> > At: 12/18 17:13
> >> >
> >> > What likely happened is that you FETCHED the date into a GLOBAL
> >> > which is initialized to zero (ie 12/31/1899) (or a local in R4GL).
> >> > Since the column was NULL the FETCH did not alter the current
> >> > contents of the variable so it remained zero!
> >>
> >> Surely not? If you fetch a null value, then the previous value in
> >> the variable must be overwritten to indicate that the fetched value
> >> is now null. Only if there was no data to fetch or an error occurred
> >> would the variable be unaltered (and it might have been altered if an
> >> error occurred).
> >
> >Why _must_ the previous value in the variable be overwritten?
> >
> >>From 9.2 Syntax guide p. 2-426
> >--
> >You cannot select a null value from a table column and place that value
> >into an output variable. If you know in advance that a table column
> >contains a null value, after you select the data, check the indicator
> >variable that is associated with the column to determine if the value
> >is null.
> >--
> >
> >To me this implies that if a null value is selected, what is contained
> >in the variable holder is undefined, only what is contained in the
> >indicator holder is defined.
> >
> >What is actually the case is not something I can't test seeing I do not
> >have 4gl. If what is in the variable defines whether or not something
> >is NULL, why have the INDICATOR at all?
>
> Art Kagel countered with a similar comment, and I've replied to him and
> c.d.i with one counter-example; William Rice gave a page number to
> check. Sure enough, there on p2-246 is the comment. What intrigues me
> is that the comment is in the section on EXECUTE, and not in the FETCH
> section as I would have expected. Further, the FETCH section does not
> seem to have the same restriction.
>
> I postulate that this is some sort of documentation error. I've adapted
> the example I sent to Art to use EXECUTE instead of a simple SELECT, and
> it shows the same result as the simple SELECT:
>
> $ cat xx2.ec
> #include <stdio.h>
> int main(void)
> {> EXEC SQL BEGIN DECLARE SECTION;
> int j = 0;
> EXEC SQL END DECLARE SECTION;
> EXEC SQL WHENEVER ERROR STOP;
> EXEC SQL CONNECT TO 'stores';
> EXEC SQL CREATE TEMP TABLE T(I INTEGER);
> EXEC SQL INSERT INTO T VALUES(NULL);
> EXEC SQL PREPARE p FROM "SELECT I FROM T";
> printf("Before EXECUTE: %d\\n", j);
> EXEC SQL EXECUTE p INTO :j;
> printf("After EXECUTE: %d\\n", j);
> EXEC SQL DISCONNECT ALL;
> return 0;
> }
> $ rmk -u xx2
> INFORMIXC="gcc" esql -O xx2.ec -o xx2
> rm -f xx2.[co]
> $ ./xx2> Before EXECUTE: 0
> After EXECUTE: -2147483648
> $
>
> (As before, this is ESQL/C 9.40.UC2, from CSDK 2.50.UC2, on Sun
> UltraSparc running Solaris 7 against Foundation 2000 9.21.UC2).
>
> Since this appears to be a documentation problem, I've done what the
> manuals ask you to do -- sent email to doc@informix.com with the issue.
>
> As before, I would not dispute for a second that indicator variables are
> the better way to go, nor that integers are easier to deal with than,
> say, VARCHAR variables. However, empirically, unless I'm
> misunderstanding something worse than usual, the documentation is not in
> alignment with reality.
>
> --
> Yours,
> Jonathan Leffler (Jonathan.Leffler@Informix.com) #include <disclaimer.h>
> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN
> "I don't suffer from insanity; I enjoy every minute of it!"
In article <91ri2c$k9e$1@news.xmission.com>,
Jonathan Leffler <Jonathan.Leffler@informix.com> wrote:
>
> On Tue, 19 Dec 2000, William Rice wrote:
> >Jonathan Leffler <jleffler@informix.com> wrote:
> >> "ART KAGEL, BLOOMBERG/ NEW YORK" wrote:
> >> > ----- Original Message -----
> >> > To: mdstock@mydas.freeserve.co.uk
> >> > At: 12/18 17:13
> >> >
> >> > What likely happened is that you FETCHED the date into a GLOBAL
> >> > which is initialized to zero (ie 12/31/1899) (or a local in
R4GL).
> >> > Since the column was NULL the FETCH did not alter the current
> >> > contents of the variable so it remained zero!
> >>
> >> Surely not? If you fetch a null value, then the previous value in
> >> the variable must be overwritten to indicate that the fetched
value
> >> is now null. Only if there was no data to fetch or an error
occurred
> >> would the variable be unaltered (and it might have been altered if
an
> >> error occurred).
> >
> >Why _must_ the previous value in the variable be overwritten?
> >
> >>From 9.2 Syntax guide p. 2-426
> >--
> >You cannot select a null value from a table column and place that
value
> >into an output variable. If you know in advance that a table column
> >contains a null value, after you select the data, check the
indicator
> >variable that is associated with the column to determine if the
value
> >is null.
> >--
> >
> >To me this implies that if a null value is selected, what is
contained
> >in the variable holder is undefined, only what is contained in the
> >indicator holder is defined.
> >
> >What is actually the case is not something I can't test seeing I do
not
> >have 4gl. If what is in the variable defines whether or not
something
> >is NULL, why have the INDICATOR at all?
>
> Art Kagel countered with a similar comment, and I've replied to him
and
> c.d.i with one counter-example; William Rice gave a page number to
> check. Sure enough, there on p2-246 is the comment. What intrigues
me
> is that the comment is in the section on EXECUTE, and not in the
FETCH
> section as I would have expected. Further, the FETCH section does
not
> seem to have the same restriction.
>
> I postulate that this is some sort of documentation error. I've
adapted
> the example I sent to Art to use EXECUTE instead of a simple SELECT,
and
> it shows the same result as the simple SELECT:
>
> $ cat xx2.ec
> #include <stdio.h>
> int main(void)
> {> EXEC SQL BEGIN DECLARE SECTION;
> int j = 0;
> EXEC SQL END DECLARE SECTION;
> EXEC SQL WHENEVER ERROR STOP;
> EXEC SQL CONNECT TO 'stores';
> EXEC SQL CREATE TEMP TABLE T(I INTEGER);
> EXEC SQL INSERT INTO T VALUES(NULL);
> EXEC SQL PREPARE p FROM "SELECT I FROM T";
> printf("Before EXECUTE: %d\\n", j);
> EXEC SQL EXECUTE p INTO :j;
> printf("After EXECUTE: %d\\n", j);
> EXEC SQL DISCONNECT ALL;
> return 0;
> }
> $ rmk -u xx2
> INFORMIXC="gcc" esql -O xx2.ec -o xx2
> rm -f xx2.[co]
> $ ./xx2> Before EXECUTE: 0
> After EXECUTE: -2147483648
> $
>
> (As before, this is ESQL/C 9.40.UC2, from CSDK 2.50.UC2, on Sun
> UltraSparc running Solaris 7 against Foundation 2000 9.21.UC2).
>
> Since this appears to be a documentation problem, I've done what the
> manuals ask you to do -- sent email to doc@informix.com with the
issue.
>
> As before, I would not dispute for a second that indicator variables
are
> the better way to go, nor that integers are easier to deal with than,
> say, VARCHAR variables. However, empirically, unless I'm
> misunderstanding something worse than usual, the documentation is not
in
> alignment with reality.
>
> --
> Yours,
> Jonathan Leffler (Jonathan.Leffler@Informix.com) #include
<disclaimer.h>
> Guardian of DBD::Informix v1.00.PC1 -- http://www.perl.com/CPAN
> "I don't suffer from insanity; I enjoy every minute of it!"
>
>
Back to what the Original poster was doing which is the printing of a
date.
I ran the following program
--
#include <stdio.h>
EXEC SQL include datetime;
main()
{
char out_str[16];
EXEC SQL BEGIN DECLARE SECTION;
datetime year to hour dt1;
EXEC SQL END DECLARE SECTION;
/* Initialize dt1 */
dtcurrent(&dt1);
/* Convert the internal format to ascii for displaying */
dttoasc(&dt1, out_str);
/* Print it out*/
printf("\\tThe value of dt1 (year to hour) is: %s\\n", out_str);
EXEC SQL DATABASE wrice;
EXEC SQL SELECT dt INTO :dt1 FROM wdr_date;
dttoasc(&dt1, out_str);
printf("\\tThe value of dt1 (year to hour) is: %s\\n", out_str);
}
--
I got the following output
--
The value of dt1 (year to hour) is: 2000-12-21 11
The value of dt1 (year to hour) is:
--
Would 4gl be doing things any differently? If so. why was the original
poster getting a different result?
Will
Sent via Deja.com
http://www.deja.com/