Loader doing weird things in 9.1 and above

[FredS]

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]

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]

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), "[email protected]"
+param7 = varchar(2112), "NmJ0QGvnXYikeWRwMq8/..."

[FredS]

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

[pavelpd]

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]

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', '[email protected]', '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.

[pavelpd]

Hi Fred,

Please note that we were unable to reproduce the specified behavior.

We ran your example without errors, the one entry you specified was copied. Since you are using embedded, please create a complete sample with all the necessary files for the embedded version to work correctly, and send it to us via the e-support contact form: https://www.devart.com/company/contactform.htm

[FredS]

Hi Pavel,

Embedded is irrelevant same happens when copying data to a new db on the same FB server.
Libraries are irrelevant, happens with the latest FB v4.0.1 but this also failed with v4.0 and v3.0.7.

Errors only occur when a unique index is violated.
There should only be truncated data in the code supplied.
see: BC4 output of the two statements, above

It's interesting that I can:
* create the db
* generate the DDL
* load data via TUniScript
* then load data via ExecSQL
* yet the Loader fails with FB yet works with MSSQL

Somehow the Loader doesn't work without the UTF8 info as shown in the Trace.log earlier.
If I close the connection and reopen it all works just fine..

[pavelpd]

Hi Fred!

No, we don't have truncated data.

As we ask earlier, please create a complete sample with all the necessary files for the embedded version to work correctly, and send it to us via the e-support contact form: https://www.devart.com/company/contactform.htm

So that we could easily reproduce the behavior you specified on our side.

[FredS]

Hi Pavel,

Here is what I see happening at the top level:

• TUniIBCScriptProcessor.CreateDatabase creates a connection to the server (?), the db doesn't exist.

• I notice the Pooled setting is saved and restored but this mock connection is never closed.

• Now when the creation part is over the scripts are fired and all works until the Loader.

• I can tell you that AfterConnect is never fired this way. Going to assume that means there is initialization code needed by Loader that never gets called either. Certainly I have code to Map stuff to Legacy in that event which is never called.

• After wrapping the NoPreConnect Script in a method and closing this mock connection immediately after all works. This is because the DDL script that follows has NoPreConnect=False which triggers a real connection event.

..more than enough wasted time on this for me..

[pavelpd]

Hi Fred,

We have thoroughly tested our components and have successfully completed a large number of automated tests using the embedded version. We still haven't been able to get a result similar to yours.

Therefore, if you can come back to this question again, we will expect a complete sample from you, with which we can get results identical to yours.