UniLoader - how skip columns not exists on target

Discussion of open issues, suggestions and bugs regarding UniDAC (Universal Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
ertank
Posts: 172
Joined: Wed 13 Jan 2016 16:00

UniLoader - how skip columns not exists on target

Post by ertank » Wed 25 Dec 2019 10:34

Hello,

I am using Delphi 10.3.3, UniDAC 8.1.2, both databases are FirebirdSQL 2.5.9.

I am trying to copy data from one database to another. I am using UniLoader for performance concerns.
There are missing columns on target table and I get exception for them when I run UniLoader.

Is it possible to skip these missing columns in target table?

Please note that I do not know beforehand which columns are missing as there are more than one database and table to copy data from. I am looking for a general solution without any need to specify column names manually.

Thanks & regards,
Ertan

ViktorV
Devart Team
Posts: 2870
Joined: Wed 30 Jul 2014 07:16

Re: UniLoader - how skip columns not exists on target

Post by ViktorV » Wed 25 Dec 2019 12:37

To solve your problem, you can try using the following code:

Code: Select all

  UniQuery.SQL.Text := 'select * from source_table';
  UniQuery.Open;
  UniLoader.TableName := 'target_table';
  UniLoader.CreateColumns;
  UniLoader.LoadFromDataSet(UniQuery);

ertank
Posts: 172
Joined: Wed 13 Jan 2016 16:00

Re: UniLoader - how skip columns not exists on target

Post by ertank » Wed 25 Dec 2019 14:59

This has changed my error message into following:

Code: Select all

attempted update of read-only column.
There is one domain column in that table of named BOOLEAN created using following SQL:

Code: Select all

CREATE DOMAIN "BOOLEAN" AS
  SMALLINT
  CHECK (value is null or value in (0,1));
I suspect this column is considered as a read-only, but cannot be sure as there is no column name/index displayed in error message.

ertank
Posts: 172
Joined: Wed 13 Jan 2016 16:00

Re: UniLoader - how skip columns not exists on target

Post by ertank » Wed 25 Dec 2019 17:20

It turned out that there are some columns created like:

Code: Select all

  INPUTPRICEEURO                 COMPUTED BY (CASE WHEN INPUTPRICE IS NOT NULL THEN CASE WHEN COALESCE(EURO,0) =0 Then 0 else INPUTPRICE / EURO END ELSE 0 END),
Such fields are identified as "ftFloat" and "ReadOnly = False" after opening a TUniQuery.

Is it possible for me to make them identified as ReadOnly so that UniLoader simply skip them and do not try to copy data in them?

Thanks & regards,
Ertan

ViktorV
Devart Team
Posts: 2870
Joined: Wed 30 Jul 2014 07:16

Re: UniLoader - how skip columns not exists on target

Post by ViktorV » Thu 26 Dec 2019 10:00

The point is that TUniLoader loads data to the destination table using all fields of this table. To avoid such behaviour, you should remove these columns from the TUniLoader.Columns property. Here is an example:

Code: Select all

  UniQuery.SQL.Text := 'select * from source_table';
  UniQuery.Open;
  UniLoader.TableName := 'target_table';
  UniLoader.CreateColumns;
  UniLoader.LoadFromDataSet(UniQuery);
  i := 0;
    while i < UniLoader1.Columns.Count do
    if SameText(UniLoader.Columns[i].Name, 'SkipField1') or SameText(UniLoader1.Columns[i].Name, 'SkipField2') then
      UniLoader1.Columns.Delete(i)
    else
      inc(i);
  UniLoader1.LoadFromDataSet(UniQuery1);
This way, TUniLoader does not use the fields SkipField1 and SkipField2 .
Note that you'll need to manually define columns that should be removed.

ertank
Posts: 172
Joined: Wed 13 Jan 2016 16:00

Re: UniLoader - how skip columns not exists on target

Post by ertank » Thu 26 Dec 2019 19:25

I had to use following SQL to find any computed columns in any given table.

Code: Select all

select
  rdb$relation_fields.rdb$field_name
from
  rdb$fields
join 
  rdb$relation_fields on rdb$relation_fields.rdb$field_source = rdb$fields.rdb$field_name
where
  rdb$computed_blr is not null and rdb$relation_name = :tablename
Then it is only finding fieldname matches and removing them from TUniLoader.Columns (after calling TUniLoader.CreateColumns() of course).

My thinking, something like above should be implemented in TUniLoader as it is impossible to copy data in such computed columns in FirebirdSQL or alike in other DBMS. TUniLoader already provided target dataset which means it has necessary information.

ViktorV
Devart Team
Posts: 2870
Joined: Wed 30 Jul 2014 07:16

Re: UniLoader - how skip columns not exists on target

Post by ViktorV » Fri 27 Dec 2019 06:44

If you want us to implement the feature, please post this suggestion at our user voice forum:

Code: Select all

https://devart.uservoice.com/forums/104635-delphi-data-access-components/
If the suggestion gets a lot of votes, we will consider the possibility to implement it.

ertank
Posts: 172
Joined: Wed 13 Jan 2016 16:00

Re: UniLoader - how skip columns not exists on target

Post by ertank » Fri 27 Dec 2019 07:19

ViktorV wrote:
Fri 27 Dec 2019 06:44
If you want us to implement the feature, please post this suggestion at our user voice forum:

Code: Select all

https://devart.uservoice.com/forums/104635-delphi-data-access-components/
I simply cannot add a new idea even I would like to. Page says I have used all my votes. It does not allow me to post an idea with 0 (zero) votes.

It is not logical for me to reduce my vote on another idea that I support so I do not want to do that.

Thanks.

ViktorV
Devart Team
Posts: 2870
Joined: Wed 30 Jul 2014 07:16

Re: UniLoader - how skip columns not exists on target

Post by ViktorV » Fri 27 Dec 2019 08:55

Thank you for interest to our product.
Feel free to contact us if you have any further questions about our products.

Post Reply