This week's book giveaways are in the Refactoring and Agile forums.
We're giving away four copies each of Re-engineering Legacy Software and Docker in Action and have the authors on-line!
See this thread and this one for details.
Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

What happens when the length of the text exceed the limit of the column VARCHAR

 
Tejas Jain
Ranch Hand
Posts: 119
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I use VARCHAR(4000) for a column in a table in MySQL. 4000 is the max char allowed in MySQL. What will happen if the text length exceeds the 4000? Does the DB (MySQL or Oracle) automatically chop off anything that above 4000, but still allows you to insert or update the text of the first 4000 chars? Or the DB will reject the entire message if it over 4000? Any error messages?

How the length of the text is measured for the non-English languages, the raw length of the encoding protocol (e.g., UTF8)?
 
Jeanne Boyarsky
author & internet detective
Marshal
Posts: 34084
337
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Most databases give you an error. I recommend trying it to be sure on your database.
 
Martin Vajsar
Sheriff
Pie
Posts: 3751
62
Chrome Netbeans IDE Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Don't know how about MySQL, but in Oracle the 4000 size limit counts in bytes. Therefore, if your String in database encoding is represented using some multi-byte characters, it may not fit into a VARCHAR2 though its length in Java is less than 4000 characters.

I don't know how to determine the size of the String in database encoding in a general case. You'd probably have to obtain the database encoding and use it to convert the String into a byte array, then look at the length of the array. It might be easier to just catch the database exception that would be generated by using a String that is too long.

If you do need to store longer strings, you'll need to use a CLOB (maximal size of a CLOB is database dependent, but generally counts in the gigabytes).
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic