Issue with database connection since upgrade to RO 10.0.0.1597 - Invalid object name 'dbo.XXX'

fvancrae · December 23, 2024, 3:58pm

I recently upgraded to RO 10.0.0.1597 and while all code appeared to work
I notice some DA4 RO DA services don’t work properly anymore

It looks like the DB connection associated with the service is not pointing to the correct DB.

I get an error from MSSQL (SDAC) about the tablename object

The callstack shows the error is for the UpdateData statement (GetNextAutoInc2)

I haven’t pinpointed the issue yet.
It might be that it occurs for UpdateData in a TDataAbstractService service which did not have a preceding GetData.

Does this already ring some bells?
Are there known issues?

FYI: Our main -TDARemoteService based- DA service in the same server process appears to work fine.

fvancrae · December 23, 2024, 3:59pm

FYI: Our service code (pretty empty)

unit GFDService_Impl;

// ----------------------------------------------------------------------
// This file was automatically generated by Remoting SDK from a
// RODL file downloaded from a server or associated with this project.
//
// Do not modify this file manually, or your changes will be lost when
// it is regenerated the next time you update your RODL.
// ----------------------------------------------------------------------

{$I RemObjects.inc}

interface

uses
{$IFDEF DELPHIXE2UP}System.SysUtils{$ELSE}SysUtils{$ENDIF},
{$IFDEF DELPHIXE2UP}System.Classes{$ELSE}Classes{$ENDIF},
{$IFDEF DELPHIXE2UP}System.TypInfo{$ELSE}TypInfo{$ENDIF},
uROEncoding,
uROXMLIntf,
uROClientIntf,
uROClasses,
uROTypes,
uROServer,
uROServerIntf,
uROSessions,
uRORemoteDataModule,
uDAInterfaces,
uDAServerInterfaces,
uDADelta,
uDABusinessProcessor,
uDASchema,
DataAbstractService_Impl,
DataAbstract3_Intf,
DataAbstract4_Intf,
OffServerLibrary_Intf, uDAFields, uDACore, uDASchemaClasses,
uDAScriptingProvider, uDADataStreamer, uDABin2DataStreamer, uROComponent,
uDAStreamableComponent, uDAClientSchema;

type
{ Forward declarations }
TGFDService = class;

{ TGFDService }
TGFDService = class(TDataAbstractService, IGFDService)
Bin2DataStreamer: TDABin2DataStreamer;
BPQueGFDSession: TDABusinessProcessor;
BPGFDItems: TDABusinessProcessor;
BPQueGFDItems: TDABusinessProcessor;
BPGFDItemsToRevoke: TDABusinessProcessor;
DAGFDSchema: TDASchema;
procedure DataAbstractServiceCreate(Sender: TObject);
private
protected
{ IGFDService methods }
end;

implementation

{$IFDEF DELPHIXE2UP}
{%CLASSGROUP ‘System.Classes.TPersistent’}
{$ENDIF}
{$IFNDEF FPC}
{$R *.dfm}
{$ENDIF}
{$IFDEF FPC}
{$R *.lfm}
{$ENDIF}

uses
u_HelperRoutines,
uROClassFactories,
DataAbstract3_Invk, DataAbstract4_Invk, OffServerLibrary_Invk,
RODACheckServerBusinessProcessor;

var fClassFactory_GFDService: IROClassFactory;

procedure Create_GFDService(out anInstance: IInterface);
begin
anInstance := TGFDService.Create(nil);
end;

procedure TGFDService.DataAbstractServiceCreate(Sender: TObject);
begin
OffDACopyStatementNameInStatementConnectionType( Self );
TRODACheckTDARemoteService.RODACheckRemoteService(Self);
end;

initialization
// fClassFactory := TROClassFactory.Create(‘GFDService’, Create_GFDService, TGFDService_Invoker);
//** MA (12/11/2013) : pool maar niet preinit : voor hen die geen gfd hebben heeft het weinig zin… **//
// fClassFactory := TROPooledClassFactory.Create(‘GFDService’, Create_GFDService, TGFDService_Invoker,1,pbCreateAdditional,False);
fClassFactory_GFDService := TROPooledClassFactory.Create(‘GFDService’, {$IFDEF FPC}@{$ENDIF}Create_GFDService, TGFDService_Invoker,1,pbCreateAdditional,False);
// RegisterForZeroConf(fClassFactory_GFDService, ‘_GFDService_rosdk._tcp.’);
finalization
UnRegisterClassFactory(fClassFactory_GFDService);
fClassFactory_GFDService := nil;
end.

EvgenyK · December 23, 2024, 4:02pm

Hi,

try to set valid generator name for this table:

fvancrae · December 23, 2024, 6:01pm

I’m confused.
The GeneratorName appears to be a function to create primkeys

However, the table has an identity primkey.
It looks correct in DA

I assume nothing needs to be entered in the GeneratorName field of my statements.

EvgenyK · December 24, 2024, 8:08am

Hi,

according to this callstack:

it calls MSSQL_GetNextAutoinc:

function MSSQL_GetNextAutoinc(const GeneratorName: string; Query: IDAServerDataset): Variant;
begin
  try
    Query.SQL := 'SELECT NEXT VALUE FOR ' + AnsiQuotedStr(GeneratorName, '"');
    Query.Open;
    Result := Query.Fields[0].Value;
  finally
    Query := nil;
  end;
end;

Looks like you haven’t dbo.GFDStatus sequence …

