I am getting this same problem:
How do i truncate a VARCHAR towards the table area length Instantly in Derby using SQL?
To be precise:
CREATE TABLE A ( B VARCHAR(2) ) Place Right Into A B VALUES ('1234') would throw a SQLException:
A truncation error was experienced attempting to shrink VARCHAR '123' to length 2.
that's already clarified:
No. You need to chop them back after checking the meta-data. Or maybe you don't wanna look into the meta-data each time, then you definitely must keep both your code and database synchronized. But this isn't a large deal, its a usual practice in validators.
but my doubt is: is not VARCHAR suppose to variate its size to suit the information? Wrong with apache derby's VARCHAR?
No, that which you describe is not related to that: varchar won't pad with spaces, char will pad with spaces
if you have
CREATE TABLE A ( B VARCHAR(20) ); INSERT INTO A B VALUES ('1234')
it'll use 4 bytes not 20 bytes, nevertheless, you can't fit a lot more than 20 figures into that column, either check before placing or use something similar to a LEFT(val,2) function
However again can you not need to know that the data is not fitting and being cut down. In the end the most crucial factor is the data...in case your information is fault you've got nothing
In SQL Server there's an environment you should use also it is going to do this for you personally, see Suppress string or binary data would be truncated messages with the ANSI WARNINGS setting
Essentially you'll need SET ANSI_Alerts OFF but I'm not sure in the event that works together with your DB
The VARCHAR type is variable length, but the quantity you supply in the definition may be the maximum quantity of figures you are permitting it to possess.
VARCHAR(2) will stores values which are -2 figures, instead of
CHAR(2) that will store 2 figures no matter what data it really has.
Side Note: Once you're able to 255 figures, think about using the
TEXT type rather.