SQL Server, ODBC & Locking via Axiant (Glenn Baxter)

[Greig Morrison]

Glenn,

I have seen this done a couple of ways; either declaring a Cursor for
the Control file in Axiant (option A), or using a SQL CALL within a
procedure (option B). Using the SQL CALL frees you up from declaring the
file in Axiant and leaves the code to retrieve and update your sequence
number in the database.

;--------- OPTION A --------------

;Data Access Section
DECLARE NEXTREQNO CURSOR FOR &
SELECT MAX(REQ_NO) AS NEXT_REQUEST_NO FROM REQ_MASTER
CURSOR NEXTREQNO &
REFERENCE 


;Screen Procedure
PROCEDURE INTERNAL GET_REQ_NO
BEGIN
  FETCH NEXTREQNO 
  LET T_NEXT_NO = NEXT_REQ_NO + 1
  LET REQ_NO OF REQ_MASTER = T_NEXT_NO
END

;Note: This is using a MAX function on the actual Primary file/cursor
REQ_MASTER, but you could use a Control table on a separate Transaction.
You could then Update the Control table and Commit the Transaction.



--------- OPTION B --------------

-- Call a Database Stored Procedure, and have the stored procedure do
all the work.

--  SQL code for SQL Server  --

CREATE procedure next_no
@t_id integer, any_pass_parameter_here ...  , @t_next_no integer output

 as

select @t_next_no = (select  max(next_no)  from table_next where (id =
@t_id) )

if @t_next_no is null
  set @t_next_no = 1
else set @t_next_no = @t_next_no + 1

end


;Screen Procedure
PROCEDURE INTERNAL GET_REQ_NO
BEGIN
   SQL IN dbname CALL DBNAME."next_no" (x_id, any_pass_parameter_here
...) RETURNING t_next_no
   LET REQ_NO OF REQ_MASTER = t_next_no
END

;Note: The Stored Procedure is using the MAX function, just substitute
SQL to retrieve and update the Control
;      table.


Regards,
Greig
 
-----Original Message-----
Date: Thu, 15 Jun 2006 10:42:43 +1000
From: "Glenn Baxter" <Glenn at baxterit.com.au>
Subject: SQL Server, ODBC & Locking via Axiant
To: <powerh-l at lists.sowder.com>
Message-ID:
	
<E852308A7EA38646B451665346EA774003F08C at baxterit-c.BaxterITI.local>
Content-Type: text/plain; charset="us-ascii"

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>