Maximum varchar columns exceeded.

In IDS 9.40, it appears that there is a limit of 231 special columns in a row but the create table statement can only do 195. You can add the other 36 special columns using alter table. Page 2-216 of the 9.40 SQL Syntax manual states: "Because of the maximum row size limit of 32,767 bytes, no more than 195 columns in the table can be of the data types BYTE, TEXT, ROW, LVARCHAR, NVARCHAR, VARCHAR, and varying-length UDTs. Similarly, no more than 97 columns can be of COLLECTION data types (SET, LIST, and MULTISET)." IDS 7.3 had a limit of 231 special columns so it would appear that perhaps an error was made porting the alter table code from 7.3.to 9.x. The reality though is that you CAN create those extra columns and they do store data. You can do a dbexport but dbimport will fail. This is causing me grief because I am supporting a third party application that allows the users to add columns arbitrarily and character fields are always stored in varchar columns. The vendor is not interested in dealing with the problem. (It's not technically their fault anyway). Does anyone have any further knowledge of this situation? I worry that there must have been a reason to impose the 195 special column limit and we are violating it. Is this going to cause me additional problems besides not being able to dbimport? Tom Benton

9.40UC??? OS? Maybe fixed in later 9.40? Bob Roussey Unix / Informix Administration Spirit Airlines Robert.Roussey@SpiritAir.com 586.741.8991 -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of Benton, Tom Sent: Wednesday, January 18, 2006 6:26 PM To: ids@iiug.org Subject: Maximum varchar columns exceeded. [6227] In IDS 9.40, it appears that there is a limit of 231 special columns in a row but the create table statement can only do 195. You can add the other 36 special columns using alter table. Page 2-216 of the 9.40 SQL Syntax manual states: "Because of the maximum row size limit of 32,767 bytes, no more than 195 columns in the table can be of the data types BYTE, TEXT, ROW, LVARCHAR, NVARCHAR, VARCHAR, and varying-length UDTs. Similarly, no more than 97 columns can be of COLLECTION data types (SET, LIST, and MULTISET)." IDS 7.3 had a limit of 231 special columns so it would appear that perhaps an error was made porting the alter table code from 7.3.to 9.x. The reality though is that you CAN create those extra columns and they do store data. You can do a dbexport but dbimport will fail. This is causing me grief because I am supporting a third party application that allows the users to add columns arbitrarily and character fields are always stored in varchar columns. The vendor is not interested in dealing with the problem. (It's not technically their fault anyway). Does anyone have any further knowledge of this situation? I worry that there must have been a reason to impose the 195 special column limit and we are violating it. Is this going to cause me additional problems besides not being able to dbimport? Tom Benton ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.