Thursday, August 12, 2004

Special formatting characters in SQL data

I recently worked through a bug with someone who was storing data queries in sql data columns. Now I'm not going to get into why this is a bad idea in the first place. There are many limiting factors present in the development of this solution that substantiate the decision to store the query this way. The real interest here is that the query in question contained line feeds, carriage returns, and tab characters to make the query more readable in Enterprise Manager. Unfortunately, because this data was then read directly into a string by a data reader, the formatting characters were present in the query text when it was presented back to the RDBMS for execution thus causing an exception.

The database analyst argued that many of his queries contained carriage returns, line feeds, and tab characters and that the problem was therefore with the code that retrieved the query from the database. However, all of the other queries were stored procedures which the SQL data engine parses into compiled code thus removing the special characters. This example demonstrates one of the differences between stored procedures and plain text queries, especially where formatting characters are involved.


Post a Comment

<< Home