ADO update visability between workstations

[Rick Lipkin]

To All

I have completed a major SQL ADO application and am finding that workstation recordsets are not being updated as other workstations make changes.

I am caching the recordsets to the local client ..

In looking on MSDN I see a ReSync method ?? has anyone used this to just resync a specific record in a recodset ?? The documentation seems to support the single table record refresh .. I would think the syntax would be :

oRs:ReSync()

Issue that and the object record will be refreshed from the table and included in the client cache ??

Rick Lipkin
SC Dept of Health, USA


cSQL := "SELECT * FROM CERT order by reg_no"

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

TRY
oRs:Open( cSQL,'Provider='+xPROVIDER+';Data Source='+xSOURCE+';Initial Catalog='+xCATALOG+';User Id='+xUSERID+';Password='+xPASSWORD )
CATCH oErr
MsgInfo( "Error in Opening CERT table" )
oDlg:End()
RETURN(.F.)
END TRY

[R.F.]

Rick:

Your cursor type is wrong, you must use adOpenDynamic (2) in order to see changes others make in the recordset.

However not all the databases support the dynamic update of the recordset.

[Rick Lipkin]

Rene

According to the doccumentation I found :

(1)adOpenKeyset: A static snap-shot of the primary key values of the records that match your search criteria are put into the recordset. As you scroll backwards and forwards, the primary key value in the recordset is used to fetch the current data for that record from the database. This cursor thus allows you to see updates to the data made by other users, but it doesn't let you see new records that have been added by other users (because the primary key values for those records are not in your recordset).

(2)adOpenDynamic: A dynamic snapshot of the database is maintained by OLEDB/ADO. All changes by other users to the underlying database are visible. Obviously this is the most sophisticated cursor, and thus is usually the most expensive. Because the data in the recordset is dynamic,
attributes like AbsolutePosition and AbsolutePage can not be set. The adOpenDynamic cursor is not supported by the Jet OLEDB Provider.


AbsolutePosition I need for the skipper in the listbox .. that is why I chose OpenKeyset .. I have not tried OpenDynamic .. I can easily try it .. I did find a Resync method which seems to be my answer ... I am in 'un-charted' territory .. just curious if you have used Resync() .. and what the syntax would look like ?? oRs:ReSync() ?? when I open a record to view or edit ??

Rick Lipkin


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

cSQL := "SELECT * FROM USERINFO order by USERID"

TRY

oRs:Open( cSQL,'Provider='+xPROVIDER+';Data Source='+xSOURCE+';Initial Catalog='+xCATALOG+';User Id='+xUSERID+';Password='+xPASSWORD )

CATCH oErr
MsgInfo( "Error in Opening USERINFO table" )
RETURN(.F.)
END TRY

oRs:Find("USERID = '"+xLOGIN+"'" )
IF oRs:eof
oRs:MoveFirst()
ENDIF

DEFINE WINDOW oUser ;
FROM 2,2 to 25,65 ;
of oWndMDI ;
TITLE "USERINFO Records Browse" ;
MENU BuildMenu(oRs) ;
NOMINIMIZE ;
NOZOOM ;
MDICHILD

@ 0, 0 LISTBOX oBrow FIELDS ;
oRs:Fields("USERID"):Value, ;
oRs:Fields("READ"):Value, ;
oRs:Fields("WRITE"):Value, ;
oRs:Fields("INSP"):Value, ;
oRs:Fields("SUPER"):Value, ;
oRs:Fields("DISTRICT"):Value ;
SIZES 90,60,60,60,60,100 ;
HEADERS "Userid", ;
"Read", ;
"Write", ;
"Insp", ;
"Super", ;
"Dist" ;
ON DBLCLICK _userview( "V" ) ;
of oUser ;
UPDATE

oBrow:bLogicLen := { || oRs:RecordCount }
oBrow:bGoTop := { || oRs:MoveFirst() }
oBrow:bGoBottom := { || oRs:MoveLast() }
oBrow:bSkip := { | nSkip | Skipper( oRs, nSkip ) }
oBrow:cAlias := "ARRAY"

