Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]


Groups > comp.lang.clipper.visual-objects > #13629 > unrolled thread

Vo2Ado and multiuser environment locks

Started bySebastián BASSANO <ccyasa.sbassano@gmail.com>
First post2022-08-26 04:58 -0700
Last post2022-08-31 05:54 -0700
Articles 10 — 4 participants

Back to article view | Back to comp.lang.clipper.visual-objects


Contents

  Vo2Ado and multiuser environment locks Sebastián BASSANO <ccyasa.sbassano@gmail.com> - 2022-08-26 04:58 -0700
    Re: Vo2Ado and multiuser environment locks Jamal <nospam@example.com> - 2022-08-26 13:57 -0400
      Re: Vo2Ado and multiuser environment locks Sebastián BASSANO <ccyasa.sbassano@gmail.com> - 2022-08-26 11:10 -0700
        Re: Vo2Ado and multiuser environment locks Jamal <nospam@example.com> - 2022-08-26 15:11 -0400
          Re: Vo2Ado and multiuser environment locks Sebastián BASSANO <ccyasa.sbassano@gmail.com> - 2022-08-26 12:16 -0700
        Re: Vo2Ado and multiuser environment locks JohnMartens <martens@tennishulp.info> - 2022-08-26 21:23 +0200
          Re: Vo2Ado and multiuser environment locks Sebastián BASSANO <ccyasa.sbassano@gmail.com> - 2022-08-26 12:41 -0700
            Re: Vo2Ado and multiuser environment locks JohnMartens <martens@tennishulp.info> - 2022-08-27 08:25 +0200
    Re: Vo2Ado and multiuser environment locks "Wolfgang Riedmann" <wriedmann@gmail.com> - 2022-08-31 09:51 +0200
      Re: Vo2Ado and multiuser environment locks Sebastián BASSANO <ccyasa.sbassano@gmail.com> - 2022-08-31 05:54 -0700

#13629 — Vo2Ado and multiuser environment locks

FromSebastián BASSANO <ccyasa.sbassano@gmail.com>
Date2022-08-26 04:58 -0700
SubjectVo2Ado and multiuser environment locks
Message-ID<ab8087ef-f208-435b-98bf-407ce70e7a4an@googlegroups.com>
Hi, i have an operation that needs to authorize a document via a third party web server and then update a table on my database. The process works fine but i need to add multiuser support, means that when an user is doing that operation the other users can't. I work with Vo2Ado.
Today the code (simplified, without the BEGIN SEQUENCE/RECOVER USING code block) is similar to this:


// Get third party authorization code via web service
cAuthorizationCode:= oWebService:GetAuthorization(...)

// Update table
oConnection:BeginTrans()
cCommand:= "UPDATE document SET authorizationCode = " + cAuthorizationCode + " WHERE (id = " + NTrim(cId) + ")"
oConnection:Execute(cCommand, @liAffectedRecords, NIL)
if (liAffectedRecords > 0)
	oConnection:CommitTrans()
ELSE
	oConnection:Rollback()
ENDIF


This code doesn't work on multiuser environment because the record lock is applied on line:

oConnection:Execute(cCommand, @liAffectedRecords, NIL)

and at this point two or more users could get more authorization codes because the third party web service don't receive any local information to test if the authorization code already exists. I need to lock the record before start the communication with web service.
I understand that i can't lock records explicitly as with DbServer:LockCurrentRecord ¿is this correct?
If i'm correct ¿what would be the correct way to achieve this?

Maybe something this? I'm not convinced...


oConnection:BeginTrans()

// Force to lock the record updating value with the actual value
cCommand:= "UPDATE document SET authorizationCode = authorizationCode + " WHERE (id = " + NTrim(cId) + ")"
oConnection:Execute(cCommand, @liAffectedRecords, NIL)

if (liAffectedRecords > 0)
	// Get third party authorization code via web service
	cAuthorizationCode:= oWebService:GetAuthorization(...)

	// Update table
	cCommand:= "UPDATE document SET authorizationCode = " + cAuthorizationCode + " WHERE (id = " + NTrim(cId) + ")"
	oConnection:Execute(cCommand, @liAffectedRecords, NIL)
	oConnection:CommitTrans()
ELSE
	oConnection:Rollback()
ENDIF


I appreciate some help.
Regards.

Sebastián

[toc] | [next] | [standalone]


#13630

FromJamal <nospam@example.com>
Date2022-08-26 13:57 -0400
Message-ID<teb1j0$15nd$1@gioia.aioe.org>
In reply to#13629
Try adding:

WITH (ROWLOCK)

to your SQL UPDATE statement.

