2

I have a SQL statement in a TFDQuery tied to an SQLite3 database. If I perform the following query it works fine if there rows that meet the WHERE clause. In this case MyMield is a float and when I pre-build the table fields using the field editor it shows up correctly as a TFloatField. However, if there are no matching rows (empty result), the MyMaxField is return as a TWideStringField. This does not match the type of field object I created at design them and returns and error (even at design time).

SELECT max(MyField) AS MyMaxfield FROM MyTable WHERE MyOtherFfield=10

I also tried using casting the max() to a REAL and it still fails.

Delphi 10.2

Bill F
  • 201
  • 2
  • 11
  • I think you need to handle this from your Delphi code. If the result is an empty set, then there isn't much you can do from the SQLite side. – Tim Biegeleisen Oct 01 '17 at 07:16

1 Answers1

6

This 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 to resolve your problem simply specify data type for your expression column. For example:

SELECT
   MAX(MyField) AS "MyMaxfield::REAL"
FROM
   MyTable
WHERE
   MyOtherFfield = 10
Victoria
  • 7,822
  • 2
  • 21
  • 44
  • Isn't this q & a basically a duplicate of https://stackoverflow.com/questions/44769558/tfdquery-and-sqlite-type-mismatch-for-field-expecting-largeint-actual-widest? – MartynA Oct 01 '17 at 15:25
  • @MartynA, solution is same. Actually, I've casted a duplicate close vote here and waited for a while. As no one else voted, I've retracted it and posted an answer. [I wanted to make the older question more general](https://stackoverflow.com/review/suggested-edits/16741951), but my edit proposal was declined. So, I'm a bit confused what to do now. Shall I answer, cast close vote, or make the older question more general and cast close vote? – Victoria Oct 01 '17 at 15:47
  • Well, I've just marked this one as a duplicate, apparently successfully. But I'm baffled why your edit to improve your answer on the other q should have been rejected, because obviously it should make a good answer better. Why not try it again and let me know and I'll vote for it? – MartynA Oct 01 '17 at 15:58
  • @MartynA, thank you! :) I can edit without review now, so I can theoretically do it by myself. Do you think that making the other question more general and modifying my answer accordingly is the right way to deal with this situation? – Victoria Oct 01 '17 at 16:07
  • Personally, I always hesitate at changing the *sense* of a q, or even the way it is expressed, because after all it's the OP's q, not mine. but I can't see any harm at all in editing your answer to say something like "Btw, your is an example of a more general problem with Sqlite column types which FireDAC has a way of solving, e.g. ..." You get the idea, I'm sure. – MartynA Oct 01 '17 at 16:12
  • This is the perfect solution. I was surprised that CAST() did not work. Where did you find this documented? – Bill F Oct 01 '17 at 21:12
  • `CAST` didn't work because it's an expression as well. Documented it's in the linked documentation chapter (it's quite hidden there). – Victoria Oct 01 '17 at 21:16