我是新手,想知道如何在where子句的MESSAGE参数中转义RTF语法。
select count(*)
from communicationoutgoingmessagescheduledetails
where email='chris.green@waynesreaves.com'
and message='{\rtf1\deff0{\fonttbl{\f0 Times New Roman;}}{\colortbl\red0\green0\blue0 ;\red0\green0\blue255 ;}{\*\listoverridetable}{\stylesheet {\ql\cf0 Normal;}{\*\cs1\cf0 Default Paragraph Font;}{\*\cs2\sbasedon1\cf0 Line Number;}{\*\cs3\ul\cf1 Hyperlink;}}\splytwnine\sectd\pard\plain\ql{\cf0 first }{\b\cf0 paymen}{\b\cf0 t bold }{\cf0 rem}{\cf0 inder}\par\pard\plain\ql{\ul\cf0 se}{\ul\cf0 cond PAYMENT }{\b\ul\cf0 reminder}\b\ul\par}'
and succeddful=-1
and senttime::timestamp::date='2/7/2013 12:00:00 AM'
更新
我遇到的错误如下:
WARNING: nonstandard use of escape in a string literal LINE 4: and message='{\rtf1\deff0{\fonttbl{\f0 Times New Roman;}}{\c...
HINT: Use the escape string syntax for escapes, e.g., E'\r\n'.
ERROR: invalid Unicode escape LINE 4: and message='{\rtf1\deff0{\fonttbl{\f0 Times New Roman;}}{\c...
HINT: Unicode escapes must be \uXXXX or \UXXXXXXXX.
********** Error **********
ERROR: invalid Unicode escape SQL state: 22025 Hint: Unicode escapes must be \uXXXX or \UXXXXXXXX. Character: 124
更新 2
这是我执行的代码,用于从 PostgreSQL 获取结果集:
string sql = "select count(*) from communicationoutgoingmessagescheduledetails " +
"where email='" + email + "' ";
if (message != string.Empty)
sql += String.Format("and message='{0}' ", message);
if (subject != string.Empty)
sql += String.Format("and subject='{0}' ", subject);
sql += "and successful=true " +
"and senttime::timestamp::date='" + date.Date + "'";
System.Data.IDbConnection connection = ORGen.Access.Config.Instance.Connection;
IDbCommand command = connection.CreateCommand();
connection.Open();
command.CommandText = sql;
bool retVal = Convert.ToInt16(command.ExecuteScalar()) > 0 ? true : false;
connection.Close();
command = null;
connection = null;