"UPDATE document SET authorizationCode = " + cAuthorizationCode + " WITH 
(ROWLOCK) WHERE (id = " + NTrim(cId) + ")"

Jamal

On 8/26/2022 7:58 AM, Sebastián BASSANO wrote:
> Hi, i have an operation that needs to authorize a document via a third party web server and then update a table on my database. The process works fine but i need to add multiuser support, means that when an user is doing that operation the other users can't. I work with Vo2Ado.
> Today the code (simplified, without the BEGIN SEQUENCE/RECOVER USING code block) is similar to this:
> 
> 
> // Get third party authorization code via web service
> cAuthorizationCode:= oWebService:GetAuthorization(...)
> 
> // Update table
> oConnection:BeginTrans()
> cCommand:= "UPDATE document SET authorizationCode = " + cAuthorizationCode + " WHERE (id = " + NTrim(cId) + ")"
> oConnection:Execute(cCommand, @liAffectedRecords, NIL)
> if (liAffectedRecords > 0)
> 	oConnection:CommitTrans()
> ELSE
> 	oConnection:Rollback()
> ENDIF
> 
> 
> This code doesn't work on multiuser environment because the record lock is applied on line:
> 
> oConnection:Execute(cCommand, @liAffectedRecords, NIL)
> 
> and at this point two or more users could get more authorization codes because the third party web service don't receive any local information to test if the authorization code already exists. I need to lock the record before start the communication with web service.
> I understand that i can't lock records explicitly as with DbServer:LockCurrentRecord ¿is this correct?
> If i'm correct ¿what would be the correct way to achieve this?
> 
> Maybe something this? I'm not convinced...
> 
> 
> oConnection:BeginTrans()
> 
> // Force to lock the record updating value with the actual value
> cCommand:= "UPDATE document SET authorizationCode = authorizationCode + " WHERE (id = " + NTrim(cId) + ")"
> oConnection:Execute(cCommand, @liAffectedRecords, NIL)
> 
> if (liAffectedRecords > 0)
> 	// Get third party authorization code via web service
> 	cAuthorizationCode:= oWebService:GetAuthorization(...)
> 
> 	// Update table
> 	cCommand:= "UPDATE document SET authorizationCode = " + cAuthorizationCode + " WHERE (id = " + NTrim(cId) + ")"
> 	oConnection:Execute(cCommand, @liAffectedRecords, NIL)
> 	oConnection:CommitTrans()
> ELSE
> 	oConnection:Rollback()
> ENDIF
> 
> 
> I appreciate some help.
> Regards.
> 
> Sebastián

[toc] | [prev] | [next] | [standalone]


#13631

FromSebastián BASSANO <ccyasa.sbassano@gmail.com>
Date2022-08-26 11:10 -0700
Message-ID<d68c452b-73b6-44ca-9c9a-f711e18018f3n@googlegroups.com>
In reply to#13630
El Friday, August 26, 2022 a la(s) 2:57:55 PM UTC-3, Jamal escribió:
> Try adding: 
> 
> WITH (ROWLOCK) 
> 
> to your SQL UPDATE statement. 
> 
> "UPDATE document SET authorizationCode = " + cAuthorizationCode + " WITH 
> (ROWLOCK) WHERE (id = " + NTrim(cId) + ")" 
> 
> Jamal
> On 8/26/2022 7:58 AM, Sebastián BASSANO wrote: 
> > Hi, i have an operation that needs to authorize a document via a third party web server and then update a table on my database. The process works fine but i need to add multiuser support, means that when an user is doing that operation the other users can't. I work with Vo2Ado. 
> > Today the code (simplified, without the BEGIN SEQUENCE/RECOVER USING code block) is similar to this: 
> > 
> > 
> > // Get third party authorization code via web service 
> > cAuthorizationCode:= oWebService:GetAuthorization(...) 
> > 
> > // Update table 
> > oConnection:BeginTrans() 
> > cCommand:= "UPDATE document SET authorizationCode = " + cAuthorizationCode + " WHERE (id = " + NTrim(cId) + ")" 
> > oConnection:Execute(cCommand, @liAffectedRecords, NIL) 
> > if (liAffectedRecords > 0) 
> > oConnection:CommitTrans() 
> > ELSE 
> > oConnection:Rollback() 
> > ENDIF 
> > 
> > 
> > This code doesn't work on multiuser environment because the record lock is applied on line: 
> > 
> > oConnection:Execute(cCommand, @liAffectedRecords, NIL) 
> > 
> > and at this point two or more users could get more authorization codes because the third party web service don't receive any local information to test if the authorization code already exists. I need to lock the record before start the communication with web service. 
> > I understand that i can't lock records explicitly as with DbServer:LockCurrentRecord ¿is this correct? 
> > If i'm correct ¿what would be the correct way to achieve this? 
> > 
> > Maybe something this? I'm not convinced... 
> > 
> > 
> > oConnection:BeginTrans() 
> > 
> > // Force to lock the record updating value with the actual value 
> > cCommand:= "UPDATE document SET authorizationCode = authorizationCode + " WHERE (id = " + NTrim(cId) + ")" 
> > oConnection:Execute(cCommand, @liAffectedRecords, NIL) 
> > 
> > if (liAffectedRecords > 0) 
> > // Get third party authorization code via web service 
> > cAuthorizationCode:= oWebService:GetAuthorization(...) 
> > 
> > // Update table 
> > cCommand:= "UPDATE document SET authorizationCode = " + cAuthorizationCode + " WHERE (id = " + NTrim(cId) + ")" 
> > oConnection:Execute(cCommand, @liAffectedRecords, NIL) 
> > oConnection:CommitTrans() 
> > ELSE 
> > oConnection:Rollback() 
> > ENDIF 
> > 
> > 
> > I appreciate some help. 
> > Regards. 
> > 
> > Sebastián
Hi Jamal, thanks for your response.
the problem is that when a user reaches that line of code in the third party web service an authorization code has already been managed.

