dbaccess silently truncates string data input to varchar column

We are running Informix 8.21. Suppose I create table mytest (foo varchar(4)); insert into mytest(foo) values ('hello'); The operation succeeds, but the string has been truncated to 'hell' ! This also happens with the load command in dbaccess. This seems like a very serious bug. It corrupted data being loaded into a table. In the old 7.1 documentation "Informix Guide to SQL" under "Get Diagnostics" for "List of SQLSTATE Codes" it says that Class 01, Subclass 004 means "String data, right truncation" Is there a way to request that the engine treat this as an error? I do not want to merely control dbaccess, but all ways to populate the database. We could not find an option to do this. We could not find even an option to control dbaccess. I believe this behavior, treat truncation as an error, should be the default. This SQLSTATE code of 01004 is ANSI, according to the doc. What if anything does ANSI SQL say about having the system treat this and other kinds of warnings as errors? Are any other kinds of input value transformations merely "warnings"? I searched the FAQ and dejanews for this, but got no hits. Is there some "keyword" I should be looking for? P.S. Perl DBI does permit us to treat it as an error if we wish, and in fact by default we treat all SQLSTATE codes except 00000, pure Success, as errors. Steve --- Steven Tolkin steve.tolkin@fmr.com 617-563-0516 Fidelity Investments 82 Devonshire St. R24D Boston MA 02109 There is nothing so practical as a good theory. Comments are by me, not Fidelity Investments, its subsidiaries or affiliates.

varchar(4) is a varchar column up to 4 characters long maximum. Did you mean:varchar(255, 4) which is a varchar up to maximum size that reserves at least 4 chars? Art S. Kagel Steven Tolkin wrote: > > We are running Informix 8.21. Suppose I > create table mytest (foo varchar(4)); > insert into mytest(foo) values ('hello');> > The operation succeeds, but the string has been truncated to 'hell' ! > > This also happens with the load command in dbaccess. > > This seems like a very serious bug. > It corrupted data being loaded into a table. > > In the old 7.1 documentation "Informix Guide to SQL" under "Get > Diagnostics" for "List of SQLSTATE Codes" it says that > Class 01, Subclass 004 means "String data, right truncation" > > Is there a way to request that the engine treat this as an error? > I do not want to merely control dbaccess, but all ways to populate the > database. > We could not find an option to do this. > We could not find even an option to control dbaccess. > I believe this behavior, treat truncation as an error, should be the > default. > This SQLSTATE code of 01004 is ANSI, according to the doc. > What if anything does ANSI SQL say about having the system > treat this and other kinds of warnings as errors? > > Are any other kinds of input value transformations merely "warnings"? > > I searched the FAQ and dejanews for this, but got no hits. > Is there some "keyword" I should be looking for? > P.S. Perl DBI does permit us to treat it as an error if we wish, > and in fact by default we treat all SQLSTATE codes except 00000, > pure Success, as errors. > > Steve > --- > Steven Tolkin steve.tolkin@fmr.com 617-563-0516 > Fidelity Investments 82 Devonshire St. R24D Boston MA 02109 > There is nothing so practical as a good theory. Comments are by me, > not Fidelity Investments, its subsidiaries or affiliates.

