UniLoader & Firebird - Defaults expected, NULLs found

[tonymeadors]

I'm not sure where the issue/fix may lie, please see what you think.

Seems that when using LOADFROMDATASET() under Firebird, integer fields that are
not explicitly given a value end up as NULL rather than 0 despite
the field being type INTEGER DEFAULT 0.

Specifically:

If you loop to execute SQL insert statements that
insert 2 data fields, all the INTEGER fields in the destination
table not specifically filled (having DEFAULT 0) do receive that default.

But if you select a dataset having only 2 data fields,
then do mUniloader.loadfromdataset(dset);
It seems that the not-filled INTEGER fields in the destination
table (having DEFAULT 0) contain NULLs.

Examples:

destination table:
create table CD_DENTAL (
id integer not null,
master_id integer default 0,
status integer default 0,
recip_hold default 0,
primary key(id));

A) Simple insertions:

q.sql.Add('select id, master_id from cd_med');
q.open;
q2.sql.add('insert into cd_dental(id, master_id)values(:id, :master_id)');
while not q.eof do
begin
q2.parambyname('id').asBCD:=q.fieldbyname('id').asinteger;
q2.parambyname('master_id').asBCD:=q.fieldbyname('master_id').asinteger;
q2.execsql;
q.Next;
end;

when I examine CD_DENTAL all the values in STATUS and RECIP_HOLD (not filled!)
contain 0s as expected



B)Loadfromdataset (CD_MED ->> CD_DENTAL):

mUniloader.TableName:='cd_dental';
q.sql.Add('select id, master_id from cd_med');
q.open;
mUniloader.createcolumns;
mUniloader.loadfromdataset(q);

When I examine CD_DENTAL all the values in STATUS and RECIP_HOLD (not-filled!)
contain NULL

Summary:
The issue is with those fields *not being specifically filled*
In one case they get the expected DEFAULT of 0,
In the other case they contain NULL.

version 4.0.2 for D2010
firebird 2.1

thanks,
tonyM

[AndreyZ]

The point is that TUniLoader loads data to the destination table using all fields of this table. If the source dataset does not have some fields (in your case it is the status and recip_hold fields), these fields obtain the null value. To avoid such behaviour, you should remove these columns from the TUniLoader.Columns property. Here is an example:

Code: Select all

var
  i: integer;
begin
  UniQuery1.SQL.Text := 'select id, master_id from cd_med';
  UniQuery1.Open;
  UniLoader1.TableName := 'CD_DENTAL';
  UniLoader1.CreateColumns;
  i := 0;
  while i < UniLoader1.Columns.Count do
    if SameText(UniLoader1.Columns[i].Name, 'status') or SameText(UniLoader1.Columns[i].Name, 'recip_hold') then
      UniLoader1.Columns.Delete(i)
    else
      inc(i);
  UniLoader1.LoadFromDataSet(UniQuery1);
end;

Using such approach, TUniLoader does not use the status and recip_hold fields. Therefore, they will obtain the default values specified on the server.

[tonymeadors]

Thank you - that works perfectly ! No more forced nils everywhere.

FYI I override the createcolumns method to allow syncing with the dataset before load.

So:
UniQuery1.SQL.Text := 'select id, master_id from cd_med';
UniQuery1.Open;
UniLoader1.TableName := 'CD_DENTAL';
UniLoader1.CreateColumns(UniQuery1); //******this deletes all unwanted columns
UniLoader1.LoadFromDataSet(UniQuery1);

BONUS:
When comparing 10,000 insert statements vs a uniloader load,
earlier testing found tremendous gains for SQL Server,
good gains for Oracle, and little improvement with Firebird.

With this modification firebird now is in line with the other DBs:
Firebird record loading is 50% faster with Uniloader in most tests.

thanks again!

tonyM

[AndreyZ]

I am glad I could help. If any other questions come up, please do not hesitate to contact us.