An example:

Time1 - User 1 open associated dialog to get authorization code on PC1
Time2 - User 2 open associated dialog to get authorization code on PC2
Time3 - User 1 get authorization code (before this i have to lock to avoid that other used get authorization code)
Time4 - User 1 update authorizarion code on database
Time5 - User 2 get authorization code
Time6 - User 2 update authorizarion code on database

I hope my problem is better understood, English is not my native language.
Regards.

[toc] | [prev] | [next] | [standalone]


#13632

FromJamal <nospam@example.com>
Date2022-08-26 15:11 -0400
Message-ID<teb5sl$10id$1@gioia.aioe.org>
In reply to#13631
May be you can use the same approach when you SELECT the row and before 
you get the authorization, then update the row.

If this does not work, try creating another table that indicates that 
you're in an update process and handle your update process accordingly.

Jamal


On 8/26/2022 2:10 PM, Sebastián BASSANO wrote:
> El Friday, August 26, 2022 a la(s) 2:57:55 PM UTC-3, Jamal escribió:
>> Try adding:
>>
>> WITH (ROWLOCK)
>>
>> to your SQL UPDATE statement.
>>
>> "UPDATE document SET authorizationCode = " + cAuthorizationCode + " WITH
>> (ROWLOCK) WHERE (id = " + NTrim(cId) + ")"
>>
>> Jamal
>> On 8/26/2022 7:58 AM, Sebastián BASSANO wrote:
>>> Hi, i have an operation that needs to authorize a document via a third party web server and then update a table on my database. The process works fine but i need to add multiuser support, means that when an user is doing that operation the other users can't. I work with Vo2Ado.
>>> Today the code (simplified, without the BEGIN SEQUENCE/RECOVER USING code block) is similar to this:
>>>
>>>
>>> // Get third party authorization code via web service
>>> cAuthorizationCode:= oWebService:GetAuthorization(...)
>>>
>>> // Update table
>>> oConnection:BeginTrans()
>>> cCommand:= "UPDATE document SET authorizationCode = " + cAuthorizationCode + " WHERE (id = " + NTrim(cId) + ")"
>>> oConnection:Execute(cCommand, @liAffectedRecords, NIL)
>>> if (liAffectedRecords > 0)
>>> oConnection:CommitTrans()
>>> ELSE
>>> oConnection:Rollback()
>>> ENDIF
>>>
>>>
>>> This code doesn't work on multiuser environment because the record lock is applied on line:
>>>
>>> oConnection:Execute(cCommand, @liAffectedRecords, NIL)
>>>
>>> and at this point two or more users could get more authorization codes because the third party web service don't receive any local information to test if the authorization code already exists. I need to lock the record before start the communication with web service.
>>> I understand that i can't lock records explicitly as with DbServer:LockCurrentRecord ¿is this correct?
>>> If i'm correct ¿what would be the correct way to achieve this?
>>>
>>> Maybe something this? I'm not convinced...
>>>
>>>
>>> oConnection:BeginTrans()
>>>
>>> // Force to lock the record updating value with the actual value
>>> cCommand:= "UPDATE document SET authorizationCode = authorizationCode + " WHERE (id = " + NTrim(cId) + ")"
>>> oConnection:Execute(cCommand, @liAffectedRecords, NIL)
>>>
>>> if (liAffectedRecords > 0)
>>> // Get third party authorization code via web service
>>> cAuthorizationCode:= oWebService:GetAuthorization(...)
>>>
>>> // Update table
>>> cCommand:= "UPDATE document SET authorizationCode = " + cAuthorizationCode + " WHERE (id = " + NTrim(cId) + ")"
>>> oConnection:Execute(cCommand, @liAffectedRecords, NIL)
>>> oConnection:CommitTrans()
>>> ELSE
>>> oConnection:Rollback()
>>> ENDIF
>>>
>>>
>>> I appreciate some help.
>>> Regards.
>>>
>>> Sebastián
> Hi Jamal, thanks for your response.
> the problem is that when a user reaches that line of code in the third party web service an authorization code has already been managed.
> 
> An example:
> 
> Time1 - User 1 open associated dialog to get authorization code on PC1
> Time2 - User 2 open associated dialog to get authorization code on PC2
> Time3 - User 1 get authorization code (before this i have to lock to avoid that other used get authorization code)
> Time4 - User 1 update authorizarion code on database
> Time5 - User 2 get authorization code
> Time6 - User 2 update authorizarion code on database
> 
> I hope my problem is better understood, English is not my native language.
> Regards.