oUSER:oClient := oBROW
oUSER:SetControl( oBROW )

ACTIVATE WINDOW oUser ;
VALID ( IIF( !lOK, UserClose(.T., oRs), .F. ))

RETURN( NIL )

//-------------------------------
STATIC FUNCTION SKIPPER( oRsx, nSkip )

LOCAL nRec := oRsx:AbsolutePosition

oRsx:Move( nSkip )

IF oRsx:EOF; oRsx:MoveLast(); ENDIF
IF oRsx:BOF; oRsx:MoveFirst(); ENDIF

RETURN( oRsx:AbsolutePosition - nRec )


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

[Rick Lipkin]

Rene

Changing the open dynamic did not seem to work .. the recordset opened and I had a top and bottom to my listbox .. however .. there was no visability of updates .. don't think SQL server supports dynamic and it just opened it like it was static.

The Resync() option gave a run-time error unfortunitly .. I have implemented a signature field so I can stop a stale recordset from writing over updated records .. I may have to abandon the local caching of the recordset in favor of just pointing to the server .. going to take a hit in performance .. I may have to re-think how I am presenting the data to the users and force them to query a record each time for get a fresh recordset.

Antonio .. I am using the same mechanism you are using in the ADO RDD with the same open methods .. have you tested multiple workstation visability ??

Rick Lipkin

[Enrico Maria Giordano]

Rick, I want to thank you for sharing these valuable informations.

EMG

[pymsoft]

Only a test, but seems work:

Code: Select all | Expand

  oRs := CREATEOBJECT( "ADODB.Recordset" )
  cSQL := "SELECT * FROM Articoli ORDER BY codice ASC"

  TRY
    oRs:Open( cSQL, oConnection, adOpenStatic, adLockOptimistic ) // 3, 3 

  CATCH oError
    MsgStop(oError:Operation+CRLF+oError:Description,"Ado Connection")
    RETURN NIL

  END TRY

    DEFINE TIMER oTimer;
           INTERVAL 1000;
           ACTION ( nTimer++, IIF( nTimer > 10, RefreshSQL( @nTimer, oTimer, oRs, oBrw ), "" ) ) OF oDlg
    ACTIVATE TIMER oTimer
    oTimer:deActivate()

  DEFINE DIALOG oDlg ;
    FROM 7,7 to 35,104 ;
    TITLE "Articoli"
  
  @ 0, 0 LISTBOX oBrw;
      FIELDS "", "", "", "", "";
      HEADERS "Codice", "Descrizione", "Desc. Aggiuntiva", "Nota 1", "Nota 2";
      OF oDlg

      oBrw:bLine     := { || { oRs:Fields( "codice" ):Value, oRs:Fields( "descrizione" ):Value, oRs:Fields( "descrizione_aggiuntiva" ):Value, oRs:Fields( "nota1" ):Value, oRs:Fields( "nota2" ):Value } }
      oBrw:bLogicLen := { || oRs:RecordCount }
      oBrw:bGoTop    := { || oRs:MoveFirst() }
      oBrw:bGoBottom := { || oRs:MoveLast() }
      oBrw:bSkip     := { | nSkip | SkipperAdo( oRs, nSkip ) }
      oBrw:cAlias    := ""
  
  ACTIVATE DIALOG oDlg;
    ON INIT ( oDlg:SetControl( oBrw ), nSecFine:= SECONDS(), InfStat( NTRIM( nSecFine-nSecIni ) + " secondi... - " + NTRIM( oRs:RecordCount ) + " records..." ), oTimer:activate() )

    oTimer:end()

  TRY
    oRs:Close()
  CATCH
  END TRY


  TRY
    oConnection:Close()
  CATCH
  END TRY


RETURN NIL


FUNCTION RefreshSQL( nTimer, oTimer, oRs, oBrw )
LOCAL nRec := oRs:AbsolutePosition

  nTimer := 0
  oTimer:deActivate()
  CursorWait()

  TRY
    oRs:Requery()
  CATCH
  END TRY


  TRY
    oRs:Move( nRec-1 )
  CATCH
  END TRY

  oBrw:refresh()
  CursorArrow()
  oTimer:Activate()

