[SOLVED] Can I check if data was truncated after query?

Issue

If I have a table with some varchar columns, whose lengths will obviously be limited, then I would have to show on the front-end whenever insertion of too large values fails. For example, if the limit on the name column is 20, but someone enters a name that is 30 characters long, I should notify them of the error.

This gets to be a lot of work when the application becomes big.

What I would like, to make life a bit easier, and skip taking care of individual limits for every step of the users’ journey, is to just carry on with the normal functioning of the application, but show them a warning that their data was not saved in entirety because it was too long. So if MySQL would provide some method that would allow me to ask if all data was saved in its entirety, or some strings were truncated due to their respective varchar fields being shorter (or maybe a property on the MySQLi object that I can check), then my main method for saving data in the database could always check that after any inserts or updates have been executed and just issue a warning on the next page load.

Does MySQL provide such functionality?

Solution

Sure you can. MySQL throws a warning, when data is truncated.

You can check is any warning occured by checking @@warning_count

SELECT @@warning_count;

Or

SHOW COUNT(*) WARNINGS; 

To check what warning has occured:

SHOW WARNINGS [LIMIT [offset,] row_count]

More info:
http://dev.mysql.com/doc/refman/5.0/en/show-warnings.html

Answered By – Jakub Matczak

Answer Checked By – Dawn Plyler (BugsFixing Volunteer)

Leave a Reply

Your email address will not be published.