Bookmark Topic Watch Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • Report post to moderator
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
     
    Today's lesson is that you can't wear a jetpack AND a cape. I should have read this tiny ad:
    a bit of art, as a gift, the permaculture playing cards
    https://gardener-gift.com
      Bookmark Topic Watch Topic
    • New Topic