It’s time to talk code again….

But first, a little Mom update…

I spoke with her on the commute home yesterday and she’s doing pretty well. She’s still on Oxygen during the day, but is getting though the night without it. They’re still running a bunch of tests on the old girl, but besides still being very tired, she seems to be recovering pretty well.

The Dr's seem to be debating the next step, one wants to do a cardiac cath, the other doesn't think that's called for... typical.

I told her she’s gotta stop scaring us like that as I’m not getting any younger either!!

My thanks to each and every one of you who’ve expressed your concern and well wishes for my Mom. All I can say is you are all the best… and I appreciate each one of you!!

So… back to code stuff….

(for those of you who have already asked, I formated the code segment with my latest utility "Prg2Html", it takes a FoxPro program, it sets the 'color syntax' and preserves the formatting for posting)

I thought I’d talk a bit today about updateable ‘remote views’ and VFP.

A remote view, is a look, at a table that is not native to VFP (SQL, Oracle or DB2 for example). One of the great tools in the VFP arsenal is the ability to work with these non-native tables almost as though they were native.

FoxPro handles the data translations, behind the scenes, between the two systems so all you have to do is focus on the tasks at hand.

I’ve been working with, as most of you know, a process that writes literally hundreds of thousands of records to DB2 database tables, on a mainframe, from Visual FoxPro.

VFP does a great job with this type of work. However, there are a number of things that can go wrong, that you wouldn’t normally see when writing directly to native VFP tables, or even tables stored in SQL Server, or Oracle that are on the network.

One of the major issues with some DB2 systems is that the administrators place ODBC type access at the very lowest priority, and that the error messages returned to the client are less than clear as to what the problem is. They put the ODBC access behind everything else, primarily (or so I’ve been told) because of the burden it places on the server itself.

As a result, these major updates often take several hours. So I kick them off as I’m leaving for the day rather than tie up my desktop system and network/mainframe resources during the day.

Unfortunately, if one of the several hundred things that could go wrong, does go wrong, I come back in the morning to a partially completed update process.

I hate when that happens, and I’m not at all into ‘baby-sitting’ computers, so, I implement a recovery process in an attempt to mediate that risk.

First, I wrap the actual write/update process in a ‘DO WHILE’ loop, so that the actual program looks something similar to the code segment at the end of this post.

In reality, I’ve actually got a lot more going on between these various routines than what I’ve shown you here. The concept however is identical.

Ok…why do I do it this way?

I find that this cleans up the entry point, allows me to reuse the open and close files code, as well as allowing the process to auto restart if communication can be reestablished with the mainframe. It saves a lot of ‘crappy’ mornings, believe me!

I’ve been focusing quite a bit lately on ways to make programs ‘self-repairing’. With so many of the problems we encounter in the field not being things you’d ‘expect’ to see. But, instead, they’re the result of a problem on the remote system that is not always clearly reported back to your program, for a variety of reasons (usually beyond your control).

The harsh truth is, that network errors are real, fairly common and at times nearly impossible to (easily) recover from. If you’ve ever lost a document, or a spreadsheet, because of some problem that prevented you from saving it, you know that even big companies, with huge resources still can’t get their applications to recover from everything.

The key, in my mind, is to identify those problems you can code around, and code for them. The increased reliability is such a huge improvement in the end result that the additional process times, and coding efforts, are a small price to pay. Or at least I think so.

Do you think so? How do you handle programmatic recovery from system/network errors? Do you simply hope they won’t happen? Is there any initiative where you work to move in this direction?


-- Code Segment Example --

PROCEDURE RemoteLoad
WAIT CLEAR
WAIT WINDOW 'Beginning Remote Update....' NOWAIT
STORE 0 TO x1,x2,x3,x4,x5,OpenCount
lStat = .T.
lUpdt = OpenTheFiles()
IF lUpdt
  lDone = .F.
  DO WHILE ! lDone
    lDone = MainProc()
  ENDDO
ENDIF
=CloseTheFiles()
WAIT CLEAR
IF lStat
  MESSAGEBOX('Done.... - Normal Ending')
ELSE
  MESSAGEBOX('Abend, Could NOT Auto Restart!')
ENDIF

RETURN
*-----------------------------

*----------------------------*
*  Functions and Procedures  *
*----------------------------*
* -- MainProc                *
*----------------------------*
FUNCTION MainProc
SELECT CifDunsOct
AFIELDS(dbArray,'CifDunsOct')
x = ALEN(dbArray,1)
STORE 0 TO Y,y1,z
a = SECONDS()

