Hello,

I've a table, and i want to make an alphanumeric sequence like this : DDDFFF000000001

With Oracle, it's pretty simple, a sequence and a trigger can do that :

CREATE SEQUENCE PK_SEQUENCE_DDD START WITH 1 INCREMENT BY 1 MAXVALUE 999999999;

CREATE TRIGGER DDDPKTRIGGER BEFORE INSERT ON MYTABLE FOR EACH ROW BEGIN IF :NEW.ID is null THEN SELECT 'DDDFFF'||LPAD(PK_SEQUENCE_DDD.NEXTVAL,9,'0') INTO :NEW.ID FROM DUAL; END IF; END;

The user must use the sequence when the ID is null or specify an ID himself.

So, i want to use this table with LLBLGEN Pro, but when i want to make a Save with refetch, ID is not known (out of sync on every fields ). And the designer solution who specify a sequence don't allow to adapt this sequence to make an alphanumeric sequence. If I'm lucky we can adapt the sequence in designer mode, but can we force the ID ?

So how can I retrieve my generated ID ?

thanks for help.

Sorry, for my english.

VB.NET + LLBLGEN Pro v2.0 + Oracle 9

Please have a look at thread. You will find it useful.

http://www.llblgen.com/tinyforum/Messages.aspx?ThreadID=3082&HighLight=1

So how can I retrieve my generated ID ?

Just read it back from entity after the save

If you need a sequenced field, make it a numeric field, otherwise set the PK field manually. C'est la vie.

Ok, If i want to create my sequence manually ... I would like to read my sequence like that : SELECT MYSEQUENCE.NEXTVAL FROM DUAL.

Is it possible to read it from LLBLGEN classes ?

please tell me yes ...

Create a small action stored procedure, say GET_MYSEQUENCE_NEXTVAL. It will return the new value in an out parameter.

Then you do: int newVal = 0; ActionProcedures.GetMysequenceNextval(ref newVal); CustomerEntity c = new CustomerEntity(); c.PKField = newVal; //...

Ach ... this answer, I expected it ... But, I have a constraint ... I can't create stored procedure ...

I know it's stupid but I'm not the DBA of the program, and i didn't choose anything about the database.

So can we make a simple SQL Statement ? Or can we use the db connection to put an SQL statement ?

Thx for your answer.

Then u can use a Getscalar() or a typedlist custom query with an aggregate, You may have a look in the manual for how to deal with that.

But the principle is the same, just fetch your max pk, and perform the increment in the code. It may not be very elegant by leaving the sequence away, but it shouldn't be that bad. The only thing is you need to apply optimistic persistence, it requires that you're the only one to deal with the dbase, in which case you should be able to track the sequence from the code without having to retrieve it every time.

But now if some other systems get to perform saves, then you should retrieve some "proof of consistency together with your pk", and add some concurrency support (there are some features supported by llblgen, see the manual reference), to make sure that they were not any inserts between your pk fetchs and your own inserts.

Basically you add a where conditional clause to your statements which checks for consistency.

Actually I'm thinking of something that might be feasible to keep up with a single optimistic query (Frans to say): Use a combination of an expression applied to your pk field with a comparesetpredicate to directly compute your sequence on the insert statement. You can do things similar which are documented in the manual (see "field expressions and aggregates"), but it might be just too demanding for this one.

Anyway, there are workarounds obviously.

I'have already thinking about this solution ... But if I read the max PK, I can't be sure that another user read it at the same time and when the both want create a new entry, one of them take an error.

The sequence is secure at this level. And I don't find in the doc how to make a simple SQL statement without create a stored procedure.

Is that right ? In this case, I tell to my client that it's impossible to keep is format of PK and secure the generated PK...

Well, I tried to anticipate both of your questions in my last post.

Concurrency control is something LLBLGen tries to deal with. It does not just relate to your situation. Basically, most architechture assume optimistic persistence, and there are many situation where two users editing the same bits of data at the same time may produce a crash.

Look for concurrency control in the manual and you 'll find a way to pass conditional filters to ensure data consistency.

then about simple sql statement: since the trigger ensures the sequence does not get out of sync you can rely on the max pk instead right? then retrieving the current max pk is something quite trivial: myAdapter.GetScalar(YourEntity.PkField, AggregateFunction.Max) might do the job

Then the last thing I was evoking though I'm not sure if that's realistic, is that you could try and run insert/update statements by applying a computing expression on your pkfield, which would do the increment on the fly.