fvancrae · December 24, 2024, 9:00am

What do you mean?

Should the GeneratorName field be empty?

Any idea what could cause this?
Any idea how I can identify the problem better?

The connection gets set at runtime.
The DB table OffBI.dbo.OrderLog exists.

At startup the program calls
SetConnectionString(ConnectionManager.Connections.Connections[6],sServer,‘OffBI’,sUser,sPwd);

procedure SetConnectionString(connection:TDAConnection;const server,dbname,user,pwd:string);
begin
connection.ConnectionString :=
‘SDAC?’ +
‘Server=’ + server + ‘;’ +
‘Database=’ + dbname + ‘;’ +
‘UserID=’ + user + ‘;’ +
‘Password=’ + pwd + ‘;’+
‘Options.DefaultLockTimeout=-1;’;
end;

The service contains
ConnectionName := ‘OffBI SDAC Connection’; // do not localize

I already traced in TDAConnectionManager.NewConnection but there a correct connection appears to be obtained.

fvancrae · December 24, 2024, 9:05am

FYI: With the tablename in the generatorname field the error becomes

fvancrae · December 24, 2024, 9:14am

FYI: An important thing to note (and that I failed too detect up until now) is that we use a patched version of uDABusinessProcessor.

I’ll investigate this first

EvgenyK · December 24, 2024, 9:39am

Hi,

if generator name is specified then DA tries to get autoinc value from correspondent sequence, otherwise DA will use Identity value:

function MSSQL_DoGetLastAutoInc(const GeneratorName: string; Query: IDAServerDataset): Variant;
begin
  try
    Query.SQL := 'SELECT IsNull(@@Identity, 0) as LastInc';
    Query.Open;
    Result := Query.Fields[0].Value;
  finally
    Query := nil;
  end;
end;

fvancrae · December 24, 2024, 10:31am

So given my table OrderLog which has an identity field as primary key
we want GeneratorName to be empty and we expect uDABusinessProcessor to call IDAUseGenerators2.GetNextAutoInc2 with an empty parameter (for my SDAC MSSQL connection)

Correct?

EvgenyK · December 24, 2024, 10:35am

Hi,

BP uses these interfaces (IDAUseGenerators/IDAUseGenerators2) only when GeneratorName is set:

usegenerators := Supports(aConnection, IDAUseGenerators);
usegenerators2 := Supports(aConnection, IDAUseGenerators2);
..
l_genName := ChooseDefaultGeneratorName(lDefaultGeneratorName, lParam.GeneratorName);
if (lParam.DataType in [datAutoinc, datLargeAutoInc]) and
	(usegenerators or usegenerators2) and
	(l_genName <> '') and
	(change.ChangeType = ctInsert) and
	(mapptr^[x].MappingType = mtNewValue) then begin
	// Gets the next generator values from the DB. This is for DBs such as IB or Oracle which lack autoinc fields
..
	if usegenerators2 then
		autoincvalue := (aConnection as IDAUseGenerators2).GetNextAutoinc2(l_genName)
	else
		autoincvalue := (aConnection as IDAUseGenerators).GetNextAutoinc(l_genName);

fvancrae · December 24, 2024, 11:57am

I found the cause of my issue.

It is related to a change in RODA.
The uDABusinessProcessor.pas now contains a define FIX_MSSQL_GENERATOR_BUG.

It clears the GeneratorName of a parameter for MSSQL connections if it is equal to the logicalname

However my LogicalName contains OrderLog and my param GeneratorName contains dbo.OrderLog.
I assume this last field gets (got?) automatically filled in by RODA.

So in my case GeneratorName did not get cleared.

Clearing the GeneratorName of the field solves the issue.
Improving the uDABusinessProcessor.pas code would also solve the issue.

Note: If I perform ‘Update Fields’ the GeneratorName does not get cleared (but also not filled in)
If I perform a recreate field then the GeneratorName content gets cleared.

Screenshot of GeneratorName content that should not be there.

Below is the code I am talking about.

What is your opinion on this?
Bug or not?
Should it be fixed via DA schema or via code?

              {$IFDEF FIX_MSSQL_GENERATOR_BUG}
              if (aConnection.ConnectionType = MSSQL_DriverType) and
                 (lParam.GeneratorName = aDelta.LogicalName) then lParam.GeneratorName := '';
              {$ENDIF}
              l_genName := ChooseDefaultGeneratorName(lDefaultGeneratorName, lParam.GeneratorName);
              if (lParam.DataType in [datAutoinc, datLargeAutoInc]) and
                 (usegenerators or usegenerators2) and
                 (l_genName <> '') and
                 (change.ChangeType = ctInsert) and
                 (mapptr^[x].MappingType = mtNewValue) then begin
                // Gets the next generator values from the DB. This is for DBs such as IB or Oracle which lack autoinc fields
                DAError(autoincvalue <> -1, err_MultipleAutoIncrementalFieldsNotSupported);
                parname := lParam.Name;
                change.OldValueByName[parname] := val;
                if usegenerators2 then
                  autoincvalue := (aConnection as IDAUseGenerators2).GetNextAutoinc2(l_genName)
                else
                  autoincvalue := (aConnection as IDAUseGenerators).GetNextAutoinc(l_genName);

EvgenyK · December 24, 2024, 12:19pm

Hi,

You can specify sequence name as generator name if you are using it.
in this case it can receive value from this sequence.

I suggest to clear GeneratorName in schema for tables that don’t use sequences.