Sqlite WHERE 子句和 Delphi XE 长字值

发布于 2024-10-14 00:07:08 字数 697 浏览 3 评论 0原文

在 Delphi XE 中，我将字符串的 crc32 哈希存储在 SQlite 数据库中声明为 INTEGER 的列中。我的理解是SQlite不区分整数类型：int、int64、有符号和无符号，就数据库而言它们都是相同的。但是，当我在 Delphi 中存储声明为长字的值时，WHERE 子句稍后无法匹配该值。

我的插入语句（此处删减）是：

INSERT INTO main VALUES (id, crc) (?, ?);

长字值绑定到第二个参数，一切顺利。但是当我执行

SELECT id FROM main WHERE crc = ?;

查询时没有返回结果。

在SQLiteSpy中查看数据库，长字值显示为负数整数。如果我使用从该显示复制并粘贴的负值执行上述 SELECT，则查询将返回预期的记录。

看起来，当我将长字值绑定到 INSERT 语句时，SQLite 会执行其他操作，然后当我将相同的长字值绑定到 SELECT 语句时。将值转换为整数（在 Delphi 代码中，而不是在 SQL 中）可以解决问题，但这不是必需的，而且很容易忘记在其他地方进行转换。有更好的解决方案吗？ SQLite 的行为是否正确？

原文

In Delphi XE, I am storing crc32 hash of a string in an SQlite database, in a column declared as INTEGER. My understanding is that SQlite does not distinguish among integer types: int, int64, signed and unsigned, they're all the same as far as the database is concerned. However, when I store a value declared as longword in Delphi, a WHERE clause fails to match on that value later.

My insert statement (trimmed down here) is:

INSERT INTO main VALUES (id, crc) (?, ?);

The longword value gets bound to the second parameter and all goes well. But when I do

SELECT id FROM main WHERE crc = ?;

the query returns no results.

Viewing the database in SQLiteSpy, the longword values are displayed as negative integers. If I execute the above SELECT with the negative value copy-and-pasted from that display, the query returns the expected record.

It would seem that when I bind a longword value to the INSERT statement, SQLIte does something else then when I bind the same longword value to the SELECT statement. Casting the value to integer (in Delphi code, not in SQL) fixes the problem, but it should not be necessary, and it'll be easy to forget to cast in other places. Is there a better solution? Is SQLite behaving correctly?

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

心不设防 2024-10-21 00:07:08

问题不在于 SQLite，而在于将参数绑定到 SQLite 引擎的方式。

根据您使用的 SQlite 框架，您必须将 Int64 显式绑定到您的语句：

INSERT INTO main VALUES (id, crc) (?, ?);

例如，使用我们的框架，将 CRC 声明为基数，您必须使用以下代码（而不是任何整数 (CRC)）：

sqlite3_check(RequestDB,sqlite3_bind_Int64(Request,2,Int64(CRC)));

如果上面的代码不起作用，这将始终起作用：

var CRC64: Int64;
    CRC64Rec: TInt64Rec absolute CRC64;
begin
  CRC64Rec.Lo := CRC;
  CRC64Rec.Hi := 0;
  sqlite3_check(RequestDB,sqlite3_bind_Int64(Request,2,CRC64));

在所有情况下，如果不显示您自己的有关绑定参数的代码，则很难知道代码中出了什么问题。

The problem is not in SQLite, but in the way you're binding your parameters to the SQLite engine.

Depending on the SQlite framework you're using, you must explicitely bound an Int64 to your statement:

INSERT INTO main VALUES (id, crc) (?, ?);

For example, using our framework, with a CRC declared as a Cardinal, you must use this code (and NOT any integer(CRC)):

sqlite3_check(RequestDB,sqlite3_bind_Int64(Request,2,Int64(CRC)));

If the above code doesn't work, this will always work:

var CRC64: Int64;
    CRC64Rec: TInt64Rec absolute CRC64;
begin
  CRC64Rec.Lo := CRC;
  CRC64Rec.Hi := 0;
  sqlite3_check(RequestDB,sqlite3_bind_Int64(Request,2,CRC64));

In all cases, without showing your own code about binding parameters, it's difficult to know what's wrong in your code.

回复收藏 0 原文

~没有更多了~