[toc] | [prev] | [next] | [standalone]


#13633

FromSebastián BASSANO <ccyasa.sbassano@gmail.com>
Date2022-08-26 12:16 -0700
Message-ID<853d9b96-9b6e-4cac-b60c-05abe26adc3en@googlegroups.com>
In reply to#13632
El Friday, August 26, 2022 a la(s) 4:11:19 PM UTC-3, Jamal escribió:
> May be you can use the same approach when you SELECT the row and before 
> you get the authorization, then update the row. 
> 
> If this does not work, try creating another table that indicates that 
> you're in an update process and handle your update process accordingly. 
> 
> Jamal
> On 8/26/2022 2:10 PM, Sebastián BASSANO wrote: 
> > El Friday, August 26, 2022 a la(s) 2:57:55 PM UTC-3, Jamal escribió: 
> >> Try adding: 
> >> 
> >> WITH (ROWLOCK) 
> >> 
> >> to your SQL UPDATE statement. 
> >> 
> >> "UPDATE document SET authorizationCode = " + cAuthorizationCode + " WITH 
> >> (ROWLOCK) WHERE (id = " + NTrim(cId) + ")" 
> >> 
> >> Jamal 
> >> On 8/26/2022 7:58 AM, Sebastián BASSANO wrote: 
> >>> Hi, i have an operation that needs to authorize a document via a third party web server and then update a table on my database. The process works fine but i need to add multiuser support, means that when an user is doing that operation the other users can't. I work with Vo2Ado. 
> >>> Today the code (simplified, without the BEGIN SEQUENCE/RECOVER USING code block) is similar to this: 
> >>> 
> >>> 
> >>> // Get third party authorization code via web service 
> >>> cAuthorizationCode:= oWebService:GetAuthorization(...) 
> >>> 
> >>> // Update table 
> >>> oConnection:BeginTrans() 
> >>> cCommand:= "UPDATE document SET authorizationCode = " + cAuthorizationCode + " WHERE (id = " + NTrim(cId) + ")" 
> >>> oConnection:Execute(cCommand, @liAffectedRecords, NIL) 
> >>> if (liAffectedRecords > 0) 
> >>> oConnection:CommitTrans() 
> >>> ELSE 
> >>> oConnection:Rollback() 
> >>> ENDIF 
> >>> 
> >>> 
> >>> This code doesn't work on multiuser environment because the record lock is applied on line: 
> >>> 
> >>> oConnection:Execute(cCommand, @liAffectedRecords, NIL) 
> >>> 
> >>> and at this point two or more users could get more authorization codes because the third party web service don't receive any local information to test if the authorization code already exists. I need to lock the record before start the communication with web service. 
> >>> I understand that i can't lock records explicitly as with DbServer:LockCurrentRecord ¿is this correct? 
> >>> If i'm correct ¿what would be the correct way to achieve this? 
> >>> 
> >>> Maybe something this? I'm not convinced... 
> >>> 
> >>> 
> >>> oConnection:BeginTrans() 
> >>> 
> >>> // Force to lock the record updating value with the actual value 
> >>> cCommand:= "UPDATE document SET authorizationCode = authorizationCode + " WHERE (id = " + NTrim(cId) + ")" 
> >>> oConnection:Execute(cCommand, @liAffectedRecords, NIL) 
> >>> 
> >>> if (liAffectedRecords > 0) 
> >>> // Get third party authorization code via web service 
> >>> cAuthorizationCode:= oWebService:GetAuthorization(...) 
> >>> 
> >>> // Update table 
> >>> cCommand:= "UPDATE document SET authorizationCode = " + cAuthorizationCode + " WHERE (id = " + NTrim(cId) + ")" 
> >>> oConnection:Execute(cCommand, @liAffectedRecords, NIL) 
> >>> oConnection:CommitTrans() 
> >>> ELSE 
> >>> oConnection:Rollback() 
> >>> ENDIF 
> >>> 
> >>> 
> >>> I appreciate some help. 
> >>> Regards. 
> >>> 
> >>> Sebastián 
> > Hi Jamal, thanks for your response. 
> > the problem is that when a user reaches that line of code in the third party web service an authorization code has already been managed. 
> > 
> > An example: 
> > 
> > Time1 - User 1 open associated dialog to get authorization code on PC1 
> > Time2 - User 2 open associated dialog to get authorization code on PC2 
> > Time3 - User 1 get authorization code (before this i have to lock to avoid that other used get authorization code) 
> > Time4 - User 1 update authorizarion code on database 
> > Time5 - User 2 get authorization code 
> > Time6 - User 2 update authorizarion code on database 
> > 
> > I hope my problem is better understood, English is not my native language. 
> > Regards.

