
BIIIIIIIG Problem using Oracle / ODBC / RDO
People,
I really need your help this time:
Situation:
An application I maintain uses RDO and VB5 to connect to an Oracle8 database
using ODBC. This program is used to register orders for a company in the
Netherlands.
How it works:
When a new order is to be written to the database, a new ordernr is
generated by a Oracle sequence on a table with 2 fields:
OrderNr
InsertToken.
The ordernr is generated by inserting a new record with as value a token
made of username, date and time. Oracle will than add the Ordernr making it
1 higher then the previous number.
The ordernr is then collected by doing a select on the table with this token
in the Where - clause. This works OK.
After this the order is written to the database also, with the collected
ordernr to identify it. This also works OK.
After this, a log table is used to insert the ordernr, the orderstage (order
entry), the date/time and the usernr. This also works OK.
Problem:
Everty now and again the program seems to have written the order ok, no
errors generated, but when the order is about to be retreived for further
processing, the order can not be found in the database. Also the ordernumber
in the sequence is missing and the logentry also has vanished. The ordernr 1
lower and higher are present, so it seems that the number has been generated
and used but somewhere at the end of the line the oracle server generated a
massive rollback undoing all transactions without notifying.
Has anybody a clue about what is going on here? There are no errors, nothing
seems to go wrong, but the order had vanished.
Any help is welcome.
Grtz,
C.