mORMot Open Source

damiand

Member

From: Greece

Registered: 2018-09-21

Posts: 94

TSynSQLStatementDataSet misses TDatetime ColumnType in SQLite3

I have the following model implemented in a SQLite3 database:

  TSQLSubmission = class(TSQLRecord)
  private
    fProtocolNumber: RawUTF8;
    fSubject: RawUTF8;
    fDecisionTypeId: RawUTF8;
    fSubmTimestamp: TDateTime;
    fStatus: RawUTF8;
    fAda: RawUTF8;
    fDocURL: RawUTF8;
  published
    property ProtocolNumber: RawUTF8 read fProtocolNumber write fProtocolNumber;
    property Subject: RawUTF8 read fSubject write fSubject;
    property DecisionTypeId: RawUTF8 read fDecisionTypeId write fDecisionTypeId;
    property SubmTimestamp: TDateTime read fSubmTimestamp write fSubmTimestamp;
    property Status: RawUTF8 read fStatus write fStatus;
    property Ada: RawUTF8 read fAda write fAda;
    property DocURL: RawUTF8 read fDocURL write fDocURL;
  end;

And I feed a VCL DBGrid with the contents of the Submission table using a TDatasource linked to a TSynSQLStatementDataSet:

var
  statement: TSQLDBStatement;
  sCommand: RawUTF8;
begin
  if dsHistory.DataSet <> nil then
     dsHistory.DataSet.Free;
  sCommand := StringToUTF8('SELECT * FROM Submission');
  statement := fProps.NewThreadSafeStatement;
  statement.Execute(sCommand, true);
  dsHistory.DataSet := ToDataSet(dsHistory, statement)
end;

The SubmTimestamp DataSet Field is shown in the grid as an iso8601 UTC string and the dsHistory.DataSet.FieldByName('SubmTimestamp').DataType is ftWideString.

Any attempt to format the SubmTimestamp using one of the SQLite3 datetime functions e.g. :

 sCommand := StringToUTF8('SELECT ID, ProtocolNumber, Subject, DecisionTypeId, Datetime(SubmTimestamp,''localtime'') AS SubmTimestamp, Status, Ada, DocURL FROM Submission');
statement := fProps.NewThreadSafeStatement;
statement.Execute(sCommand, true);
dsHistory.DataSet := ToDataSet(dsHistory, statement)

raises the following exception when the last line of the above sample (DataSet assignment) is executed:

exception class EDatabaseError with message 'GetFieldData ColumnType=Unknown'

How can I format the SubmTimestamp in the Grid appropriately, appart from manipulating the column text using the OnDrawColumnCell event which I thing, is an overkill?

Last edited by damiand (2019-03-22 13:34:48)

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,655

Website

Re: TSynSQLStatementDataSet misses TDatetime ColumnType in SQLite3

Please do not write such big code in the threads.
See https://synopse.info/forum/misc.php?action=rules

How is your fProps defined?
It is a DB provider problem, i.e. in SynDB.pas and the associated SynDB*.pas provider unit, not in the ORM.

IMHO this is not a good idea to directly connect to the DB from the client.
Publish your TSQLSubmission over a REST service for the client.

damiand

Member

From: Greece

Registered: 2018-09-21

Posts: 94

Re: TSynSQLStatementDataSet misses TDatetime ColumnType in SQLite3

I'm sorry for the extended code parts.

fProps is created from TSQLDBSQLite3ConnectionProperties.Create(TSQLDatabase). I understand your reservations about the direct connection but my application is very simple, with local (in-memory) connection to the database file.

For the time being, I reformat the column's text using the DBGrid OnDrawColumnCell event.

Last edited by damiand (2019-03-22 17:53:45)