TFDQuery和SQLite：字段的类型不匹配，期望：LargeInt实际：WideString [英] TFDQuery and SQLite: Type mismatch for field, expecting: LargeInt actual: WideString

问题描述

使用Delphi 10.2，SQLite和Teecharts。我的SQLite数据库有两个字段，使用以下字段创建：

Using Delphi 10.2, SQLite and Teecharts. My SQLite database has two fields, created with:

CREATE TABLE HistoryRuntime ('DayTime' DateTime, Device1 INTEGER DEFAULT (0));

我使用 TFDQuery 访问表 qryGrpahRuntime 使用以下SQL：

I access the table using a TFDQuery called qryGrpahRuntime with the following SQL:

SELECT DayTime AS TheDate, Sum(Device1) As DeviceTotal
FROM HistoryRuntime 
WHERE  (DayTime >= "2017-06-01") and (DayTime <= "2017-06-26") 
Group by Date(DayTime)

在Delphi IDE中使用字段编辑器，我可以添加两个持久字段，将 TheDate 用作 TDateTimeField 和 DeviceTotal 作为 TLargeIntField 。

Using the Field Editor in the Delphi IDE, I can add two persistent fields, getting TheDate as a TDateTimeField and DeviceTotal as a TLargeIntField.

我在程序中运行此查询以创建TeeChart，该TeeChart是在设计时创建的。只要查询返回一些记录，所有这些工作。但是，如果没有所请求日期的记录，则会出现 EDatabaseError 异常，并显示以下消息：

I run this query in a program to create a TeeChart, which I created at design time. As long as the query returns some records, all this works. However, if there are no records for the requested dates, I get an EDatabaseError exception with the message:

qryGrpahRuntime：字段 DeviceTotal的类型不匹配，期望：LargeInt实际：Widestring

qryGrpahRuntime: Type mismatch for field 'DeviceTotal', expecting: LargeInt actual: Widestring

我已经做了很多在网络上搜索解决方案，以防止在空查询中出现此错误，但还没有碰到我发现的任何东西。据我所知，当没有数据返回时，SQLite默认为宽字符串字段。我曾尝试在查询中使用CAST，但似乎没有任何区别。

I have done plenty of searching for solutions on the web on how to prevent this error on an empty query, but have had not luck with anything I found. From what I can tell, SQLite defaults to the wide string field when no data is returned. I have tried using CAST in the query and it did not seem to make any difference.

如果我删除了持久字段，则查询将在空返回集上打开而不会出现问题。但是，为了在IDE中使用TeeChart编辑器，看来我需要持久性字段。

If I remove the persistent fields, the query will open without problems on an empty return set. However, in order to use the TeeChart editor in the IDE, it appears I need persistent fields.

有没有办法让我对持久性字段起作用？我将不得不扔掉持久字段，然后在运行时添加TeeChart系列吗？

Is there a way I can make this work with persistent fields, or am I going to have to throw out the persistent fields and then add the TeeChart Series at runtime?

推荐答案

此行为在< FireDAC的SQLite手册的a href = http://docwiki.embarcadero.com/RADStudio/zh/Using_SQLite_with_FireDAC#Adjusting_FireDAC_Mapping rel = noreferrer> 调整FireDAC映射 一章： / p>

This behavior is described in Adjusting FireDAC Mapping chapter of the FireDAC's SQLite manual:

对于SELECT列表中的表达式，SQLite避免使用类型名
信息。当结果集不为空时，FireDAC使用第一条记录中的值
数据类型。当为空时，FireDAC将那些
列描述为dtWideString。要显式指定列数据类型，
将 :::<类型名称> 附加到列别名：

从mytab中选择count（*）为 cnt :: INT

因此修改您的命令，例如这样（我使用 BIGINT ，但是您可以使用任何 伪数据类型 ，该数据类型映射到64位带符号整数数据类型，并且不会自动递增，这与您的持久 TLargeIntField 字段）：

So modify your command e.g. this way (I used BIGINT, but you can use any pseudo data type that maps to a 64-bit signed integer data type and is not auto incrementing, which corresponds to your persistent TLargeIntField field):

SELECT
   DayTime AS "TheDate",
   Sum(Device1) AS "DeviceTotal::BIGINT"
FROM
   HistoryRuntime 
WHERE
   DayTime BETWEEN {d 2017-06-01} AND {d 2017-06-26}
GROUP BY
   Date(DayTime)

PS我通过使用 BETWEEN 运算符（仅评估列值）进行了小的优化一次），并使用 转义序列 用于日期常量（我想实际上是用参数替换；所以只是出于好奇）。

P.S. I did a small optimization by using BETWEEN operator (which evaluates the column value only once), and used an escape sequence for date constants (which, in real you replace by parameter, I guess; so just for curiosity).

数据类型提示由 FDSQLiteTypeName2ADDataType 过程，该过程在其 AColName 参数中采用格式为<列名> ::<类型名> 的列名进行解析。

This data type hinting is parsed by the FDSQLiteTypeName2ADDataType procedure that takes and parses column name in format <column name>::<type name> in its AColName parameter.

这篇关于TFDQuery和SQLite：字段的类型不匹配，期望：LargeInt实际：WideString的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！