Ingres Q & A
Hi Helen et al,
I've attached Helens mail to this update for those who want to play.
She has observed this problem on both DEC Alpha OSF patch 5054 and on
Solaris patch 5175. Hence this is notsome problem associated with 32 bit
vs 64 bit architecture. This could be a generic feature of OI1.2!
The second point to be raised is code solutions to the problem. We have
investigated and found that:
ifnull(numeric_field,'')
Is syntactically incorrect unlike 6.4/05. This is perfectly
acceptable to me.
ifnull(varchar_field,'')
Is syntactically correct but will not work. Its performance is
erratic. We have cases where unrelated fields have been corrupted
causing (for example) id numbers to be reset to other valid numbers.
Which is a real bitch in any system! Just to add some spice the
erratic behaviour has even been observed to relate to the number
of ifnulls used in the code. It took a while before I got
convinced on that one!
ifnull(varchar_filed,'null') or ifnull(varchar_field,varchar(''))
Are syntactically correct and work.
It seems like the problem is related to the type of the field. However why
'' is not acceptable varchar is beyond me.
I've asked CA for an urgent fix, as far as I'm concerned I have several
unusable applications. We make extensive use of ifnulls and there is no
way on Gods Green Earth that I'm going to ask the programming staff to
code around a fairly obvious flaw in OI1.2. Fortunatly this is all on my
development site. But unless I get a fix pronto this site is going to be
returned to 6.4/05.
I suggest if you have ifnull tests that you start checking what the code
is doing. All sorts of nasty things could be happening under your noses.
Martin Bowes.
> Just wanted to give you a quick update. We definately have the ifnull problem
> in both Solaris OI patch 5175 and DEC OI patch 5057. I called CA to report
> it, I have an issue logged with Tech Support but they haven't called me back
> yet. I was trying to put in some debug statements to see exactly where the
> problem is occurring for us. In the following snippets of code (well,
> somewhere in one of them :)) it ends up setting the effective_from_date to
> null, even though values were entered for both the effective from and to dates.
>
> We could probably find other examples of this behavior.
>
>
> PROCEDURE saveDataInArray (
> keyName = VARCHAR(20) NOT NULL
> ) =
>
> DECLARE
> i = INTEGER NOT NULL;
> rtnCode = INTEGER NOT NULL;
> status = INTEGER NOT NULL;
> userName = VARCHAR(15) NOT NULL;
> timeStamp = DATE NOT NULL;
>
> BEGIN
> SELECT userName = DBMSINFO('username'), timeStamp = DATE('now');
>
> _InitWorking;
> _Working;
>
> // Hack up current row if it is a ROW_INSERTED and no_resources
> // and to-date are both null. We initialize the from-date which
> // makes the row look like a bad row.
> IF crews._STATE = ROW_INSERTED AND crews.effective_from_date IS NOT NULL
> AND crews.effective_to_date IS NULL AND crews.no_resources IS NULL THEN
> DELETEROW crews;
>
> REDISPLAY;
> ENDIF;
> rtnCode = CALLPROC check_form_for_nulls('', FALSE, BYREF(curField));
> IF rtnCode != SUCCESS THEN
> CALLPROC PopupPrintf (msg_GENFINISHFORM2, keyName);
> RETURN :curField;
> ENDIF;
>
> /* May have outstanding date overlap, check each row against all.
> I've done it this way to permit moving about the tablefield
> while the overlap exists, to fix it.
> Can't use unloadtable, called proc uses it and can't nest.
> */
> INQUIRE_FORMS TABLE '' (totalRows = DATAROWS(crews));
> i = 1;
> WHILE i <= totalRows DO
> SCROLL crews TO i;
> status = crews._STATE;
> IF status <> ROW_DELETED AND status <> ROW_UNDEFINED THEN
> IF IFNULL(crews.effective_to_date,'') <
> IFNULL(crews.effective_from_
> date,'') THEN
> CALLPROC PopupPrintf(msg_BADDATERANGE,'From','To');
> RETURN 'effective_from_date';
> ENDIF;
> status = checkDates();
> IF status <> SUCCESS THEN
> RETURN 'effective_from_date';
> ENDIF;
> ENDIF;
> i = i + 1;
> ENDWHILE;
>
> rtnCode = ArrayClear(wcCrews);
>
> // *** MAKE SURE ANYTHING INSERTED HERE IS ALSO DONE IN THE UNLOADTABLE AT THE
> // *** TOP OF THE PROGRAM.
>
> // First, fix any time/user stamps.
> i = 0;
> UNLOADTABLE crews (status = _STATE)
> BEGIN
> IF status = ROW_CHANGED THEN
> crews.mod_user_id = userName;
> crews.mod_date = timeStamp;
> crews.mod_frame_id = ii_frame_name('current');
> ELSEIF status = ROW_INSERTED THEN
> crews.create_user_id = userName;
> crews.create_date = timeStamp;
> crews.create_frame_id = ii_frame_name('current');
> ENDIF;
>
> IF status != ROW_DELETED AND status != ROW_UNDEFINED THEN
> INSERTROW wcCrews [i] (
> no_resources = IFNULL(crews.no_resources,0),
> effective_from_date = IFNULL(crews.effective_from_date,''),
> effective_to_date = IFNULL(crews.effective_to_date,''),
> audit_trail_id = crews.audit_trail_id,
> text_id = crews.text_id,
> uda_id = crews.uda_id,
> mod_date = crews.mod_date,
> mod_user_id = crews.mod_user_id,
> mod_frame_id = crews.mod_frame_id,
> create_date = crews.create_date,
> create_user_id = crews.create_user_id,
> create_frame_id = crews.create_frame_id);
> i = i + 1;
> ENDIF;
> END;
>
> RETURN '';
>
> END
>
> ******************************************************************************
>
>
>
> PROCEDURE checkDates() =
> DECLARE
>
> curRow = INTEGER NOT NULL, /* Current row number */
> endDate = DATE NOT NULL, /* Effective ending date */
> rowNo = INTEGER NOT NULL, /* Dataset row number */
> startDate = DATE NOT NULL, /* Effective starting date */
> state = INTEGER NOT NULL, /* Tablefield row state */
> status = INTEGER NOT NULL /* Return status */
>
> BEGIN
>
> curRow = crews._RECORD;
> startDate = IFNULL(crews.effective_from_date,'');
> endDate = IFNULL(crews.effective_to_date,'');
> status = SUCCESS;
> UNLOADTABLE crews (state = _STATE, rowNo = _RECORD)
> BEGIN
> IF state <> ROW_DELETED AND state <> ROW_UNDEFINED AND rowNo <> curRow THEN
> state = INTEGER NOT NULL, /* Tablefield row state */
> status = INTEGER NOT NULL /* Return status */
>
> BEGIN
>
> curRow = crews._RECORD;
> startDate = IFNULL(crews.effective_from_date,'');
> endDate = IFNULL(crews.effective_to_date,'');
> status = SUCCESS;
> UNLOADTABLE crews (state = _STATE, rowNo = _RECORD)
> BEGIN
> IF state <> ROW_DELETED AND state <> ROW_UNDEFINED AND rowNo <> curRow THEN
> IF crews.effective_from_date IS NOT NULL
> AND crews.effective_to_date IS NOT NULL THEN
> IF (startDate >= crews.effective_from_date
> AND startDate <= crews.effective_to_date)
> OR (endDate >= crews.effective_from_date
> AND endDate <= crews.effective_to_date)
> OR (startDate < crews.effective_from_date
> AND endDate > crews.effective_to_date) THEN
> CALLPROC PopupPrintf(msg_DATEOVERLAP);
> status = FAILURE;
> ENDLOOP;
> ENDIF;
> ENDIF;
> ENDIF;
> END;
>
> RETURN status;
>
> END /* checkDates */
Hi Karl & Betty,
So far the variables have been form fields (by which I take it you mean
not database fields).
However the code has largely been 4GL and not SQL. ie iftests
Martin Bowes.
>
> >Hi Everyone,
> >
> > Further to the posting yesterday on ifnull in OI1.2 DEC Alpha,
> >
> > I've just got this form one of my programmers.
> > [snip]
>
> Martin, in the code example, are the variables being ifnull'ed both
> form fields?
>
> There have been persistent 1.2 bugs involving form fields being referenced
> directly inside SQL statements, and I vaguely recall one where the
> problem showed up in an expression. I thought these were all fixed, but
> maybe not, or maybe there is a regression on the Alpha.
>
> The Telesis stuff (big 4GL app system in my most recent life) uses
> ifnull's all over the place in its older sections, and just as I was
> typing this email I remembered that they had to back that most recent
> axp.osf patch out of a site because of mysterious "doesn't work" stuff
> that nobody could pin down. Maybe it was the same thing you are seeing.
>
> You might want to send helenb@telesismfg.com a little email and see if
> the two of you can come up with something that CA can chew on.
> I'd call this a level 1 bug as far as that patch's usability.
> (helen is hugely busy, she got stuck with all my stuff when I left,
> be patient please.)
>
>
> Karl R. Schendel, Jr.
> K/B Computer Associates wiz@kbcomputer.com
> Ingres and Unix Expertise
>
> NAIUA Member-At-Large atlarge@naiua.org
Martin Bowes wrote:
>
> Hi Everyone,
>
> We are running OI1.2 patched to 5152 on a DEC Alpha. We have come across
> the following problem. Read on and weep!
>
[...]
> > A bug has been discovered with the use of the ifnull() function
> > in OI. For example,
> > And this works - but the original example should have worked anyway.
> >
> >
> > This has been raised as an issue with Computer Associates.
> >
>
> Any ideas from anyone?
>
> I suspect this may be a DEC Alpha feature(?).
>
> Martin Bowes
What you are telling about reminds me something I found two months ago.
We are running OI 1.2/00 (axp.vms/01)on AXP/VMS 7.1 with the patch 5017.
I use the ADA preprocessor but I think it could happen with any
language. When running the program, I noticed an SQL error and after
some investigation, it turned out that the preprocessor had generated
the code "ifnull(char_column,0)" instead of "ifnull(char_column,'')" as
I wrote in the original code. It disappeared by itself after some
modifications I couln't understand so I thought I missed something and
forgot it ; is there any connection with what you are speaking about ?
Also it seems to me that OI 1.2 is less permissive than 6.4 for this
problem : I found that "ifnull(integer_column,'')" which was accepted in
6.4 is now rejected by OI 1.2 (and it's the right way, isn'it ?).
I don't know if it really helps, I just only wanted to mention that.
Lecorche Eric
Grand Accelerateur National d'Ions Lourds
BP 5027 14076 Caen France
e-mail : lecorche@ganil.fr
web : http://www.ganil.fr
Hi Terence,
CA think the problem is in '' being interpreted as char3!
According to Helen Best at Telesis:
It appears that something could have been left out of the Ingres shared
libraries for DEC 5057 and Solaris 5175 and later patches.
She has run successfully with a patch shandy of 5057 and 4909, where she
uses the shared libraries from the earlier patch.
That is just totally cool.
A patch is being worked on now apparently.
Martin Bowes.
> >
> > ifnull(varchar_field,'')
> > Is syntactically correct but will not work. Its performance is
> > erratic. We have cases where unrelated fields have been corrupted
> > ifnull(varchar_filed,'null') or ifnull(varchar_field,varchar(''))
> > Are syntactically correct and work.
>
> Could it be that Ingres is seeing '' as a Char?
>
> It's just that I remember tracking down some problems with some ESQL/C
> a while ago, it was the C that was wrong, not the ESQL bit, but...
>
> We'd moved an application from an ICL DRS3000 (i486 SVR4) to a DRS6000
> (Sparc SVR4). The problem turned out to be a dodgy strcmp(), on the
> DRS3000 strcmp(*s,'') worked, on the 6000 it core dumped. Changing
> the '' to "" fixed it.
>
> Of course in this case short of the explicit cast which shouldn't
> be needed the same 4GL code fix wouldn't work, and the problem
> needs to be fixed at the Ingres end.
>
> --
>
Ingres Q & A
Back to William's Home Page
© William Yuan 2000
Email William