WinBatch Tech Support Home

Database Search

If you can't find the information using the categories below, post a question over in our WinBatch Tech Support Forum.

TechHome

WinBatch
OLE COM ADO CDO ADSI LDAP
Errors

Can't find the information you are looking for here? Then leave a message over on our WinBatch Tech Support Forum.

OLE Error Trapping

 Keywords:

Question:

I'm just looking for opinions.....
I have put together several scripts which are refreshing/updating either Access or Oracle Tables. So I might execute an SQL Statement like:
DROP TABLE [myTable];
which, of course, will result in a Winbatch OLE Exception if the table does not exist. And there are several ways to handle this:
  1. Simply place Errormode(@OFF) prior to the DB.Exceute("DROP TABLE [myTable]")

  2. Write code to iterate the tables into a string and put in code like 
    If isTable(myTable)
   DB.Execute("DROP TABLE [myTable]")
Endif

  3. Create an OLE error Object, then write a :cancel goto/gosub code block that passes the OLE Object to an error-handler UDF.

I have at various times employed all three but they increase in complexity and lines of code ( from 1-3 ). Assuming that if I executed the DROP statement and it errored - I would receive a wwwbatch.ini entry under [OLE] - "Target Table Does Not Exist" - which would be pretty similar to the error description if I used #3 ( but I would also get the SQLState and other info ).

I guess I am looking for suggestions to split the difference between Winbatch and OLE error-handling, capture the error ( perhaps echo to SQL.log ), keep the script alive - but with minimal coding.

Answer:

Well, in general the motto is "Whatever works".

It may be easier if you look to see if the table exists first, by looking thru the table collection in Access.

The following is a script that'll list all the tables in a given database, then print out a "quickie" structure desc. You should be able to adapt it for what you need. 

;	first get the directory...
flags = 1|2
accessdir = askdirectory("Working Directory", "C:", "", "Are you sure?", flags)
;	get the database...
types="*.mdb|*.svy"
dbname=AskFileName("SelectFile", accessdir, types, "*.mdb", 1)
; setup access constants
dbBigInt = 16
dbBinary = 9
dbBoolean = 1
dbByte = 2
dbChar = 18
dbCurrency = 5
dbDate = 8
dbDecimal = 20
dbDouble = 7
dbFloat = 21
dbGUID = 15
dbInteger = 3
dbLong = 4
dbLongBinary = 11
dbMemo = 12
dbNumeric = 19
dbSingle = 6
dbText = 10
dbTime = 22
dbTimeStamp = 23
dbVarBinary = 17
;	init the var to hold the structure text...
tabletext = strcat(dbname, @crlf, @crlf)
;	open access and the specified database...
AccessApp = objectopen("Access.Application")
AccessApp.opencurrentdatabase(dbname)
db = AccessApp.currentdb
;	set up the ref to tabledefs collection...
tdefs = db.tabledefs
;	loop thru the table defs...
for t = 0 to tdefs.count-1
	; setup the ref for this table in the collection...
	table = db.tabledefs(t)
	;	ignore the system tables...
	if strindexwild(table.name, "MSys", 1) == 0
		;	set up the reference to the field collection...
		tabletext = strcat(tabletext, "TABLE:", table.name, @crlf)
		tabletext = strcat(tabletext, " FIELD NAME                      FIELD TYPE                     FIELD LENGTH", @crlf)
;	setup the ref for the table collection...
		flds = table.fields
		for f = 0 to flds.count-1
		;  set up the reference to this field in the collection...
		;  and their properties & values...
			field = table.fields(f)
			fname = field.name
			ftype = field.type
			fsize = field.size
