[SOLVED] FireDAC SQLITE with LEFT JOIN reassign NULL entries to an empty string results with Type Mismatch?

FireDAC SQLITE with LEFT JOIN reassign NULL entries to an empty string results with Type Mismatch?

I'm trying to fix the sort order in a TDBGrid by changing NULL entries (for a VARCHAR(255)) to an empty string but I get:

EDatabaseError with message 'Table1: Type mismatch for field 'MyName', expecting: String actual: WideString'.

The query in a TFDQuery looks like:

Select Table1.*, IFNULL(Table2.Name,'') AS MyName
From Table1
LEFT JOIN Table2
ON (Table1.LinkField=Table2.UniqueId)

What am I doing wrong?

More Info:

If I set the Params["StringFormat"]="ANSI" that message goes away but then my existing text in a TEXT field turns to Chinese.

I now understand what the FireDAC SQLite Mapping means. In this case:

VARCHAR -> dtAnsiString, Len = L
TEXT -> dtWideMemo

How do I tell IFNULL to use a character string instead of a wide string? Table2.Name is a VARCHAR.

TIA!!

Solution

SQLLite supports the CAST operator, which is what the standard SQL uses to declare the exact type that you need for an expression.

So in this case your query would be:

SELECT Table1.*, 
       IFNULL(Table2.Name, CAST('' as varchar(20))) AS MyName
FROM Table1
     LEFT JOIN Table2 ON Table1.LinkField = Table2.UniqueId

PS: you won't need to change the query if you just drop the persistent field for the MyName field, and re-create it (right-click over the TFDQuery component, and chose the Field Editor option), so now it would create it as a TWideStringField, matching the new result for that query.

This is where you can check the class type of a component (the persistent field) through the Object Inspector: