SQL Server, ODBC & Locking via Axiant

[Glenn Baxter]

Our Environment Details
- Powerhouse Version 8.41D1
- Axiant 3.41D1 IDE and Thin Client Deployment
- Nt Server 2003 with Powerhouse, NETD and Sql Server 2000
- Nt Server 2003 Domain Controller
 
We have a screen with a primary record, lets call it ORDER_DETAIL, it
has a TOTAL_QUANTITY field.
The Record is identified by a ORDER_ID which is obtained from a
CONTROL_FILE fields, NEXT_ORDER_ID
 
The object is to find a primary record and SPLIT (make multiple records)
of one TOTAL_QUANTITY.
 
The code is being migrated from a Hp3000 code base.
 
The original code has a hand built UPDATE procedure which in short
 
- Gets the CONTROL_FILE and NEXT_ORDER_ID field value
- Locks the CONTROL_FILE
- does a series of puts on the ORDER_DETAIL, initialising the ORDER_ID
value from the CONTROL_FILE, finally
- Ascending the NEXT_ORDER_ID
- PUTting the CONTROL_FILE
- releasing the LOCK then
- running a Subscreen to display the new records.
 
In this way, 2 or more users can perform the same task and get unique
NEXT_ORDER_ID values each time.
 
Using SQL via ODBC we have no lock. The default Concurrency mode behaves
correctly but only reports that 
 
"this record has changed since you found it etc" referring to the
CONTROL_FILE.
 
I have done some reading from the Powerhouse and Relational Databases
PDF which would suggests that the Consistency model would be a better
choice. The books I have are for version 8.3 and have little/no
reference to SQLServer/ODBC database use.
 
In short; I am seeking some advice regarding best practice under similar
circumstances in order to restrict "this record has changed..." and
avoid locking the database..(uncontrollably!) 
 
Thanks in Advance..
Glenn Baxter
______________
Baxter IT Solutions
e: glenn at baxterit.com.au <blocked::mailto:glenn at baxterit.com.au> 
 
-------------- next part --------------
An HTML attachment was scrubbed...
URL: http://lists.sowder.com/pipermail/powerh-l/attachments/20060615/00941e86/attachment.html