EUnsupportedDataTypeMapping with message 'Unsupported data type mapping: "Unknown" to "Extended"

[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!

[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

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

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

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

[AlexP]

Thank you for the sample. We have reproduced and fixed the problem. The fix will be included to the next UniDAC version.