1. Forum
  2. Usenet
  3. COMP.LANG.CLIPPER.VISUAL-

  • Vo2Ado and multiuser environment locks

    From =?UTF-8?Q?Sebasti=C3=A1n_BASSANO?=@21:1/5 to All on Fri Aug 26 04:58:47 2022
    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Jamal@21:1/5 to All on Fri Aug 26 13:57:52 2022
    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

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From =?UTF-8?Q?Sebasti=C3=A1n_BASSANO?=@21:1/5 to All on Fri Aug 26 11:10:43 2022
    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.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Jamal@21:1/5 to All on Fri Aug 26 15:11:18 2022
    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.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From =?UTF-8?Q?Sebasti=C3=A1n_BASSANO?=@21:1/5 to All on Fri Aug 26 12:41:11 2022
    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!

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From =?UTF-8?Q?Sebasti=C3=A1n_BASSANO?=@21:1/5 to All on Fri Aug 26 12:16:14 2022
    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!

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From JohnMartens@21:1/5 to All on Fri Aug 26 21:23:40 2022
    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.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From JohnMartens@21:1/5 to All on Sat Aug 27 08:25:50 2022
    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!

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Wolfgang Riedmann@21:1/5 to All on Wed Aug 31 09:51:23 2022
    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.



    --

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From =?UTF-8?Q?Sebasti=C3=A1n_BASSANO?=@21:1/5 to All on Wed Aug 31 05:54:56 2022
    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!

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)