2

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

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

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)

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

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: Type mismatch for field 'DeviceTotal', expecting: LargeInt actual: Widestring

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.

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.

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?

Rick Hollerich
  • 197
  • 1
  • 7
  • See https://stackoverflow.com/a/32818322; there is no column type because the result column does not come directly from a table column. Apparently, there is no way to override that. – CL. Jun 27 '17 at 09:21
  • @CL, so if there is no way to override the column type once the query is open, is there anything I can do to tell if the query will be empty before I run it? Opening the query is where I get the error. I simply need to know if the query will be empty, then I can just not open the TeeChart, there by stopping the error. – Rick Hollerich Jun 27 '17 at 19:25
  • FYI: I am using the Professional version without the FireDAC addon, so I do not have the source code to FireDac to research. – Rick Hollerich Jun 27 '17 at 19:27
  • You could execute this query (or a simpler one) separately. – CL. Jun 27 '17 at 19:44
  • @CL, that is the way I am working around the problem right now. I am just selecting the Daytime value for the requested dates, then checking if that is empty before running the full query. Not the best way to do it, but everything else I have tried failed and this should work for my TeeChart forms at least. – Rick Hollerich Jun 28 '17 at 15:19
  • The SQLite engine should throw [integer overflow](https://sqlite.org/lang_aggfunc.html#sumunc) exception when the integer value overflows during `SUM` computation, so as it seems, `TIntegerField` should be enough here. Very interesting question, though. Could you please add [tag:delphi] and [tag:firedac] tags and include your attempt to `CAST`, please? – Victoria Jul 10 '17 at 23:58
  • @Victoria, this isn't a problem with an overflow of the Sum calculation. It has to do with SQLite returning a Widestring field for the Sum calculation when the result set is empty, and a LargeInt field when the result set has data. So when using persistent fields, created in the IDE, you get a field mismatch with an empty set. Using CAST to return a LargeInt field regardless of whether the result set is empty or not doesn't work. It still returns a Widestring field if the set is empty. – Rick Hollerich Jul 13 '17 at 04:43

1 Answers1

7

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

For an expression in a SELECT list, SQLite avoids type name information. When the result set is not empty, FireDAC uses the value data types from the first record. When empty, FireDAC describes those columns as dtWideString. To explicitly specify the column data type, append ::<type name> to the column alias:

SELECT count(*) as "cnt::INT" FROM mytab

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)

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).

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.

Victoria
  • 7,822
  • 2
  • 21
  • 44