mORMot Open Source

halted

Member

Registered: 2019-12-18

Posts: 4

NewTableFromRows columns types

Hi,

I am trying to export data from SQL Server to SQLite

I am using TOleDBMSSQL2012ConnectionProperties to  connect to the source.
I then use this code

  Query.Execute(psQryText,true,[]);

  SetLength(ColTypes, Query.ColumnCount);
  for idx := 0 to Query.ColumnCount - 1 do
    ColTypes[idx] := Query.ColumnType(idx);

  lpoConnExt.NewTableFromRows(psFileName, Query ,True,ColTypes);
  Query.Free;

The Query statement reads from a view.
The DateTime types are saved as text.

Is there a way to ensure the correct data types in the destination table ?

Best regards,

JM

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,548

Website

Re: NewTableFromRows columns types

There is no native date/time type in SQLite3, as there is with MSSQL.
Iso8601 text is a valid way to store SQLite3 date/time.
https://www.sqlite.org/datatype3.html#d … e_datatype

halted

Member

Registered: 2019-12-18

Posts: 4

Re: NewTableFromRows columns types

Thanks Arnaud.
The problem occurs when I reload the data with firedac TBatchMove in the destination database : it complains about the format difference (text vs DateTime)

I guess my "mix" is not right

ab

Administrator

From: France

Registered: 2010-06-21

Posts: 14,548

Website

Re: NewTableFromRows columns types

If you can force the column type into DB.pas date/time in your TBatchMove it should work.
But I don't know well this part of FireDAC, so I can't help precisely on it.