Watch, Follow, &
Connect with Us

For forums, blogs and more please visit our
Developer Tools Community.


Welcome, Guest
Guest Settings
Help

Thread: BatchMove from TextReader to SQLWriter - less fields in the source



Permlink Replies: 4 - Last Post: May 11, 2016 7:14 AM Last Post By: Dmitry Arefiev
Kelver Merlotti

Posts: 12
Registered: 2/8/04
BatchMove from TextReader to SQLWriter - less fields in the source
Click to report abuse...   Click to reply to this thread Reply
  Posted: May 3, 2016 1:01 PM
Hello everyone!
I'm trying to do a data import project, where "any" csv file can be imported to a database if there is a table with the same name on it.
For example: XYZ.csv -> XYZ table inside the database. My target database at this time is Firebird 3.
The problem is when the csv file contains less fields than the database table, then I'm getting the following exception:

[FireDAC][Comp][DM]-608. Undefined source field or expression for destination field [FIELD_NOT_PRESENT_IN_CSV]

And as the csv files are dynamically generated by another application, there's no way to garantee that it will contain all the fields.
So, how can I tell to FD that all fields not present in the csv file can be set to null in the database?
This is a sample of how I'm trying to do it:

var
  LFiles: TStringDynArray;
  LFile, lFileName, LTable: String;
begin
  LFiles := TDirectory.GetFiles('C:\CsvDir\', '*.csv', TSearchOption.soTopDirectoryOnly);
  for LFile in LFiles do
  begin
    LFileName := ExtractFileName(LFile);
    LTable := Copy(lFileName, 1, Length(lFileName)-4);
    bmCsvReader.FileName := LFile;
    bmSqlWriter.TableName := LTable;
    bm.GuessFormat;
    bm.Execute;
  end;
end;


Thanks in advance,
KM
Kelver Merlotti

Posts: 12
Registered: 2/8/04
Re: BatchMove from TextReader to SQLWriter - less fields in the source
Click to report abuse...   Click to reply to this thread Reply
  Posted: May 6, 2016 1:09 PM   in response to: Kelver Merlotti in response to: Kelver Merlotti
Folks, I found a way get it working after some tests and some help from Alan Glei - another MVP here in Brazil.
First of all, I have to manually remove from BatchMove.Mappings all the fields that exists in the database table, but are not in the csv file. I did it this way:
      i := 0;
      while i < bm.Mappings.Count do
      begin
        if (bm.Mappings[i].SourceFieldName = EmptyStr) then
          bm.Mappings.Delete(i)
        else
          Inc(i);
      end;

Second, I needed to replace the SQLWriter with a DatasetWriter, because it was failing to fill the SQL parameters. It generates a SQL like this...
MERGE INTO TABLENOME USING RDB$DATABASE ON (ID = :ID) 
WHEN NOT MATCHED THEN 
  INSERT (ID, EMAIL, GROUP) VALUES (:ID$#1, :EMAIL, :GROUP) 
WHEN MATCHED THEN 
  UPDATE SET ID = :ID$#2, EMAIL = :EMAIL$#1, GROUP = :GROUP$#1

...and it never fills the parameters with $# on their names, even changing the Mapping setting ID$#1.SourceFieldName to the same value as ID.SourceFieldName!
And third, using the DatasetWriter, it wasn't possible to use the Direct mode because TFDAdaptedDataSet.ServerSetKey is not implemented yet.
Thus, the finall code is something like this:
var
  LFiles: TStringDynArray;
  LFile, lFileName, LTable: String;
  i, p, idx: Integer;
begin
  LFiles := TDirectory.GetFiles('C:\CsvDir', '*.csv', TSearchOption.soTopDirectoryOnly);
  for LFile in LFiles do
  begin
    ResetComponents;
    LFileName := ExtractFileName(LFile);
    LTable := Copy(lFileName, 1, Length(lFileName)-4);
    bmCsvReader.FileName := LFile;
    qryDest.SQL.Text := 'select * from ' + LTable;
    qryDest.Open;
    bm.GuessFormat;
    bm.Mappings.AddAll;
    i := 0;
    while i < bm.Mappings.Count do
    begin
      if (bm.Mappings[i].SourceFieldName = EmptyStr) then
        bm.Mappings.Delete(i)
      else
        Inc(i);
    end;
    bm.Execute;
  end;
end;

I hope it helps anyone else.
Thanks all for the attention and thanks again to Alan Glei for the help!
Cheers,
KM
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: BatchMove from TextReader to SQLWriter - less fields in the source
Click to report abuse...   Click to reply to this thread Reply
  Posted: May 9, 2016 11:47 PM   in response to: Kelver Merlotti in response to: Kelver Merlotti
Thank you for reporting that.

Less source field & TextReader & SQLWriter & Mode=dmAppendUpdate
This mode was completely broken. We just fixed it. If you like I can provide
you a patch, which may be applied to DX10.1 code base.

--
With best regards,
Dmitry
Kelver Merlotti

Posts: 12
Registered: 2/8/04
Re: BatchMove from TextReader to SQLWriter - less fields in the source
Click to report abuse...   Click to reply to this thread Reply
  Posted: May 10, 2016 11:52 AM   in response to: Dmitry Arefiev in response to: Dmitry Arefiev
Dmitry Arefiev wrote:
Thank you for reporting that.

Less source field & TextReader & SQLWriter & Mode=dmAppendUpdate
This mode was completely broken. We just fixed it. If you like I can provide
you a patch, which may be applied to DX10.1 code base.

--
With best regards,
Dmitry

Sure, I'd like it!
Thanks a lot!
KM
Dmitry Arefiev

Posts: 1,406
Registered: 12/7/03
Re: BatchMove from TextReader to SQLWriter - less fields in the source
Click to report abuse...   Click to reply to this thread Reply
  Posted: May 11, 2016 7:14 AM   in response to: Kelver Merlotti in response to: Kelver Merlotti
https://forums.embarcadero.com/thread.jspa?threadID=211133

I posted there two patches:
1) batchmove1.patch - resolves the issue with minimal required changes.
Still this change is interface breaking, so it is not for DX10.1 Update 1.
2) batchmove2.patch - resolves the issue (and some other related ones)
in right / effective way. It is for sure for DX10.2. This patch must be applied
after applying batchmove1.patch.

I will appreciate a feedback regarding the patches.

--
With best regards,
Dmitry
Legend
Helpful Answer (5 pts)
Correct Answer (10 pts)

Server Response from: ETNAJIVE02