Thanks, i will try in that way!

[toc] | [prev] | [next] | [standalone]


#13634

FromJohnMartens <martens@tennishulp.info>
Date2022-08-26 21:23 +0200
Message-ID<teb6jt$2180$1@dont-email.me>
In reply to#13631
Hi Sebastian,

Do you NEED to have two users to use the authorisation process at the 
same time or do you want to avoid that thigs are messing up when users 
try to use the procedure at the same time ?

If the latter is the case and you can have users wait for echt other you 
could add a waiting list to a data base. If there is no waiting list, 
start the process and it will run fine.
If there is a waiting list, add a user/PC specific ID to the end of the 
waiting list, present the user with a waiting message and use a timer to 
check once in a while if this waiting PC is now first in line and start 
the auhorisation.
In this way serveral PC's/users may have small waiting time and youre 
code can run free of errors

John


Op 26-8-2022 om 20:10 schreef Sebastián BASSANO:
> El Friday, August 26, 2022 a la(s) 2:57:55 PM UTC-3, Jamal escribió:
>> Try adding:
>>
>> WITH (ROWLOCK)
>>
>> to your SQL UPDATE statement.
>>
>> "UPDATE document SET authorizationCode = " + cAuthorizationCode + " WITH
>> (ROWLOCK) WHERE (id = " + NTrim(cId) + ")"
>>
>> Jamal
>> On 8/26/2022 7:58 AM, Sebastián BASSANO wrote:
>>> Hi, i have an operation that needs to authorize a document via a third party web server and then update a table on my database. The process works fine but i need to add multiuser support, means that when an user is doing that operation the other users can't. I work with Vo2Ado.
>>> Today the code (simplified, without the BEGIN SEQUENCE/RECOVER USING code block) is similar to this:
>>>
>>>
>>> // Get third party authorization code via web service
>>> cAuthorizationCode:= oWebService:GetAuthorization(...)
>>>
>>> // Update table
>>> oConnection:BeginTrans()
>>> cCommand:= "UPDATE document SET authorizationCode = " + cAuthorizationCode + " WHERE (id = " + NTrim(cId) + ")"
>>> oConnection:Execute(cCommand, @liAffectedRecords, NIL)
>>> if (liAffectedRecords > 0)
>>> oConnection:CommitTrans()
>>> ELSE
>>> oConnection:Rollback()
>>> ENDIF
>>>
>>>
>>> This code doesn't work on multiuser environment because the record lock is applied on line:
>>>
>>> oConnection:Execute(cCommand, @liAffectedRecords, NIL)
>>>
>>> and at this point two or more users could get more authorization codes because the third party web service don't receive any local information to test if the authorization code already exists. I need to lock the record before start the communication with web service.
>>> I understand that i can't lock records explicitly as with DbServer:LockCurrentRecord ¿is this correct?
>>> If i'm correct ¿what would be the correct way to achieve this?
>>>
>>> Maybe something this? I'm not convinced...
>>>
>>>
>>> oConnection:BeginTrans()
>>>
>>> // Force to lock the record updating value with the actual value
>>> cCommand:= "UPDATE document SET authorizationCode = authorizationCode + " WHERE (id = " + NTrim(cId) + ")"
>>> oConnection:Execute(cCommand, @liAffectedRecords, NIL)
>>>
>>> if (liAffectedRecords > 0)
>>> // Get third party authorization code via web service
>>> cAuthorizationCode:= oWebService:GetAuthorization(...)
>>>
>>> // Update table
>>> cCommand:= "UPDATE document SET authorizationCode = " + cAuthorizationCode + " WHERE (id = " + NTrim(cId) + ")"
>>> oConnection:Execute(cCommand, @liAffectedRecords, NIL)
>>> oConnection:CommitTrans()
>>> ELSE
>>> oConnection:Rollback()
>>> ENDIF
>>>
>>>
>>> I appreciate some help.
>>> Regards.
>>>
>>> Sebastián
> Hi Jamal, thanks for your response.
> the problem is that when a user reaches that line of code in the third party web service an authorization code has already been managed.
> 
> An example:
> 
> Time1 - User 1 open associated dialog to get authorization code on PC1
> Time2 - User 2 open associated dialog to get authorization code on PC2
> Time3 - User 1 get authorization code (before this i have to lock to avoid that other used get authorization code)
> Time4 - User 1 update authorizarion code on database
> Time5 - User 2 get authorization code
> Time6 - User 2 update authorizarion code on database
> 
> I hope my problem is better understood, English is not my native language.
> Regards.

