ADO RecordSet: Working locally without connecting to Server

[nageswaragunupudi]

Please see the post of Mr James Bott here
viewtopic.php?f=3&t=26787&p=149225&hilit=persisting+data#p149225

Please also see MSDN Article about Persisting recordsets on local disk
http://msdn.microsoft.com/en-us/library/windows/desktop/ms675273(v=vs.85).aspx

We can read recordset(s) from a remote server, save them locally and disconnect from the server. We can continue to work with the saved recordset(s) and if we make any modifications we can upload the modifications to the Server, when we next connect to server.
This feature is useful in remote locations where connectivity to main server is not reliable and need for update data is minimal.

This example shows how to use this feature. Experts in ADO who are wellversed with this feature need to read no further. This post is intended for starters of ADO and those who are not familiar with this feature.

This example does not use the FW_ ADO functions and users without these functions also can test this example.
(This example uses only FW_CreateMDB function. If you do not have that function, you may use an alternative or use an exsisting MDB)

This example
1. Creates a new MDB with a Table
2. Connects to MDB, Opens the table in a recordset and displays in browse
3. Saves the recordset locally. closes connection and renakes MDB as MDD. This is to sumulate non-availability of the server.
4.and 5: Uses the local recordset to edit and add records locally
6. Renaems MDD to MDB, Connects to Database and attaches the connection object to our local recordset Then all changes in the local recordset are UPLOADED to the Server.
7. View the table on the server to confirm uploading all changes.

Please compile the program as it is and un in any folder you like.
For each step the program explains the stage of the program. It is better you keep the source code open and watch the same along with each step executed by the program.

Code: Select all  Expand view

/*
*
*  AdoSave.prg
*  Aug 02-2013 12:12 AM
*
*/

#include "FiveWin.Ch"
#include "adodef.ch"
#include "xbrowse.ch"
#include "hbcompat.ch"

static cPath, cMdb, cBak, cDat, cStr

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

function Main()

   local oCn, oRs

   SET DATE ITALIAN
   SET CENTURY ON
   XbrNumFormat( 'A', .t. )
   SetGetColorFocus()
   //
   cPath    := cFilePath( ExeName() )
   cMdb     := "tutor02.mdb"
   cBak     := cFileSetExt( cMdb, "mdd" )
   cDat     := cFileSetExt( cMdb, "dat" )
   cStr     := "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + cPath + cMdb + ";"

//----------------------------------------------------------------------------//
// STEP-1: Create New Database TUTOR02.MDB and create a table DLYCLN
//----------------------------------------------------------------------------//

   MsgInfo( "Step-1" + CRLF + "Create new mdb database: " + cMdb )
   CreateNewDatabase()
   MsgInfo( "Data base '" + cMdb + "' and table 'DLYCLN' created." )

//----------------------------------------------------------------------------//
// STEP-2: OPEN TABLE "DLYCLN" in the database and show contents
//----------------------------------------------------------------------------//

   MsgInfo( "Step-2" + CRLF + "Opening recordset of DLYCLN table" )
   oRs      := CreateObject( "ADODB.RecordSet" )
   oRs:CursorLocation   := adUseClient
   oRs:Open( "DLYCLN", cStr, adOpenStatic, adLockBatchOptimistic, adCmdTable )
   xbrowser oRs TITLE cMdb + ":DLYCLN"

//----------------------------------------------------------------------------//
// STEP-3: SAVE RECORDSET TO LOCAL DISK, CLOSE RECORDSET AND CONNECTION TO DATABASE
//         RENAME THE MDB AS MDD, SO THAT WE CAN NOT CONNECT TO THE DATABASE
//         THIS IS TO SIMULATE NON-AVAILABILITY OF SERVER
//----------------------------------------------------------------------------//

   MsgInfo( "Step-3: Saving RecordSet Locally as " + cDat + CRLF + "Syntax: oRs:Save( cDat, 0 )" )
   if File( cDat ); FErase( cDat ); endif
   oRs:Save( cDat, 0 )
   MsgInfo( "Record Set saved as: " + cDat )
   MsgInfo( "Closing Connection" )
   oRs:Close()
   oRs := nil
   MsgInfo( "Renaming " + cMdb + " as " + cBak + CRLF + ;
      "This is equivalent to not having connection with Server" )
   ? FRename( cMdb, cBak )
   MsgInfo( "Now we can not connect to Database " + cMdb )

