VARCHAR does not work as expected in Apache Derby

Question

I'm having this same problem:

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.

that is already answered:

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.

but my doubt is: isn't VARCHAR suppose to variate its size to fit the data? What's wrong with apache derby's VARCHAR?

Answer 1

No, what you describe has nothing to do with that: varchar will not pad with spaces, char will pad with spaces

so if you have

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

it will use 4 bytes not 20 bytes, however you can't fit more than 20 characters into that column, either check before inserting or use something like a LEFT(val,2) function

But then again would you not want to know that your data isn't fitting and being truncated. After all the most important thing is your data...if your data is fault you have nothing

In SQL Server there is a setting you can use and it will do this for you, see Suppress string or binary data would be truncated messages with the ANSI WARNINGS setting

Basically you need SET ANSI_WARNINGS OFF but I don't know if that works with your DB

Answer 2

The VARCHAR type is variable length, but the number you supply in its definition is the maximum number of characters you're allowing it to have.

VARCHAR(2) will stores values that are 0-2 characters, as opposed to CHAR(2) which will store 2 characters regardless of what data it actually has.

Side Note: Once you get to 255 characters, consider using the TEXT type instead.