LVARCHAR data type

I have been trying to find a way to insert less than the maximum number of characters into an LVARCHAR column using SPL or 4GL and there doesn't seem to be a way. If I insert nothing at all, then NULL is stored. If I store anything at all in the column it is padded with spaces to the maximum length. I am doing this testing using SPL. We get the same results using 4GL. If I insert with an SQL insert from dbaccess then the data is not padded with spaces. I have tried NULL terminating the string and it does not work either. In the example below I am using only LVARCHAR(100). In production I have a table with 2 LVARCHAR(3000) columns and about 75,00 rows and growing. I am using an extra ~6000 bytes of storage per row, all white space. Not the desired result of using the LVARCHAR type. I have a case open with IBM Informix, #422848. Anyone run into this and know a way around it? Regards, Bill The table: create table "informix".tab2 ( key serial not null , text lvarchar(100), primary key (key) ); The SPL insert: drop procedure proc2; create procedure proc2()define text char(100); let text = "test"; insert into tab2 values(0,text);end procedure; The SQL insert executed from dbaccess: insert into tab2 values(0,"test") The data: unload to /tmp/tab2.unl select *,length(text) from tab2 1|test |4| 2|test|4| The SPL insert NULL terminated: drop procedure proc2; create procedure proc2()define text char(100); let text = "test"; let text[5,100] = NULL; insert into tab2 values(0,text);end procedure; The data: 1|test |4| 2|test|4| 3|test |4| sending to informix-list

The problem is in this define text char(100); change it to define text lvarchar(100) ; and it will be OK. I tested this on 9.21. Ravi.

Hi :-) You are tricking yourself in a very neat way. Your problem in the SP is that a char(100) is always 100 characters long (or null). So change your SP to: create procedure proc2()define text char(100); let text = "test"; insert into tab2 values(0,trim(text));end procedure; or use the lvarchar type for text too. Regards, Dirk -- -- Dirk Gunsthoevel IT Systemanalyse phone: +49 (0)251 28446-0 -- Hammer Str. 13 fax: +49 (0)251 28446-55 -- D-48153 Muenster http://www.GunCon.de/ -- "Toto, I don't think we're in Kansas anymore..." "Bill Dare" <dareb@jevic.com> schrieb im Newsbeitrag news:1105636215.86855608d8474c41a554316585b9c29c@teranews... > > I have been trying to find a way to insert less than the maximum number of characters into an LVARCHAR column using SPL or 4GL and there doesn't seem to be a way. If I insert nothing at all, then NULL is stored. If I store anything at all in the column it is padded with spaces to the maximum length. I am doing this testing using SPL. We get the same results using 4GL. If I insert with an SQL insert from dbaccess then the data is not padded with spaces. I have tried NULL terminating the string and it does not work either. > > In the example below I am using only LVARCHAR(100). In production I have a table with 2 LVARCHAR(3000) columns and about 75,00 rows and growing. I am using an extra ~6000 bytes of storage per row, all white space. Not the desired result of using the LVARCHAR type. I have a case open with IBM Informix, #422848. > > Anyone run into this and know a way around it? > > Regards, > Bill > > The table: > > create table "informix".tab2 > ( > key serial not null , > text lvarchar(100), > primary key (key) > ); > > The SPL insert: > > drop procedure proc2; > create procedure proc2()> define text char(100); > let text = "test"; > insert into tab2 values(0,text);> end procedure; > > The SQL insert executed from dbaccess: > > insert into tab2 values(0,"test")> > The data: > > unload to /tmp/tab2.unl select *,length(text) from tab2> > 1|test |4| > 2|test|4| > > > The SPL insert NULL terminated: > > drop procedure proc2; > create procedure proc2()> define text char(100); > let text = "test"; > let text[5,100] = NULL; > insert into tab2 values(0,text);> end procedure; > > The data: > > 1|test |4| > 2|test|4| > 3|test |4| > > > sending to informix-list