;		setup the field description based on the type value...
			select ftype
			   case dbBigInt
				fldtext = "Big Integer"
				break
			   case dbBinary
				fldtext = "Binary"
				break
			   case dbBoolean
				fldtext = "Boolean"
				break
			   case dbByte
				fldtext = "Byte"
				break
			   case dbChar
				fldtext = "Char"
				break
			   case dbCurrency
				fldtext = "Currency"
				break
			   case dbDate
				fldtext = "Date/Time"
				break
			   case dbDecimal
				fldtext = "Decimal"
				break
			   case dbDouble
				fldtext = "Double"
				break
			   case dbFloat
				fldtext = "Float"
				break
			   case dbGUID
				fldtext = "GUID"
				break
			   case dbInteger
				fldtext = "Integer"
				break
			   case dbLong
				fldtext = "Long"
				break
			   case dbLongBinary
				fldtext = "Long Binary (OLE Object)"
				break
			   case dbMemo
				fldtext = "Memo"
				break
			   case dbNumeric
				fldtext = "Numeric"
				break
			   case dbSingle
				fldtext = "Single"
				break
			   case dbText
				fldtext = "Text"
				break
			   case dbTime
				fldtext = "Time"
				break
			   case dbTimeStamp
				fldtext = "Time Stamp"
				break
			   case dbVarBinary
				fldtext = "VarBinary"
				break
			endselect
			;	format the display fields
			fldtext = strfix(fldtext, " ", 30)
			fldname = strfix(fname, " ", 30)
			tabletext = strcat(tabletext, @tab, fldname, @tab, fldtext, @tab, field.size, @crlf)
		next
	;	finished with this table add an extra CRLF to the text...
	if strindexwild(table.name, "MSys", 1) == 0 then tabletext = strcat(tabletext, @crlf)
	endif
next
;	write the data to a file...
output = "eaprint.txt"
oh = fileopen(output, "write")
filewrite(oh, tabletext)
fileclose(oh)
;	close up, go home...
objectclose(AccessApp)
Run("notepad.exe",output)
return
exit
I'm sure ADO has a tabledef collection or something similar. Just run thru the tabledef collection, build a list and then do a 
if ItemLocate(tablelist, @tab) <> 0 then db.execute(sql)

You might also consider using User Defined Subroutines, which have their own private variable space, UDS's share the global variable space with the calling program. Thus you can make an easy to code function that can also reference the main script's variables, and thus you may be able to do something.

Or...Using IntControl 73 you could write an error handler. That *might* work out well.

More:

Ok, here it is - the basic work in progress. The script is a complete test and can be replicated by anyone with MDAC.

You can easily change a few settings to
(1) display a button to cance raher than resume
(2) not display the error dialog at all.

Then, there is a lot of open-ended stuff ( I told you it was a strategy for lazy people ) and some questions I have about the relationship between the WB error handler and the error object.

There is the issue of using the global array which makes it very clumsy to initialize OLE Objects ( I prefer DB= as opposed to aE[0]= for a connection object. ) 

; ///////////////////////////////////////////////////////////
; WINBATCH - UDF's to handle OLE Connection Errors         //
;                                                          //
; NOTE: This is just a first go at it, needs to be tested  //
;       with ADOX Objects                                  //
;                                                          //
;       lots of random thoughts, maybe some potential      //
;                                                          //
;                                                          //
; Stan Littlefield 05/12/2002 - work in progress           //
; ///////////////////////////////////////////////////////////

#DefineFunction isMDAC()
Return( RegExistKey(@RegClasses,"ADODB.Connection") )
#EndFunction

; /////////////   global error array   //////////////////////
; aE[0] = OLE Connection
; aE[1] = Reserved - Recordset
; aE[2] = Reserved - Column or Command
; aE[3] = OLE Error Object
; aE[4] = Winbatch LastError()
; aE[5] = Line in Script where error occurred
; aE[6] = Error Handler Flag - @TRUE=quit on error @FALSE=Try To Recover
; aE[7] = Reserved - perhaps from error recovery state
;                     0 = do nothing
;                     1 = enumerate tables
;                     2 = offer user chance to re-type SQL Command
;                     3 = retry previous line ( for timeouts/file locking )
;                     and on and on...
; aE[8] = Reserved - I'll think of something
; aE[9] = If True display error dialog
; /////////////   where   ///////////////////////////////////
#DefineFunction erenum()
aE  = ArrDimension(10)
For i=0 to 9
   aE[i] = 0
Next
Return( aE )
#EndFunction


#DefineFunction iserr(aE)
; ======== Object State Enum ========
; adStateClosed       = 0
; adStateOPen         = 1
; adStateConnecting   = 2
; adStateExecuting    = 4
; adStateFetching     = 8
n         = 0
If aE[0] <> 0
   aE[3]  = aE[0].Errors
   n      = aE[3].Count
Endif
If n == 0  && aE[4] == 0 Then Return @FALSE

e1     = aE[5]
e2     = ""
e3     = ""

