Loader doing weird things in 9.1 and above

Discussion of open issues, suggestions and bugs regarding UniDAC (Universal Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
FredS
Posts: 264
Joined: Mon 10 Nov 2014 17:52

Loader doing weird things in 9.1 and above

Post by FredS » Tue 04 Jan 2022 16:29

First I don't see any data in DBMonitor which makes me wonder if this uses yet another method to send data and bypasses those fixes in: IBCClassesUni.pas

Once again a big waste of my time!
Figured I'd use an existing method to pump data, one that works well to pump FB>MSSQL and ended up having to debug Loader..

Using:

Code: Select all

          Loader.TableName := LTableName;
          Loader.LoadFromDataSet(MyQuery);
I get partial data inserted which triggers a Unique index error, fbtrace.log:

Code: Select all

Statement 1226:
-------------------------------------------------------------------------------
INSERT INTO SERVERS(ID,DOMAINID,SERVER,ISDC,WASONLINE,OSTYPE,OSCOMMENT,OSFLAGS,EXCLUDEFROMFETCH,INFETCHDATA) VALUES (?,?,?,?,?,?,?,?,?,?)

param0 = bigint, "2"
param1 = bigint, "1"
param2 = varchar(252), "VMFI" << 'VMFileServer'
param3 = boolean, "<false>"
param4 = boolean, "<true>"
param5 = integer, "0"
param6 = varchar(320), "Hell" << 'Hello'
param7 = integer, "<NULL>"
param8 = boolean, "<true>"
param9 = boolean, "<false>"

Statement 1227:
-------------------------------------------------------------------------------
INSERT INTO SERVERS(ID,DOMAINID,SERVER,ISDC,WASONLINE,OSTYPE,OSCOMMENT,OSFLAGS,EXCLUDEFROMFETCH,INFETCHDATA) VALUES (?,?,?,?,?,?,?,?,?,?)

param0 = bigint, "3"
param1 = bigint, "1"
param2 = varchar(252), "VMFI" << 'VMFileServer2'
param3 = boolean, "<false>"
param4 = boolean, "<true>"
param5 = integer, "0"
param6 = varchar(320), "Test"
param7 = integer, "<NULL>"
param8 = boolean, "<true>"
param9 = boolean, "<false>"

DFM:

Code: Select all

object Loader: TUniLoader
  Connection = TestCon
  Transaction = TestTransaction
  SpecificOptions.Strings = (
    'SQL Server.KeepIdentity=True'
    'SQL Server.KeepNulls=True'
    'InterBase.AutoCommit=False')
end

MaximG
Devart Team
Posts: 1776
Joined: Mon 06 Jul 2015 11:34

Re: Loader doing weird things in 9.1 and above

Post by MaximG » Mon 10 Jan 2022 14:21

The provided information is not enough for investigating the issue. Please compose and us a small sample, the execution of which causes the issue. For your convenience, please use the e-support form https://www.devart.com/company/contactform.html

FredS
Posts: 264
Joined: Mon 10 Nov 2014 17:52

Re: Loader doing weird things in 9.1 and above

Post by FredS » Mon 10 Jan 2022 16:54

A simple app failed to reproduce but here are my findings:

Con is the source (Embedded), while TestCon is the destination. Both fbclient.dll are latest v4.0.1 but this also failed with v4.0.

In the failing app1 both ConnectStrings where logged just before executing the Loader:

Code: Select all

Con: Provider Name=InterBase;Database=..\PA.fdb;
User ID=SYSDBA;Client Library=..\fb4_(64)\fbclient.dll;
Character Set=UTF8;Role=RDB$ADMIN;Use Unicode=True;Login Prompt=False;Pooling=True;Min Pool Size=4 

TestCon: Provider Name=InterBase;Data Source=LOCALHOST;Database=PA-Test.fdb;
Client Library=..\system32\fbclient.dll;
Character Set=UTF8;Role=RDB$ADMIN;Use Unicode=True;Login Prompt=False
These strings where used to load both connections with identical data.
They where logged again before running the Loader with app2:

Code: Select all

Debug Output: 
Con: Provider Name=InterBase;Database=..\PA.fdb;
User ID=SYSDBA;Client Library=..\fb4_(64)\fbclient.dll;
Character Set=UTF8;Role=RDB$ADMIN;Use Unicode=True;Login Prompt=False;Pooling=True;Min Pool Size=4

TestCon: Provider Name=InterBase;Data Source=LOCALHOST;Database=PA-Test.fdb;
Client Library=..\system32\fbclient.dll;
Character Set=UTF8;Role=RDB$ADMIN;Use Unicode=True;Login Prompt=False
fbtrace.log shows only one with UTF8, the one that works:
-2022-01-10T08:18:43.9440 (10064:0000000001AA04C0) EXECUTE_STATEMENT_START
- ..\PA-Test.FDB (ATT_3, SYSDBA:NONE, NONE, TCPv6:::1/65464)
- ..\.App1.exe:12032
- (TRA_8, INIT_3, READ_COMMITTED | READ_CONSISTENCY | NOWAIT | READ_WRITE)
+2022-01-10T08:16:24.0110 (10064:0000000001AA04C0) EXECUTE_STATEMENT_START
+ ..\PA-Test.fdb (ATT_9, SYSDBA:RDB$ADMIN, UTF8, TCPv6:::1/65334)
+ ..\.App2.exe:12552
+ (TRA_24, READ_COMMITTED | READ_CONSISTENCY | NOWAIT | READ_WRITE)
Parameters:
Note the size diff between (-) and (+), in (-) the destination size is used in (+) the source.
Yet both sql are identical:

Code: Select all

SELECT * FROM MNGDDOMAINS WHERE 1=0
BC4 output of the two statements:

Code: Select all

-Statement 1222:
+Statement 183:
 -------------------------------------------------------------------------------
 EXECUTE BLOCK (p0 BIGINT = ?,p1 VARCHAR(63) = ?,p2 VARCHAR(128) = ?,p3 VARCHAR(155) = ?,p4 INTEGER = ?,p5 BOOLEAN = ?,p6 VARCHAR(144) = ?,p7 VARCHAR(528) = ?,p8 BIGINT = ?,p9 VARCHAR(184) = ?,p10 BIGINT = ?,p11 VARCHAR(63) = ?,p12 VARCHAR(128) = ?,p13 VARCHAR(155) = ?,p14 INTEGER = ?,p15 BOOLEAN ...
 
 param0 = bigint, "2"
-param1 = varchar(63), "WHAT"
-param2 = varchar(128), "what"
-param3 = varchar(155), "vmWh"
+param1 = varchar(252), "WHATNOT*"
+param2 = varchar(512), "whatnot.local*"
+param3 = varchar(620), "vmWhatnot"
 param4 = integer, "14"
 param5 = boolean, "<true>"
-param6 = varchar(144), "Admi"
-param7 = varchar(528), "NmJ0"
+param6 = varchar(576), "Administrator@WHATNOT.LOCAL"
+param7 = varchar(2112), "NmJ0QGvnXYikeWRwMq8/..."

FredS
Posts: 264
Joined: Mon 10 Nov 2014 17:52

Re: Loader doing weird things in 9.1 and above

Post by FredS » Mon 10 Jan 2022 17:25

I should add that I can reproduce the error in UniDAC 8.4.4 (15-Jul-2021).

pavelpd
Devart Team
Posts: 5
Joined: Thu 06 Jan 2022 14:16

Re: Loader doing weird things in 9.1 and above

Post by pavelpd » Fri 14 Jan 2022 06:36

Hi Fred,
We tried to reproduce the issue in our environment according to the data you provided. However, it was not possible to get the described error using the sample we compiled. Therefore, to study the issue, we need the simplest sample of an application whose execution leads to an error. Even when using the loader, we do not have information about the structure and composition of the tables you download.
Can you provide a test sample of such a database?
You can send all additional information to us using the e-support form https://www.devart.com/company/contactform.html
Regards, Pavel

FredS
Posts: 264
Joined: Mon 10 Nov 2014 17:52

Re: Loader doing weird things in 9.1 and above

Post by FredS » Fri 14 Jan 2022 15:46

Simple and reproducible:

- Create the source DB and Table, scripts in DFM
- Data below
- Run
- Click 'btCreate'
- Click 'btLoad'
- Check the data

Code: Select all

INSERT INTO MNGDDOMAINS (ID, DOMAINNAME, FQDN, DC, DCTYPE, USEALTCRED, ALTUSER, ALTUSERPWD, LASTSNAPSHOTID, DOMAINSID) 
VALUES ('1', 'WHATNOT', 'whatnot.local', 'vmWhatnot', '14', 'true', 'Administrator@WHATNOT.LOCAL', 'Base64', '4', 'S-1-5-21');

Code: Select all

object Form1: TForm1
  Left = 0
  Top = 0
  Caption = 'Form1'
  ClientHeight = 299
  ClientWidth = 635
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'Tahoma'
  Font.Style = []
  OldCreateOrder = False
  PixelsPerInch = 96
  TextHeight = 13
  object btLoad: TButton
    Left = 249
    Top = 237
    Width = 75
    Height = 25
    Caption = 'btLoad'
    TabOrder = 1
    OnClick = btLoadClick
  end
  object btCreate: TButton
    Left = 249
    Top = 206
    Width = 75
    Height = 25
    Caption = 'btCreate'
    TabOrder = 0
    OnClick = btCreateClick
  end
  object TestCon: TUniConnection
    ProviderName = 'InterBase'
    Database = 'PA-Test.fdb'
    SpecificOptions.Strings = (
      'InterBase.Charset=UTF8'
      'InterBase.UseUnicode=True'
      'InterBase.Role=RDB$ADMIN'
      'InterBase.ForceUnloadClientLibrary=True')
    Options.KeepDesignConnected = False
    DefaultTransaction = TestTransaction
    Server = 'LOCALHOST'
    LoginPrompt = False
    Left = 276
    Top = 57
  end
  object TestTransaction: TUniTransaction
    DefaultConnection = TestCon
    Left = 276
    Top = 129
  end
  object Con: TUniConnection
    ProviderName = 'InterBase'
    Database = 'PA-Test-src.fdb'
    SpecificOptions.Strings = (
      'InterBase.Charset=UTF8'
      'InterBase.UseUnicode=True'
      'InterBase.Role=RDB$ADMIN'
      'InterBase.ForceUnloadClientLibrary=True'
      'InterBase.TrustedAuthentication=True')
    Options.AllowImplicitConnect = False
    Options.KeepDesignConnected = False
    Options.DefaultSortType = stCaseInsensitive
    PoolingOptions.MinPoolSize = 4
    Pooling = True
    Username = 'SYSDBA'
    Server = 'LOCALHOST'
    LoginPrompt = False
    Left = 181
    Top = 61
  end
  object InterBaseUniProvider: TInterBaseUniProvider
    Left = 97
    Top = 75
  end
  object quDataPump: TUniQuery
    Encryption.Fields = 'USERGROUPNAME'
    Connection = Con
    AutoCalcFields = False
    Left = 119
    Top = 177
  end
  object Loader: TUniLoader
    Connection = TestCon
    Transaction = TestTransaction
    SpecificOptions.Strings = (
      'SQL Server.KeepIdentity=True'
      'SQL Server.KeepNulls=True'
      'InterBase.AutoCommit=False')
    OnProgress = LoaderProgress
    Left = 349
    Top = 61
  end
  object scrptTable: TUniScript
    SQL.Strings = (
      'CREATE TABLE MNGDDOMAINS'
      '('
      
        '  ID bigint GENERATED BY DEFAULT AS IDENTITY (START WITH 1) NOT ' +
        'NULL,'
      '  DOMAINNAME varchar(63) NOT NULL,'
      '  FQDN varchar(128) NOT NULL,'
      '  DC varchar(155) NOT NULL,'
      '  DCTYPE integer DEFAULT 0,'
      '  USEALTCRED boolean DEFAULT FALSE,'
      '  ALTUSER varchar(144),'
      '  ALTUSERPWD varchar(528) CHARACTER SET ASCII,'
      '  LASTSNAPSHOTID bigint,'
      '  DOMAINSID varchar(184),'
      '  CONSTRAINT PK_MNGDDOMAINS PRIMARY KEY (ID)'
      ');')
    Connection = TestCon
    SpecificOptions.Strings = (
      'InterBase.AutoDDL=False')
    Left = 480
    Top = 160
  end
  object scrptDB: TUniScript
    SQL.Strings = (
      'CREATE DATABASE '#39'PA-Test.fdb'#39
      'USER '#39'SYSDBA'#39' PASSWORD '#39'masterkey'#39
      'PAGE_SIZE 16384'
      'default character set UTF8 collation UNICODE_CI_AI ;')
    NoPreconnect = True
    Connection = TestCon
    SpecificOptions.Strings = (
      'InterBase.AutoDDL=False')
    Left = 480
    Top = 103
  end
end

Code: Select all

unit Unit1;

interface

uses
  Winapi.Windows, Winapi.Messages, System.SysUtils, System.Variants, System.Classes, Vcl.Graphics,
  Vcl.Controls, Vcl.Forms, Vcl.Dialogs, UniProvider, InterBaseUniProvider, DBAccess, Uni, Data.DB, DALoader, UniLoader, Vcl.StdCtrls, MemDS, SQLServerUniProvider, DAScript, UniScript;

type
  TForm1 = class(TForm)
    TestCon: TUniConnection;
    TestTransaction: TUniTransaction;
    Con: TUniConnection;
    InterBaseUniProvider: TInterBaseUniProvider;
    quDataPump: TUniQuery;
    btLoad: TButton;
    Loader: TUniLoader;
    scrptTable: TUniScript;
    scrptDB: TUniScript;
    btCreate: TButton;
    procedure btCreateClick(Sender: TObject);
    procedure btLoadClick(Sender: TObject);
    procedure LoaderProgress(Sender: TObject; Percent: Integer);
  private
  public
    procedure CopyDataToNewServerDb;
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}


procedure TForm1.btCreateClick(Sender: TObject);
begin
  TestCon.AutoCommit := False;
  scrptDB.Execute;
  TestCon.StartTransaction;
  scrptTable.Execute;
  TestCon.Commit;
end;

procedure TForm1.btLoadClick(Sender: TObject);
begin
  TestCon.Connect;
  try
    CopyDataToNewServerDb;
  finally
    TestCon.Disconnect;
  end;
end;

procedure TForm1.CopyDataToNewServerDb();
const
  tables : array[0..0] of string =     ( 'MNGDDOMAINS');
var
  i : integer;
  LTableName : string;
begin
  OutputDebugString(PChar('Con: ' + Con.ConnectString));
  OutputDebugString(PChar('TestCon: ' + TestCon.ConnectString));
  TestCon.StartTransaction;
  Con.Connect;
  try
    quDataPump.Active := False;
    quDataPump.Options.QueryRecCount := True;
    try
      for i := Low(tables) to High(tables) do begin
        LTableName := tables[i];
        Loader.TableName := LTableName;
        quDataPump.SQL.Text := 'SELECT * FROM ' + LTableName;
        quDataPump.Open;
        try
          Loader.LoadFromDataSet(quDataPump);
        finally
          quDataPump.Close;
        end;
      end;
      TestCon.Commit;
    except
      TestCon.Rollback;
    end;
  finally
    Con.Disconnect;
  end;
end;

procedure TForm1.LoaderProgress(Sender: TObject; Percent: Integer);
begin
  Loader.Tag := Percent;
end;

end.

Post Reply