community.borland.com

Article #25154: arithmetic exception, numeric overflow, or string truncation.

Problem:
Inserting/updating record A (field length (10) with a value from 
 record B (field length (60)) results in a string truncation error.

Solution:
Metadata:
	CREATE DATABASE test.gdb
	CREATE TABLE T1 (
		c1 CHAR(5),
		c2 CHAR(2));

	Example: (incorrect usage)

		INSERT INTO t1(c1) VALUES ("a");
		INSERT INTO t1(c1) VALUES ("ab");
		INSERT INTO t1(c1) VALUES ("abc");

			SELECT * FROM t1;
	
			C1     C2     
			====== ====== 
			a       
			ab      
			abc     

		The following statements fail with the error: 
	"arithmetic exception, numeric overflow, or string truncation"
as they should.  Otherwise you would have data loss with the last record "abc".

		INSERT INTO t1(c2) SELECT c1 FROM t1;
		UPDATE t1 SET c2 = c1;
		UPDATE t1 SET c2 = CAST(c1 AS CHAR(2));

	Example: (correct usage)
		You can insert values into a smaller column from 
		a larger column as long as the value being inserted
		 will fit into the range of the destination column. 
		 You can do this with the CAST() function.

			SELECT * FROM t1;
	
			C1     C2
			====== ======
			a      
			ab     

		The following statement succeeds as it should.
		UPDATE t1 SET c2 = CAST(c1 AS CHAR(2));

		SELECT * FROM t1;
	
			C1     C2
			====== ======
			a      a
			ab     ab

Last Modified: 29-SEP-00