RE: Differences btw 7.30 and 7.23 when handling blanks

How 'bout this part of the problem: Have a column defined as CHAR(10) NOT NULL. Populate the table, with some rows having "<space>" as the contents of that column. Oh-gee-whiz-I-have-too-many-extents!!! Unload data using "unload to...select from..." Drop & re-create the table. Attempt to load the data. *** At the point when the engine attempts to load the row that had a non-zero length value of spaces, it will error out with a NULL violation. To me, this falls into the realm of bug by this definition: The engine is INCONSISTENT as to whether a non-zero length value of spaces is "NULL". Good luck! > -----Original Message----- > From: William Rice [SMTP:ricew@operamail.com] > Sent: Thursday, September 23, 1999 12:57 PM > To: informix-list; kagel@bloomberg.net > Subject: RE: Differences btw 7.30 and 7.23 when handling blanks > > please correct me if my observation is incorrect, > This does mean that if I am storing a space in a char column as > valid data then for me to retrieve the data I need to do the following > > Fetch the data > check the null indicator > if what I got back from the indicator is not null then > check and see if my value was a null. > if so expand my value to a space > > This does seem to be a bug to me, but I am making the assumption > storing a space in a character column is a valid thing to do. > > Will > > >===== Original Message From kagel@bloomberg.net ===== > >Pierre Henrotay wrote: > >> > >> When using 7.23 and ESQL/C, a CHAR column filled with one or more > blank(s) > >> returns one blank followed with one ASCII null when selecting into a > STRING > >> variable. This makes sense. > >> However, when using 7.30 and ESQL/C, a CHAR column filled with one or > more > >> blank(s) returns one ASCII null followed with another ASCII null when > >> selecting into a STRING variable. No blanks anymore ! This makes > porting > >> quite difficult. > >> Is this a known bug ? > > > >I assume you were using the single blank to detect the difference between > a > >column of spaces and a NULL column. The real problem is that this is NOT > >the correct way to detect a NULL. The correct method is to declare a > short > >and use it as an INDICATOR. On FETCH the indicator will be set to -1 if > the > >column value fetched is NULL, >0 if the column is a character column and > was > >truncated on retrieval to fit in the host variable (the indicator > contains > >the actual length), and zero otherwise. The ESQL/C manual has ALWAYS > stated > >that the contents of a host variable after FETCHing a NULL is undefined > and > >should not be relied upon. Yes many programmers initialize to a known > value, > >like NULL, and check to see if the variable was modified to detect NULLs > but > >this is relying on undocumented behavior and is explicitely recommended > >against. So, to fix your code: > > > > EXEC SQL BEGIN DECLARE SECTION; > > short col1_ind; > > char col1[21]; > > EXEC SQL END DECLARE SECTION; > > > > ::::::::::::: > > > > EXEC SQL SELECT col1 INTO :col1 INDICATOR col1_ind WHERE keyval = 3; > > > > if (col1_ind < 0) { > > col1[0] = (char)NULL; > > } else if (col1[0] == (char)NULL) { > > col1[0] = ' '; > > col1[1] = (char)NULL; > > } > > > > ::::::::::::::: > > > >Now your remaining code will work as before. > > > >Art S. Kagel > > ------------------------------------------------------------ > This e-mail has been sent to you courtesy of OperaMail, a > free web-based service from Opera Software, makers of > the award-winning Web Browser - http://www.operasoftware.com > ------------------------------------------------------------