Jump to content
Sign in to follow this  
Stéphane Wierzbicki

TFDBatchMoveSQLWriter and table structure updates

Recommended Posts

Hello

I transfer Excel data to an MS SQL database. To do this I use the TFDMemTable, TFDBatchMove and TFDBatchMoveSQLWriter components. 

The TFDMemTable field definitions are automatically created according to the Excel file (Excel columns and data type are used to create the field definitions).
 
The name of the Excel file is used to fill in the Tablename property of the TFDBatchMoveSQLWriter component.

When the TFDBatchMove is executed, the "TableName" table is created if it does not exist in the MS SQL database. If the table does exist, the data is inserted into it. This is perfect.

I have a problem when the Excel file evolves and especially when a new column appears. TFDBatchMoveSQLWriter does not update the SQL table structure and the new field(s) are not created.

 

Anyone have an idea to help me solve this problem? Maybe should I use another FireDac for this purpose?


Here is my function

 

 

function StoreDataset(aFDConnection: TFDConnection; aDataset: TDataSet;
  aTableName: string = ''; aOptions: TFDBatchMoveOptions = [poIdentityInsert,
  poCreateDest, poSkipUnmatchedDestFields, poUseTransactions];
  aMode: TFDBatchMoveMode = dmAlwaysInsert): boolean;
var
  FFDTable: TFDTable;
  FBatchmove: TFDBatchMove;
  FBatchmoveDatasetReader: TFDBatchMoveDataSetReader;
  FBAtchMoveSQLWriter: TFDBatchMoveSQLWriter;

begin
  Result := False;

  if not assigned(aFDConnection) then
    raise Exception.Create('Please provide a Connection');

  if not assigned(aDataset) then
    raise Exception.Create('Please provide a Dataset');

  if aTableName = '' then
    aTableName := aDataset.Name;

  if aTableName = '' then
    raise Exception.Create('Please provide a table name');

  FBatchmove := TFDBatchMove.Create(nil);
  FBatchmoveDatasetReader := TFDBatchMoveDataSetReader.Create(nil);
  FBAtchMoveSQLWriter := TFDBatchMoveSQLWriter.Create(nil);
  FFDTable := TFDTable.Create(nil);
  try

    FBatchmove.Reader := FBatchmoveDatasetReader;
    FBatchmove.Writer := FBAtchMoveSQLWriter;
    FBatchmove.Options := aOptions;
    FBatchmove.Mode := aMode;
    FBAtchMoveSQLWriter.Connection := aFDConnection;
    aFDConnection.Connected := True;

    FBatchmoveDatasetReader.Dataset := aDataset;
    FBAtchMoveSQLWriter.TableName := aTableName;
    try
      FBatchmove.Execute;
      Result := True;
    except
      on e: Exception do
      begin
        Result := False;
        TMSLogger.Exception(e.message);
      end;
    end;

  finally
    aFDConnection.Connected := False;
    FBatchmove.Free;
    FBatchmoveDatasetReader.Free;
    FBAtchMoveSQLWriter.Free;
    FFDTable.Free;
  end;
end;

 

 

Share this post


Link to post
15 hours ago, Uwe Raabe said:

Can't you just delete the table before executing the batch move? 

No, I'm sorry, I'm receiving those files every month and data needs to be appended into the same table. 

Edited by Stéphane Wierzbicki

Share this post


Link to post

OK, there is an undocumented option poSkipUnmatchedDestFields which you can set to False (although I don't know what it does).

Share this post


Link to post
9 hours ago, Uwe Raabe said:

OK, there is an undocumented option poSkipUnmatchedDestFields which you can set to False (although I don't know what it does).

I forgot to tell you that I've already tried this. Removing from the the options set did not changed any thing. 

Share this post


Link to post
24 minutes ago, Dmitry Arefiev said:

TFDBatchMoveSQLWriter cannot alter table structure, it can only create a new table.

Thanks Dmitry, do you have some advice on how to get this done in another way ? 

Edited by Stéphane Wierzbicki

Share this post


Link to post

@Stéphane Wierzbicki It's not that easy as it sounds. What if a column name changes? Is it a new column or just the name changed? What if a datatype changes? etc..etc..

 

Anyway, I can't even imagine what can you do with this kind of data in an SQL Server where columns are occasionally added. Either you should unpivot the excel before importing it or storing the file in a blob.

Share this post


Link to post
22 hours ago, Attila Kovacs said:

@Stéphane Wierzbicki It's not that easy as it sounds. What if a column name changes? Is it a new column or just the name changed? What if a datatype changes? etc..etc..

It's not 😉, I know. I've implemented some checks before uploading data. I do have a mapping table used to control XLS file. XLS file is not uploaded if there is a change in the header (a column name changed for example). 

 

Problem is that I need to both update the mapping table and to create new fields in my database table. I wanted the later to be automatically handled by Tfdbatchmove component. 

 

 

Share this post


Link to post

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×