Using a generator for non Id fields?

[Weetch_Russell (Russell Weetch)]

I'm moving to Sphinx for authentication but all my existing apps use an Integer Id for users. This isn't too much of a problem structurally as I can add a property InternalId: Integer to hold this value and can transfer it across from the existing records.

However I will need to generate this for new users, so my questions are:

Is there a way to use an attribute to instruct Aurelius to get a value from a generator for a field that isn't an Id?
If not, is there a built in method within Aurelius to get a value from a generator in an OnInserting event? Or do I need to implement a query to get this directly from the database?

Thanks

[wlandgraf (Wagner Landgraf)]

There is no such attribute to automatically generate an integer value that is not the id of the entity.

Yes, you can use the OnInserting event but you will have to fire your own query to get the id from the database.

[Weetch_Russell (Russell Weetch)]

Thanks. I thought that would be the case, but no harm in asking.

[Weetch_Russell (Russell Weetch)]

In case this is of use to anyone:

1. You can add the attribute [Sequence('SEQ_USER', 1000, 1)] to your descended TMyUser and even though it is not used for the Id, it will be created by Aurelius. Very useful for ensuring that the Database is created properly.

2. In The [OnInserting] event add the code

var
  AUser: TMyUser;
begin
  AUser := TMyUser(Args.Entity);
  AUser.InternalId := TConnectionUtils.GetNextSequenceValue(TObjectManager(Args.Manager).Connection, 'SEQ_USER');
end;

3. Which is calling my support object:

TConnectionUtils = class
  private
    class function GetFDConnection(AConnection: IDBConnection): TFDConnection;
  public
    class function GetQuery(AConnection: IDBConnection): TFDQuery;
    class function GetNextSequenceValue(AConnection: IDBConnection; const SequenceName: string): Int64;
  end;

implemented as follows (this is for Firebird, but you'll get the idea

class function TConnectionUtils.GetFDConnection(AConnection: IDBConnection): TFDConnection;
begin
  Result := (AConnection as IDBConnectionAdapter).AdaptedConnection as TFDConnection;
end;

class function TConnectionUtils.GetNextSequenceValue(AConnection: IDBConnection; const SequenceName: string): Int64;
resourcestring
  SQL = 'SELECT NEXT VALUE FOR %s FROM RDB$DATABASE';
var
  Query: TFDQuery;
begin
  Query := GetQuery(AConnection);
  try
    Query.Open(Format(SQL, [SequenceName.ToUpper]));
    Result := Query.Fields[0].AsInteger;
  finally
    Query.Free;
  end;
end;

class function TConnectionUtils.GetQuery(AConnection: IDBConnection): TFDQuery;
begin
  Result := TFDQuery.Create(nil);
  Result.Connection := GetFDConnection(AConnection);
end;

[wlandgraf (Wagner Landgraf)]

Thank you for the contribution, @Weetch_Russell. Also note that there is no need to use the TFDQuery component directly, you can use Aurelius interfaces directly and thus the code is not dependent on a specific component:

class function TConnectionUtils.GetNextSequenceValue(AConnection: IDBConnection; const SequenceName: string): Int64;
const
  SQL = 'SELECT NEXT VALUE FOR %s FROM RDB$DATABASE';
var
  Query: IDBStatement;
  ResultSet: IDBResultSet;
begin
  Query := AConnection.CreateStatement;
  Statement.SetSQLCommand(Format(SQL, [SequenceName.ToUpper]));
  ResultSet := Statement.ExecuteQuery;
  if ResultSet.Next then
    Result := ResultSet.GetFieldValue(0)
  else
    Result := -1;
end;

I'd also recommend not using resourcestring for SQL statements.