RETURN NIL

[Antonio Linares]

Rick,

>
Antonio .. I am using the same mechanism you are using in the ADO RDD with the same open methods .. have you tested multiple workstation visability ??
>

No, sorry, we have just done local tests with it

[Rick Lipkin]

Pedro

I did see the ReQuery method .. however I would prefer not to re-query a full recordset .. Have you tried the ReSync method ?? Any ideas the syntax ??

Rick Lipkin

[nageswaragunupudi]

Resync for current record only:

oRs:Resync( adAffectCurrent, adResyncAllValues )

There are times when Resync fails. (Discussion on this topic is too lengthy) Better put it in TRY CACH END construct and use ReQuery as alternative

[pymsoft]

Rick,

Resync method don't work to me. (in my tests allways fails, I'm a begginer with ADO)
Requery seems to be fast (in a local net)


Regards

[nageswaragunupudi]

There was a discussion above to use adOpenDynamic or adOpenKeyset for opening a recordset. But when a recordset is opened on clientside (with cursorlocation as adUseClient) the provider ignores what we speicfy as CursorType (adOpenDynamic or Keyset), and returns a recordset with cursortype as adOpenStatic only.

adOpenDynamic or adOpenKeyset cursortype is useful only for recorsets opened with cursor location adUseSever. Often this is not what we do.

[Rick Lipkin]

1 2
oRs:Resync( adAffectCurrent, adResyncAllValues )

Excellant .. that is what I was looking for .. I will definitly surround it with Try\Catch .. and I will report back my success ..

adAffectCurrent = 1
adResyncAllValues = 2

I have tried the above and it fails each time .. does it matter if the data has changed or not ??

Rick Lipkin

[nageswaragunupudi]

I work extensively with very large applications ADO and Oracle. My experience with MSSQL is limitted to smaller applications. Resync generally works even when the other users update the same row but occassionally fails. Better we keep in mind how Resync command internally works:

RecordSet object has Properities collection.
oRs:Properties("Resync Command"):Value is what ADO internally uses to retrieve the values of the current row. This value can be set by our program but i am not able to change this property through xharbour. (works in vbasic)

ADO has to know the primary key or some unique id of the row to internally construct this command. For example a table has two columns "custid", "custname" with custid as primary key and if ado can know this, it will internelly construct the resync command as "select * from customers where custid = ?" and calls this command with custid as the parameter each time it refreshes. For oracle it uses rowid instead of primary key unless we configure ado differently. This has its own side effects.

This should give you some idea about the limiations of Resync command.

So we can use Resync, keeping in mind that it can fail at times and for some recordsets. Solution is to programitically assign right values to the two properties oRs:Properties("Root Table") and oRs:Properties("Resync Command")

[Rick Lipkin]

RecordSet object has Properities collection.
oRs:Properties("Resync Command"):Value is what ADO internally uses to retrieve the values of the current row. This value can be set by our program but i am not able to change this property through xharbour. (works in vbasic)

ADO has to know the primary key or some unique id of the row to internally construct this command. For example a table has two columns "custid", "custname" with custid as primary key and if ado can know this, it will internelly construct the resync command as "select * from customers where custid = ?" and calls this command with custid as the parameter each time it refreshes. For oracle it uses rowid instead of primary key unless we configure ado differently. This has its own side effects.

Ok .. let me see here .. I have a table called CERT and a unique ID assigned to each row called EID

Programatically, how would you assign the above properties for the Resync method to work ??

cEid := oRs:Fields("eid"):Value
oRs:Properties("Resync Command"):Value := cEID
oRs:ReSync( 1, 2 )

Rick Lipkin

[nageswaragunupudi]

It should be like this.

Code: Select all | Expand

oRs:Properties("Resync Command"):Value := "SELECT * FROM CERT WHERE EID = ?"

But as I said earlier, assignment is not working through xHarbour.

Please read this topc on msn

http://msdn2.microsoft.com/en-us/library/ms676094.aspx

However in most cases resync is working for me usually with some exceptions.