Strange Bug when switching Databases

[FredS]

Hello,

With the new FB3 support in UniDac I took a few hour out on the weekend to move the db to FB3. Essentially the application now uses a FB2.54 embedded and can be switched to a FB3 server. All works surprisingly well..

Now when I switch between the two Databases, both Interbase Providers, I get a null insert error only on the first execution of a SQL statement. Keep in mind that if I run this again after the error all works fine.
Something I am failing to re-initialize or is this in the Provider?

Error=validation error for column "USERGROUPLIST"."SID", value "*** null ***"

call stack:
:7669c42d KERNELBASE.RaiseException + 0x58
:01251d9f TGDSConnection.IBCError + $A7
:01257268 TGDSCommand.Check + $1C
:0125a20b TGDSCommand.Execute + $33
CRAccess.TCRRecordSet.ExecCommand
:0125cdc7 TGDSRecordSet.ExecCommand + $4B
DBAccess.TCustomDADataSet.InternalExecute
Uni.TCustomUniDataSet.InternalExecute
DBAccess.TCustomDADataSet.Execute
DBAccess.TDBAccessUtils.Execute($44FD860)
DBAccess.TDADataSetUpdater.UpdateExecute([stInsert])
DBAccess.TDADataSetUpdater.PerformSQL('',[stInsert])
DBAccess.TDADataSetUpdater.PerformAppend
:0126c3b0 TCustomIBCDataSetUpdater.PerformAppend + $7C
MemDS.TDataSetUpdater.DoPerformAppend
MemDS.TMemDataSet.DoPerformAppend
MemData.TData.InternalAppend($709E8A0)
MemData.TMemData.InsertRecord($709E8A0)
MemDS.TMemDataSet.InternalPost
DBAccess.TCustomDADataSet.InternalPost
:006bb7c4 TDataSet.CheckOperation + $2C
:006bb2d5 TDataSet.Post + $4D
DataDataMod.TDataDM.NewUserGroup('S-1-5-21-3781965827-2510423845-1729914270-1106','DNSAdmins','SMALLBUSINESS',True,False,1,False)

[ViktorV]

Unfortunately, we can't reproduce this problem. To investigate this behavior of UniDAC, please send a small sample to demonstrate the issue to viktorv*devart*com, including a script to create and fill in the test database object.

[FredS]

Unfortunately, we can't reproduce this problem.

I tested this a bit more and I can switch between FB2 and SQL-Server and FB3 and SQL-Server in both directions without error. Only when I switch between FB3 and FB2 in either direction this occurs..

Will try to reproduce in a simple demo..

[FredS]

Hi ViktorV,

Seems a Macro value assignment is the issue. The code below works fine if run without the Macro assignment and fails otherwise.

Code: Select all

  procedure UpdateMacrosforFBMajorVersion;
  begin    
    FFbMajorVersion := GetSingletonIntegerResult('SELECT Cast(LEFT(rdb$get_context(''SYSTEM'', ''ENGINE_VERSION''),1) as Integer) as MajorVersion from rdb$database');
    with unConnection do begin
      Assert(MacroByName('True').Condition = 'InterBase');
      Assert(MacroByName('False').Condition = 'InterBase');
      if FbMajorVersion = 3 then begin
        MacroByName('True').Value := 'True';
        MacroByName('False').Value := 'False';
      end else begin
        MacroByName('True').Value := '1';
        MacroByName('False').Value := '0';
      end;
    end;

    // Any insert via a uniQuery should do 
      quUserGroupList.Open;
     NewUserGroup('s-test2','test','test',false, false,1);
  end;

[ViktorV]

Unfortunately, we still can't reproduce the problem taking on the basis of your comments.
Here is an example of console application that works with Firebird 2.5.4 and 3.0.
Please modify the sample so, that it reproduces the described error.

Code: Select all

program Test_Bool;
{$APPTYPE CONSOLE}