[toc] | [prev] | [next] | [standalone]


#13635

FromSebastián BASSANO <ccyasa.sbassano@gmail.com>
Date2022-08-26 12:41 -0700
Message-ID<3112a339-5a6a-4a5d-ad42-079e329d92can@googlegroups.com>
In reply to#13634
El Friday, August 26, 2022 a la(s) 4:23:42 PM UTC-3, JohnMartens escribió:
> Hi Sebastian, 
> 
> Do you NEED to have two users to use the authorisation process at the 
> same time or do you want to avoid that thigs are messing up when users 
> try to use the procedure at the same time ? 
> 
> If the latter is the case and you can have users wait for echt other you 
> could add a waiting list to a data base. If there is no waiting list, 
> start the process and it will run fine. 
> If there is a waiting list, add a user/PC specific ID to the end of the 
> waiting list, present the user with a waiting message and use a timer to 
> check once in a while if this waiting PC is now first in line and start 
> the auhorisation. 
> In this way serveral PC's/users may have small waiting time and youre 
> code can run free of errors 
> 
> John 
> 
> 
> Op 26-8-2022 om 20:10 schreef Sebastián BASSANO:
> > El Friday, August 26, 2022 a la(s) 2:57:55 PM UTC-3, Jamal escribió: 
> >> Try adding: 
> >> 
> >> WITH (ROWLOCK) 
> >> 
> >> to your SQL UPDATE statement. 
> >> 
> >> "UPDATE document SET authorizationCode = " + cAuthorizationCode + " WITH 
> >> (ROWLOCK) WHERE (id = " + NTrim(cId) + ")" 
> >> 
> >> Jamal 
> >> On 8/26/2022 7:58 AM, Sebastián BASSANO wrote: 
> >>> Hi, i have an operation that needs to authorize a document via a third party web server and then update a table on my database. The process works fine but i need to add multiuser support, means that when an user is doing that operation the other users can't. I work with Vo2Ado. 
> >>> Today the code (simplified, without the BEGIN SEQUENCE/RECOVER USING code block) is similar to this: 
> >>> 
> >>> 
> >>> // Get third party authorization code via web service 
> >>> cAuthorizationCode:= oWebService:GetAuthorization(...) 
> >>> 
> >>> // Update table 
> >>> oConnection:BeginTrans() 
> >>> cCommand:= "UPDATE document SET authorizationCode = " + cAuthorizationCode + " WHERE (id = " + NTrim(cId) + ")" 
> >>> oConnection:Execute(cCommand, @liAffectedRecords, NIL) 
> >>> if (liAffectedRecords > 0) 
> >>> oConnection:CommitTrans() 
> >>> ELSE 
> >>> oConnection:Rollback() 
> >>> ENDIF 
> >>> 
> >>> 
> >>> This code doesn't work on multiuser environment because the record lock is applied on line: 
> >>> 
> >>> oConnection:Execute(cCommand, @liAffectedRecords, NIL) 
> >>> 
> >>> and at this point two or more users could get more authorization codes because the third party web service don't receive any local information to test if the authorization code already exists. I need to lock the record before start the communication with web service. 
> >>> I understand that i can't lock records explicitly as with DbServer:LockCurrentRecord ¿is this correct? 
> >>> If i'm correct ¿what would be the correct way to achieve this? 
> >>> 
> >>> Maybe something this? I'm not convinced... 
> >>> 
> >>> 
> >>> oConnection:BeginTrans() 
> >>> 
> >>> // Force to lock the record updating value with the actual value 
> >>> cCommand:= "UPDATE document SET authorizationCode = authorizationCode + " WHERE (id = " + NTrim(cId) + ")" 
> >>> oConnection:Execute(cCommand, @liAffectedRecords, NIL) 
> >>> 
> >>> if (liAffectedRecords > 0) 
> >>> // Get third party authorization code via web service 
> >>> cAuthorizationCode:= oWebService:GetAuthorization(...) 
> >>> 
> >>> // Update table 
> >>> cCommand:= "UPDATE document SET authorizationCode = " + cAuthorizationCode + " WHERE (id = " + NTrim(cId) + ")" 
> >>> oConnection:Execute(cCommand, @liAffectedRecords, NIL) 
> >>> oConnection:CommitTrans() 
> >>> ELSE 
> >>> oConnection:Rollback() 
> >>> ENDIF 
> >>> 
> >>> 
> >>> I appreciate some help. 
> >>> Regards. 
> >>> 
> >>> Sebastián 
> > Hi Jamal, thanks for your response. 
> > the problem is that when a user reaches that line of code in the third party web service an authorization code has already been managed. 
> > 
> > An example: 
> > 
> > Time1 - User 1 open associated dialog to get authorization code on PC1 
> > Time2 - User 2 open associated dialog to get authorization code on PC2 
> > Time3 - User 1 get authorization code (before this i have to lock to avoid that other used get authorization code) 
> > Time4 - User 1 update authorizarion code on database 
> > Time5 - User 2 get authorization code 
> > Time6 - User 2 update authorizarion code on database 
> > 
> > I hope my problem is better understood, English is not my native language. 
> > Regards.