//----------------------------------------------------------------------------//
// STEP-4: OPEN LOCALLY SAVED FILE AS RECORDSET WITHOUT CONNECTING TO ANY DATABASE
//         AND EDIT THE TABLE IN XBROWSE AND SAVE CHANGES LOCALLY
//----------------------------------------------------------------------------//

   MsgInfo( "Step-4" + CRLF + "Now we open locally saved RecSet without any connection" + CRLF + ;
            "You may edit any data inline" )
   oRs      := CreateObject( "ADODB.RecordSet" )
   oRs:CursorLocation   := adUseClient
#ifdef __XHARBOUR__
   oRs:Open( cDat, OleDefaultArg(), adOpenStatic, adLockBatchOptimistic, adCmdFile )
#else
   oRs:Source     := cDat
   oRs:LockType   := adLockBatchOptimistic
   oRs:Open()
#endif

   xbrowser oRs TITLE "localcopy:DLYCLN (fastedit mode)" FASTEDIT  SETUP ;
      ( oBrw:bSaveData := { || .t. } )
   oRs:Save()
   oRs:Close()

//----------------------------------------------------------------------------//
// STEP-5: RE-OPEN LOCALLY SAVED FILE AS RECORDSET WITHOUT CONNECTING TO ANY DATABASE
//         ADD TWO NEW ROWS
//         AND EDIT THE TABLE IN XBROWSE AND SAVE CHANGES LOCALLY
//----------------------------------------------------------------------------//

   MsgInfo( "Step-5: Saved and closed. Reopening Again" )

   oRs      := CreateObject( "ADODB.RecordSet" )
   oRs:CursorLocation   := adUseClient
#ifdef __XHARBOUR__
   oRs:Open( cDat, OleDefaultArg(), adOpenStatic, adLockBatchOptimistic, adCmdFile )
#else
   oRs:Source     := cDat
   oRs:LockType   := adLockBatchOptimistic
   oRs:Open()
#endif
   MsgInfo( "Adding two new rows to the local copy" )

   oRs:AddNew( { "WDAY", "COLN" }, { "FriDay", 800 } )
   oRs:AddNew( { "WDAY", "COLN" }, { "SaturDay", 800 } )

   xbrowser oRs TITLE "localcopy:DLYCLN (fastedit mode)"  FASTEDIT  SETUP ;
      ( oBrw:bSaveData := { || .t. } )
   oRs:Save()

//----------------------------------------------------------------------------//
// STEP-6: NOW WE RENAME THE MDD AS MDB. THE DATABASE IS AVAILABLE TO CONNECT
//         WE CONNECT TO THE DATABASE AND ATTACH OUR LOCAL RECSET TO THE CONNECTION
//         UPLOAD LOCAL CHANGES TO THE DATABASE, DELETE LOCAL FILE
//----------------------------------------------------------------------------//

   MsgInfo( "Step-6" + CRLF + "Now we rename " + cBak + " to " + cMdb + CRLF + ;
      "Connect to Database and Upload local changes to Main DataBase" )

   ? FRename( cBak, cMdb )
   MsgInfo( cMdb + " now available. Connecting" )
   oCn   := CreateObject( "ADODB.Connection" )
   oCn:CursorLocation   := adUseClient
   oCn:Open( cStr )
   MsgInfo( "Connected to Database. Connecting Local RecSet to Database" + CRLF + ;
            "Syntax: oRs:ActiveConnection := oCn" )
   oRs:ActiveConnection := oCn
   MsgInfo( "Now our recordset is connected to database" + CRLF + ;
            "We shall upload changes. Syntax: oRs:UpdateBatch()" )
   oRs:UpdateBatch()
   MsgInfo( "Uploaded. Now we close RecSet & Connection" )

   oRs:Close()
   oCn:Close()
   oRs := oCn := nil
   ? FErase( cDat )

