CODEWITHSUNDEEP
sqlitedelphifiredac
Jeff Bannon
Jeff Bannon

Reputation: 243

Reading 'bad' Sqlite data with FireDAC

I just learned that the Sqlite Manager plug-in for Firefox will go away in November, so I've been trying to recreate its functionality in Delphi: open Sqlite databases, enter SQL queries. I'm running Tokyo.

My problem comes with Sqlite fields defined as 'date.' While Sqlite allows specification of data types, it allows pretty much anything to be put in any field. FireDAC handles bad entries in integer or float fields ('bar' becomes 0, '32foo' becomes 32), but it hiccups on fields described as 'date.'

For example, I have a table:

CREATE TABLE "someTable" ("id" INTEGER, "s" text(10), "d" date)

With this data:

INSERT INTO "someTable" VALUES ("1","good date","2017-09-09");
INSERT INTO "someTable" VALUES ("2","bad date","2017-09-0b");
INSERT INTO "someTable" VALUES ("3","empty d","");
INSERT INTO "someTable" VALUES ("4","null date",null);

Opening FDQuery q1 with SQL = "select * from someTable", a bad date (such as the second record) raises an EConvertError ("Invalid argument to date encode"). I tried to get around it by adding a maprule (q1 is a FDQuery):

with q1.FormatOptions do begin
  OwnMapRules := True;
  with MapRules.Add do begin
    SourceDataType := dtDate;
    TargetDataType := dtAnsiString;
    sizemin := 10;
    sizemax := 256;
    PrecMin := -1;
    PrecMax := -1;
    ScaleMin := -1;
    ScaleMax := -1;
  end;
end;

However, that raises an EFDException:

[FireDAC][DatS]-32. Variable length column overflow. Value length - [10], column maximum length - [0].

What am I missing?

Upvotes: 1

Views: 1233

Answers (1)

Victoria
Victoria

Reputation: 7912

Why do I get "Invalid argument to date encode" exception when fetching tuple containing invalid DATE type field value?

The problem is that FireDAC expects DATE data type values represented as a string in fixed format YYYY-MM-DD where all members must be integers. Internally used FDStr2Int function doesn't use any detection of invalid input and works directly with ASCII ordinary values shifted by the 0 char, so input like e.g. GHIJ-KL-MN results in year 25676, month 298, day 320 after parsing. And just these misinterpreted values are then passed to the EncodeDate function, which fails for such values with the exception you've mentioned:

Invalid argument to date encode

The above happens inside the GetData method (ParseDate nested function). One possible way for fixing this issue on FireDAC's side could be using safer function TryEncodeDate instead of a direct encoding attempt. Similar problem is with TIME data type string value.

Upvotes: 1

Related Questions