Hi John, it is indeed the latter case.
I think I'm going to end up using your idea because I don't like what I was thinking of doing because it leaves the second user locked until the first user's transaction finishes (and I think with the option WITH
(ROWLOCK) option the same thing happens).
Thanks!

[toc] | [prev] | [next] | [standalone]


#13636

FromJohnMartens <martens@tennishulp.info>
Date2022-08-27 08:25 +0200
Message-ID<tecdde$817g$1@dont-email.me>
In reply to#13635
Hope it will get you to a solution
john

Op 26-8-2022 om 21:41 schreef Sebastián BASSANO:
> El Friday, August 26, 2022 a la(s) 4:23:42 PM UTC-3, JohnMartens escribió:
>> Hi Sebastian,
>>
>> Do you NEED to have two users to use the authorisation process at the
>> same time or do you want to avoid that thigs are messing up when users
>> try to use the procedure at the same time ?
>>
>> If the latter is the case and you can have users wait for echt other you
>> could add a waiting list to a data base. If there is no waiting list,
>> start the process and it will run fine.
>> If there is a waiting list, add a user/PC specific ID to the end of the
>> waiting list, present the user with a waiting message and use a timer to
>> check once in a while if this waiting PC is now first in line and start
>> the auhorisation.
>> In this way serveral PC's/users may have small waiting time and youre
>> code can run free of errors
>>
>> John
>>
>>
>> Op 26-8-2022 om 20:10 schreef Sebastián BASSANO:
>>> El Friday, August 26, 2022 a la(s) 2:57:55 PM UTC-3, Jamal escribió:
>>>> Try adding:
>>>>
>>>> WITH (ROWLOCK)
>>>>
>>>> to your SQL UPDATE statement.
>>>>
>>>> "UPDATE document SET authorizationCode = " + cAuthorizationCode + " WITH
>>>> (ROWLOCK) WHERE (id = " + NTrim(cId) + ")"
>>>>
>>>> Jamal
>>>> On 8/26/2022 7:58 AM, Sebastián BASSANO wrote:
>>>>> Hi, i have an operation that needs to authorize a document via a third party web server and then update a table on my database. The process works fine but i need to add multiuser support, means that when an user is doing that operation the other users can't. I work with Vo2Ado.
>>>>> Today the code (simplified, without the BEGIN SEQUENCE/RECOVER USING code block) is similar to this:
>>>>>
>>>>>
>>>>> // Get third party authorization code via web service
>>>>> cAuthorizationCode:= oWebService:GetAuthorization(...)
>>>>>
>>>>> // Update table
>>>>> oConnection:BeginTrans()
>>>>> cCommand:= "UPDATE document SET authorizationCode = " + cAuthorizationCode + " WHERE (id = " + NTrim(cId) + ")"
>>>>> oConnection:Execute(cCommand, @liAffectedRecords, NIL)
>>>>> if (liAffectedRecords > 0)
>>>>> oConnection:CommitTrans()
>>>>> ELSE
>>>>> oConnection:Rollback()
>>>>> ENDIF
>>>>>
>>>>>
>>>>> This code doesn't work on multiuser environment because the record lock is applied on line:
>>>>>
>>>>> oConnection:Execute(cCommand, @liAffectedRecords, NIL)
>>>>>
>>>>> and at this point two or more users could get more authorization codes because the third party web service don't receive any local information to test if the authorization code already exists. I need to lock the record before start the communication with web service.
>>>>> I understand that i can't lock records explicitly as with DbServer:LockCurrentRecord ¿is this correct?
>>>>> If i'm correct ¿what would be the correct way to achieve this?
>>>>>
>>>>> Maybe something this? I'm not convinced...
>>>>>
>>>>>
>>>>> oConnection:BeginTrans()
>>>>>
>>>>> // Force to lock the record updating value with the actual value
>>>>> cCommand:= "UPDATE document SET authorizationCode = authorizationCode + " WHERE (id = " + NTrim(cId) + ")"
>>>>> oConnection:Execute(cCommand, @liAffectedRecords, NIL)
>>>>>
>>>>> if (liAffectedRecords > 0)
>>>>> // Get third party authorization code via web service
>>>>> cAuthorizationCode:= oWebService:GetAuthorization(...)
>>>>>
>>>>> // Update table
>>>>> cCommand:= "UPDATE document SET authorizationCode = " + cAuthorizationCode + " WHERE (id = " + NTrim(cId) + ")"
>>>>> oConnection:Execute(cCommand, @liAffectedRecords, NIL)
>>>>> oConnection:CommitTrans()
>>>>> ELSE
>>>>> oConnection:Rollback()
>>>>> ENDIF
>>>>>
>>>>>
>>>>> I appreciate some help.
>>>>> Regards.
>>>>>
>>>>> Sebastián
>>> Hi Jamal, thanks for your response.
>>> the problem is that when a user reaches that line of code in the third party web service an authorization code has already been managed.
>>>
>>> An example:
>>>
>>> Time1 - User 1 open associated dialog to get authorization code on PC1
>>> Time2 - User 2 open associated dialog to get authorization code on PC2
>>> Time3 - User 1 get authorization code (before this i have to lock to avoid that other used get authorization code)
>>> Time4 - User 1 update authorizarion code on database
>>> Time5 - User 2 get authorization code
>>> Time6 - User 2 update authorizarion code on database
>>>
>>> I hope my problem is better understood, English is not my native language.
>>> Regards.
> 
> Hi John, it is indeed the latter case.
> I think I'm going to end up using your idea because I don't like what I was thinking of doing because it leaves the second user locked until the first user's transaction finishes (and I think with the option WITH
> (ROWLOCK) option the same thing happens).
> Thanks!