//----------------------------------------------------------------------------//
// STEP-7: NOW WE CONNECT TO DATABASE NORMALLY, OPEN TABLE IN THE DATABASE
//         TO CONFIRM THAT ALL CHANGES ARE PRESENT IN THE DATABASE
//----------------------------------------------------------------------------//

   MsgInfo( "Step-7: Now we open the table in the database and" + CRLF + "verify if all changes are uploaded" )

   oRs   := CreateObject( "ADODB.RecordSet" )
   oRs:CursorLocation   := adUseClient
   oRs:Open( "DLYCLN", cStr, adOpenStatic, adLockBatchOptimistic, adCmdTable )
   xbrowser oRs

   MsgInfo( "All Done." + CRLF +  "Cleaning Up" )
   oRs:Close()
   oRs   := nil

return (0)

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

static function CreateNewDatabase()

   local cSql, oCn, n
   local aData := { { "MonDay", 40 }, { "TuesDay", 50 }, { "WednesDay", 60 } }

   if File( cMdb )
      FErase( cMdb )
   endif
   if File( cBak )
      FErase( cBak )
   endif
   FW_CreateMDB( cMdb )

   TEXT INTO cSql
   CREATE TABLE DLYCLN (
   ID AUTOINCREMENT PRIMARY KEY,
   WDAY VARCHAR(20),
   COLN MONEY
   )
   ENDTEXT

   oCn   := CreateObject( "ADODB.Connection" )
   oCn:CursorLocation   := adUseClient
   oCn:Open( cStr )
   oCn:Execute( cSql )

   for n := 1 to Len( aData )
      cSql  := "INSERT INTO DLYCLN ( WDAY, COLN ) VALUES ( '" + ;
               aData[ n, 1 ] + "'," + cValToChar( aData[ n, 2 ] ) + " )"
      oCn:Execute( cSql )
   next n
   oCn:Close()
   oCn   := nil

return nil

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

[Armando]

Mr. Rao:

Thanks a lot for your aportation, with the RecordSet's GetRows() method can you get the same result?

Regards

[nageswaragunupudi]

Mr. Rao:

Thanks a lot for your aportation, with the RecordSet's GetRows() method can you get the same result?

Regards

With GetRows() method you can read the recordset into an array.
Agreed that you can do anything with the array locally.
You can make modifications too.

When you reconnect, how do you upload the changes?
You need to develop your own logic to identify (1) new rows inserted, (2) rows deleted and (3) rows modified and the columns in the rows that are modified.
Then you need to develop your own logic to add those new lines, delete those rows and update the specific modified columns of the modified rows.

Is all the trouble worth it? And even you do it, can you do it optimally and without errrors at all?

With the persistent recordset, you need to do only two operations:
oRs:ActiveConnection := <justopened connection object>
oRs:UpdateBatch()

Ofcourse you need to use TRY..CATCH for all ADO methods.

You may say that you do not want to modify the data but only want for reference. The why not GetRows() enough?
Yes but only for that session.
When the user closes the application and reopens, the information in the array is lost. Till you connect to the Server you can not get the info.

In the case of persistent recordset what we do is :

When the user starts the program
Check if connection to server is available.
(i) If avaliable Synchronize local recordset with Server ( Push and Pull )
(ii) If not, continue with the local recordset

[nageswaragunupudi]

Let me add. Handling recordsets is more cumbersome than RDD and TData(Base)

[Armando]

Mr. Rao:

Thank for your comments.

Regards

[Maurizio]

Mr. Rao:

Thanks for your works,

I have a problem with

MsgInfo( "Renaming " + cMdb + " as " + cBak + CRLF + ;
"This is equivalent to not having connection with Server" )

? FRename( cMdb, cBak )

frename returns -1 , this is because cMdb is still open

Regards
Maurizio

[nageswaragunupudi]

? FRename( cMdb, cBak )

frename returns -1 , this is because cMdb is still open

If it returns -1, then renaming was not successful for whatever reason and one of such reasons could be that the mdb is not fully closed and released.

If you execute the code I posted without any changes, the connection should be closed and the renaming should be successful.

Though I made this sample with access for easy testing, this principle can be tested on networks or over internet by disconnecting the net connection.

[Maurizio]

Mr. Rao

with the FWH 13.07 revised build it works

Thanks
Maurizio