{"action":"closed","issue":{"url":"https://api.github.com/repos/praeclarum/sqlite-net/issues/185","repository_url":"https://api.github.com/repos/praeclarum/sqlite-net","labels_url":"https://api.github.com/repos/praeclarum/sqlite-net/issues/185/labels{/name}","comments_url":"https://api.github.com/repos/praeclarum/sqlite-net/issues/185/comments","events_url":"https://api.github.com/repos/praeclarum/sqlite-net/issues/185/events","html_url":"https://github.com/praeclarum/sqlite-net/issues/185","id":12402866,"number":185,"title":"QueryAsync, Query don't handle non-English characters in query ","user":{"login":"dmitrykm","id":1682890,"avatar_url":"https://avatars.githubusercontent.com/u/1682890?v=3","gravatar_id":"","url":"https://api.github.com/users/dmitrykm","html_url":"https://github.com/dmitrykm","followers_url":"https://api.github.com/users/dmitrykm/followers","following_url":"https://api.github.com/users/dmitrykm/following{/other_user}","gists_url":"https://api.github.com/users/dmitrykm/gists{/gist_id}","starred_url":"https://api.github.com/users/dmitrykm/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/dmitrykm/subscriptions","organizations_url":"https://api.github.com/users/dmitrykm/orgs","repos_url":"https://api.github.com/users/dmitrykm/repos","events_url":"https://api.github.com/users/dmitrykm/events{/privacy}","received_events_url":"https://api.github.com/users/dmitrykm/received_events","type":"User","site_admin":false},"labels":[],"state":"closed","locked":false,"assignee":null,"assignees":[],"milestone":null,"comments":0,"created_at":"2013-03-25T14:40:23Z","updated_at":"2016-10-20T11:43:48Z","closed_at":"2016-10-20T11:43:48Z","body":"﻿Suppose we have table called \"Room\" that has field \"CenterName\":\r\n\r\nName\t:CenterName\r\nDeclared Type: varchar(140)\r\n\r\n\r\nWith sufficient test data in Room, query like \r\n SELECT DISTINCT CenterName as Hub FROM Room WHERE CenterName LIKE '%х%'\r\n\r\nretrieves all relevant rows, for example, it gives results like\r\n\r\nновый хаб\r\nновый хаб 2 но с более длинным названием\r\n\r\nI'm testing this with SQLite Expert Pro.\r\n\r\nThe same query using QueryAsync and Query doesn't work if LIKE parameter contains non-English text, but works if the parameter is English text. \r\n\r\nFor example following query\r\n\r\n var query = string.Format(\"SELECT DISTINCT CenterName as Hub FROM Room WHERE CenterName LIKE '%{0}%'\", \r\n                                     pattern);\r\n            return dbConn.Conn.QueryAsync<HubResult>(query);   \r\n\r\n\r\nwhere \r\n\r\n public class HubResult\r\n    {\r\n        [CanBeNull]\r\n        public string Hub { get; set; }\r\n\r\n        public bool All { get; set; }\r\n\r\n        [NotNull]\r\n        public string DisplayName\r\n        {\r\n            get\r\n            {\r\n                if (All)\r\n                    return \"All hubs\";\r\n\r\n                Check.Assert(Hub!=null, \"for not All hubs hub!=null\");\r\n                return Hub;\r\n            }\r\n        }\r\n    }\r\n\r\ngives 0 rows if pattern==\"х\" (not English x here).\r\n\r\nI fixed this by rewriting Prepare2 declaration to take bytes instead of LPStr. \r\n\r\nThis is my rewritten Prepare2 wrapper:\r\npublic static IntPtr Prepare2 (IntPtr db, string query)\r\n\t\t{\r\n\t\t\tIntPtr stmt;\r\n\r\n\t\t    var cb = Encoding.UTF8.GetByteCount(query);\r\n            byte[] queryAsBytes = new byte[cb + 1];\r\n            Encoding.UTF8.GetBytes(query, 0, query.Length, queryAsBytes, 0);\r\n\r\n            var r = Prepare2(db, queryAsBytes, cb, out stmt, IntPtr.Zero);\r\n\t\t\tif (r != Result.OK) {\r\n\t\t\t\tthrow SQLiteException.New (r, GetErrmsg (db));\r\n\t\t\t}\r\n\t\t\treturn stmt;\r\n\t\t}\r\n\r\n\r\nand this is new declaration of Prepare2:\r\n\t\t[DllImport(\"sqlite3\", EntryPoint = \"sqlite3_prepare_v2\", CallingConvention=CallingConvention.Cdecl)]\r\n        public static extern Result Prepare2(IntPtr db, byte[]sql, int numBytes, out IntPtr stmt, IntPtr pzTail);\r\n\r\n\r\n\r\n\r\n\r\n\r\n"}}
{"actor":{"display_login":"dmitrykm"}}