[toc] | [prev] | [next] | [standalone]


#13637

From"Wolfgang Riedmann" <wriedmann@gmail.com>
Date2022-08-31 09:51 +0200
Message-ID<jn8i7qFn7ruU1@mid.individual.net>
In reply to#13629
Hi Sebastián,

I would solve that in a completely different manner: I would add a
table "operations_in_progress", and when a user starts an authorization
process, I would first check if there is already a process active, and
if not, add a record there and start the authorization process.
If the authorization progress is successful, you can then write the
result to the document table, and remove the record from the in_progres
table in the same transaction.

Wolfgang


> Hi, i have an operation that needs to authorize a document via a
> third party web server and then update a table on my database. The
> process works fine but i need to add multiuser support, means that
> when an user is doing that operation the other users can't. I work
> with Vo2Ado.  



-- 

[toc] | [prev] | [next] | [standalone]


#13638

FromSebastián BASSANO <ccyasa.sbassano@gmail.com>
Date2022-08-31 05:54 -0700
Message-ID<09c518e8-edf5-49b1-a1f5-8e8919d0c838n@googlegroups.com>
In reply to#13637
El Wednesday, August 31, 2022 a la(s) 4:51:25 AM UTC-3, Wolfgang Riedmann escribió:
> Hi Sebastián, 
> 
> I would solve that in a completely different manner: I would add a 
> table "operations_in_progress", and when a user starts an authorization 
> process, I would first check if there is already a process active, and 
> if not, add a record there and start the authorization process. 
> If the authorization progress is successful, you can then write the 
> result to the document table, and remove the record from the in_progres 
> table in the same transaction. 
> 
> Wolfgang
> > Hi, i have an operation that needs to authorize a document via a 
> > third party web server and then update a table on my database. The 
> > process works fine but i need to add multiuser support, means that 
> > when an user is doing that operation the other users can't. I work 
> > with Vo2Ado.
> --
Hi Wolfgang, just the way you indicate is the one I am finally implementing after reading John's answers.
Thanks!

[toc] | [prev] | [standalone]


Back to top | Article view | comp.lang.clipper.visual-objects


csiph-web