Error from ADO

[byron.hopp]

All,

I get this error:

Error description: (DOS Error -2147352567) WINOLE/1007 Row cannot be located for updating. Some values may have been changed since it was last read. (0x80040E38): Microsoft Cursor Engine.

I've got the recordset in a browse and the user can navigate around with cursor keys. I don't understand what triggers the error.

Thanks,

Byron ...

[Rick Lipkin]

Bryon

How are you creating your oRs Object .. Here are the parameters I use especially a local cache and opendkeyset

Code: Select all  Expand view

oRsAcc := TOleAuto():New( "ADODB.Recordset" )
oRsAcc:CursorType     := 1        // opendkeyset
oRsAcc:CursorLocation := 3        // local cache
oRsAcc:LockType       := 3        // lockoportunistic
 

Questions ..
1) I presume this is a network mulit-user application ?
2) If Yes, does this application have a lot of transactions that may change quickly and make a buffered up oRs on a client machine stale ?

Generally speaking .. I like the Local Cache parameter because the fetched cursor ( data to be browsed ) is buffered on the Client and not necessarily ( physically ) linked back to the original data on the server especially using the opendkeyset cursor type

If you are using the FW_Ado wrappers ( adofuncs.prg ) it uses the 2 parameter ( if not specified ) or adOpenDynamic for the Cursor Type .. there are pros and cons on Open and Dynamic keysets and they are mentioned here :

https://docs.microsoft.com/en-us/sql/ad ... et-cursors

If you are using the Ado Wrappers .. FW_OpenRecordSet( oCn, cSql, nLockType, nCursorType, nMaxRecords, nOpt ) .. try nLockType 3 and nCursorType 1 and see if that makes any difference.... by default Fw_OpenRecordSet uses Local cache.

Rick Lipkin

[byron.hopp]

Lots of info, thanks very much, I will start checking it out.

Byron ...

[byron.hopp]

I seem to still be getting this error when processing certain ADO methods, it seems the Update() method is the problem, but the error system reports on Update(), even though the line number it points to is AddNew().

Is there anyway to completely eliminate this problem, I have very unhappy users (there circling their wagons). I do believe the new way is reduced the number of errors, but is there a way to eliminate them completely?

A unique Keyno field is a part of the select statement, it seems the row should be found quite easily:
"where keyno='" + cKeyno + "'"

I assume it is complaining about the data in the recordset at the time, because the transaction is an ADD.

Error description: (DOS Error -2147352567) WINOLE/1007 Row cannot be located for updating. Some values may have been changed since it was last read. (0x80040E38): Microsoft Cursor Engine

Using the function below to open the RecordSet()


Function Mcs_RecSet(cConnStr,cSql,lNew)
Local nI := 1
Local oRs := CreateObject("ADODB.RecordSet")
Local e := nil
Default lNew := FALSE
Try
If lNew
// New way...
// Suggested by Rick Lipkin from the FW Tech Board.
oRs:CursorType := 1 // adOpenKeyset
oRs:CursorLocation := 3 // adUseClient
oRs:LockType := 3 // adLockOptimistic
Else
oRs:CursorType := adOpenDynamic
oRs:CursorLocation := adUseClient
oRs:LockType := adLockOptimistic
Endif

oRs:ActiveConnection := cConnStr
oRs:Source := cSql
oRs:Open()
Catch e
MsgInfo(e:description,cSql)
if oRs:ActiveConnection:Errors:Count > 0
FOR nI := 0 TO oRs:ActiveConnection:Errors:Count -1
msgInfo(oRs:ActiveConnection:Errors(nI):Description,"Cannot Process Query...")
NEXT
Break
endif
End
Return oRs

[Rick Lipkin]

Bryon

You can open your recordset two ways ..

Code: Select all  Expand view

Function Mcs_RecSet(cConnStr,cSql,lNew)

Local nI := 1
Local oRs //:= CreateObject("ADODB.RecordSet")
Local e := nil

If empty(lNew)
   lNew := .f.
ENdif

//Default lNew := FALSE

