Page 1 of 1
Virtual Column with explicit value datatype
Posted: Thu 09 Nov 2017 02:44
by egrobler
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
Re: Virtual Column with explicit value datatype
Posted: Thu 09 Nov 2017 02:51
by egrobler
Similar issue:
Code: Select all
create table test(id int);
-- if empty table:
select id from test -- Integer
select max(id) from test -- WideMemo
Re: Virtual Column with explicit value datatype
Posted: Mon 13 Nov 2017 13:36
by MaximG
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);
Re: Virtual Column with explicit value datatype
Posted: Wed 15 Nov 2017 01:41
by egrobler
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
Re: Virtual Column with explicit value datatype
Posted: Wed 15 Nov 2017 14:13
by MaximG
You can use SQLite function CAST to get the required type result. The provided query will return a field of the SQLITE_INTEGER type