Page 1 of 1
UniLoader - how skip columns not exists on target
Posted: Wed 25 Dec 2019 10:34
by ertank
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
Re: UniLoader - how skip columns not exists on target
Posted: Wed 25 Dec 2019 12:37
by ViktorV
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);
Re: UniLoader - how skip columns not exists on target
Posted: Wed 25 Dec 2019 14:59
by ertank
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.
Re: UniLoader - how skip columns not exists on target
Posted: Wed 25 Dec 2019 17:20
by ertank
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
Re: UniLoader - how skip columns not exists on target
Posted: Thu 26 Dec 2019 10:00
by ViktorV
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.
Re: UniLoader - how skip columns not exists on target
Posted: Thu 26 Dec 2019 19:25
by ertank
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.
Re: UniLoader - how skip columns not exists on target
Posted: Fri 27 Dec 2019 06:44
by ViktorV
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.
Re: UniLoader - how skip columns not exists on target
Posted: Fri 27 Dec 2019 07:19
by ertank
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.
Re: UniLoader - how skip columns not exists on target
Posted: Fri 27 Dec 2019 08:55
by ViktorV
Thank you for interest to our product.
Feel free to contact us if you have any further questions about our products.