Question

Insert records in database using SQL

-1

Hi, Our application integrates with a 3rd party application (reporting) using a SQL database connection. In short, SQL inserts are done in a database table created by the Mendix application (domain model entity). In the 253 release this worked fine. However, in the 421 release the change to using BIGINT id's is now causing an issue (the id values need to be provided, while previously we did not have to provide the id column values). How could we determine the 'next' sequence value to be used for the id column? NOTE: we are aware that this might not be the ideal mendix supported solution however it does offer a solution for our integration requirement.

asked 2012-11-02

Brian Golsteijn

2 answers

-2

Our application has 2 entitities that are only used for complex reporting purposes. For performance reasons the data is created (and refreshed) with a sql-statement. There is no other functionality in the application that will create additional records. Is it possible to get the first value (bigint) from the mendix tables using sql?

answered 2012-11-06

Rene Pieterse

Sebastiaan van den Broek · Accepted Answer · 2012-11-02

In short, this will be very hard to get working properly and should preferably not be done.

The long version is that in Mendix 4 we don't automatically insert objects into the database when creating them, for performance reasons and for making transient objects work. Obviously we still need unique identifiers for objects which is why we reserve batches of identifiers up front.

Multiple Mendix Runtime servers with the same application can operate on a single database so this is all done neatly in transactions such that one server can claim a set of identifiers and immediately updates the table keeping these, the next server will never claim the same range.

These identifiers are currently reserved in sets of 100 at a time and their current number is kept in a special system table called mendixsystem$entityidentifier with 1 row per entity. These rows are stored by entity id, the entity name can be found in mendixsystem$entity.

When identifiers are about to run out, we can do an asynchronous database query claiming some more identifiers. If a runtime server stops then the ids not spent are basically lost, which is not an issue since the identifiers are 48 bits.

The actual identifiers you will see in the id column are made up of these 48 bits (least significant) AND an additional 16 bits (most significant) indicating what entity type they actually are. This is needed for inheritance and this part is fixed for a certain entity in a specific database. This number is the column short_id in the entityidentifier table.

I honestly think you'll have too much trouble getting this to work in a guaranteed bug-free way, and any mistakes here could seriously corrupt your database. Additionally, we can change this implementation since this is internal server logic. Is there no other way to do what you want to achieve?