Virtual Column with explicit value datatype

Discussion of open issues, suggestions and bugs regarding LiteDAC (SQLite Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
egrobler
Posts: 29
Joined: Mon 03 Dec 2012 14:35

Virtual Column with explicit value datatype

Post by egrobler » Thu 09 Nov 2017 02:44

Hi DevArt,

We have this inconsistent behaviour:

Code: Select all

SELECT 0 as dummy from mytable where 1=1      -- SQLITE_INTEGER  ftLargeInt (CORRECT)
SELECT 0 as dummy from mytable where 0=1      -- SQLITE_NULL     ftWideMemo (WRONG)
SELECT 'test' as dummy from mytable where 0=1 -- SQLITE_NULL     ftWideMemo (CORRECT)
Is their a way to fix 'select 0 as x' to map to INTEGER even if no rows are returned?

Regards
Eric

egrobler
Posts: 29
Joined: Mon 03 Dec 2012 14:35

Re: Virtual Column with explicit value datatype

Post by egrobler » Thu 09 Nov 2017 02:51

Similar issue:

Code: Select all

create table test(id int);
-- if empty table:
select id  from test      -- Integer
select max(id) from test  -- WideMemo

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: Virtual Column with explicit value datatype

Post by MaximG » Mon 13 Nov 2017 13:36

That's right, when SQLite cannot determine a field type returned by a query, LiteDAC creates the corresponding Field with the Memo type. In your sample, these are queries that return SQLITE_NULL. In order for such fields to be created with the type required by the developer, you can use DataTypeMapping, a special mechanism provided by our components: https://www.devart.com/litedac/docs/?da ... apping.htm

For example, the returned value of the Memo type of the field 'dummy' in your case can be converted to an Integer type:

Code: Select all

uses LiteDataTypeMap;
....
LiteQuery.DataTypeMap.AddFieldNameRule ('dummy', ftInteger);

egrobler
Posts: 29
Joined: Mon 03 Dec 2012 14:35

Re: Virtual Column with explicit value datatype

Post by egrobler » Wed 15 Nov 2017 01:41

Hi MaximG,

Even casting explicitly won't help?
Will sqlite3_column_type also return SQLITE_NULL for this query?

Code: Select all

select cast(max(id) as int) from test

MaximG
Devart Team
Posts: 1822
Joined: Mon 06 Jul 2015 11:34

Re: Virtual Column with explicit value datatype

Post by MaximG » Wed 15 Nov 2017 14:13

You can use SQLite function CAST to get the required type result. The provided query will return a field of the SQLITE_INTEGER type

Post Reply