Hello,

I'm working on a windows application, I use C# 2.0 and llblgen in selfservicing mode.

For most of my database tables (not all of them), primary keys are of datatype INT64 or bigint if you prefer and are generated using a custom formula which consists of

(the site ID, range [1 to 99]) * 10^16 + (A number sequence based on a counter)

It gives numbers like

10000000000000001 10000000000000002 10000000000000003

for site #1

And numbers like

20000000000000001 20000000000000002 20000000000000003

for site #2

The point of all that is to allow the sites to consolidate the content of their respective database into a centralized one on a daily basis without primary key conflicts.

The question is, how should I do that in LLBLgen pro? My current idea is to have all the concerned entities implementing a specific interface which defines the Int64 GetNextID() method

Then I could implement it using partial classes in each of my Entities and find a way to have something like the following code

if( IsNew && PrimaryKeyField == null) { PrimaryKeyField = GetNextID(); }

automatically executed before a new row is inserted into the DB, not sure how, perhaps by overriding the OnValidateEntityBeforeSave method.

This solution requires writing a lot of redundant code, so I wonder if there would be an another way or something I'm not aware of.
Thanks in advance for every suggestion you would have.

if this occurs in the BLL I would recommend ovrriding the OnSaveEntity event of the Adapter assuming the adapter model. This is last method before presisting the object. check the entity type. if it's the correct type create the PK, assign it and call base.OnSaveEntity(saveQuery, entityToSave);

if this is a db function/stored proc I would recommend placing an insert trigger on the table which would populate the field.

In my opinion, I would have this done in a trigger on the database.

Thanks, Doing it with some insert trigger was my first idea but I'm worried about one thing... How could llblgen pro read the inserted record back from the database if it is unaware of what the trigger did with the primary key? Would it even allow me to insert an entity with a null value as PK?

PS: I use firebird 2.01 and selfservicing

How could llblgen pro read the inserted record back from the database if it is unaware of what the trigger did with the primary key?

Check Trigger based sequence values (Oracle/Firebird); in the manual's section "Using the generated code -> Application configuration through .config files"

Would it even allow me to insert an entity with a null value as PK?

Yes it would.

Thanks, I wasn't planning to use fb generators. Anyway I have just figured out that they do support 64bits values (I wasn't aware of that, thanks to IB6 outdated documentation).

Using fb generators could be an answer as you can tell llblgen to automatically retrieve the next PK value to use BEFORE inserting a new Entity. Doing it after insert isn't safe enough.

But if I bind an llblgen entity field to a generator, is it still possible to manually specify the PK value to use before saving a new entity?

Using fb generators could be an answer as you can tell llblgen to automatically retrieve the next PK value to use BEFORE inserting a new Entity. Doing it after insert isn't safe enough.

But if I bind an llblgen entity field to a generator, is it still possible to manually specify the PK value to use before saving a new entity?

What do you mean doing it after insert is not safe enough? After the row is inserted, the trigger will update that value. LLBLGen will retrieve that value and/or use it in any relationships - that is the safe way.

Yes - you can set a primary key field value with LLBLGen - it is a method on the entity like 'ForceFieldValue' or something like that...

What do you mean doing it after insert is not safe enough? After the row is inserted, the trigger will update that value. LLBLGen will retrieve that value and/or use it in any relationships - that is the safe way.

Let's take the following example:

You assign a generator to the primary key field of an entity using the llblgen pro designer. Then when using the code, saving a new instance of that Entity will generate two queries.

-The first one queries the generator for it's next value. Something like SELECT GEN_ID(<generator name>, 1) FROM RDB$DATABASE;

-The second one inserts the record in the database table INSERT INTO table_name ( PrimKeyField, ...) VALUES ( <value fetched from query 1>, ...);

