Application objective : input unique serial numbers through a temporary file using a blank form 2 and update serial number file.

Checking the threads I found that I musn't have any retry.point or commit.transaction in my update function ( managed by the std pgm ), but I still have the " transaction is on " message.

attached pgm script summary

I don't know where you picked up that you shouldn't need a commit/abort.transaction, but since you insert a new record into a baan-table, you do need it...

Application objective : input unique serial numbers through a temporary file using a blank form 2 and update serial number file.

Checking the threads I found that I musn't have any retry.point or commit.transaction in my update function ( managed by the std pgm ), but I still have the " transaction is on " message.

attached pgm script summary

|******************************************************************************
|* tssma9511 0 VRC B40C c4 expl
|* Saisie des numéros de Série
|* Noel LEBERT Resp Baan
|* 2007-02-01
|******************************************************************************
|* Main table tssma959 Fichier temporaire, Form Type 2
|******************************************************************************

|****************************** declaration section ***************************
declaration:
table ttssma959 | Input file
table ttssma951 | serial number

extern domain tcitem prog.item | item

|****************************** Program section *******************************
before.program:
import("prog.item", prog.item)

|****************************** field section *********************************
field.tssma959.sern:
when.field.changes:
if sn_exist()
then input.again()
endif
|****************************** function section ******************************
functions:
function domain tcbool sn_exist()
{

db.retry.point()

domain tcbool ret

SELECT tssma951.*
FROM tssma951 for update
WHERE tssma951._index1 = {:tssma959.sern, :prog.item}
AS SET WITH 1 ROWS
SELECTDO
message("Serial number exists")
ret = true
SELECTEMPTY
tssma951.item = prog.item
tssma951.sern = tssma959.sern
db.insert(ttssma951, db.retry)
commit.transaction()
ret = false
message("mise a jour")
ENDSELECT
commit.transaction()
return(ret)
}
|*******************************************************************************

Thanks for your quick answers

I took my information in thread : commit only in library

When I apply the suggestion, I get an error message 206 ( Record is not locked ) on main table when saving main form.

As explained in thread reply that's std pgm will run an commit.transaction that as already been runned through the function (That I feel logical).
It's the reason I tried don't to use retry.point or commit in function .

Have you an other other idea ?

Try to use only one commit.transaction()

|****************************** field section *********************************
field.tssma959.sern:
when.field.changes:
if sn_exist()
then input.again()
endif
|****************************** function section ******************************
functions:
function domain tcbool sn_exist()
{

db.retry.point()

domain tcbool ret

SELECT tssma951.*
FROM tssma951 for update
WHERE tssma951._index1 = {:tssma959.sern, rog.item}
AS SET WITH 1 ROWS
SELECTDO
message("Serial number exists")
ret = true
SELECTEMPTY
tssma951.item = prog.item
tssma951.sern = tssma959.sern
db.insert(ttssma951, db.retry)
ret = false
message("mise a jour")
ENDSELECT
commit.transaction()
return(ret)
}


I think it should work !
Good Luck !

Hi there,

a superfluous commit shouldn't be the problem, I rather guess that you forgot to code "for update" in your select statement (that's what triggers the lock :-)

hth

Norbert

Hi,

I am not sure but just and idea, It seems that the concerning form is not running directly as a main form but calling from the other form through the programing, and there a transaction is already started, so in that case there is not need to call retry.point of commit.transaction. The update in the main table of this form should be as part of already started transaction.

MRF

Finally I found the following solution that works perfectly.

Instead of invoking a function ( or DLL ), I invoke a session ( script 3GL without stand pgm ) that check and update the secondary table (called through a zoom.to$ - z.session).
This avoids any conflict with db.retry and commit transaction.
Thanks every body for your help