sqlite - Monotouch - sqlite 和希伯来字符

Question

我正在 Monotouch 项目中使用预加载的 sqlite 数据库尝试以下查询:

select * from my_table where id not in (1,2,3,4) and field not in ('תווים בעברית') order by RANDOM() limit 55;

但我不断收到 SQLiteException。如果我用英语字符替换希伯来语字符串，它就可以正常工作。

如何解决这个问题？

另请注意，同样的查询在 Android 项目(用 Java 编写)中运行良好。

编辑(代码)

var conn = new SQLiteConnection (System.IO.Path.Combine (folder, "mydb.db3"));

string sql = select * from my_table where id not in (1,2,3,4) and field not in ('תווים בעברית') order by RANDOM() limit 55;

IEnumerable<myObject> objects = conn.Query<myObject> (sql.ToString()); //Here is the exception

抛出的异常:

SQLite.SQLiteException: near "lim": syntax error at SQLite.SQLite3.Prepare2 (IntPtr db, System.String query) [0x00000] in :0 at SQLite.SQLiteCommand.Prepare () [0x00000] in :0 at SQLite.SQLiteCommand+d_01[MyAppName.Question].MoveNext () [0x00000] in <filename unknown>:0
at System.Collections.Generic.List1[MyAppName.Question].AddEnumerable (IEnumerable1 enumerable) [0x00000] in <filename unknown>:0
at System.Collections.Generic.List1[MyAppName.Question]..ctor (IEnumerable1 collection) [0x00000] in <filename unknown>:0
at System.Linq.Enumerable.ToList[Question] (IEnumerable1 source) [0x00000] in :0 at SQLite.SQLiteCommand.ExecuteQuery[Question] () [0x00000] in :0 at SQLite.SQLiteConnection.Query[Question] (System.String query, System.Object[] args) [0x00000] in :0 at MyAppName.DBHelper.getQuestions (Int32 level) [0x000f1] in /Users/mmac/Documents/projects/MyAppNameMono/MyAppName/MyAppName/dal/DBHelper.cs:43 at MyAppName.AppDelegate.m_0 (System.Object ) [0x00002] in /Users/mmac/Documents/projects/MyAppNameMono/MyAppName/MyAppName/AppDelegate.cs:36

Answer 1

near "lim": syntax error

此错误消息表明查询字符串的最后六个字符被截断，即 MonoTouch 无法正确计算字符串长度。

向 Xamarin 报告此错误，和/或尝试升级。

要解决此问题，请尝试将字符串值作为参数传递(这首先是避免格式问题和 SQL injection attacks 的好主意):

string sql = "select ... field not in (?) ...";
string value = "תווים בעברית";
IEnumerable<myObject> objects = conn.Query<myObject>(sql, value);