I meant what I said varchar(4), a column that holds up to 4 chars. Clearly there is a mismatch if the DDL say the max length is 4 but the data stream has 5 chars. Presumably the fix is to increase the column length. But I want Informix dbaccess to report this error, not silently load the truncated data. Steve Art S. Kagel wrote: > > varchar(4) is a varchar column up to 4 characters long maximum. Did > you mean:varchar(255, 4) which is a varchar up to maximum size that > reserves at least 4 chars? > > Art S. Kagel > > Steven Tolkin wrote: > > > > We are running Informix 8.21. Suppose I > > create table mytest (foo varchar(4)); > > insert into mytest(foo) values ('hello');> > > > The operation succeeds, but the string has been truncated to 'hell' ! > > > > This also happens with the load command in dbaccess. > > > > This seems like a very serious bug. > > It corrupted data being loaded into a table. > > > > In the old 7.1 documentation "Informix Guide to SQL" under "Get > > Diagnostics" for "List of SQLSTATE Codes" it says that > > Class 01, Subclass 004 means "String data, right truncation" > > > > Is there a way to request that the engine treat this as an error? > > I do not want to merely control dbaccess, but all ways to populate the > > database. > > We could not find an option to do this. > > We could not find even an option to control dbaccess. > > I believe this behavior, treat truncation as an error, should be the > > default. > > This SQLSTATE code of 01004 is ANSI, according to the doc. > > What if anything does ANSI SQL say about having the system > > treat this and other kinds of warnings as errors? > > > > Are any other kinds of input value transformations merely "warnings"? > > > > I searched the FAQ and dejanews for this, but got no hits. > > Is there some "keyword" I should be looking for? > > P.S. Perl DBI does permit us to treat it as an error if we wish, > > and in fact by default we treat all SQLSTATE codes except 00000, > > pure Success, as errors. > > > > Steve > > --- > > Steven Tolkin steve.tolkin@fmr.com 617-563-0516 > > Fidelity Investments 82 Devonshire St. R24D Boston MA 02109 > > There is nothing so practical as a good theory. Comments are by me, > > not Fidelity Investments, its subsidiaries or affiliates. -- Hopefully helpfully yours, Steve --- Steven Tolkin steve.tolkin@fmr.com 617-563-0516 Fidelity Investments 82 Devonshire St. R24D Boston MA 02109 There is nothing so practical as a good theory. Comments are by me, not Fidelity Investments, its subsidiaries or affiliates.

Steven Tolkin wrote: > > We are running Informix 8.21. Suppose I > create table mytest (foo varchar(4)); > insert into mytest(foo) values ('hello');> > The operation succeeds, but the string has been truncated to 'hell' ! > > This also happens with the load command in dbaccess. > > This seems like a very serious bug. No. It seems like a very sensible way of dealing with mildly erroneous data. If you didn't try to do the impossible (squeeze 5 bytes into a 4 byte pot), there wouldn't be an issue. > It corrupted data being loaded into a table. At the ESQL/C level, a warning was generated. This warning was ignored, as is customary. If you want a loader which pays attention to such warnings, write it. There's a good starting point in the SQLCMD available from the IIUG web site (http://www.iiug.org). If you want to know more about what to do, contact me (I have some ideas after reading your message). > In the old 7.1 documentation "Informix Guide to SQL" under "Get > Diagnostics" for "List of SQLSTATE Codes" it says that > Class 01, Subclass 004 means "String data, right truncation" > > Is there a way to request that the engine treat this as an error? No. > I do not want to merely control dbaccess, but all ways to populate the > database. > We could not find an option to do this. > We could not find even an option to control dbaccess. DB-Access normally ignores errors and makes believe all is well. Anything as subtle as a warning is way beyond its abilities. > I believe this behavior, treat truncation as an error, should be the > default. No, if only because of backwards compatability. Also, this is the first time I've ever heard of such a complaint. If you use I4GL or ESQL/C, you can use WHENEVER WARNING STOP to simulate this. It requires explicit code, though. It has never been perceived as desirable to enforce it. > This SQLSTATE code of 01004 is ANSI, according to the doc. > What if anything does ANSI SQL say about having the system > treat this and other kinds of warnings as errors? > > Are any other kinds of input value transformations merely "warnings"? > > I searched the FAQ and dejanews for this, but got no hits. > Is there some "keyword" I should be looking for? > P.S. Perl DBI does permit us to treat it as an error if we wish, > and in fact by default we treat all SQLSTATE codes except 00000, > pure Success, as errors. Hmmm; yes, I suppose you probably can. That's an interesting side effect of the code. It certainly wasn't designed into DBD::Informix per se -- I would never inflict such behaviour on the unsuspecting user (eg me!) without an explicit option turning it on. -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix v0.60 -- see http://www.perl.com/CPAN #include <disclaimer.h>