Refetching the record after the INSERT is not a problem because llblgen already knows the value of its primary key, it just has to use it within a SELECT statement. Generators are thread-safe and perform outside transaction scope (which means a transaction rollback won't restore their previous value). You'll probalby agree it's safe.

About the trigger way :

You save a new entity, llblgen generates an INSERT STATEMENT without a primary key value, your trigger assigns one, the record is successfully saved, everything ok. Now you would like llblgen to refetch the newly inserted Entity. In order to do that, it needs to execute a SELECT query with the PK value as parameter. The problem is that it can't guess what your trigger actually did with the PK!

The only thing it can do (if you configure it so) is to query the generator for its current value.
SELECT GEN_ID(<generator name>, 0) FROM RDB$DATABASE;

So then it will be possible to refetch the record according to its primary key value.

But it's dangerous because you can't be sure that the current ID is the right one, I mean the one that was assigned to YOUR record in case there are many users working on the same database table. This is why I consider it unsafe.

Don't you agree?

I am unsure excatly how LLBLGen works with Firebird, but it certainly does not 'guess' what the next key value is in SQL Server.

The insert statement looks like this in SQL Server:

insert into table (blah, blah) values (@parm1, @parm2) select SCOPE_IDENTITY()

This causes the insert to return the new primary key. I imagine it does something similar with firebird - it would not possibly try to guess.

Ok I understand your point of view,

If you are working with sql server, you can use @@identity and scope_identity() to know the last identity value that was generated by your session. If an another user inserts a new record in the database and causes a new identity value to be generated right after yours, calling select @@identity would still return the expected value.

Firebird doesn't offer such a functionality, so you have better to know the primary key value before inserting your record if you plan to read it back from the database. If you don't, the best you can do is selecting the last used ID right after the insert but you can't be sure it's yours.

I have tried different ways to force a custom value as PK before saving an entity, unfortunately llblgen overwrites it by calling the generator. Could anyone confirm that it's impossible to prevent that?

Being able to force the PK is a requirement as I will have to import and consolidate data from many databases. If it isn't feasible, my possible choices are

-Coding it all manually (I would miss that nice feature of llblgen) at the end of validatebeforesave. -Creating two llblgen projects, which could lead to maintenance nightmares.

Don't really know what to do.

Do you need to import a lot of DIFFERENT entities or just a couple? If there are just a couple, you could opt for mapping a new entity onto the same table (say CustomerImport) and uncheck the checkbox for the PK that it's an identity field.

Still, this can lead to problems, as you have to switch the table into a mode that it accepts identity values in the insert query. This is a custom statement you have to execute on the connection before the insert, so you have to call a proc to do so.

Unfortunately there are quite a few, (approx 40).

Still, this can lead to problems, as you have to switch the table into a mode that it accepts identity values in the insert query.

Really.... Isn't it specific to sql server? I work with firebird and my primary key fields are of type INT64 or BIGINT. Specifying the primary key in the insert query is what llblgen currently does, it queries a generator and then use the value in its insert statement.

If I specify the primary key manually by affecting the property, the database would accept it for sure, but it's not possible because llblgen overwrites the value before executing the insert.

Would be glad if I found a solution, or an acceptable dirty hack.

stefcl wrote:

Unfortunately there are quite a few, (approx 40).

Still, this can lead to problems, as you have to switch the table into a mode that it accepts identity values in the insert query.

Really.... Isn't it specific to sql server? I work with firebird and my primary key fields are of type INT64 or BIGINT. Specifying the primary key in the insert query is what llblgen currently does, it queries a generator and then use the value in its insert statement.

Sorry, then indeed you don't have to worry about that.

If I specify the primary key manually by affecting the property, the database would accept it for sure, but it's not possible because llblgen overwrites the value before executing the insert.

Would be glad if I found a solution, or an acceptable dirty hack.

You use selfservicing, so it's a bit of a problem to derive a class from the Firebird DQE's DynamicQueryEngine class to override the insertquery routine.

What I would do is NOT making the fields identity fields, but leave them as normal fields. Then to obtain the value for the PK, you call a routine, for example an action stored procedure which obtains the new sequence value for the sequence specified. THen simply set the PK with that value.

This is safe as the sequence will never return the same value twice.

Thanks! As I thought, I'll have to handle the PK generation in my code. That's not that bad after all.

One last thing. What do you think about testing the PK value in the end of ValidateBeforeSave and then obtaining a sequence number if necessary?

stefcl wrote:

Thanks! As I thought, I'll have to handle the PK generation in my code. That's not that bad after all.

One last thing. What do you think about testing the PK value in the end of ValidateBeforeSave and then obtaining a sequence number if necessary?

You mean, in the case where the PK hasn't been set? Should work OK. the query hasn't been created at that point, so it should work

Yes with something like that

f( IsNew && PrimaryKeyField == null)
{
     PrimaryKeyField = GenerateNewID();
}

I'll go that way, thanks again