Type Mismatch - expecting String actual: Memo

Discussion of open issues, suggestions and bugs regarding LiteDAC (SQLite Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
FarshadV
Posts: 22
Joined: Sat 31 Jan 2009 21:55

Type Mismatch - expecting String actual: Memo

Post by FarshadV » Mon 02 Dec 2013 06:19

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.

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Type Mismatch - expecting String actual: Memo

Post by AlexP » Mon 02 Dec 2013 12:18

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

FarshadV
Posts: 22
Joined: Sat 31 Jan 2009 21:55

Re: Type Mismatch - expecting String actual: Memo

Post by FarshadV » Tue 03 Dec 2013 01:42

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.

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Type Mismatch - expecting String actual: Memo

Post by AlexP » Tue 03 Dec 2013 07:58

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;
..

FarshadV
Posts: 22
Joined: Sat 31 Jan 2009 21:55

Re: Type Mismatch - expecting String actual: Memo

Post by FarshadV » Tue 03 Dec 2013 11:50

Thank you very much.

Regards

Farshad Vossoughi

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Type Mismatch - expecting String actual: Memo

Post by AlexP » Tue 03 Dec 2013 12:10

Hello,

If you have any other questions, feel free to contact us.

FarshadV
Posts: 22
Joined: Sat 31 Jan 2009 21:55

Re: Type Mismatch - expecting String actual: Memo

Post by FarshadV » Thu 09 Jan 2014 06:11

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

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Type Mismatch - expecting String actual: Memo

Post by AlexP » Thu 09 Jan 2014 12:55

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

FarshadV
Posts: 22
Joined: Sat 31 Jan 2009 21:55

Re: Type Mismatch - expecting String actual: Memo

Post by FarshadV » Fri 10 Jan 2014 01:44

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

AlexP
Devart Team
Posts: 5530
Joined: Tue 10 Aug 2010 11:35

Re: Type Mismatch - expecting String actual: Memo

Post by AlexP » Fri 10 Jan 2014 11:27

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);

myicq
Posts: 8
Joined: Sat 01 Jun 2019 16:11

Re: Type Mismatch - expecting String actual: Memo

Post by myicq » Thu 15 Oct 2020 12:14

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 !

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

Re: Type Mismatch - expecting String actual: Memo

Post by MaximG » Fri 16 Oct 2020 08:44

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.

Post Reply