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 2089]

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 Sunday at 11:39 PM by MathiasBurbach

[Uwe Raabe 2089]

7 hours ago, MathiasBurbach said:

So a general mapping rule would not help.

Mapping rules can be added to each query and have a NameMask. This allows to specify a rule for only one field of one query.

Edited Monday at 06:48 AM by Uwe Raabe

[Uwe Raabe 2089]

Adding this mapping to the query makes field c a Double:

[Dmitry Arefiev 109]

https://docwiki.embarcadero.com/RADStudio/Athens/en/Using_SQLite_with_FireDAC#Adjusting_FireDAC_Mapping

Quote

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

 

[MathiasBurbach 0]

Thanks, Uwe & Dmitry, for your replies.

I didn't know that I could trick SQLite to return a column of a particular type like Dmitry suggested. I tested it already with one of our queries and the column comes back as an Integer instead of a Wide String. That makes it easy to stick to the current approach of calculating columns by SQLite expressions.

 

We can mark this thread as "answered". 🙂

[Cristian Peța 108]

16 hours ago, MathiasBurbach said:

I didn't know that I could trick SQLite to return a column of a particular type like Dmitry suggested.

You don't trick SQLite. FireDAC is using this information. You can look in FireDAC.Phys.SQLite.pas unit at FDSQLiteTypeName2ADDataType() to see how this is done.

[Brian Evans 111]

The type-less nature of SQLite makes it not ideal for data in transit between databases. You end up doing work for edge cases that a typed database would handle on it's own.

 

Want real fun? Have both English and French users, like in Canada with it's two official languages, where the decimal separator is period and comma respectively. Soon as strings start appearing in a column storing transaction values the fun begins. It can work as long as you listen to Egon Spengler and never cross the streams. Soon as you do however ...... think an online store that occasionally gives out 99.9% discounts level bad (few would complete the reverse - an order with items having a 1000x price increase except maybe the government).

Edited 18 hours ago by Brian Evans

[Cristian Peța 108]

18 minutes ago, Brian Evans said:

Soon as strings start appearing in a column storing transaction values the fun begins.

Are you storing float in string fields?

I create fields as REAL in SQLite and I can change the decimal separator without issues. I see period or comma and all is working.

I can update a REAL column using period in sqlite3 tool, because the tool uses only period, and I can see values with comma in my app.

[Brian Evans 111]

18 minutes ago, Cristian Peța said:

Are you storing float in string fields?

I create fields as REAL in SQLite and I can change the decimal separator without issues. I see period or comma and all is working.

I can update a REAL column using period in sqlite3 tool, because the tool uses only period, and I can see values with comma in my app.

Outside of the recently (relatively anyway) introduced STRICT tables the type of a column is really just a suggestion and it will happily stick a string anywhere if asked. Most tools try to tame this by enforcing the type but don't always succeed.

 

SQLite website: 

1. Datatypes In SQLite

Most SQL database engines (every SQL database engine other than SQLite, as far as we know) uses static, rigid typing. With static typing, the datatype of a value is determined by its container - the particular column in which the value is stored.

SQLite uses a more general dynamic type system. In SQLite, the datatype of a value is associated with the value itself, not with its container. The dynamic type system of SQLite is backwards compatible with the more common static type systems of other database engines in the sense that SQL statements that work on statically typed databases work the same way in SQLite. However, the dynamic typing in SQLite allows it to do things which are not possible in traditional rigidly typed databases. Flexible typing is a feature of SQLite, not a bug.

Update: As of version 3.37.0 (2021-11-27), SQLite provides STRICT tables that do rigid type enforcement, for developers who prefer that kind of thing.

 

 

[MathiasBurbach 0]

Hello Cristian,

 

Thanks for your reply.

 

Yes we now understand this is a FireDAC feature. We are using it already.

 

Unfortunately the old MS Access application used to create tables from Select statements for reporting. We will change that. But in order to get there we did not want to touch it immediately.

 

So when you ExecuteSQL
Create Temporary Table Report As
Select 1 "A::INT", NULL "B::INT"

 

the column data types are unknown. So we need to create the table up-front:
Create Temporary Table Report(A INT, B INT)

and then do a Insert Into Report As Select 1 "A::INT", NULL "B::INT"

 

Brian, we will look at converting our tables to STRICT tables to get a better type check. But the STRICT option doesn't work for tables created from a select statement. As you can clearly see I am fairly new to SQLite.

 

Salut, Mathias