Friday, November 18, 2005

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: - - -

6 comments:

Firehawk said...

Bill,

You said you werern't into babysitting computers...I've been doing that at least 2-3 hours a day for weeks. All the computers in our system need software updates and minor tune-ups. My supervisor called it a "mindless, thankless task." The only positive is that I can take a book around with me and read it as I'm waiting for downloads, installations, and scans to take place.

I've always been amazed by people who could learn enough machine language to create programs. I've never had the willpower to do it myself.

Glad your Mom's doing better.

Bill said...

Firehawk - I'm not into it, but I end up doing a lot of it anyway!!

I used to do the same thing, read while waiting for the PC to finish whatever it's doing... however, every employer I've had, who saw I was waiting on a computer gave me a second, or third one for my desk!!

There's not much 'machine language' stuff going on at this end either. It's far too tedious for me, and you've got to be some sort of 'binary wizard' to be even remotely productive.

Thanks, the jury's still out on Mom's prognosis... but, at least she's in good spirits!!

No_Newz said...

All the best happy well vibes to your mom! I hope her doctors make some kind of decision soon.
As for you speaking in code... it's like reading Chinese for me, Bill. I just am not gonna get it. ;)
Have a great weekend!
Lois Lane

Bill said...

Lois - Thanks, I'll pass them along to her.

Don't feel bad, there are days it might as well be 'Chinese' to me too...

And... Unles I know the language, a lot of it looks like so much gibberish anyway...

You have a great weekend too!! It's supposed to be cool, but sunny here.

Spirit Of Owl said...

Glad to hear that your mum's picking up, although she's clearly still very ill. It's great that you're all pulling together as a family, and looking for ways to make things better for your mum, and each other. Best of luck with all of that.

I must say I LOVE babysitting computers! There's a special joy to being in a room with 20 computers all doing a large scale net installation, or network virus sweep, or some other fantastically longwinded status bar creeping task that requires all the PCs to be on and whirring in some sort of weird cyber race - and wondering which will be the "fallers"....

You know I'm kidding. :D Man, sheesh.

The DO-WHILE construct is so clearly the best way of dropping out of a process whenever error flags have been set, and yet I've had many arguments with programmers who say that it's a dangerous loop and that the WHILE-WEND is better. They're wrong, and I'm right. I see you think the same way...

:D LOL!

Bill said...

Spirit - Thanks man... she is doing better, or so it seems. There's clearly a joint effort going on, at least between 3 of us 7 kids, I don't know what's the other 4 are doing as I haven't heard from them.

I did know you were kidding... Although I used to work with a guy who truly did LOVE it... better him than me!

I used to get into these esoteric 'perfect code' discussions all the time, it seems there's always someone who believes one particular way is better than another.

All (or most) languages really offer virtually identical constructs, for example:

*-Visual FoxPro
DO WHILE n < 100
n = n + n
ENDDO

*-Basic
Do While n < 100
n = n + n
Loop

*--Pascal
while n < 100 do
n := n + n;

*--C/C++
while(n < 100)
n += n;

All perform, under the covers, in exactly the same manner.

I think the real difference, is in the comfort of 'what we know'. Every construct is potentially dangerous in the hands of a less than competent programmer!

So, 'code wizards' that we are... we must be right, and they must be wrong!! :)