Page 1 of 1
Type Mismatch - expecting String actual: Memo
Posted: Mon 02 Dec 2013 06:19
by FarshadV
I have just upgraded to the latest edition of this component and am using Delphi XE5, and in the process of re-compiling an existing program, everywhere that I had an alias column in my SQL statement is now having a Type mismatch issue. For example, the following query worked fine previously:
SELECT
f.ReadOnly,
(CASE WHEN f.ReadOnly = 1 Then 'Yes' Else 'No' END) AS 'ReadOnlyText'
FROM
TaxFormInputField f
And now it does not.
Your help would be very much appreciated.
Re: Type Mismatch - expecting String actual: Memo
Posted: Mon 02 Dec 2013 12:18
by AlexP
Hello,
When using the CASE construction,aggregate functions, etc., SQLite returns field type as Unknown, since it cannot define the result field type, therefore we map such fields as TMemoField. To solve the problem, you can use the Data Type Mapping technology in order to explicitly specify the type, to which such fields should be mapped for DataSet
Re: Type Mismatch - expecting String actual: Memo
Posted: Tue 03 Dec 2013 01:42
by FarshadV
Thank you for the reply but if I change my query to the following:
SELECT
f.ReadOnly,
CAST((CASE WHEN f.ReadOnly = 1 Then 'Yes' Else 'No' END) AS VARCHAR(10)) AS 'ReadOnlyText'
FROM
TaxFormInputField f
I still get the column (ReadOnlyText) as memo even though I have explicitly identified the return type. Should it not return as a string?
BTW: If I use the
IFNULL statement again it returns a memo value instead of matching the type of the column which IFNULL was performed on.
Re: Type Mismatch - expecting String actual: Memo
Posted: Tue 03 Dec 2013 07:58
by AlexP
Hello,
To display such fields content, you can use Data Type Mapping by tunning up mapping in the LiteQuery dialog (the Data Type Mapping tab), or in code
Code: Select all
uses ..., LiteDataTypeMap;
..
LiteQuery1.SQL.Text := 'SELECT ' +
'f.ReadOnly, ' +
'(CASE WHEN f.ReadOnly = 1 Then 'Yes' Else 'No' END) ' +
'AS RedOnlyText FROM TaxFormInputField f';
LiteQuery1.DataTypeMap.AddFieldNameRule('ReadOnlyText', ftString);
LiteQuery1.Open;
..
Re: Type Mismatch - expecting String actual: Memo
Posted: Tue 03 Dec 2013 11:50
by FarshadV
Thank you very much.
Regards
Farshad Vossoughi
Re: Type Mismatch - expecting String actual: Memo
Posted: Tue 03 Dec 2013 12:10
by AlexP
Hello,
If you have any other questions, feel free to contact us.
Re: Type Mismatch - expecting String actual: Memo
Posted: Thu 09 Jan 2014 06:11
by FarshadV
Just updated to version 2.2.5 for Delphi XE5 and now the same code that was working is having the following exception: EDataMappingError 'String Value too long'
Any ideas as to what could be causing this. BTW: I can still run my executable which was generated using the older version of the component, operating on the exact same database and it works just fine.
Thanks,
Farshad Vossoughi
Re: Type Mismatch - expecting String actual: Memo
Posted: Thu 09 Jan 2014 12:55
by AlexP
Hello,
We cannot reproduce the problem. The code I have provided works with no errors on the latest version. Please send your DB file to alexp*devart*com
Re: Type Mismatch - expecting String actual: Memo
Posted: Fri 10 Jan 2014 01:44
by FarshadV
Hi,
I think I have found the problem. If I turn "Ignore Errors" on then it works, so it looks like the string field is just not wide enough. Can you tell me when I do the command:
Code: Select all
LiteQuery1.DataTypeMap.AddFieldNameRule('ReadOnlyText', ftString);
What is the default field length that is created? The reason for the question is that if I set the field length to 255 in the "Data Type Mapping" tab, also avoids the error.
Thanks,
Farshad
Re: Type Mismatch - expecting String actual: Memo
Posted: Fri 10 Jan 2014 11:27
by AlexP
Hello,
If you haven't set the field length explicitly, it will be 20, and if the text is longer, you will get a corresponding error. If you have enabled the IgnoreErrors option and the string length is greater than 20 characters, the string will be cut to a corresponding field length. You can set the field length at both design-time and run-time.
Code: Select all
LiteQuery1.DataTypeMap.AddFieldNameRule('ReadOnlyText', ftString, 255);
Re: Type Mismatch - expecting String actual: Memo
Posted: Thu 15 Oct 2020 12:14
by myicq
If you haven't set the field length explicitly, it will be 20
This choice of limiting the default string to (exactly) 20 by default took me about 2 days to discover. Why on earth this choice was made is beyond me. I wish I could change the default in some global setting, so that I don't have to set this every time.
Good that you have the forum to find out about such matters !
Re: Type Mismatch - expecting String actual: Memo
Posted: Fri 16 Oct 2020 08:44
by MaximG
We're glad you've found a solution. ftString fields are created this way when you don't specify their length because of the specifics of TFieldDef; our components cannot affect that in any way.