Re: Empty strings on Informix, using Centura

Empty strings are Informix specific, other RDBMS including MS SQLServer, Sybase, Oracle do not allow storing empty string within a table row. Sybase and MSSQLServer represent empty strings with NULL, which can be used in concatenations like empty strings (opposed to the standard behavior of NULL values). With them the '' constant is in fact a one-blank CHAR(1) constant, and it differs from NULL strings. And the ' ' two-blanks string does not differ from the ' ' one-blank string (because they are CHAR(n) internally and because padding blanks are ignored when comparing two CHAR(n) values). However the ' ' (two-blanks) and ' ' (one-blank) differ by their datatype CHAR(2) and CHAR(1) respectively, so that when concatenated to VARCHAR(n) values, the padding blanks are not stripped. So when you concatenate an "empty" string '' with Sybase, in fact you concatenate a blank. Oracle does not have a representation for empty strings. It uses a unique VARCHAR2(n) storage datatype which is used to map VARCHAR(n) and CHAR(n) datatypes. When inserting in a CHAR(n) column, padding is automatically applied to NOT NULL values. And the "empty" string '' constant is in fact a VARCHAR2(1) one-blank string. NULL strings in Oracle and informix both produce a NULL result in concatenations, where Sybase and MSSQL consider them as an empty string (only when all components of a concatenation are NULL, is the resulting string NULL). Moreover, MS Access has an additional & concatenation operator which has the standard behavior (returning NULL when only one component is NULL). Also Informix CHAR(n) datatype stores (appropriately and conforming to SQL standards, and to Oracle and Sybase behavior) empty strings with blanks. When retrieved, padding blanks are removed, but the result is not NULL, so a whole-blank CHAR(n) is returned as an empty string. However your client software (Centura), to conform with other RDBMS behaviors does not use the empty string, which it considers to be a NULL value. So it replaces the empty string by a single blank, so that the result is not NULL (and allows successfull matching when joining tables). So: do not use empty strings in your Informix tables ! Use them only in stored procedures to compute strings within variables in a stored procedure, so that NULL values that occur in tables may be conveniently replaced when appropriate by empty strings prior to returning a concatenation (for example when building a complete address line using a street number, street kind, street name, zip code, city and country, when some of them may be NULL). Always choose between the NULL or one-blank values when storing empty strings in a table. I hope this will fix your problem. infohos a 'crit dans le message <01bce077$fa18f180$d09ad2c1@infohos.unicall.be>... >EMPTY STRINGS ON INFORMIX >------------------------- >Centura 1.1.1 >NT-Client 4.0 >Informix On-Line 7.22C1 >Windows NT 3.51 > > >On Informix, If you try to insert an empty string into a char field >in the database (eg. CHAR(5)), a space is inserted into the database. > >If a user wants to update that record using a form developed in >Centura 1.1.1 that space is displayed in the field. > >If the user wants to update that record, he has to remove that space >before entering correct data into that field !!! > >Is there somebody who has the same problem ? >Please send me a solution. > > >E-mail : INFOHOS@UNICALL.BE > (Thiel Franky) >