Truncating strings

Question

Hey,

I'm working with third party user data that may or may not fit into our database. The data needs to be truncated if it is too long.

We are using IBatis with Connector/J. If the data is too long a SQL exception is thrown. I have had two choices: either truncate the strings in Java or truncate the strings in sql using substring.

I don't like truncating the strings in sql, because I am writing table structure in our Ibatis XML, but SQL on the other hand knows about our database collation (which isn't consistent and would be expensive to make consistent) and can truncate string in a multibyte safe manner.

Is there a way to have the Connector/J just straight insert this SQL and if not which route would people recommend?

Answer 1

According to the MySQL documentation it's possible that inserting data that exceeds the length could be treated as a warning:

Inserting a string into a string column (CHAR, VARCHAR, TEXT, or BLOB) that exceeds the column's maximum length. The value is truncated to the column's maximum length.

One of the Connector/J properties is jdbcCompliantTruncation. This is its description:

This sets whether Connector/J should throw java.sql.DataTruncation exceptions when data is truncated. This is required by the JDBC specification when connected to a server that supports warnings (MySQL 4.1.0 and newer). This property has no effect if the server sql-mode includes STRICT_TRANS_TABLES. Note that if STRICT_TRANS_TABLES is not set, it will be set as a result of using this connection string option.

If I understand correctly then setting this property to false doesn't throw the exception but inserts the truncated data. This solution doesn't require you to truncate the data in program code or SQL statements, but delegates it to the database.