If n>0
   For i     = 0 To n-1
      x      = aE[0].Errors(i)
      e2 = Strcat(e2,x.Description," ")
      e3 = StrCat(e3,"[",x.Source,"] ",x.SQLState)
   Next
   aE[3].Clear()
Endif

handle = FileOpen( StrCat( dirget(),"adoerr.txt"), "WRITE" )
FileWrite(handle,e1)
FileWrite(handle,e2)
FileWrite(handle,e3)
FileClose(handle)

If ! aE[9] Then Return( aE[6] )

eRFormat=`WWWDLGED,5.0`

eRCaption=`Error In Program Execution`
eRX=56
eRY=93
eRWidth=281
eRHeight=97
eRNumControls=8

If aE[6]
   eR01=`140,2,132,DEFAULT,PUSHBUTTON,DEFAULT,"Close This Dialog and Cancel Program Execution",1`
Else
   eR01=`140,2,132,DEFAULT,PUSHBUTTON,DEFAULT,"Close This Dialog and Resume Program Execution",1`
Endif
eR02=`6,4,132,DEFAULT,STATICTEXT,DEFAULT,"This Error Information Written To Text File [adoerr.txt]"`
eR06=`6,18,64,DEFAULT,STATICTEXT,DEFAULT,"PROGRAM LINE #"`
eR03=`6,30,264,DEFAULT,VARYTEXT,e1,""`
eR07=`6,42,64,DEFAULT,STATICTEXT,DEFAULT,"OLE/ADO/SQL ERROR"`
eR04=`6,54,264,DEFAULT,VARYTEXT,e2,""`
eR08=`6,66,64,DEFAULT,STATICTEXT,DEFAULT,"ADO SOURCE/SQLState"`
eR05=`6,78,264,DEFAULT,VARYTEXT,e3,""`

ButtonPushed=Dialog("eR")

Return( aE[6] )
#EndFunction

;======= Script Starts Here =================================

If ! isMDAC()
   message("Error:Cannot Continue","MDAC Must Be Installed. Contact IS")
   exit
Endif

;// set up Winbatch error handler as a Gosub
IntControl(73,2,0,0,0)
;// since we are using a UDF set up a global array to handle OLE Objects
aE     = erenum()

;//the next two values could be read in from an INI file
;aE[6]  = @FALSE    ; try to recover from error
aE[6]  = @TRUE    ; Close Program on Error
aE[9]  = @TRUE    ; Display Dialog to shoow user the error

;// this is just for testing - create a sample MDB file using ADOX
cMDB         = StrCat(dirget(),"errtest.mdb")
cConn        = "Provider=MicroSoft.Jet.OLEDB.4.0; Data Source=%cMDB%;"
If FileExist( cMDB ) Then FileDelete( cMDB )
cat          = ObjectOpen("ADOX.Catalog")
cat.Create(cConn)
ObjectClose(cat)

;// with MDB created, initialize array elements as OLE Objects
aE[0]  = ObjectOpen("ADODB.Connection")
aE[3]  = ObjectOpen("ADODB.Error")

aE[0].Open(cConn)

;//should get error that Table does not Exist
;//Want the UDF to recover from the errr
RS = aE[0].EXECUTE("SELECT * FROM MYTABLE")

;// since I have decided to recover from the error
;// perhaps check the state of aE[7] for recovery options


:close
ErrorMode(@off)
aE[0].Close()
ObjectClose(aE[0])
ObjectClose(aE[3])
Exit

;// the biggest challenge appears to be to separate Winbatch
;// errors from OLE errors
:WBERRORHANDLER
aE[4] = LastError()
aE[5] = wberrorhandlerline
n = 0
v = ""
aE[8].MoveFirst()
aE[8].Find( StrCat("ERRNUM = '",aE[4],"' " ) ) ; find error in
; XML error lookup
If ! aE[8].Eof()
fld = aE[8].Fields("ERRDESC")
v = fld.Value
fld = aE[8].Fields("ERRCASE") ; numeric: OLE errors=1, 11 if Fatal
; Winbatch errors=2, 10 if Fatal
If fld.Value == 1
If iserr(aE) Then Goto close
Else
Message( StrCat( "Winbatch Error ",aE[4] ),StrCat( v,@CRLF,aE[5] ) )
Goto close
Endif
Else
Goto close
Endif
Return


Article ID:   W15243

File Created: 2002:09:05:13:50:48
Last Updated: 2002:09:05:13:50:48