I've written some code that is supposed to move records from a table (tivkm001) in one company (112) to the same table in another company (114). However, what it does is place the records back in the same company (112) it just deleted them from.
Any ideas? Here's the code...


choice.user.4:
after.choice:

project = tivkm001.orno

db.retry.point()
select tivkm001.*
from tivkm001 for update
where tivkm001.orno = :project and
tivkm001._compnr = 114
selectdo
db.delete(ttivkm001, db.retry)
commit.transaction()
endselect

location.f = " 0"
location.t = " 600"

db.retry.point()
select tivkm001.*
from tivkm001 for update
where tivkm001.orno = :project and
(tivkm001.loca inrange :location.f and :location.t or
tivkm001.loca = " 1010" or
tivkm001.loca = " 1020") and
tivkm001._compnr = 112
selectdo
db.delete(ttivkm001, db.retry)
commit.transaction()

a.code = tivkm001.code

db.retry.point()
select tivkm001.*
from tivkm001
where tivkm001._index1 = {:project, :a.code} and
tivkm001._compnr = 114
selectdo
db.insert(ttivkm001, db.retry)
commit.transaction()
selectempty
db.insert(ttivkm001, db.retry)
commit.transaction()
endselect
selectempty
endselect

Try to use the function switch.to.company or check.compnr instead of using just the clause "_compnr = ".

http://www.baanboard.com/programmers_manual_baanerp_help_functions_company_operations_compnr_check

I tried switch.to.company() but got the same result. I've never had a problem with the _compnr before... it's really perplexing

add the following line before the insert and that should do the trick:
tivkm001._compnr = 114

That was the answer. Thanks for the help.

Evertsen:
choice.user.4:
after.choice:

project = tivkm001.orno

db.retry.point()
...
commit.transaction()
...
db.retry.point()
...
commit.transaction()
...
db.retry.point()
...
commit.transaction()
...
...
commit.transaction()
...
Are you afraid of not performing these actions? What will happen with your data when the second commit fails? Or the third? In my opinion it would be better to put all into one transaction to be sure that if it fails somewhere along the line, your situation is restored like it was before (that is where you use the option db.retry in your deletes and inserts for.

What you say makes sense but I'm not sure how to code it all into one transaction...

Start that section with a db.retry.point() and end with a commit.transaction(). So replace all intermediate commits and retry points with one set that spans the whole action.

After a little reorganization, I got it to work... thanks for your help.

In general: commits and retry points (and abort transactions) are performance costly. Try to limit their use.

Test the use of retry points with -- set TEST_RETRY=2 (you can do this in the debugger once to take good notice of all variables).

Note that when you set a variable outside the transaction and you change this variable within the transaction and then write it to the database, when on a retry point, the variable is changed...

a = 10
db.retry point
a = a+1
insert a into database
commit

run program:
a = 10
a = 10 + 1 = 11
commit succeeds
a = 11 is inserted

run program with TEST_RETRY=2
a = 10
db.retry.point
a = 10 + 1 = 11
insert prepared
commit fails (1)
jump to db.retry.point
a = 11 + 1 = 12
insert prepared
commit fails (2)
jump to db.retry.point
a = 12 + 1 = 13
insert prepared
commit succeeds
a = 13 is inserted.

sounds like good advice...I'll put it into practice.