uses
  System.SysUtils,
  Uni,
  InterbaseUniProvider;

var
  UniConnection: TUniConnection;
  UniQuery: TUniQuery;

begin
  UniConnection := TUniConnection.Create(nil);
  try
    UniConnection.ProviderName := 'InterBase';
    UniConnection.Username := 'sysdba';
    UniConnection.Password := 'masterkey';
    UniConnection.Macros.Add('True', '', 'InterBase');
    UniConnection.Macros.Add('False', '', 'InterBase');

    //FB 2.5.4
    UniConnection.Server := Server FB 2.5.4;
    UniConnection.Port := Port FB 2.5.4;
    UniConnection.Database := Database FB 2.5.4;
    UniConnection.SpecificOptions.Values['InterBase.ClientLibrary'] := ClientLibrary FB 2.5.4;
    Assert(UniConnection.MacroByName('True').Condition = 'InterBase');
    Assert(UniConnection.MacroByName('False').Condition = 'InterBase');
    UniConnection.MacroByName('True').Value := '1';
    UniConnection.MacroByName('False').Value := '0';

    UniConnection.Connect;

    UniConnection.ExecSQL('EXECUTE BLOCK AS BEGIN ' +
      'IF (NOT exists(SELECT 1 FROM RDB$RELATIONS WHERE RDB$RELATION_NAME = ''TEST_BOOL'')) THEN ' +
      'EXECUTE STATEMENT ''CREATE TABLE TEST_BOOL (ID INTEGER, F_BOOL "BOOLEAN");'';' +
      'ELSE ' +
      'EXECUTE STATEMENT ''DELETE FROM TEST_BOOL;''; ' +
      'END');

    UniQuery := TUniQuery.Create(nil);
    try
      UniQuery.Connection := UniConnection;
      UniQuery.Close;
      UniQuery.SQL.Text := 'SELECT * FROM TEST_BOOL';
      UniQuery.Open;
      UniQuery.InsertRecord([1, True]);
      WriteLn('Insert FB 2.5.4 Ok');
    finally
      UniConnection.Disconnect;
    end;

    //FB 3.0.0
    UniConnection.Server := Server FB 3.0.0;
    UniConnection.Port := Port FB 3.0.0;
    UniConnection.Database := Database FB 3.0.0;
    UniConnection.SpecificOptions.Values['InterBase.ClientLibrary'] := ClientLibrary FB 3.0.0;
    Assert(UniConnection.MacroByName('True').Condition = 'InterBase');
    Assert(UniConnection.MacroByName('False').Condition = 'InterBase');
    UniConnection.MacroByName('True').Value := 'True';
    UniConnection.MacroByName('False').Value := 'False';

    UniConnection.Connect;

    UniConnection.ExecSQL('EXECUTE BLOCK AS BEGIN ' +
      'IF (NOT exists(SELECT 1 FROM RDB$RELATIONS WHERE RDB$RELATION_NAME = ''TEST_BOOL'')) THEN ' +
      'EXECUTE STATEMENT ''CREATE TABLE TEST_BOOL (ID INTEGER, F_BOOL BOOLEAN);'';' +
      'ELSE ' +
      'EXECUTE STATEMENT ''DELETE FROM TEST_BOOL;''; ' +
      'END');

    try
      UniQuery.Close;
      UniQuery.Open;
      UniQuery.InsertRecord([1, True]);
      WriteLn('Insert FB 3.0.0 Ok');
    finally
      UniConnection.Disconnect;
    end;

  ReadLn;

  finally
    UniConnection.Free;
    UniQuery.Free;
  end;
end.

[FredS]

Emailing Console source with table creation and data to reproduce.

[ViktorV]

Thank you for the information. We are investigating this behavior of UniDAC, and we will inform you about the results.

[ViktorV]

We have fixed this issue, the fix will be included in the next UniDAC build.

[ViktorV]

The new UniDAC build with a fix for the issue is available for download now.