Hi guys...

As many of you are probably now aware (due to all of you helping me!) I have been writing a new session. I am glad to say that with all your help it went very well, however I am having a few extra problems.

On the session I have set up a first free number scenario, on inserting a new record a first free number table is looked at and there fore a unique number assigned to the record.

When I copy a record so that all the user has to do is change 1 field , it will not work.

Here is the code that gets the first free number...
|**************** main table i/o section ***************

main.table.io:
before.write:
get.first.free.number()
before.rewrite:
get.first.free.number()

|**************** function section *******************

function get.first.free.number()
{
db.retry.point()
select tdsls900.*
from tdsls900 for update
where tdsls900.constant = 1
selectdo
tdsls901.bncdr = tdsls900.number + 1
tdsls900.number = tdsls900.number + 1
db.update(ttdsls900, db.retry)
commit.transaction()
endselect
}

**************************************************
The function works great on a new record, but doesn't if I copy the record, modify a copy of lines and then try to save it.
:confused:

Can anyone help?

Cheers

I'm not sure if this is going to help, but I used to experience difficulties when I used commit.transaction() in main.table.io section.

Try to skip it. Transactions are going to be commited anyway as a result of commiting changes to the main table. I think...

You are in the main.table.io section, where the db.retry point is set in the STP, also the commit.transaction is handled in the STP. So you should not call a function where you usedb.update(ttdsls900, db.retry)
commit.transaction()
endselect

See also: BAAN Tools Main Index (ttindex) > Programmer's Information (ttprog0005) > 4GL Language Features (ttprog0015) > [Relations] > Flow of Standard Program (ttprog0015sf0020) > * Update Database:before.update.db
db.retry.point()
for all occurrences
before.write / before.rewrite / before.delete
if not skip then
write / rewrite / delete record
after.write / after.rewrite / after.delete
else
after.skip.write / after.skip.rewrite / after.skip.delete
endif
endfor
back.to.old.key
after.update.db
commit.transaction()
after.update.db.commit
So in that case the before.update.db section might fit.

Hi thanks for this,

I am not too sure what u mean....

So where should I use before.update.db section and in place of what?


Cheers

What I meant:
if you need to use the commit.transaction then you need to keep your transaction outside of the transaction in the main.table.io section.

But you do not want to store a number and later run the risk that that stored number is never going to be used (because of the main.table.io action aborts for example).

So in that case use the transaction of the main.table.io.

Also. Always keep the db.retry() and commit.transaction() in the same level. This means: no commit.transaction in a function without a db.retry point and vv. If you later decide to call a function twice or in another setting your (halfway) transaction may mess up things. A good programming practice is (in case you program a db.retry and commit in a function) to show this in the name of the function. You can use any fancy coding, but stick to the code.function commit.ffno.selection()
{
db.retry.point()
...
db.update(..., db.retry)
...
commit.transaction()
}

Im sorry if I sound stupid, but if this is my code, how am I changing it?
|********** main table i/o section ****************

main.table.io:
before.write:
get.first.free.number()

|*********** function section ******************

function get.first.free.number()
{
db.retry.point()
select tdsls900.*
from tdsls900 for update
where tdsls900.constant = 1
selectdo
tdsls901.bncdr = tdsls900.number + 1
tdsls900.number = tdsls900.number + 1
db.update(ttdsls900, db.retry)
commit.transaction()
endselect
}
Could you show me where I am making my changes?

should I be using this instead.....
|********** main table i/o section ****************

main.table.io:
before.write:
db.retry.point()
select tdsls900.*
from tdsls900 for update
where tdsls900.constant = 1
selectdo
tdsls901.bncdr = tdsls900.number + 1
tdsls900.number = tdsls900.number + 1
db.update(ttdsls900, db.retry)
commit.transaction()
endselect

**********************************************
Cheers

before.update.db:
db.retry.point() | from STP: it is not in your script but added by std_gen6.1
for all occurrences
before.write:
get.first.free.number() =
db.retry.point()
select tdsls900.* ...
db.update(ttdsls900, db.retry)
commit.transaction()
before.rewrite:
get.first.free.number() =
db.retry.point()
select tdsls900.* ...
db.update(ttdsls900, db.retry)
commit.transaction()
before.delete:
if not skip then
write / rewrite / delete record
after.write: / after.rewrite: / after.delete:
else
after.skip.write: / after.skip.rewrite: / after.skip.delete:
endif
endfor
back.to.old.key
after.update.db:
commit.transaction() | from STP: it is not in your script but added by std_gen6.1
after.update.db.commit:This is what you propose: but in that case:
what is going to be committed in your commit.transaction() (in the function).
Where to jump to when the write / rewrite fails? There are 2 db.retry.point()s. What is committed then?

Proposal would be: skip the commit.transaction and the db.retry.point in your function.

At first I thought you only used a commit.transaction() in the function without db.retry.point() which is not correct...
even then you can imagine that your function as is leaves an open transaction when there is no record found... then the commit.transaction is not passed. You can expect errors like "Transaction is on" to happen...

Errors with nested retry.points and commit.transactions can be shown when tested in debug (Capital C) and started with -- -set TEST_RETRY=2. In that case the program will start looping. With capital C you can see that happen.

the db.retry and commit.transaction are for the first free number table, basically it is so that if another uses accesses that table without the commit.transaction happening from the first use then it stops there being a clash of the first free number by the 2 users.

