Sybase NNTP forums - End Of Life (EOL)

The NNTP forums from Sybase - forums.sybase.com - are now closed.

All new questions should be directed to the appropriate forum at the SAP Community Network (SCN).

Individual products have links to the respective forums on SCN, or you can go to SCN and search for your product in the search box (upper right corner) to find your specific developer center.

Record locks

2 posts in Visual Basic Last posting was on 2005-01-13 22:43:38.0Z
Tom Snider Posted on 2005-01-13 21:55:47.0Z
Reply-To: "Tom Snider" <thomasgsnider@sbcglobal.net>
From: "Tom Snider" <thomasgsnider@sbcglobal.net>
Newsgroups: advantage.visual_basic
Subject: Record locks
Date: Thu, 13 Jan 2005 15:55:47 -0600
Lines: 21
Organization: Tom Snider Software
X-Priority: 3
X-MSMail-Priority: Normal
X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
NNTP-Posting-Host: 69.153.60.199
Message-ID: <41e6ec9f@solutions.advantagedatabase.com>
X-Trace: 13 Jan 2005 14:48:15 -0700, 69.153.60.199
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!69.153.60.199
Xref: solutions.advantagedatabase.com Advantage.Visual_Basic:924
Article PK: 1137717

I am using VB6 with ADS 7.1. I have an standard form for editing certain
tables. When the user indicates that he/she wants to 'edit' the current
record, I *lock* the record using ACE record locking function, then when the
user decides to 'Save' the record, I issue an Update SQL statement that
updates only the columns that were changed. My problem is that the Update
statement gets a 5035 Lock Failed error because of the lock that I placed on
that record. I would think that, since I'm the same process, that the SQL
Update would understand and go ahead with the update.

Is there something I'm missing? My intention is to prevent other users from
changing the 'record being edited' by placing the lock on it. Obviously, I
can check for a 'lock' before executing my Update SQL statement, and
'unlock' it if necessary...but I'm hoping for a better approach. How is
everybody else dealing with this situation?

I will appreciate any direction.

Tom Snider
San Antonio, Texas


Mark Wilkins Posted on 2005-01-13 22:43:38.0Z
Message-ID: <8162632412275732835118@solutions.advantagedatabase.com>
From: Mark Wilkins <mwilkins@extendsys.com>
Subject: Re: Record locks
Newsgroups: Advantage.Visual_Basic
References: <41e6ec9f@solutions.advantagedatabase.com>
Content-Type: text/plain; charset=iso-8859-1; format=flowed
X-Newsreader: JetBrains Omea Reader 381.17
NNTP-Posting-Host: 198.102.102.86
Date: 13 Jan 2005 15:43:38 -0700
X-Trace: 13 Jan 2005 15:43:38 -0700, 198.102.102.86
Lines: 45
Path: solutions.advantagedatabase.com!solutions.advantagedatabase.com!198.102.102.86
Xref: solutions.advantagedatabase.com Advantage.Visual_Basic:926
Article PK: 1137721

Hi Tom,

That behavior is by design. If it were allowed, then it would essentially
be providing the ability for two independent instances of the table to change
the record. The SQL statement runs at the server and essentially opens its
own instance of the table, locks the record, and updates the record. If
Advantage allowed SQL to lock the record because the existing lock is owned
by the same user, it would lead to potential conflict. The following sequence
of psuedo-code attempts to illustrate it:

AdsOpenTable( "T", &hTable );
AdsLockRecord( hTable, 1 );
AdsSetField( hTable, "F", "abc" );
execute sql: UPDATE T set F = 'xyz'
AdsUnlockRecord( hTable, 1 );

The result would be that "abc" would be in field F in record 1 even though
the SQL statement set the value to "xyz". The unlock call would flush any
changes made to the client's table instance.

Mark Wilkins
Advantage R&D

> I am using VB6 with ADS 7.1. I have an standard form for editing
> certain tables. When the user indicates that he/she wants to 'edit'
> the current record, I *lock* the record using ACE record locking
> function, then when the user decides to 'Save' the record, I issue an
> Update SQL statement that updates only the columns that were changed.
> My problem is that the Update statement gets a 5035 Lock Failed error
> because of the lock that I placed on that record. I would think that,
> since I'm the same process, that the SQL Update would understand and
> go ahead with the update.
>
> Is there something I'm missing? My intention is to prevent other users
> from changing the 'record being edited' by placing the lock on it.
> Obviously, I can check for a 'lock' before executing my Update SQL
> statement, and 'unlock' it if necessary...but I'm hoping for a better
> approach. How is everybody else dealing with this situation?
>
> I will appreciate any direction.
>
> Tom Snider
> San Antonio, Texas