Re: Inserting an empty string inserts a space

Sorry, but Informix CAN distinguish NULL strings, empty strings, one-space strings, and multiple-spaces strings. However empty strings is not standard SQL, but an Informix extension. The ANSI standard for VARCHAR(n) corresponds to Informix VARCHAR(n,1), which means that a VARCHAR(n) can be either NULL or at least 1 character long. Informix has the specific VARCHAR(n,0) datatype to enable storage of empty strings. And Informix VARCHAR(n,n) corresponds to ANSI datatype CHAR(n), which means that a VARCHAR(n,n) is either NULL or of length n. By default, Informix behavior is not the ANSI standard, which means that VARCHAR(n) is VARCHAR(n,0) when you declare a varaible or parameter, BUT NOT WHEN YOU CREATE A TABLE, because the database can be moved (irreversibly) to the ANSI mode, so that tables definitions are kept, with their currently stored data rows. The behavior of stored procedures is changed in ANSI mode: BEGIN WORK is no more supported, and COMMIT WORK or ROLLBACK WORK is required after all queries. Informix manage the "" or '' constants like empty strings of type VARCHAR(1,0) NOT NULL, this behavior differs slightly from Sybase or MS-SQLServer rules (they manage them as a one-blank VARCHAR(1) NOT NULL). Informix NULL strings cannot be concatenated (NULL is always returned), so this is why the empty string is supported. Sybase supports concatenation of NULL as if they were empty strings. In addition ODBC has two different operators for concatenation: the double-pipe is ANSI compatible (NULL is returned if one operand is null), and the ampersand operator behaves like Sybase rules (NULL operands are ignored like if they were empty strings). On Informix the & operators via ODBC generates extra code to replace NULL values by empty strings. lantz <lantz@osm.com> a 'crit dans l'article <33B81ADE.5AA1@osm.com>... > I don't know the particulars about how the data is handled internally by > informix. Black box techniques reveal that informix doesn't seem to > distinguish between an empty string, a string with a single space > character, or strings with multiple space characters. Try to query from > your table where url = ''; I think it will find your row even though it > displays it as a single space. > ======================================================================== > Tauren Mills wrote: > > UPDATE product_info SET prod_id = 1, prod_type_info_id = 1,> > short_description = 'Test', url = '', sort_order = 1 WHERE > > prod_info_id=1; > > > > The field "url" should be set to an empty string, but every time this > > statement is executed, the "url" field has a single space in it: ' '. >