Escape single quotation mark in sql query...

crosspost from http://rex.la/blogs/work/

just a reminder of forming a sql query.

for concating strings in different databases, refer to my post here.

for concatenation, MS SQL uses " + " operator , Oracle uses " || " operator (as well as MySql uses function CONCAT() ) . so if one is designing a Data Repository using Repository Pattern, one may need to process this in repository level, which like providing a function for other code to concate strings in forming a query.

fortunately, for escaping single quotation mark in a query , it's the same as in MsSql and Oracle , they both used 2 single quotation marks to escape single quotation mark in a single-quotation-mark quoted string. (eg: for a string like o'connell to put in a query, it's 'o''connell' , " ' ' " is escaped and represents " ' ").

by the way, as NHibernate is just transforming object propertires in to real database field names, those rules above also apply to HQL query. also true if one is using escape chars in a "like" search (eg: select .. from class1 cls where cls.prop1 like '%My\\_Name%' escape '\\' ), the translation to real sql query both for mssql and oracle is tested without problems. 

Technorati Tags: hibernate , nhibernate , orm , database , programming