JavaRanch Home    
 
This page:         last edited 23 March 2012         What's Changed?         Edit

Avoid Implicit Conversions in SQL statements   

Avoid implicit conversions

Implicit conversion in SQL statements occurs whenever columns or parameters of different, but compatible types occurs in the same expression. Most often a text is mixed with a number or a date. The database then usually converts the number or date to a text representation to evaluate the expression. This can lead to many problems:

  • Results of queries containing implicit conversions generally depend on the database they are being processing in, but may also depend on other settings of the database (typically session-specific number and date format). This can cause a query which works in one database to fail in another, or query which works in an SQL client to fail when used in JDBC code, or even cause a query that has been reliable running for years to start to fail all of sudden for no apparent reason. "Fail" in these cases usually means that the query runs, but provides incorrect results, which - in the worst case - may go unnoticed for a long time.
  • The textual representation of a number or date has different ordering from the correct data type. This is especially important with inequality comparison (an expression WHERE SIZE < '20' - with number incorrectly specified as text - will yield different rows from those that were wanted).
  • Even when an implicit conversion does not lead to erroneous behavior, it may cause the query to be processed sub-optimally by the database, for example by precluding an existing index to be used.

Though less usual, implicit conversion can be caused by improper usage of PreparedStatement too. For example, when a parameter to a query comes from a web form or a text box, it might be tempting to set it into the query using setString(), even though the corresponding database type is a number or a date.


SqlBestPractices

JavaRanchContact us — Copyright © 1998-2014 Paul Wheaton