From: mfgsys!birdsall@kaiser.spk.wa.us
Subject: Ingres Null Indicator Un-documented Feature
Organization: National Radio Astronomy Observatory
Date: Tue, 29 Sep 1992 17:44:00 GMT


   WARNING!! Ingres undocumented feature - NULL Indicator variables:

      I recently discovered, and Ingres has verified, that contrary to Ingres
   documentation, indicator variables can assume values other than '0' for
   NOT NULL and '-1' for NULL. In particular, if one tries to write a char or 
   varchar into a target shorter than the source length, the indicator variable
   will contain the schema value of the source column instead of '0' for NOT
   NULL.

      Ingres claims that within the Ingres environment (i.e. ABF, RBF, Windows
   4GL) they handle the 'greater than zero' cases. But embedded SQL should 
   check indicator variables for greater than '-1' for NOT NULL, rather than
   equal '0' or just 'truth':

      i.e. USE   if (-1 < indicator.var)

           NOT   if (0 == indictor.var)  /or  if (indicator.var)

      Ingres tech support has logged a documentation bug on null indicators
   and will inform their install base of the bug with release 6.5, but not
   before.


      Included below is the 'abstract' I received from Ingres on their null
   indicators:
      
___ Fri Sep 25 15:00:06 1992 ___________________________________________________
Ref#: 195086     Linked.  Confirmed            Major Subject    Minor Subject   
Doc #: US-10803   Status: OK    Links: 4       sql              embedded_sql    
Dept:  DUS    Level: 3 PEER   Advisor: PEND    Product Area/Owner               
+----------- Problem Description ----------+   sql/cathys                       
|ESQL eql. Null indicator variable is getti|                                    
|ng being set to a value >1 when trying to |                                    
|fetch into a character variable.          |   Keywords           Bug/Sirs      
|                                          |  +----------------+ +--------+     
|                                          |  |permits         | |        |     
|                                          |  |                | |        |     
|                                          |  |                | |        |     
+------------------------------------------+  +----------------+ +--------+     
+-------------- Resolution ----------------+   Error#'s      Document Xref      
|This indicates that the actual length of t|  +--------+  +-+----------+--+     
|he data item extended the length of the ta|  |        |  | |          |  |     
|rget variable. The data is truncated, and |  |        |  | |          |  |     
|the null indicator variable holds the pre-|  |        |  | |          |  |     
|truncated length of the data requested. Fo|  +--------+  +-+----------+--+     
|r example, trying to fetch a char(12) colu|   Platform/Versions affected       
|mn into a char(10) variable:nullvariable  |   y/n HW  OS  Begin    End         
+------------------------------------------+  +---+---+---+-------+-------+     
+-------------- Comments ------------------+  |y  |all|all|6.0/00 |       |     
|will return nullvariable=12.              |  |   |   |   |       |       |     
|                                          |  +---+---+---+-------+-------+     
+-------------------------------------------                                    
________________________________________________________________________________


   Jim Birdsall, Kaiser Aluminum, Trentwood, Spokane, WA.
Newsgroups: comp.databases.ingres