If lNew = .t.
   
   // -------- standard ADO

   oRs := TOleAuto():New( "ADODB.Recordset" )
   oRs:CursorType     := 1        // opendkeyset
   oRs:CursorLocation := 3        // local cache
   oRs:LockType       := 3        // lockoportunistic

   TRY
      oRs:Open( cSQL,cConnStr )
   CATCH oErr
       MsgInfo( "Error in Opening RECORDSET" )
      RETURN(.F.)
   END TRY
   // ---------
   //  or
    // -- using Fw_Ado Wrappers
   
    oRs := FW_OpenRecordSet( cConnStr, cSql, 3, 1 ) //  nLockType 3 and nCursorType 1
   
    If empty(oRs)
       MsgInfo( "Error opening Recordset)
       Return(.f.)
   Endif

I would not try to trap the Try Catch oErr .. ( ado error ) ... just sufficient to know if it returned .t. or .f. .... Also .. if you are having ADO workstation Update visability errors on add .. sometimes after an Add .. it is best to close the recordset oRs:CLose ... then re-open it with the same query .. or you can do oRs:ReQuery(), but I have found that method not to be very reliable.

Hope that makes sense .. Send me an email ( r1.1955@live.com ) and I will give you my phone number here is South Carolina and we can talk thru your problem.

Thanks
Rick Lipkin

[nageswaragunupudi]

CursorLocation
1 adUseNone (Obsolete and does not work)
2. adUseServer (Can not navigate and is not useful for us)
3. adUseClient : This is the only choice we have

LockType:
1. adLockUnspecified (not to be used for new recordsets)
2. adLockReadOnly : This is not what you want when you want to make changes. (Note: This is useful when you read data not to be modified)
3. adLockPessimistic: We need to lock each record for modification. Rarely anybody uses it.
5. adLockBatchOptimistic: Useful. But for more advanced users.
4. adLockOptimistic: This is the only choice we have

CursorType:
0. adOpenForwardOnly : Works only with adUseServer and in anycase this is not what we want
1. adOpenKeyset: Whether we like or not this work only with adUseServer
2. adOpenDynamic: This also does not work with adUseClient
3. adOpenStatic: This is the only choice left to us.

Even if you try to use adOpenKeySet or adOpenDynamc the ADO system will open the recordset with adUseStatic only.

Test:
Try opening a recordset with cursor types 1 or 2. After opening the recordset, try ? oRs:CursorType. The result always is 3 (adOpenStatic)

We are left with these choices only and we have no other choices:
CursorLocation : adUseClient (3)
LockType: adLockOptimistic (4) // If you know how to handle, adLockBatchOptimistic can also be used.
CursorType: adOpenStatic. (3)

Now this also means that trying other values, like adOpenKeyset or adOpenDynamic, has no meaning and is a sheer waste of time.

[Horizon]

CursorLocation
1 adUseNone (Obsolete and does not work)
2. adUseServer (Can not navigate and is not useful for us)
3. adUseClient : This is the only choice we have

LockType:
1. adLockUnspecified (not to be used for new recordsets)
2. adLockReadOnly : This is not what you want when you want to make changes. (Note: This is useful when you read data not to be modified)
3. adLockPessimistic: We need to lock each record for modification. Rarely anybody uses it.
5. adLockBatchOptimistic: Useful. But for more advanced users.
4. adLockOptimistic: This is the only choice we have

CursorType:
0. adOpenForwardOnly : Works only with adUseServer and in anycase this is not what we want
1. adOpenKeyset: Whether we like or not this work only with adUseServer
2. adOpenDynamic: This also does not work with adUseClient
3. adOpenStatic: This is the only choice left to us.

Even if you try to use adOpenKeySet or adOpenDynamc the ADO system will open the recordset with adUseStatic only.

Test:
Try opening a recordset with cursor types 1 or 2. After opening the recordset, try ? oRs:CursorType. The result always is 3 (adOpenStatic)

We are left with these choices only and we have no other choices:
CursorLocation : adUseClient (3)
LockType: adLockOptimistic (4) // If you know how to handle, adLockBatchOptimistic can also be used.
CursorType: adOpenStatic. (3)

Now this also means that trying other values, like adOpenKeyset or adOpenDynamic, has no meaning and is a sheer waste of time.

Hi Mr. Rao,

Are these valid for MariaDB recordset?

Any example using MariaDB?

Thanks.

[nageswaragunupudi]

This information is relevant if you are using MySql or MariaDB through ADO.

If you are using built-in MariaDB library of FWH, then you can ignore all this. This library simplifies everything and is more powerful than ADO. The built-in library enables you to do things not possible with ADO or any other MySql libraries available.

[Horizon]

Hi Mr. Rao,

I start to use fwh mariadb functions. not ADO. I could not understand that how to handle multiuser environment in fwh mariadb functions.

I need to lock one record when i enter to press update button. I don't want to enter this record to update purpose for other users. in this situation other user can only view the record.

Is it possible?

Thanks

[Horizon]

Hi Mr. Rao,

I start to use fwh mariadb functions. not ADO. I could not understand that how to handle multiuser environment in fwh mariadb functions.

I need to lock one record when i enter to press update button. I don't want to enter this record to update purpose for other users. in this situation other user can only view the record.

Is it possible?

Thanks

Hi Mr. Rao,

Can you please give an example of passimistic lock with MariaDB.

Thank you.

[nageswaragunupudi]

Whatsnew.txt

FWH 18.06

- Enhancement to method EditBaseRecord(...)
Added optional new 5th param, lLock (default .f.)
Revised syntax:
EditBaseRecord( [cFieldList], [lNew], [bEdit], [oBrw], [lLock] )
If lLock is set to true, the row is locked for edit and lock is released after edit.

[Horizon]

Whatsnew.txt

FWH 18.06

- Enhancement to method EditBaseRecord(...)
Added optional new 5th param, lLock (default .f.)
Revised syntax:
EditBaseRecord( [cFieldList], [lNew], [bEdit], [oBrw], [lLock] )
If lLock is set to true, the row is locked for edit and lock is released after edit.

Hi Mr. Rao,

We set .T. to 5th param in EditBaseRecord and this record is locked by another user, is there any error message. If yes, is it possible to set user defined function that is maintained by programmer?

Secondly, Sometimes I need to modify some fields programaticaly. How can I ensure this record is not locked.

Thanks,

[nageswaragunupudi]

We set .T. to 5th param in EditBaseRecord and this record is locked by another user, is there any error message. If yes, is it possible to set user defined function that is maintained by programmer?

If one user locks the record, other users get an error message (mysql error no:1205) that the record cannot be locked, if they try to modify the same record.

Important Note: For other users, MySQL tries to obtain the lock and waits for the lock for "innodb_lock_wait_timeout" seconds. The default value is 50 seconds, which is too large for interactive programs. So, it is necessary to set this value to a small value.

Code: Select all  Expand view

   oCn:innodb_lock_wait_timeout := 1 // one second
 

Secondly, Sometimes I need to modify some fields programaticaly. How can I ensure this record is not locked.

Simply attempt to save as usual. oRs:Save() fails with the error. If you set oRs:lShowErrors := .t., you will see the error message. If you want to handle the error inside your program without displaying message, keep oRs:lShowErrors := .f. and

Code: Select all  Expand view

   if !oRs:Save()
      if oCn:nError == 1205
         // other user locked the record
         // take suitable action
      endif
   endif
 

This is a test program. You can try with two instances of the program on the same computer or different computers.

Code: Select all  Expand view

#include "fivewin.ch"

function Main()

   local oCn, oRs, oDlg, oBrw

   oCn   := FW_DemoDB()

   oCn:innodb_lock_wait_timeout := 1

   oRs   := oCn:RowSet( "states" )
   oRs:lShowErrors   := .t.

   DEFINE DIALOG oDlg SIZE 400,400 PIXEL TRUEPIXEL ;

   @ 60,20 XBROWSE oBrw SIZE -20,-20 PIXEL OF oDlg ;
      DATASOURCE oRs AUTOCOLS CELL LINES NOBORDER FASTEDIT

   oBrw:nEditTypes      := EDIT_GET
   oBrw:CreateFromCode()

   @ 10,20 BUTTON "EDIT" SIZE 100,35 PIXEL OF oDlg ;
      ACTION oRs:EditBaseRecord( nil, .F., { |oRec| EditDlg( oRec ) }, oBrw, .T. )

   ACTIVATE DIALOG oDlg CENTERED

   oRs:Close()
   oCn:Close()

return nil

//----------------------------------------------------------------------------//

function EditDlg( oRec )

   local oDlg, oBtn
   local oCn   := oRec:uSource:oCn

   DEFINE DIALOG oDlg SIZE 360,200 PIXEL TRUEPIXEL

   @  40,20 SAY "Code" GET oRec:Code PICTURE "@!" SIZE 300,24 PIXEL OF oDlg
   @  80,20 SAY "Name" GET oRec:Name SIZE 300,24 PIXEL OF oDlg

   @ 120,240 BUTTON oBtn PROMPT "Save" SIZE 100,35 PIXEL OF oDlg ;
      ACTION ( If( oRec:Modified(), oRec:Save(), nil ), oDlg:End() )

   ACTIVATE DIALOG oDlg CENTERED

return nil

//----------------------------------------------------------------------------//
 

Note: pessimistic locking is used in the rarest of rare cases and normally we do not encounter any situation that can not be handled without using pessimistic locking.

[nageswaragunupudi]

An example of using locks programmatically:

Code: Select all  Expand view

lSuccess := .f.
oCn:BeginTransaction()
oRsLocked := oCn:RowSet( "SELECT * FROM materials WHERE code = '009' FOR UPDATE" )
if oRsLocked == nil
   // locked by other user
   oCn:RollBack()
else
   if oRsLocked:balance >= nIssueQty
      oRsLocked:balance -= nIssueQty
      if oRsLocked:Save()
         lSuccess := << save other tables also >>  
      endif
   endif
   if lSuccess
      oCn:CommitTransaction()
   else
      oCn:RollBack()
   endif

   oRsLocked:Close()
   oRsLocked := nil
endif
 

[Horizon]

Thank you very much for your examples.

I want to check locked/not locked status when I try to show the record.

In order to status of lock, I disable to Save button and my private buttons.

According to your example, I will search my record like this.

Code: Select all  Expand view

oRsOpen := oCn:RowSet( "SELECT * FROM materials WHERE code = '009' " )
if oRsOpen == nil
   // Error : There is not any record code='009'
   .......
   return
endif
oRsLocked := oCn:RowSet( "SELECT * FROM materials WHERE code = '009' FOR UPDATE" )
if oRsLocked == nil
   // locked by other user
   return
endif

// Go on

 

Is it reliable?