SCAN
  IF !SEEK(CIF_Customer_ID,'vrCifDunsCust')
    SCATTER MEMVAR
    *-Space fill any NULLS in the fields....
    FOR i = 1 TO x
      fldName = ALLTRIM(dbArray(i,1))
      IF ISNULL(&fldName) OR EMPTY(&fldName)
        VarMem = 'm.' + fldName
        &VarMem = SPACE(dbArray(i,3))
      ENDIF
    ENDFOR
    INSERT INTO vrCifDunsCust FROM MEMV
    Y = Y + 1
    *--Flush changes to remote every 20 records
    IF Y >= 20
      lUpdOk = TABLEUPDATE(.T.,.F.,'vrCifDunsCust')
      IF !lUpdOk
        FOR i = 1 TO 3
          lUpdOk = TABLEUPDATE(.T.,.F.,'vrCifDunsCust')
          IF lUpdOk
            *--Exit FOR
            EXIT
          ENDIF
        ENDFOR
        IF !lUpdOk
          lStat = UpdtRecover()
          *--Exit Scan
          EXIT
        ENDIF
      ELSE
        y1 = y1+Y
        b = SECONDS()
        WAIT WINDOW ALLTRIM(STR(y1/(b-a),10,3)) + ;
          ' Records per second Written.... ' NOWAIT
      ENDIF
      Y = 0
    ENDIF
    z = z + 1
    IF MOD(z,105) = 0
      WAIT ALLTRIM(STR(z)) + ;
        ' Records Processed... ' WINDOW NOWAIT
    ENDIF
  ELSE
    z = z + 1
    WAIT ALLTRIM(STR(z)) + ;
      ' -Records Exist and were Skipped... ' WINDOW NOWAIT
  ENDIF
ENDSCAN

IF lUpdOk
  IF Y > 0
    lUpdOk = TABLEUPDATE(.T.,.F.,'vrCifDunsCust')
  ENDIF
ENDIF
*SUSPEND
*--If we're really done, this will also
*  terminate the entire process
*-- Otherwise returning it will restart the process...
RETURN lUpdOk
*-----------------------------


*----------------------------*
* -- UpdtRecover             *
*----------------------------*
FUNCTION UpdtRecover
LOCAL xlRetVal
AERROR(MyArray)
WAIT WINDOW ;
  'Table Update Failed!! - Attempting to recover...' ;
  NOWAIT
=CloseTheFiles()
xlRetVal = OpenTheFiles()
IF !xlRetVal
  MESSAGEBOX('Table Update Failed!!' + ;
        CHR(13) + 'Could Not Auto Recover')
  *--Set global to drop out of loop, we can't recover
  lDone = .T.
  ACTIVATE WINDOW DEBUG
  SUSPEND
ENDIF
RETURN xlRetVal



*----------------------------*
* -- CloseTheFiles           *
*----------------------------*
FUNCTION CloseTheFiles
IF USED('vrCifDunsCust')
  USE IN vrCifDunsCust
ENDIF
IF USED('CifDunsOct')
  USE IN CifDunsOct
ENDIF
CLOSE DATA

RETURN

*-----------------------------

*----------------------------*
* -- OpenTheFiles            *
*----------------------------*
FUNCTION OpenTheFiles
LOCAL xlRetVal
CLEAR
xlRetVal = .T.
OpenCount = OpenCount + 1
*-- Set up the error handler
xcCurOnErr = ON("ERROR") && Save original Error setting
ON ERROR xlRetVal = .F.
IF !DBUSED('CIF')
  OPEN DATABASE ;
     P:\IRFDATA\PROD\dbtables\cif\cif.DBC SHARED
ENDIF
IF xlRetVal
  SET DATABASE TO cif
ENDIF
IF xlRetVal
  IF !USED('CifDunsOct')
    USE cif!CifDunsOct IN 0 ALIAS CifDunsOct SHARED
  ENDIF
ENDIF
IF xlRetVal
  USE cif!vrCifDunsCust IN 0 ALIAS vrCifDunsCust
ENDIF
IF xlRetVal
  SELECT vrCifDunsCust
ENDIF
IF xlRetVal
  *--Wait for View to complete loading
  ? RECCOUNT()
  INDEX ON CIF_Customer_ID TAG cif_id
ENDIF
IF xlRetVal
  SET MULTILOCKS ON
  CURSORSETPROP("Buffering",5,'vrCifDunsCust')
ENDIF

RETURN xlRetVal

Technorati Tags: Microsoft - VFP - Visual - FoxPro