NpgSQL 插入包含反斜杠“\\”的文件路径
我正在尝试创建包含文件路径的记录。使用 NpqSQL 驱动程序将插入操作插入到启用了 UTF8 的 Postgres 数据库中。
我的表定义：
CREATE TABLE images
(
id serial,
file_location character varying NOT NULL
)
我的 SQL 语句，包括执行它的代码（归结为最少）：
string sqlStatement = "INSERT INTO images (file_location) VALUES ('\\2010')";
NpgsqlConnection dbConnection = new NpgsqlConnection(connectionString);
dbConnection.Open();
NpgsqlCommand dbCommand = new NpgsqlCommand(sqlStatement , dbConnection);
int result = dbCommand.ExecuteNonQuery();
dbConnection.Close();
当使用 pgAdmin 插入上述语句时，它工作正常。通过 Visual Studio C# 使用 NpgSQL 驱动程序，它会因以下异常而失败：
"ERROR: 22021: invalid byte sequence for encoding \"UTF8\": 0x81"
正如 Milen 准确解释的那样，Postgres 将该语句解释为 八进制数 (\o201 == 0x81)。
正如 Milen 还描述的那样，路径前面的 E 没有帮助。
快速回顾一下：为什么 NpqSQL 停止插入 \\2010？
I am trying to create a record containing the path to a file. The insertion is done into a Postgres database where UTF8 is enabled, using the NpqSQL driver.
My table definition:
CREATE TABLE images
(
id serial,
file_location character varying NOT NULL
)
My SQL statement including the code that executes it (boiled down to a minimum):
string sqlStatement = "INSERT INTO images (file_location) VALUES ('\\2010')";
NpgsqlConnection dbConnection = new NpgsqlConnection(connectionString);
dbConnection.Open();
NpgsqlCommand dbCommand = new NpgsqlCommand(sqlStatement , dbConnection);
int result = dbCommand.ExecuteNonQuery();
dbConnection.Close();
When using pgAdmin to insert the above statement, it works fine. Using the NpgSQL driver through Visual Studio C#, it fails with this exception:
"ERROR: 22021: invalid byte sequence for encoding \"UTF8\": 0x81"
As Milen accurately explains, Postgres interprets the statement as an octal number (\o201 == 0x81).
As Milen also describes, the E infront of the path doesn't help.
So a quick recap: Why is NpqSQL stopping my insertion of the \\2010?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
（意识到我的评论看起来像一个答案，所以相应地转换了它们。）
你还没有显示真正的代码，所以我想你的解释器/编译器将双反斜杠解释为转义的反斜杠，然后 Postgres 只看到一个反斜杠后面跟着一些数字。它解释为八进制字节值（八进制 201 = 十六进制 81）。
关于“转义”字符串常量（以“E”开头的字符串） - 在您的情况下它们是完全不必要的。在标准 SQL 中，反斜杠没有特殊含义。
请阅读手册中的“4.1.2.1.字符串常量”和“4.1.2.2.具有C风格转义的字符串常量”(http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS< /a>）了解详细信息。
(Realised my comments look like an answer so converted them accordingly.)
You haven't showed the real code so I suppose your interpreter/compiler interprets the double backslashes as an escaped backslash and then Postgres sees only one backslash followed by some digits. Which is it interprets as a octal byte value (octal 201 = hexadecimal 81).
About the "escape" string constants (strings starting with "E") - in your case they are completely unnecessary. In standard SQL backslash has no special meaning.
Please read "4.1.2.1. String Constants" and "4.1.2.2. String Constants with C-Style Escapes" from the manual (http://www.postgresql.org/docs/current/static/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS) for details.
Milen 值得赞扬，因为他引导我找到了答案 - 谢谢！
显然，NpgSQL 在将我的 SQL 语句插入 Postgres 之前执行了一次转义迭代。因此，为了解决我的问题，我用两个反斜杠替换了所有出现的反斜杠：
Milen deserves credit for leading me to the answer - thanks!
Appearantly NpgSQL performs one escape-iteration before inserting my SQL statement into Postgres. Thus to solve my problem, I replaced all the occurances of my backslashes with two backslashes instead: