SQLite and calculated columns

[MathiasBurbach 0]

We face a problem in a Delphi project, in which we replace MS Access as the database layer to SQLite using FireDAC. This is to facilitate replication from SQLite to PostgreSQL via PowerSync.

 

Here is the situation we are facing:

Imaging the following...

CREATE TABLE t1(a int, b int);
INSERT INTO t1 values(1,1);
INSERT INTO t1 values(1,0);

Running the query

SELECT IIF(b > 0, a / b, NULL) AS c
FROM t1
ORDER BY b DESC

we get the column C as a Float. But if we change the order by clause to

SELECT IIF(b > 0, a / b, NULL) AS c
FROM t1
ORDER BY b ASC

the column C is returned as a TWideString as the first row has a NULL value for C. Even as type cast

SELECT IIF(b > 0, a / b, Cast(NULL As Float)) AS c
FROM t1
ORDER BY b ASC

isn't working.

 

Is there any way how we can force SQLite to return column C of a certain type independent of the data that has been selected?

 

Thanks for a short answer in advance.

 

Salut,
  Mathias

[Uwe Raabe 2085]

Have you tried adding a MapRule for field c? See Data Type Mapping (FireDAC)

[MathiasBurbach 0]

Thanks for your reply, Uwe.

 

The problem here is that FireDAC converts result columns to TWideString if they are NULL. So it really depends on your data what data type you end up with. We can not convert all TWideString data types to TFloatFields as there are strings stored in the database. So a general mapping rule would not help.

 

We may need to calculate these values as calculated fields in TFDQuery. But in some instances the number of columns is dynamic and we do not have static fields for the query.

 

In other cases where we have static fields we get an error message:

Quote

Type mismatch for field 'Percent', expecting: Float actual: WideString

We were hoping we could give SQLite some instruction to make the first row that returns a NULL value for a float calculation as a TFloatField instead of a TWideString.

 

We could create a TFDConnection on the fly and create field mappings just for that query. But we were hoping for a better solution.

Edited 4 hours ago by MathiasBurbach