SQL Server, ODBC & Locking via Axiant

[Robert Edis]

G'day Glenn

I'd use embedded SQL to get the table.  On the SELECT statement you can
specify the locking.

I'd also split your statements and put them into PREUPDATE and POSTUPDATE
procedures while trying to keep the UPDATE procedure auto generated.  The
CONTROL_FILE will of DESIGNER type.

Blue




                                                                           
             powerh-l-request@                                             
             lists.sowder.com                                              
             Sent by:                                                   To 
             powerh-l-bounces+         powerh-l at lists.sowder.com           
             robert_edis=notes                                          cc 
             .ntrs.com at lists.s                                             
             owder.com                                             Subject 
                                       powerh-l Digest, Vol 13, Issue 12   
                                                                           
             06/15/2006 12:02                                              
             PM                                                            
                                                                           
                                                                           
             Please respond to                                             
             powerh-l at lists.so                                             
                 wder.com                                                  
                                                                           
                                                                           




Send powerh-l mailing list submissions to
             powerh-l at lists.sowder.com

To subscribe or unsubscribe via the World Wide Web, visit
             http://lists.sowder.com/mailman/listinfo/powerh-l
or, via email, send a message with subject or body 'help' to
             powerh-l-request at lists.sowder.com
You can reach the person managing the list at
             powerh-l-owner at lists.sowder.com

When replying, please edit your Subject line so it is more specific
than "Re: Contents of powerh-l digest..."


Today's Topics:

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


----------------------------------------------------------------------

Message: 1
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>

-------------- next part --------------
An HTML attachment was scrubbed...
URL:
http://lists.sowder.com/pipermail/powerh-l/attachments/20060615/00941e86/attachment-0001.html


------------------------------

--
= = = = = = = = = = = = = = = = = = = = = = = = = = = =
Mailing list: powerh-l at lists.sowder.com
Subscribe: &quot;subscribe&quot; in message body to
powerh-l-request at lists.sowder.com
Unsubscribe: &quot;unsubscribe &lt;password&gt;&quot; in message body to
powerh-l-request at lists.sowder.com
http://lists.sowder.com/mailman/listinfo/powerh-l
This list is closed, thus to post to the list you must be a subscriber.
Add 'site:lists.sowder.com powerh-l' to your search terms to search the
list archive at Google.

End of powerh-l Digest, Vol 13, Issue 12
****************************************