Aurelius: How to retrieve Auto-incremented ID generated in the database

BIZ TMS Aurelius
1

I have an ElevateDB database with the following People table definition:

CREATE TABLE "People"
(
"ID" INTEGER DEFAULT UID() NOT NULL,
...
)

The Aurelius Entity is defined as follows:

[Entity]
[Table('People')]
[Id('FID', TIdGenerator.None)]
TPeople = class
private
[Column('ID', [TColumnProp.Required])]
FID: Integer;
public
property ID: Integer read FID write FID;
...

I call the following code in an Aurelius Server project:

var
oPeople: TPeople;
FarmerID: Integer;
begin
oPeople := TPeople.Create;
oPeople ... set some values
Manager.Save(oPeople);
Manager.Flush(oPeople);
FarmerID:= oPeople.ID; //<--- Error Here

GExperts Debugger Exception Notification

Status code: 500
Error Code: Exception
Failed to create new farmer: Id not set on entity of class TPeople.'.

In a VCL project if I call "INSERT" on a TDataset linked to People, the ID populates.

I think I have to change some code in the Aurelius project to ensure that the ID field populates?

  • Change "TIdGenerator"
  • Or do something in addition to "Manager.Flush"
2

You have TIdGenerator.None, so the calculation of the Id is up to you before saving the entity to the DB. I think you can do it by executing a query (Select Max(ID)+1 From table) and assign the value to the entity. You can also do the query in Aurelius with projections.

I use TIdGenerator.IdentityOrSequence (and AUTO_INCREMENT in the MySQL database), so I don't have this problem.

1 Like
3

Thank you Gomezel.

My database does return a value automatically to the ID field, but I am not seeing this returned into Aurelius.

I have tried adding

[Id('FId', TIdGenerator.IdentityOrSequence)]

But this does not change anything. I think it is probably because I am using the ElevateDB database, and perhaps the mechanism by which it retrieves auto-incremented keys is not dealt with by Aurelius. I know that Aurelius tries to manage the database relationship and uses caching etc.

My need is really how to call

oPeople:= TPeople.Create;

And then call some Aurelius function to actually interact with the database and get the new record.

I do not want to use your suggestion to " (Select Max(ID)+1 From table " because the mechanisms that set the Unique ID are a bit more complex, and are controlled by the "UID" function I showed here:

CREATE TABLE "People"
(
"ID" INTEGER DEFAULT UID() NOT NULL
)

If I override the retrieval of the ID value, the UID function will still fire on the database, and this will result in behavior I do not want.

4

When using ElevateDB databases, and mapping an id of the class as "IdentityOrSequence", Aurelius will create such field type as

INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 1, INCREMENT BY 1

When inserting a new record, after the execution of INSERT SQL statement, Aurelius will automatically retrieve the id value generated by ElevateDB with the following SQL statement:

SELECT LASTIDENTITY('TABLE_NAME', 'ID_FIELD_NAME')

If your ElevateDB table does exist, of course Aurelius will not create is as "DEFAULT AS IDENTITY". But still, if you map the id as IdentityOrSequece, it will try to retrieve the id value using the SELECT LASTIDENTITY statement. If such statement doesn't work when using UID(), then it will not work indeed.

I suggest you change your field type to IDENTITY (instead of DEFAULT UID), or contact ElevateDB support to learn how to retrieve the ID value when using UID() after it was inserted.

5

Thank you Wagner.

I cannot change the definition of a database which is in use with numerous installations against numerous applications. The existing design is in place for a purpose and the call to UID performs useful functions on the database side as well as returning a globally unique ID for the record which is also used in auditing purposes.

My problem is not just that the ID is not returned, I do not think any of the defaults and computed fields are returned. I am finding that my default values (not only the ID but all other defaults and function calls) are returning as NULL or Zero, even when they would have a computed value from the database.

Is there a way to replicate the process of Dataset.Insert in Aurelius?

In VCL the Dataset.Insert causes all default values to be returned.

I realise that the idea of Aurelius is to do everything in Aurelius, which is great ... but in my case I need some call which will replicate the effect of actually asking the database for the record.

I had hoped that

Manager.Save(oPeople);
Manager.Flush(oPeople);

Or something similar, would have this effect. I am pretty sure that there must be an easy solution to this, perhaps I am not asking the right question.

I am using ElevateDB.

6

You must call Manager.Refresh(Object) to update the existing properties from the database values.

The problem is that everything is referenced by an ID. If the ID is generated server-side, Aurelius can't know what to retrieve (unless it uses SELECT LAST_IDENTITY as I mentioned).

7

I am trying to make this work for me. I cannot use :

Person:= TPeople.Create;

For reasons detailed earlier in the thread.

I need to call "INSERT" on my database for a number of routines to run server-side. There are good reasons for this.

I have tried adding a call to INSERT on my ServerContainer (DataModule) which holds my EDBDatabase and EDBSession components using the following code:

var
Q: TEDBQuery;

function TServerContainer.InsertRecordReturnID(aTableName: String): Integer;
begin
Q.Close;
Q.UnPrepare;
Q.DatabaseName:= EDBDB.DatabaseName;
Q.SQL.Text:= FORMAT('INSERT INTO "%s" (ID) VALUES (:ID) ',
[aTableName]);
Q.Prepare;
Q.ParamByName('ID').ParamType:= ptOutput;
Q.ExecSQL;
Result:= Q.Params[0].AsInteger;
Q.Close;
Q.UnPrepare;
end;

This runs and returns a correct ID for a newly inserted record in the table.

Then in my Aurelius ServiceImplementation I call the following:

function TKKFUPhoneService.NewPerson: Integer;
var
oPeople: TPeople;
begin
aID:= ServerContainer.InsertRecordReturnID('People');
oPeople:= Manager.Find(aID);
... try to access oPeople here ... ERROR
end;

The following error arises:

GExperts Debugger Exception Notification
Project RemoteAurelius.exe raised exception class EObjectAlreadyPersistent with message 'Cannot turn persistent object of class TPeople with id 60104537. The object is already in persistent context.'.
Classes=[EObjectAlreadyPersistent,EAureliusException,Exception,TObject]

Using the above code in a VCL App would work. Closing and Unpreparing the Query would be enough to free the record on the server side so another call to retrieve the record would be OK.

If Aurelius thinks that the "Object Already Exists" is there any way I can access it, other than via:

oPeople:= Manager.Find<TPeople>(aID);

Which does not raise an exception?

8

Try this:

oPeople := Manager.find<TPeople>.Where(Dic.People.Id = aID).Refreshing.Take(1).UniqueResult;

if you do not have a dictionary, then replace Dic.People.Id with Linq['ID']=aID.

What I try is to force the uprating of the class informations. Maybe this works :slight_smile:

Also you an activate SQL logging and see what is being executed on SQL level, see

1 Like
9

Thank you Dino.

Using this syntax has worked.

Weirdly I still get an error if I call

Manager.Save(oPeople);

But there doesn't seem to be any need to call this method, so I have removed it, the data is persisted and seems to update well.

I am still feeling my way with Aurelius, but I am learning and it is just immensely powerful.

2 Likes
10

This topic was automatically closed 24 hours after the last reply. New replies are no longer allowed.