I don't think I should remove these unless what you have proposed covers this.

Cheers

Also...

the code that yyou have proposed should I be putting this in the main table i/o section?

Let's think some senarios over:

1) When this update on the main table fails, do you want to loose a ffno, yes or no?
- If yes, then you may use the db.retry.point / commit.transaction construction in the function BUT see A) below
- If no, then you may not use the db.retry.point / commit.transaction construction in the function AND see B) below

2) When two users access the same table at the same time, what does happen? You did select the "for update" option, so you use delayed locking. So if a read is done after the db.update, and someone else tries to do the same session, also performs a db.update, he will encounter a lock and his transaction will be rolled back. So no problem there, but also see A) below

A) When you test your session with -- -set TEST_RETRY=2, then all actions that may cause the database to jump to the db.retry.point(). Now in the main.table.io section a hidden (shown in the Flow of STP) db.retry.point is passed. In your function a second one is passed. When something in between is updated (in the STP or in your session) then TEST_RETRY will force a go to db.retry.point(1) in STP. The second time it will do the same, the third time it will pass (TEST_RETRY=2), then your action will occurr. And again TEST_RETRY=2 will force to jump to the first db.retry.point(1) of STP. Then the first database action will be done twice and then the update un your function will trigger a fail again. Again it will jump to the db.retry.point(1) etc... a loop.

Resume: constructions with nested transactions is asking for problems. Sometimes it is no problem, because of no transactions are done in between. But better: avoid problems.

When in the main.table.io section separate transactions are needed, you must do them in the before.update.db section. There you are not in the main transaction from STP. You can consider another separate transaction in the after.update.db.commit section, to revert the action in the before.update.db section in case it should not have been done. This will go wrong if some one else did pick a number in between....

B) The best option is to just use the single transaction of the main transaction. This will allow you to pick a number when the transaction succeeds, and to roll back in case something fails. In that case all is like as if it never happened.

It is complex matter, this transaction handling. And even some Baan sessions cannot run correct with the TEST_RETRY=2 option, because of nested transactions (also think of the "Transaction is on" error - search the knowledge base on it,...:p Keyword search on "transaction is on" yields 130 hits!!!).

The most impotant message here:
1) Never nest transactions
2) There is a huge transaction going on around all the main table io sections except for the before.update.db and the after.update.db.commit sections

Lesser important ones:
3) Keep start / end transactions on the same level
4) Name of functions with a commit should show this
5) NO user interaction or any other thinks (like messaging) in the transaction itself. Keep the transactions short in time.

There is more to the picture, but that is for the course advanced programming. You can opt for that - see www.profuse.nl :D

Hi

Thank you for that. If I am honest, I had the Baan development 3 day course 2 weeks ago, and being a web developer, this is where my scripting knowledge comes from.

I think that what you have explained goes a little deeper than I am yet ready to dive to....

I have made some changes and it is now working, here is the code for your interest...
|************field section**************
field.tdsls901.bncdr:
before.input:
if update.status = add.set then
attr.input = false
endif
check.input:
check.first.free.number()

|***********function section************
functions:

function check.first.free.number()
{
db.retry.point()
select tdsls901.bncdr
from tdsls901
where tdsls901.bncdr = :tdsls901.bncdr
selectdo
tdsls901.bncdr = tdsls900.number + 1
tdsls900.number = tdsls900.number + 1
commit.transaction()
endselect

}

************************************
This seems to be doing the trick, I think it goes against what you have advised, but maybe my lack of experiece is stopping me from fully understanding.

What you have said about nested transactions makes sense, but to be honest I am not sure how else I could go about it.

How does the code that is working look to you?


Thank you for all your help on this.

Cheers

Ok then,

Some final remarks.
1) If you input on the tdsls901.bncdr, change the input each time again, you will loose free numbers. If you do not mind, it is ok.
2) If you left your original coding but leave the db.retry.point() and the commit.transaction() out, it would have done the trick and even better. Because then you would only give a ffno when needed and others would not pick the same number.
3) Sorry for being too much unclear sometimes. Transaction management is difficult and hard to explain. That is to say, if you must explain it clear enough for a dummy (not to offend you) too.
4) I hope some others enjoyed the thread too and now understand where this "Transaction is on, can't continue" message came from... At least klesch now knows why this commit in the main.table.io section causes trouble.

as for me, you may close the thread.

I think I understand what you mean, the user cannot change the .bncdr field, it is automatic, so there should be no number loose as such.

In other words do you mean that if the user picks the wrong number a free number can become unusable?

In that case, I can see what you mean by not using the db.retry or transaction.commit because the update of the numbers is done on the field.


I hope that other users like me - fairly new to baan scripting - have a look at this post because it has been very helpfull, it takes a bit of close reading to get your head round though, I guess as it comes up more it becomes second nature eventually.

Thank you for all your help it has been very usefull.

Cheers:p

Take a glance at the "normal" scripts to see how the tcmcs047 table is handled...

If you do not have sources... try this: do make a .../ptdpur/ppur41200 file and a .../ptcmcs/pmcs01470 file (<> 0 bytes) before you install updates on those sessions....

will this give me access to those scripts even i do not have source then?

Just try and see what happens :D
If you have doubts... read the section about sources in the SP installation booklet.

wicked

well thanks for all your help, no doubt I shall speak to you again in the future

cheers