DBaseValidation.sml

# DBaseValidation.sml
#
# Author: Dave Breitwisch, MicroImages, Inc
# Started:  18 January 2007
#
# The purpose of this script is to open a database and table and then validate its contents based on 
# user defined functions that specify rules that must be followed.  The table contents are not changed,
# and the script reports the number of records that failed for each test.  
#
# The script is designed for additional test to be added easily and it can be modified to report additional 
# information about the records that failed each test.
clear();
# Standard parameters for database and table location (this can be replaced with popup dialogs if prefered)
###############################
class FILEPATH dbpath = "c:\test.rvc";               #  This is the filepath to your rvc link to the database
class STRING dbname = "MyDatabase";              # This is the name of your database object
class STRING openmode = "r";                            # This is the open mode for the database.  If just testing, use "r";
class STRING dbtablename = "MyTableName";   #This is the name of the table you want to test
###############################
# Open the database and table
class DATABASE dbase1 = OpenDatabase(dbpath, dbname, openmode);
class DBTABLEINFO table1 = DatabaseGetTableInfo(dbase1, dbtablename);
numeric numRecords1 = table1.NumRecords;
# Test implementation of SQL Query
#   "SELECT * FROM table1 
#   WHERE table1.field1 IN (3010, 8020) 
#   AND table1.field2 NOT IN (9999, 0)"
func TestRule1 ( ) {
	local numeric field1val, field2val, numfailed = 0;
	local numeric i;
	for i = 1 to numRecords1 {
		field1val = TableReadFieldNum(table1, "field1", i);
		if ( (field1val == 3010) or (field1val == 8020) ) {
			field2val = TableReadFieldNum(table1, "field2", i);
			if ( (field2val != 9999) and (field2val != 0) ) {
				numfailed++;
				} #end if
			} # end if
		} # end for
	return (numfailed);
	} # end TestRule1 ()
# Add additional test implementations here
# func TestRule2 ( ) {
# }
# func TestRule3 ( ) {
# }
#Main part of the script starts executing here
print("Staring Database Validation...\n");
print("Test 1 returned", TestRule1(), "failing records");
# Add Additional tests here
#print("Test 2 returned", TestRule2(), "failing records");
#print("Test 3 returned", TestRule3(), "failing records");
print("Validation Complete");
CloseDatabase(dbase1);