Page 1 of 1
EUnsupportedDataTypeMapping with message 'Unsupported data type mapping: "Unknown" to "Extended"
Posted: Wed 04 Feb 2015 16:39
by johnbitner
Getting the exception "EUnsupportedDataTypeMapping" with the message 'Unsupported data type mapping: "Unknown" to "Extended"'.
The fields in my query that are important to this issue are:
TAX_IND tax deductible indicator for the line item.
AMT REAL field with a dollar amount for the line item.
TaxAmt result of query.
NonTaxAmt result of query.
The query is basically:
SELECT
c.TAX_IND as TaxInd,
c.AMT as Amount,
case c.TAX_IND when '1' then c.AMT else 0 end as TaxAmt,
case c.TAX_IND when '0' then c.AMT else 0 end AS NonTaxAmt
FROM csCONDTL c
A small sample of data from the query:
Code: Select all
TaxInd | Amount | TaxAmt | NonTaxAmt
1 | 5.37 | 5.37 | 0
0 | 12.30 | 0 | 12.30
The goal with this simple query is to retrieve the 2 columns TaxAmt and NonTaxAmt to use in a report and to SUM the values to Tax and Non Tax deductible totals.
The problem is that in SQLite the query fields TaxAmt and NonTaxAmt are returned as an "Unknown" datatype. I thought the fix would be to use the UniDac DataTypeMapping like:
query.DataTypeMap.AddFieldNameRule('TaxAmt', ftExtended, True);
query.DataTypeMap.AddFieldNameRule('NonTaxAmt', ftExtended, True);
When I do this I get the error above. I've also tried to change the query text to "cast" the TaxAmt and NonTaxAmt values as real "cast(c.amt as real) as TaxAmt". This did not change the result.
Any suggestions?
Thanks in advance!
Re: EUnsupportedDataTypeMapping with message 'Unsupported data type mapping: "Unknown" to "Extended"
Posted: Thu 05 Feb 2015 09:00
by AlexP
Hello,
We cannot reproduce the problem on the latest UniDAC version. Try to reproduce the problem on the latest UniDAC trial version:
http://www.devart.com/unidac/download.html , and let us know the results
Re: EUnsupportedDataTypeMapping with message 'Unsupported data type mapping: "Unknown" to "Extended"
Posted: Thu 05 Feb 2015 13:19
by johnbitner
Hello,
I'm using 6.0.2 Professional with Delphi XE7 Update 1. Is there a newer build? Also, I'm hitting the SQLite database in direct mode rather than using the external dll.
Re: EUnsupportedDataTypeMapping with message 'Unsupported data type mapping: "Unknown" to "Extended"
Posted: Fri 06 Feb 2015 10:01
by AlexP
We can't reproduce the problem on the latest UniDAC version. Please modify the following code, so that the problem is reproduced and send it back to us.
Code: Select all
program Project1;
{$APPTYPE CONSOLE}
{$R *.res}
uses
System.SysUtils, Data.DB, Uni, SQLiteUniProvider;
var
UniConnection: TUniConnection;
UniQuery: TUniQuery;
begin
UniConnection := TUniConnection.Create(nil);
try
UniConnection.ConnectString := 'ProviderName=SQLite;Database=:memory:;Direct=true;LoginPrompt=False';
UniConnection.Connect;
UniConnection.ExecSQL('CREATE TABLE csCONDTL(TAX_IND INT, AMT REAL)');
UniConnection.ExecSQL('INSERT INTO csCONDTL VALUES(1, 5.37), (0, 12.30)');
UniQuery := TUniQuery.Create(nil);
try
UniQuery.Connection := UniConnection;
UniQuery.SQL.Text := 'SELECT' + #13#10 +
'c.TAX_IND as TaxInd,' + #13#10 +
'c.AMT as Amount,' + #13#10 +
'case c.TAX_IND when ''1'' then c.AMT else 0 end as TaxAmt,' + #13#10 +
'case c.TAX_IND when ''0'' then c.AMT else 0 end AS NonTaxAmt' + #13#10 +
'FROM csCONDTL c';
UniQuery.DataTypeMap.AddFieldNameRule('TaxAmt', ftExtended, True);
UniQuery.DataTypeMap.AddFieldNameRule('NonTaxAmt', ftExtended, True);
try
UniQuery.Open;
Writeln('OK');
except
on E: Exception do
Writeln(E.Message);
end;
finally
UniQuery.Free;
end;
finally
UniConnection.Free;
readln;
end;
end.
Re: EUnsupportedDataTypeMapping with message 'Unsupported data type mapping: "Unknown" to "Extended"
Posted: Fri 06 Feb 2015 18:54
by johnbitner
Looks like the problem is when the query doesn't return any results due to the conditions of the where clause. Notice that I've added 2 additional fields "batch_dt" and "status". Also notice that in the select statement I've added a where clause. If you set the 2 date parameters so that the result set is empty the error is raised. If at least 1 record is returned no error. Is it possible to assign the AddFieldNameRule rule after the query is executed so that if the result set is empty we avoid the error can be avoided?
Thank you for your help.
Code: Select all
program Project1;
{$APPTYPE CONSOLE}
{$R *.res}
uses
System.SysUtils,
Data.DB,
Uni,
SQLiteUniProvider;
var
UniConnection: TUniConnection;
UniQuery: TUniQuery;
begin
UniConnection := TUniConnection.Create(nil);
try
UniConnection.ConnectString := 'ProviderName=SQLite;Database=:memory:;Direct=true;LoginPrompt=False';
UniConnection.Connect;
UniConnection.ExecSQL('CREATE TABLE csCONDTL(TAX_IND INT, AMT REAL, BATCH_DT TEXT, STATUS TEXT)');
UniConnection.ExecSQL('INSERT INTO csCONDTL VALUES(1, 5.37, "2015-01-01", "PO"), (0, 12.30, "2015-01-05", "PO")');
UniQuery := TUniQuery.Create(nil);
try
UniQuery.Connection := UniConnection;
UniQuery.SQL.Text := 'SELECT '+
'c.TAX_IND as TaxInd, '+
'c.AMT as Amount, '+
'case c.TAX_IND when ''1'' then c.AMT else 0 end as TaxAmt, '+
'case c.TAX_IND when ''0'' then c.AMT else 0 end AS NonTaxAmt '+
'FROM csCONDTL c '+
'WHERE c.STATUS = ''PO'' and c.BATCH_DT >= :psdate and c.BATCH_DT <= :pedate '+
'ORDER BY c.BATCH_DT';
UniQuery.ParamByName('psdate').AsDateTime:= StrToDate('01/01/2015');
UniQuery.ParamByName('pedate').AsDateTime:= StrToDate('12/31/2015');
UniQuery.DataTypeMap.AddFieldNameRule('TaxAmt', ftExtended, True);
UniQuery.DataTypeMap.AddFieldNameRule('NonTaxAmt', ftExtended, True);
try
UniQuery.Open;
Writeln(UniQuery.RecordCount);
Writeln('OK');
except
on E: Exception do
Writeln(E.Message);
end;
finally
UniQuery.Free;
end;
finally
UniConnection.Free;
readln;
end;
end.
Re: EUnsupportedDataTypeMapping with message 'Unsupported data type mapping: "Unknown" to "Extended"
Posted: Mon 09 Feb 2015 09:30
by AlexP
Thank you for the sample. We have reproduced and fixed the problem. The fix will be included to the next UniDAC version.