Incorrect results from aggregates in ESQL/C?

Hi Informixers, this is a shot in the dark, but are there known problems with aggregate functions in ESQL/C (INFORMIX-ESQL Version 9.15.UC2) running against IDS 7.30 (Informix Dynamic Server Version 7.30.UC3)? I'm working on Siemens RM400 with Reliant Unix 5.43C20. There are strange results popping up every once in a while in an ESQL/C program that is called by a stored procedure which in turn is called by a trigger. The following SQL statement returns a NULL occasionally which is definitely incorrect: EXEC SQL SELECT MAX(folge) INTO :p_folge:i_folge FROM nverteil WHERE teamnr = :urlaub_team AND woche = :verteil_von; I check for NULL by looking at the indicator variable i_folge; it is set to "-1" when the result of the query is NULL. I cannot reproduce the error within "dbaccess". There NEVER was an error with this program for years, while it was running under Online 5.10, compiled with ESQL/C 5.01. We upgraded to the above versions of IDS and ESQL/C about two weeks ago, and now I get this error. A colleague of mine is just porting her programs to the new environment. She gets wrong results in a query using SELECT COUNT(...) INTO ... in an ESQL/C program, while she cannot reproduce the error in dbaccess. I didn't find any answers in Informix's web bug database. Are problems like this known? Regards, Richard -- +--------------------------+------------------------------------------+ | Dr. Richard Spitz | INTERNET: spitz@ana.med.uni-muenchen.de | | EDV-Gruppe Anaesthesie | Tel : +49-89-7095-6110 <-- NEW! | | Klinikum Grosshadern | FAX : +49-89-7095-8886 | | 81366 Munich, Germany | GSM : +49-172-8933578 | +--------------------------+------------------------------------------+

Richard Spitz wrote: > > Hi Informixers, > > this is a shot in the dark, but are there known problems with aggregate > functions in ESQL/C (INFORMIX-ESQL Version 9.15.UC2) running against > IDS 7.30 (Informix Dynamic Server Version 7.30.UC3)? I'm working on > Siemens RM400 with Reliant Unix 5.43C20. [SNIP] > EXEC SQL SELECT MAX(folge) INTO :p_folge:i_folge FROM nverteil > WHERE teamnr = :urlaub_team > AND woche = :verteil_von; [SNIP] > There NEVER was an error with this program for years, while it was running > under Online 5.10, compiled with ESQL/C 5.01. We upgraded to the above > versions of IDS and ESQL/C about two weeks ago, and now I get this error. [SNIP] What type is p_folge? In OL5.xx aggregation functions returned type INTEGER while under 7.xx aggregations return type DECIMAL. Obviously the ESQL/C should cause the engine to convert the data but it may not be convertible, the value may not be representable as a long. BTW what type is column folge? Is there any SQL error reported when this happens? Your friend is seeing the same problem, COUNT() is an aggregate also and now returns DECIMAL. Art S. Kagel

On Thu, 21 Jan 1999, Art S. Kagel wrote: > > EXEC SQL SELECT MAX(folge) INTO :p_folge:i_folge FROM nverteil > > WHERE teamnr = :urlaub_team > > AND woche = :verteil_von; > What type is p_folge? In OL5.xx aggregation functions returned type > INTEGER while under 7.xx aggregations return type DECIMAL. Obviously > the ESQL/C should cause the engine to convert the data but it may not > be convertible, the value may not be representable as a long. BTW what > type is column folge? Is there any SQL error reported when this > happens? There are no SQL errors reported. The program contains an error handling routine that does work (that's how I found out something was wrong), so I'm sure I'm not missing anything here. "folge" is of type smallint, and p_folge is defined as "int". I cannot imagine that a conversion error is happening, since the values for "folge" are in the range between 1 and about 15. The statement returns a NULL value even when there are several entries in the table that match the search criteria. It is not returning the biggest value but a NULL, which would have to be called a bug even if it resulted from a conversion error. The SQL statement ALWAYS works when I run it from dbaccess, so I'm suspecting a bug within ESQL/C because the difference is that I'm selecting INTO a host variable. Regards, Richard -- +----------------------------+-------------------------------------------+ | Dr. Richard Spitz | INTERNET: spitz@ana.med.uni-muenchen.de | | EDV-Gruppe Anaesthesie | Tel : +49-89-7095-6110 <-- NEU!!! | | Klinikum Grosshadern | FAX : +49-89-7095-8886 | | 81366 Munich, Germany | GSM : +49-172-8933578 | +----------------------------+-------------------------------------------+