2

How can I truncate a VARCHAR to the table field length AUTOMATICALLY in Derby using SQL?

To be specific:

CREATE TABLE A ( B VARCHAR(2) );
INSERT INTO A B VALUES ('1234');

would throw a SQLException:

A truncation error was encountered trying to shrink VARCHAR '123' to length 2.

Is there a easy way to suppress this exception?

Robert Munteanu
  • 67,031
  • 36
  • 206
  • 278
J-16 SDiZ
  • 26,473
  • 4
  • 65
  • 84

3 Answers3

3

No. You should chop it off after checking the meta-data. Or if you don't wanna check the meta-data everytime, then you must keep both your code and database in sync. But thats not a big deal, its a usual practice in validators.

Adeel Ansari
  • 39,541
  • 12
  • 93
  • 133
  • This table is used everywhere. Keeping the code in sync is not easy. – J-16 SDiZ Jun 19 '09 at 10:10
  • In sync with database I mean. Its not a big deal, it wouldn't be every now and then. Ask yourself a question how often you will be modifying the length of the field in your database. These kinda validation goes hand-in-hand with databases contraints, I don't see any issue with this. – Adeel Ansari Jun 19 '09 at 10:17
  • And if you really wanna do that, check for meta-data before insert, it would result in a performance lack, nonetheless. – Adeel Ansari Jun 19 '09 at 10:18
0

You can trim the varchar before inserting it. use the trim function in your insert script/procedure.

I'm not familiar with Derby, but in MSSQL I do exactly the same thing, using trim to avoid truncation error (only where I don't need the full data), it's much more preferable to increase the length of the varchar

Stuart
  • 11,775
  • 6
  • 33
  • 31
  • Increasing the varchar is no desirable here, I believe. He is having no issues with truncation, truncation is even a preferable behavior for him. Secondly, trim only work for spaces. – Adeel Ansari Jun 19 '09 at 08:57
0

You can use SUBSTR:

CREATE TABLE A ( B VARCHAR(2) );
INSERT INTO A B VALUES (SUBSTR('1234', 1, 2));

In case you use prepared statements:

INSERT INTO A B VALUES (SUBSTR(?, 1, 2));
tashuhka
  • 5,028
  • 4
  • 45
  • 64