* DENULL.PRG - stand-alone generic UDF to remove all NULL values from a given table * By M. Asherman, Copyright (c) 2000, SpaceTime Systems * 1/13/2000: initial implementation (under VFP 6.0 SP3). * 1/14/00: adjust logic to avoid blowing up on long ALTER TABLE command lines. * * This UDF alters the given table structure to disable support of NULL values, * which implicitly turns NULLs into empty values without complaining. * * Uses the reserved alias 'denulltemp' for temporary table opening. function denull * Takes 1 required argument and returns an error code (success = 0). lparameters table_arg && 1 required arg * table_arg: path and filename of the table to be altered. * This should include the explicit .DBF extension. * I considered making this argument optional, * with the default being the current alias, but * decided against this because it's too dangerous. * We may eventually want to move these macros into a separate denull.h include file. * arbitrary constants for the DENULL utility program #define DENUL_ALIAS 'denulltemp' && reserved alias for this program #define DENUL_MAXFLDS 109 && max # ALTER TABLE fields before blowing up * macros for DENULL error codes and messages #define DENUL_ERRN_NOARG 1 && unique DENULL-specific error code #define DENUL_ERRM_NOARG "Required table name argument missing." #define DENUL_ERRN_NOFILE 2 && unique DENULL-specific error code #define DENUL_ERRM_NOFILE "Table not found: " #define DENUL_ERRN_ALINUSE 3 && unique DENULL-specific error code #define DENUL_ERRM_ALINUSE "Reserved alias already in use: " local starttime, nfields, clauses, nclauses, n, elapsed * note clock reading for generating final timing statistics starttime = seconds() && # seconds since midnight * do minimal argument validation and simple, obvious error checking if empty(m.table_arg) && missing required table name arg wait window NOWAIT "DENULL: " + DENUL_ERRM_NOARG return DENUL_ERRN_NOARG && fail endif if not file(m.table_arg) && file not found wait window NOWAIT "DENULL: " + DENUL_ERRM_NOFILE + m.table_arg return DENUL_ERRN_NOFILE && fail endif if used(DENUL_ALIAS) && reserved alias already in use wait window NOWAIT "DENULL: " + DENUL_ERRM_ALINUSE + DENUL_ALIAS return DENUL_ERRN_ALINUSE && fail endif * provide progress indicator wait window nowait "Removing NULLs from " + m.table_arg + " ..." * open the given table under a temporary alias USE (m.table_arg) AGAIN IN 0 ALIAS (DENUL_ALIAS) EXCLUSIVE * final pre-loop initializations nfields = afields(fldarray, DENUL_ALIAS) && get array of field attributes clauses = '' && list of clauses for the next ALTER TABLE cmd nclauses = 0 && # clauses in next command line to be run * loop over all fields, assembling a list of ALTER NOT NULL clauses for n = 1 to alen(fldarray, 1) && loop over array rows - 1 for ea field if fldarray[m.n, 5] && field allows NULL values * add another clause to disable NULL support for this field clauses = m.clauses + ' ALTER ' + fldarray[m.n, 1] + ' NOT NULL' nclauses = m.nclauses + 1 && keep track of # clauses if m.nclauses >= DENUL_MAXFLDS && max # fields per command line reached set message to "Running ALTER TABLE up to field # " + ltrim(str(m.n)) * invoke the ALTER TABLE – SQL Command with &-macro expanded tail ALTER TABLE (m.table_arg) &clauses set message to && clear status bar msg. clauses = '' && clear the list for next command nclauses = 0 && reset counter endif endif endfor * check for a final ALTER TABLE command if not empty(m.clauses) && some unprocessed clauses remain set message to "Running ALTER TABLE for remaining fields." * invoke the ALTER TABLE – SQL Command with &-macro expanded tail ALTER TABLE (m.table_arg) &clauses set message to && clear status bar msg. endif * cleanup and return error code use in (DENUL_ALIAS) && close the temporary opening elapsed = seconds() - m.starttime && # seconds elapsed * generate final NOWAIT confirmation msg, with timing wait window nowait "Removed NULLs from " + m.table_arg ; + " in " + ltrim(transform(m.elapsed, '9999999.99')) + " seconds." return 0 && done - success