OpenVMS Source-Code Demos

MYSQL_IMPORT_HELPER

1000	%title "mysql_import_helper"						!
	%ident                                          "version_106.5"		!
	declare string constant k_program = "mysql_import_helper 106.5"		!
	!========================================================================================================================
	! title   : mysql_import_helper_xxx.bas
	! author  : Neil Rieck
	! created : 2014-07-03
	! purpose : create SQL table definitions from DEC-BASIC record declarations (for our 2014 parallel-data demonstrations)
	! overview: 1) All our RMS record definitions are stored in folder [.fil] and named whatever.rec
	!	    2) This program can read those maps and generate SQL table definitions.
	!	    3) This program can also produce BASIC template code for exporting RMS data to a csv for import by MySQL
	!	    4) I am doing it this way because there are hundreds of files -AND- I may want to do this from batch
	!	       (and maintaining this single program is less error prone than modifying hundreds of sources)
	! details : Normally you would manually create sql scripts from data found in these files (or through some analysis if
	!	    these files were lost). In our case, it makes more sense to just insert hints, in the form of BASIC comments,
	!	    which this program can detect then action. Here are the trigger phrases so far:
	!		-marker-mysql-start			(required)
	!		-marker-mysql-engine:Aria		(optional; defaults to no engine statement)
	!		-marker-mysql-database:whatever		(optional but highly recommended)
	!		-marker-mysql-datatype:WINDOWS-1252	(optional; defaults to UTF-8)
	!		-marker-mysql-table:whatever		(required; processing begins with this tag)
	!		-marker-mysql-stop			(required)
	!	    note: see examples shown at the bottom of this program
	! caveat  : This program generates BASIC code which does trial open, invokes FSP$(), then does a speculative reopen. All
	!           the docs I've ever encountered say it is better to do do a trial open with a useropen statement. That method
	!	    requires a lot more work but may be required in future versions of this code
	! history:
	! ver who when   what
	! --- --- ------ --------------------------------------------------------------------------------------------------------
	! 100 NSR 140702 1. initial effort
	!	  140703 2. more effort
	!	  140704 3. more effort ("source data collection" and "sql script creation" are reasonably complete)
	! 101 NSR 140707 1. added support for "-marker-mysql-database:"
	!		 2. started work on file data extraction
	!	  140708 3. more effort
	! 102 NSR 140708 1. decided to pull in basic fragments from an external file (why cram in all into here?)
	!		 2. now split the output into two files (one sql, one basic; what was I thinking before this?)
	!	  140709 3. a few cosmetic changes
	!	  140711 4. a few cosmetic changes
	!	  140716 5. added a few more MySQL commands to the template
	! 103 NSR 140718 1. now fs_o$ is a combination of "database and table" to avoid table naming conflicts.
	!		 2. now pull in three basic templates
	!	  140721 3. removed default response to file-spec question
	!		 4. added an over-write warning
	! 104 NSR 150106 1. added code to deal with providing more informaion when reading a corrupt RMS data file
	! 105 NSR 150912 1. now switch-off sql_log_bin before doing anything						bf_105.1
	!     NSR 150914 2. added support for meta remark "-marker-mysql-engine:"
	!     NSR 150915 3. more work
	! 106 NSR 170314 1. started adding code to do a data conversion to unicode
	!		 2. appended "_106" to the three template files
	!		 3. introduced various bug fixes and documentation changes
	!		 4. added support for meta remark "-marker-mysql-datatype:"
	!     NSR 170316 5. more work
	!     NSR 170317 6. bug fix (required for working with MySQL/MariaDB on a remote server)			bf_106.3
	!		 7. moved "csmis$sql:" into a string constant
	!========================================================================================================================
	option type=explicit							!
	set no prompt								!
	!
	!	includes
	!
	%include "lib$routines"	%from %library "sys$library:basic$starlet"	! for lib$spawn
	%include "$rmsdef"	%from %library "sys$library:basic$starlet"	! rms$
	!
	!	constants
	!
	declare string constant k_out_path	= "csmis$sql:"			! CHANGE TO WHATEVER YOU DESIRE ON YOUR SYSTEM
	declare string constant htab    	= '9'C				! horizontal tab
	declare string constant k_template1	= "mysql_import_helper_basic_template_106_part1.bas"
	declare string constant k_template2	= "mysql_import_helper_basic_template_106_part2.bas"
	declare string constant k_template3	= "mysql_import_helper_basic_template_106_part3.bas"
	!
	!	declarations
	!
	declare	string	fs0$						,	!	&
			fs1$						,	!	&
			fs7$						,	!	&
			fs8$						,	!	&
			fs9$						,	!	&
			junk$						,	!	&
			test$						,	!	&
			fdata$						,	!	&
			fdata_mc$					,	!	&
			fdata_uc$					,	!	&
			row$						,	!	&
			size$						,	!	&
			remove_row_prefix$				,	!	&
			use_engine_meta_tag$				,	!	&
		long	equal_pos%					,	!	&
			junk%						,	!	&
			junk2%						,	!	&
			state%						,	!	&
			rc%						,	!	&
			debug%						,	!	&
			c%						,	!	&
			p%						,	!	&
			z%						,	!	&
			error_count%					,	!	&
			table_count%					,	!	&
			table_attempts%					,	!	&
			line_count%					,	!	&
			d1%						,	!	&
		string	d2$						,	!		&
			p_table$					,	! parameter	&
			p_engine$					,	! parameter	&
			p_datatype$					,	! parameter	&
			p_database$						! parameter
	!
	dim string c$(500)							! comments
	dim string p$(500,3)							! extracted parameters
										! 1=DATATYPE
										! 2=ROW NAME
										! 3=size in bytes
	!=======================================================================
	!	main
	!=======================================================================
	main:									!
	print string$(len(k_program), asc("="))					!
	print k_program								!
	print string$(len(k_program), asc("="))					!
	print									!
	!
	get_fs:									!
	print "input filespec to search:"					!
	print "examples: [.fil]*.rec"						!
	print "          [.fil]p*.rec"						!
	print "          [.fil]tro*.rec"					!
	print "          [.fil]exact-name.rec"					!
	print "          exact-name.ext"					!
	input "-?-full/partial file spec? (filespec, default=exit) ";fs0$	!
	junk% = 0								! init our test
	junk% = 1 if pos(edit$(fs0$,32),".BIN",1)>0				!
	junk% = 1 if pos(edit$(fs0$,32),".EXE",1)>0				!
	junk% = 1 if pos(edit$(fs0$,32),".OBJ",1)>0				!
	junk% = 1 if pos(edit$(fs0$,32),".OLB",1)>0				!
	if junk% = 1 > 0 then							!
	    print "-e-error: illegal file extension"				!
	    goto get_fs								!
	end if 									!
	fs0$ = edit$(fs0$,4+2)							! remove controls + white space
	goto sortie if fs0$ = ""						!
	!
	if pos(fs0$,";",1) = 0 then						!
	    fs0$ = fs0$ + ";"							! we only want the most recent version
	end if 									!
	print "-i-target fs: "+ fs0$						!
	!
	!	okay, let's locate the file(s)
	!
	declare long constant k_max_file_names = 500				!
	declare long file_context%						!
	declare long file_name_pointer%						!
	file_name_pointer% = 0							!
	dim string file_names$(k_max_file_names)				! init
	!
	file_context% = 0							! init (for good form)
	read_loop1:								!
	rc% = lib$find_file(fs0$, junk$, file_context%)				! does the folder/file exist?
	select rc%								!
	    case    RMS$_NORMAL							! found something
		if file_name_pointer% < k_max_file_names then			!
		    file_name_pointer% = file_name_pointer% + 1			!
		    file_names$(file_name_pointer%)=junk$			!
		    goto read_loop1						! yeah, I know, bad form
		end if								!
	    case    RMS$_NMF							! no more files
	    case    RMS$_FNF							! file-not-found
		print "-e-oops, file not found"					!
	    case    RMS$_DNF							! directory-not-found
		print "-e-oops, directory not found"				!
	    case else								! oops
		print "-e-lib$find_file error: "+ str$(rc%)			!
	end select								!
	junk% = lib$find_file_end(file_context%)				! conserve resources; close the channel
	!
	if file_name_pointer% = 0 then						!
	    print "-e-no files were detected using your search criteria"	!
	    goto get_fs								!				***--->>>
	end if									!
	!
	!	show file(s) discovered by search
	!
	for junk% = 1 to file_name_pointer%					!
	    print using "###";junk%;						!
	    print " ";file_names$(junk%)					!
	next junk%								!
	!
	print "-?-choice? (1-"+ str$(file_name_pointer%) +", Q/uit) ";		!
	input junk$								!
	when error in								!
	    junk% = integer(junk$)						!
	use									!
	end when								!
	select junk%								!
	    case 1 to file_name_pointer%					!
	    case else								!
		goto sortie							!
	end select								!
	fs1$ = file_names$(junk%)						!
	!-----------------------------------------------------------------------
	!	make sure the user really want to continue
	!-----------------------------------------------------------------------
	print ""
	print "WARNING: continuing this procedure could overwrite existing files."
	print "-?-are you sure you want to continue? (y/N, default=N) ";	!
	input junk$								!
	junk$ = edit$(junk$,32+2)						!
	goto sortie if left$(junk$,1) <> "Y"					!
	!
	!-----------------------------------------------------------------------
	!	file processing
	!-----------------------------------------------------------------------
	input "-?-debug level? (0-2) ";junk$					!
	when error in								!
	    debug% = integer(junk$)						!
	use									!
	end when								!
	!
	input "-?-remove row prefix? (Y/n, default=Y) ";remove_row_prefix$	!
	remove_row_prefix$ = left$(edit$(remove_row_prefix$,32+2),1)		!
	remove_row_prefix$ = "Y" if remove_row_prefix$ <> "N"			!
	!
	input "-?-use engine meta tag? (y/N, default=N) ";use_engine_meta_tag$	!
	use_engine_meta_tag$ = left$(edit$(use_engine_meta_tag$,32+2),1)	!
	use_engine_meta_tag$ = "Y" if use_engine_meta_tag$ <> "N"		!
	!
	when error in								!
	    print "-i-opening: "+ fs1$						!
	    open fs1$ for input as #1, recordsize 32700, access read		!
	    state% = 0								!
	    table_count% = 0							!
	    table_attempts% = 0							!
	    print "-i-state "+ str$(state%) +", looking for START marker"	!
	    while 1								!
		linput #1, fdata$						! read file data
		line_count% = line_count% + 1					!
		fdata_mc$ = edit$(fdata$   ,128+16+8)				! trail, compress, leading		(cleanup)
		fdata_uc$ = edit$(fdata_mc$,32)					! convert to upper case		(for parsing)
		!
		!  always look for file-spec string constant declarations (eg. k_fs_tbl_data$ = "csmis$dat:TroubleDB3000.dat")
		!
		junk% = pos(fdata_uc$,"K_FS_",1)				! constant file spec here?
		if  junk% > 0 then						! yes
		    if left$(fdata_uc$,1) <> "!" then				! is this is not a comment line
			c% = c% + 1						! then prep to store in comment array
			junk2% = pos(fdata_uc$,"!",junk%+1)			! any more exclams?
			if junk2% = 0 then					! nope
			    c$(c%) = right$(fdata_mc$,junk%)			! we'll store the remainder as basic comments
			else							! yes
			    c$(c%) = seg$(fdata_mc$,junk%, junk2%-1)		! we'll store these as basic comments
			end if							!
		    end if							!
		end if								!
		!
		!	looking for start tag
		!
		if  state% = 0 then						!
		    error_count% = 0						!
		    if pos(fdata_uc$,"MARKER-MYSQL-START",1) > 0   then		!
			state% = state% + 1					!
			print "-i-state "+ str$(state%) +", looking for TABLE marker"
			table_attempts% = table_attempts% + 1			!
			p_database$	= ""					! various parameter inits (good form)
			p_table$	= ""					!
			p_engine$	= ""					!
			p_datatype$	= ""					!
		    end if							!
		    iterate							! get next line (it might be another directive)
		end if								!
		!
		if  state% = 1 then						!
		    junk% = pos(fdata_uc$,"MARKER-MYSQL-ENGINE:",1)	   	!
		    if junk% > 0 then						!
!~			state% = state% + 1					x optional field so no state change
!~			print "-i-state "+ str$(state%) +", processing begins"	x
			junk% = pos(fdata_mc$,":",1)				!
			p_engine$ = right$(fdata_mc$,junk% + 1)			!
			print "-i-engine name: "+ p_engine$			!
		    end if							!
		    junk% = pos(fdata_uc$,"MARKER-MYSQL-DATABASE:",1)	   	!
		    if junk% > 0 then						!
!~			state% = state% + 1					x optional field so no state change
!~			print "-i-state "+ str$(state%) +", processing begins"	x
			junk% = pos(fdata_mc$,":",1)				!
			p_database$ = right$(fdata_mc$,junk% + 1)		!
			print "-i-database name: "+ p_database$			!
		    end if							!
		    junk% = pos(fdata_uc$,"MARKER-MYSQL-DATATYPE:",1)	   	!
		    if junk% > 0 then						!
!~			state% = state% + 1					x optional field so no state change
!~			print "-i-state "+ str$(state%) +", processing begins"	x
			junk% = pos(fdata_mc$,":",1)				!
			p_datatype$ = right$(fdata_mc$,junk% + 1)		!
			print "-i-datatype: "+ p_datatype$			!
		    end if							!
		    junk% = pos(fdata_uc$,"MARKER-MYSQL-TABLE:",1)	   	!
		    if junk% > 0 then						!
			state% = state% + 1					! move to state 2 (begin processing data)
			print "-i-state "+ str$(state%) +", processing begins"	!
			junk% = pos(fdata_mc$,":",1)				!
			p_table$ = right$(fdata_mc$,junk% + 1)			!
			print "-i-table name: "+ p_table$			!
		    end if							!
		    iterate							! get next line (it might be another directive)
		end if								!
		!
		if  state% = 2 then						!
		    junk% = pos(fdata_uc$,"MARKER-MYSQL-STOP",1)	   	!
		    if junk% > 0 then						! if we saw the stop marker
			state% = state% + 1					!
			print "-i-state "+ str$(state%) +", processing ends"	!
		    else							!
			gosub process_this_line					!
			iterate							! get next line (could be directive or data)
		    end if							!
		end if								!
		!
		!	write out data
		!
		if state% = 3 then						!
		    !
		    !	before we begin, let's attempt to make sense of p_datatype
		    !
		    p_datatype$ = edit$(p_datatype$,32+2)			!
		    p_datatype$ = "UTF8"   if     p_datatype$ = ""		! set a default if necessary
		    p_datatype$ = "CP1252" if pos(p_datatype$,"WINDOWS",1) > 0	! eg. WINDOWS-1252
		    p_datatype$ = "CP1252" if pos(p_datatype$,"ISO"    ,1) > 0	! eg. ISO-8859-1
		    p_datatype$ = "CP1252" if pos(p_datatype$,"CP"     ,1) > 0	! eg. CP-1252
		    p_datatype$ = "UTF8"   if pos(p_datatype$,"UTF"    ,1) > 0	!
		    !
		    !	now let's define some filenames
		    !
		    fs9$ = k_out_path + "helper_" + p_database$ +"_"+ p_table$ +".bas"
		    fs8$ = k_out_path + "helper_" + p_database$ +"_"+ p_table$ +".sql"
		    fs7$ = k_out_path + "helper_" + p_database$ +"_"+ p_table$ +"2.sql"
		    !-----------------------------------------------------------
		    !	sql statements are written first
		    !-----------------------------------------------------------
		    print "-i-creating file: "+ fs8$ +" <<<---"			!
		    open fs8$ for output as #2					&
			,organization sequential				&
			,recordsize 32700					!
		    print #2, "/* ---"						!
		    print #2, " file: "+ fs8$ 					!
		    print #2, " created by:"					!
		    print #2, " pgm: "+ k_program				!
		    print #2, " src: "+ fs1$					!
		    print #2, " dat: "+ p_datatype$				!
		    print #2, " tim: "+ date4$(0) +" "+ time$(0)		!
		    print #2, " REMINDER: start mysql monitor with -v -v -v"	!
		    print #2, "--- */"						!
		    print #2, "\W"						! enable SHOW WARNINGS after each command
		    print #2, "set sql_log_bin = 0;"				!					bf_105.1
		    print #2, "create database if not exists "+ p_database$ +";"	if p_database$ <> ""
		    print #2, "use "+ p_database$	+";"				if p_database$ <> ""
		    print #2, "drop table if exists "+ p_table$ +";"		!
		    print #2, "create table "+ p_table$ +" ("			!
		    for z% = 1 to p%						!
			print #2, htab + p$(z%,2);				! row name
			select len(p$(z%,2))					!
			    case <8						!
				print #2, htab + htab + htab;			!
			    case <16						!
				print #2, htab + htab;				!
			    case else						!
				print #2, htab;					!
			end select						!
			print #2, "varchar("+ p$(z%,3) +")";			! datatype declaration and size
			if z% <> p% then					! if not last line
			    print #2, ","					! tack on a comma then EOL
			else							!
			    print #2, ""					! just EOL
			end if							!
		    next z%							!
		    !
		    junk$ = ""							! zap
		    if use_engine_meta_tag$ = "Y" then				!
			p_engine$ = edit$(p_engine$,2)				! remove all white-space just in case
			if p_engine$ <> "" then					! if an engine meta-tag was found
			    junk$ = "engine="+p_engine$				! build an engine statement
			end if							!
		    end if
		    print #2, ") " + junk$ +" \G"				! "\G" is used to replace ";"
!~~~		    print #2, "load data infile '"+				x
		    print #2, "load data local infile '"+			!				bf_106.3	&
			k_out_path	+					! csmis$sql:					&
			p_database$ +"_"+					!						&
			p_table$ +".csv' into table "+ p_table$ +		!						&
			" fields escaped by '' \G"				! "\G" is used to replace ";"
		    print #2, "/* ---"
		    print #2, "  example index definitions:"
		    print #2, "alter table TabX add         index neil2_idx (x_date_time);"
		    print #2, "alter table TabX add         index neil3_idx (x_tbl_serial, x_date);"
		    print #2, "  this will produce a unique constraint and index"
		    print #2, "alter table TabX add primary key             (x_tbl_serial);"
		    print #2, "  this produces an internal constraint, not an index"
		    print #2, "alter table TabX add foreign key neil9_fk (x_tbl_serial) references TabY (y_tbl_serial);"
		    print #2, "  CAVEAT: place actual statements in file: ";fs7$;" which won't be overwritten by the HELPER tool"
		    print #2, "--- */"
		    close #2							!
		    !-----------------------------------------------------------
		    !	basic template stuff is written second
		    !-----------------------------------------------------------
		    print "-i-creating file: "+ fs9$ +" <<<---"			!
		    open fs9$ for output as #2					&
			,organization sequential				&
			,recordsize 32700					!
		    !-----------------------------------------------------------
		    !	<<< start our meta program at line 1000 with a remarks >>>
		    !-----------------------------------------------------------
		    print #2, "1000"+ htab +'%title "'+ fs9$ +'"'
		    print #2, htab +"!--------------------------------------------------"
		    print #2, htab +"! created by:"
		    print #2, htab +"! pgm: "+ k_program
		    print #2, htab +"! src: "+ fs1$
		    print #2, htab +"! tim: "+ date4$(0) +" "+ time$(0)
		    print #2, htab +"!--------------------------------------------------"
		    print #2, htab +"! p_table   : "+ p_table$
		    print #2, htab +"! p_engine  : "+ p_engine$
		    print #2, htab +"! p_datatype: "+ p_datatype$
		    print #2, htab +"! p_database: "+ p_database$
		    print #2, htab +"!--------------------------------------------------"
		    !
		    !	tack on more remarks (usually storage constants we may have detected)
		    !
		    if c% > 0 then						! if any comments to write
			print #2, htab +"! possible storage constants:"		!
			for junk% = 1 to c%					!
			    print #2, htab +"! "+ c$(junk%)			!
			next junk%						!
			print #2, htab +"!"					!
		    end if							!
		    !-----------------------------------------------------------
		    !	now insert BASIC template 1/3
		    !-----------------------------------------------------------
		    when error in						!
			open k_template1 for input as #98			&
			    ,organization sequential				&
			    ,recordsize 32700					!
			while 1							!
			    linput #98, junk$					!
			    print #2, junk$					!
			next							!
		    use
			select err						!
			    case 11						!
			    case else						!
				print #2,"-e-error:"+str$(err)+" during xfer of template_part1"
				error_count% = error_count% + 1			!
			end select						!
		    end when							!
		    !-----------------------------------------------------------
		    !	generate code between templates 1 + 2
		    !-----------------------------------------------------------
		    !
		    print #2, htab +'!'						!
		    print #2, htab +'fs_o$ = "'+ k_out_path + p_database$ +'_'+	&
			p_table$ +'.csv;"' + htab +"! fs format: folder:database_table.csv"
		    junk$ = edit$(p_datatype$,32+2)				!
		    select p_datatype$						!
			case "ASIS"						!
			    junk% = 0						! do nothing
			case "CP1252"						!
			    junk% = 1						! best-effort conversion to CP1252
			case "UTF8"						!
			    junk% = 2						! best-effort conversion to UTF8
			case else						!
			    p_datatype$ = "ASIS"				!
			    junk% = 0						!
		    end select							!
		    print #2, htab +'requested_datatype% = '+ str$(junk%) +" ! datatype:"+ p_datatype$
		    print #2, htab +'!'						!
		    !-----------------------------------------------------------
		    !	now insert BASIC template 2/3
		    !-----------------------------------------------------------
		    when error in						!
			open k_template2	for input as #98		&
			    ,organization sequential				&
			    ,recordsize 32700					!
			while 1							!
			    linput #98, junk$					!
			    print #2, junk$					!
			next							!
		    use
			select err						!
			    case 11						!
			    case else						!
				print #2,"-e-error:"+str$(err)+" during xfer of template_part2"
				error_count% = error_count% + 1			!
			end select						!
		    end when							!
		    !-----------------------------------------------------------
		    !	generate code between templates 2 + 3
		    !-----------------------------------------------------------
		    !   1) now generate data to extact then write
		    !
		    d1% = 1							! init to position #1
		    print #2, htab +"print #99,"+ htab +"&"			!
		    for z% = 1 to p%						!
			d2$ = p$(z%,3)						! get the size (in bytes)
			junk$ = "    cleanup(mid$(xfer$,"+ str$(d1%) +","+ d2$ +"),requested_datatype%)"
			select len(junk$)					!
			    case < 16						!
				junk$ = junk$ + htab + htab + htab 		! make it look pretty
			    case < 24						!
				junk$ = junk$ + htab + htab			!
			    case < 32						!
				junk$ = junk$ + htab				!
			end select						!
			if z%<>p% then		 				! if not last line
			    print #2, htab; junk$; ";delim$; &"			! prep for continutation logic
			else							!
			    print #2, htab; junk$				! just EOL
			end if							!
			d1% = d1% + integer(d2$)				! adjust d1 for next pass
		    next z%							!
		    !-----------------------------------------------------------
		    !	now insert BASIC template 3/3
		    !-----------------------------------------------------------
		    when error in
			open k_template3	for input as #98		&
			    ,organization sequential				&
			    ,recordsize 32700					!
			while 1							!
			    linput #98, junk$					!
			    print #2, junk$					!
			next							!
		    use
			select err						!
			    case 11						!
			    case else						!
				print #2,"-e-error:"+str$(err)+" during xfer of template_part3"
				error_count% = error_count% + 1			!
			end select						!
		    end when							!
		    close #2							!
		    !
		    !	we're done files for this table. Reset variables for the next table (if any)
		    !
		    table_count% = table_count% + 1				!
		    state% = 0							! init
		    p_table$ = ""						! init
		    p_database$ = ""						! init
		    p% = 0							! init
		    mat p$ = nul$						! init
!~~~		    c% = 0							x skip
!~~~		    mat c$ = null						x skip
		    print "-i-state "+ str$(state%) +", looking for START marker (again)"
		end if								!
		!
	    next 								!
	use									!
	    print "-i-status: "+ str$(err)					!
	end when								!
	print "----------------------"
	if state% <> 0 then							!
	    print "-e-error: exited with state: "+str$(state%)+" rather than 0"	!
	    error_count% = error_count% + 1					!
	end if									!
	if table_count% = 0 then						!
	    print "-e-error: no tables were processed"				!
	    error_count% = error_count% + 1					!
	else									!
	    print "-i-processed: "+str$(table_count%)+" tables"			!
	end if									!
	if error_count% = 0 then						!
	    print "-i-no errors detected"					!
	    print "-i-Caveat:"
	    print "   Now you must make a single change to each generated BASIC program"
	    print "   then compile and link them."
	else									!
	    print "-e-error count: "+str$(error_count%)				!
	end if									!
	goto sortie								!
	!-----------------------------------------------------------------------
	!	<<< process this line >>>
	!-----------------------------------------------------------------------
	declare long 	i%,	&
			j%,	&
			k%,	&
			x%,	&
			y%,	&
			mode%
	process_this_line:
	print "-i-data: "; fdata$	if debug% > 0				!
	!
	!	discard blank or commented line
	!
	test$ = edit$(fdata_uc$,2)						! squash for test
	if test$ = "" or left$(test$,1) = "!" then				!
	    print "-i-ignoring BLANK or COMMENTED line"	if debug% > 0 		!
	    goto ptl_exit							!
	end if									!
	!
	!	locate start
	!
	i% = pos(fdata_uc$,"MAP",1   )						!
	j% = pos(fdata_uc$,"("  ,i%+1)						!
	k% = pos(fdata_uc$,")"  ,j%+1)						!
	if (i% > 0) and (j% > i%) and (k% > j%) then				!
	    x% = k%+1								! start after closing parentheses
	else									!
	    x% = 1								! start at position one
	end if									!
	!
	!	locate end
	!
	y% = len(fdata_uc$)							!
	junk% = pos(fdata_uc$,"&",1)						! locate any ampersand (ends a concatinated line)
	y% = junk%-1	if y% >= junk% and junk% <> 0				!
	junk% = pos(fdata_uc$,"!",1)						! locate any exclamation (ends real code)
	y% = junk%-1	if y% >= junk% and junk% <> 0				!
	junk% = pos(fdata_uc$,",",1)						! locate any comma (ends a concatinated list)
	y% = junk%-1	if y% >= junk% and junk% <> 0				!
	!
	!	do we have anything to test?
	!
	test$ = edit$(seg$(fdata_uc$,x%,y%),2)					!
	if test$ = "" then							!
	    print "-i-ignoring BLANK line"	if debug% > 0 			!
	    goto ptl_exit							!
	end if									!
	!
	!	look for data types
	!
	junk% = pos(fdata_uc$,"STRING ",x%)					!
	if junk% > 0 then							!
	    mode% = 1								!
	    x% = junk% + 7							!
	end if									!
	junk% = pos(fdata_uc$,"xQUAD ",x%)					! disabled
	if junk% > 0 then							!
	    mode% = 2								!
	    x% = junk% + 5							!
	end if									!
	junk% = pos(fdata_uc$,"xLONG ",x%)					! disabled
	if junk% > 0 then							!
	    mode% = 3								!
	    x% = junk% + 5							!
	end if									!
	junk% = pos(fdata_uc$,"xWORD ",x%)					! disabled
	if junk% > 0 then							!
	    mode% = 4								!
	    x% = junk% + 5							!
	end if									!
	junk% = pos(fdata_uc$,"xBYTE ",x%)					! disabled
	if junk% > 0 then							!
	    mode% = 5								!
	    x% = junk% + 5							!
	end if									!
	got_it:
	!
	print "-i-hack1: "+ seg$(fdata_mc$,x%,y%)	if debug% > 0
	!
	if mode% = 1 then							! processing datatype "string"
	    test$ = seg$(fdata_mc$,x%,y%)					! eg. d91_whatever = 5
	    equal_pos% = pos(test$,"=",1)					! expect to see "=" but none was found
	    if equal_pos% = 0 then						!
		print "-w-warning, '=' was not found (line: ";str$(line_count%);" data: ";test$;")"	!
		sleep 1								!
	    else								!
		row$ = edit$( left$ (test$,equal_pos%-1), 2)			! row name with no w/s
		!
		!	optional step (to remove the prefix)
		!
		if remove_row_prefix$ = "Y" then				! if prefix removal is desired
		    select left$(row$,1)					!
			case "D","d"						! eg. d91_whatever (or d1_whatever)
			    select mid$(row$,2,1)				! is the next character numeric?
				case "0" to "9"					! yes
				    junk% = pos(row$,"_",3)			! any trailing underscore after the number?
				    if junk% > 0 then				! yes
					row$ = right$(row$,junk% + 1)		! so extract the balance
				    end if					!
			    end select						!
		    end select							!
		end if								!
		!
		!	now check the declared length of the mapped (fixed) string
		!
		junk$ = edit$( right$(test$,equal_pos%+1), 2)			! string size
		when error in							!
		    junk% = integer(junk$)					!
		use								!
		    junk% = -999						!
		    print "-e-error, bad conversion (line: ";str$(line_count%);" data: ";test$;")"	!
		    sleep 1							!
		    error_count% = error_count% + 1				!
		end when							!
		size$ = str$(junk%)						!
		select junk%							!
		    case < 0							! this is not good
			print "-e-not storing this zero-length entry (line: ";str$(line_count%);" data: ";test$;")"
			sleep 1							!
		    case 0							! probably "align"
			print "-w-not storing this zero-length entry (line: ";str$(line_count%);" data: ";test$;")"
		    case else							!
			p% = p% + 1						! prep to insert
			p$(p%,1) = "STRING"					!
			p$(p%,2) = row$						!
			p$(p%,3) = size$					!
			print "-i-hack2: ";p$(p%,1);" ";p$(p%,2);" ";p$(p%,3)	if debug% > 0
		end select							!
	    end if								!
	    goto ptl_exit							!
	end if									!
	!
	print "-w-data type not currently supported (line: ";str$(line_count%);" data: "; test$; ")"	!
	error_count% = error_count% + 1						!
	sleep 1									!
	!
	ptl_exit:								!
	return									!
	!
	!	that's all folks
	!
32000	sortie:									!
	end									!
	!
	!########################################################################################################################
	!
	!	this is one example where I placed four sets of markers in the source code to aid in building sql scripts
	!
    %let %comments=0								! need this so I can append no-build code
    %if  %comments=1 %then							!
	!========================================================================================================================
	! Title   : TroubleDB_xxx.rec
	! Author  : Neil S. Rieck
	!
	! xxxx 920717 NSR 1. Original
	!	[...snip...]
	! 3220 140614 NSR 1. stole 3-bytes from ft_spare09 to create ft_tbl_depta (alternate control center)
	!		  2. moved d21_tbl_depta into area previously occupied by d21_spare09a
	!      140704 NSR 3. added extraction markers for mysql
	!========================================================================================================================
	!	[...snip...]
	!
	!	--- material data base records ---
	!
	map(TblMat27Save)string	d27_whole_record_save	=177	 !177
	map(TblMat27)	string	d27_whole_record	=177	,!177								&
				d27_align		=  0	 !177
	map(TblMat27)string	d27_tbl_ticket		=  9	,!  9 AB0123CTL							&
				d27_second_part		=168	,!177 total							&
				d27_align		=  0	 !177
	!-marker-mysql-start
	!-marker-mysql-database:replicant
	!-marker-mysql-datatype:cp1252
	!-marker-mysql-table:material
	map(TblMat27)string	d27_tbl_serial		=  6	,!  6 AB0123							&
				d27_tbl_dept1		=  3	,!  9 CTL (sub-total 1)						&
								 !								&
				d27_checked		=  1	,!  1				beginning of second part	&
				d27_timestamp		= 12	,! 13 CCYYMMDDhhmm						&
				d27_tbl_dept2		=  3	,! 16 WAT							&
		    		d27_initial		=  3	,! 19								&
				d27_job			=  2	,! 21								&
				d27_billable		=  1	,! 22								&
				d27_part_number		= 15	,! 37								&
				d27_sort_code		=  7	,! 44	part indicator (eg. USOC)				&
				d27_description		= 65	,!109								&
				d27_unit_cost		= 11	,!120	      xxxxx.xx	(was  7, now 11)			&
				d27_quantity		=  4	,!124		  xxxx						&
				d27_sub_total		= 12	,!136	  xxxxxxxxx.xx	(was 11, now 12)			&
				d27_total_cost		= 15	,!151	 xxxxxxxxxx.xx	(was 12, now 15)			&
				d27_pein		=  7	,!158								&
				d27_fill_10		= 10	,!168								&
				d27_align		=  0	 !168	 (sub-total 2)
	!-marker-mysql-stop
	!
	!	--- activity (time) data base records ---
	!
	map(TblActiv25Save)string	d25_whole_record_save	=330	! this must match the size of d25_whole_record (below)
	!
	map(TblActiv25)string		d25_whole_record	=330,	!							&
					d25_align		=  0	!
	map(TblActiv25)string		d25_tbl_ticket		=  9,	!  9 AB0123CTL						&
					d25_second_part		=321,	!330							&
					d25_align		=  0	!330
	map (TblActiv25)string		d25_first_part  	= 36,	!							&
					d25_alltext     	=277,	!313							&
					d25_pein		=  7,	!320							&
					d25_fill_10		= 10,	!330							&
					d25_align       	=  0	!330
	!-marker-mysql-start
	!-marker-mysql-database:replicant
	!-marker-mysql-table:activity
	map(TblActiv25)string		d25_tbl_serial		=  6	,!  6 AB0123 --+- ticket				&
					d25_tbl_dept1		=  3	,!  9 CTL    --+		(sub-total 1)		&
									 !							&
					d25_timestamp		= 12	,! 12 CCYYMMDDhhmm					&
					d25_tbl_dept2		=  3	,! 15 WAT --+-- user (required for DAR etc.)		&
					d25_initial		=  3	,! 18 NSR --+						&
					d25_min			=  4	,! 22 							&
					d25_job			=  2	,! 24							&
					d25_type		=  1	,! 25							&
					d25_billable		=  2	,! 27							&
					d25_text1		= 43	,! 70							&
					d25_text2		= 78	,!148							&
					d25_text3		= 78	,!226							&
					d25_text4		= 78	,!304							&
					d25_pein		=  7	,!311							&
					d25_fill_10		= 10	,!321							&
					d25_align		=  0	 !321 total
	!-marker-mysql-stop
	map(TblActiv25)														&
	    string														&
					d25_tbl_ticket		=  9	,!  9 AB0123CTL						&
					d25_timestamp		= 12	,! 12 CCYYMMDDhhmm					&
					d25_tbl_dept2		=  3	,! 15 WAT --+-- user (required for DAR etc.)		&
					d25_initial		=  3	,! 18 NSR --+						&
					d25_min			=  4	,! 22 							&
					d25_job			=  2	,! 24							&
					d25_type		=  1	,! 25							&
					d25_billable		=  2	,! 27							&
					d25_text_whole		=277	,!313 - will do an edit$(128+16+8) on this		&
					d25_pein		=  7	,!320							&
					d25_fill_10		= 10	,!330							&
					d25_align		=  0	 !
	!
	!	--- define the Ticket Number record (prefix: d24_) ---
	!
	map (TblTktNum24)												&
	    string													&
		d24_ccyymm		=  6	,! ccyymm								&
		d24_number		=  4	 ! xxxx		only allows 9999 tickts each month
	map (TblTktNum24)												&
	    string													&
		d24_ccyymm		=  6	,! ccyymm								&
		d24_num4		=  1	,! 0-9, A-Z	will allow 36 x 1000 tickets each month			&
		d24_num321		=  3	 ! xxx
	!
	!	--- define the Trouble History record (prefix: d23_) ---
	!
	map (TblHist23)													&
	    string	d23_whole_record	=107	,!  107								&
			d23_align		=  0	 !  107
	map (TblHist23)													&
	    string	d23_tbl_ticket		=  9	,!   9 AB0123CTL						&
			d23_timestamp		= 14	 !     CCYYMMDDhhmmss (to the second for proper visual order)
	!-marker-mysql-start
	!-marker-mysql-database:replicant
	!-marker-mysql-table:history
	map (TblHist23)													&
	    string	d23_tbl_serial		=  6	,!   6 AB0123							&
			d23_tbl_dept1		=  3	,!   9 CTL							&
			d23_timestamp		= 14	,!  23 CCYYMMDDhhmmss (to the second for proper visual order)	&
	    		d23_status		=  2	,!  25								&
			d23_tbl_dept2		=  3	,!  28 ADM							&
	    		d23_initial		=  3	,!  31 NSR							&
			d23_text		= 58	,!  89 bla bla bla						&
			d23_hold_type		=  1	,!  90 I=Informational, S=Stop(no meas), R=Resumable		&
			d23_pein		=  7	,!								&
			d23_fill_10		= 10	,!								&
			d23_align		=  0	 !  107
	!-marker-mysql-stop
	!
	!======================================================================
	!
	!	--- define the Trouble Data record (prefix: d21s_) ---
	!
	!	this is for data snap shots to detect changes
	!
	map (TblSnap21)													&
	    string													&
		s21_tbl_ticket		=  9	,!  9									&
		s21_tbl_dept2		=  3	 ! 12
	map (TblSnap21)													&
	    string													&
		!													&
		!	trouble section (main part)									&
		!													&
		s21_tbl_serial		=   6	,!  6 AB0123							line 1	&
		s21_tbl_dept1		=   3	,!  9 CTL (creator group)						&
		s21_tbl_dept2		=   3	,! 12 WAT (referred to group)						&
	        s21_tbl_report		=  50	,! 62									&
		!													&
		s21_tbl_xref		=  15	,! 77 cross-ref info						line 2	&
		s21_tbl_type		=   2	,! 79 ticket type							&
		s21_tbl_Orig		=   1	,! 80 original flag							&
		s21_tbl_grp		=   3	,! 83 teck group	who						&
		s21_tbl_ini		=   3	,! 86 tech initials	who						&
		s21_tbl_sup		=   1	,! 87 supplement flag							&
		s21_tbl_act		=   1	,! 88 activity flag							&
		s21_tbl_mat		=   1	,! 89 material flag							&
		s21_tbl_ana		=   4	,! 93 analysis flag							&
		s21_tbl_cause		=   2	,! 95 manager check							&
		!													&
		s21_tbl_status		=   2	,! 97 trouble status						line 3	&
		s21_tbl_severity	=   1	,! 98 trouble severity							&
		s21_tbl_hv		=   1	,! 99 high-value flag							&
		s21_tbl_pri		=   2	,!101 action log priority						&
		s21_tbl_interface	=   1	,!102 interface								&
		s21_tbl_cc		=   2	,!104 customer contact							&
		s21_tbl_at		=   1	,!105 attached text flag						&
		s21_tbl_action		=  50	,!155 action summary							&
		!													&
		s21_tbl_create_cc	=   2	,!157 cc							line 4	&
		s21_tbl_create		=  10	,!167 yymmdd hhmm							&
		s21_part1a_align	=   0	,!167		----------------- sub-total for PART1a			&
		!													&
		s21_tbl_hold_cc		=   2	,!169 cc		+						&
		s21_tbl_hold		=  10	,!179 yymmdd hhmm	+-- these offsets must match below		&
		s21_tbl_resum_cc	=   2	,!181 cc		+						&
		s21_tbl_resume		=  10	,!191 yymmdd hhmm	+						&
		s21_tbl_clear_cc	=   2	,!193 cc		+						&
		s21_tbl_clear		=  10	,!203 yymmdd hhmm	+						&
		s21_tbl_fn_ttr		=   1	,!204			+						&
		s21_tbl_ttr		=   6	,!210 _99:59		+						&
		!													&
		s21_tbl_onsite_cc	=   2	,!212								line 5	&
		s21_tbl_onsite		=  10	,!222 yymmdd hhmm							&
		s21_tbl_close8		=   8	,!230 ccyymmdd								&
		s21_tbl_due_cc		=   2	,!232 cc								&
		s21_tbl_due		=  10	,!242 yymmdd hhmm							&
		s21_tbl_apmt_cc		=   2	,!244 cc								&
		s21_tbl_apmt		=  10	,!254 yymmdd hhmm							&
		s21_eta_cc		=   2	,!256									&
		s21_eta			=  10	,!266									&
		!													&
		s21_tbl_contract	=  15	,!281			15>					line 6	&
		s21_tbl_sla_obj		=   4	,!285			19>						&
		s21_tbl_pro_obj		=   4	,!289			23>						&
		s21_loading_obj		=   4	,!293			27>---- sub total for PART1b			&
		s21_part1b_align	=   0	,!293			???						&
		s21_tbl_weight		=   4	,!297									&
		s21_we			=   8	,!305									&
		!												line 7	&
		s21_network_number	=   8	,!313									&
		s21_activity_number	=   4	,!317									&
		s21_cost_center		=   6	,!323									&
		s21_wbs_number		=   7	,!330									&
		s21_general_ledger	=   7	,!337									&
		s21_ion			=   7	,!344									&
		!													&
		s21_acct		=   4	,!348									&
		s21_loc			=   6	,!354									&
		s21_org			=   8	,!362									&
		s21_est			=   7	,!369									&
		s21_toe			=   3	,!372									&
		s21_pin			=   9	,!381									&
		!													&
		s21_tbl_act_charges	=  11	,!392								line 8	&
		s21_tbl_mat_charges	=  11	,!403									&
		s21_tbl_total_charges	=  12	,!415					 				&
		!												line 9	&
		!													&
		s21_tbl_src		=   3	,!418								line 10	&
		s21_tis_align		=   0	,!418 (sub-total 1 main)						&
		!													&
		!	device info section										&
		!													&
		!	Note: No longer the same as map "DevData11"							&
		!													&
		s21_station		=  15	,! 15									&
		s21_assoc_system	=  15	,! 30									&
		s21_spare06a		=   6	,! 36									&
		s21_tbl_depta		=   3	,! 39									&
		s21_device_serial	=  15	,! 54									&
		s21_device_name		=  20	,! 74									&
		s21_clli		=  12	,! 86									&
		!													&
		s21_company		=  30	,!116									&
		s21_spare18		=  18	,!134									&
		s21_postal		=  10	,!144									&
		s21_flr			=   5	,!149									&
		s21_address		=  37	,!186									&
		s21_city		=  20	,!206	 								&
		!													&
		s21_name		=  25	,!231									&
		s21_tel			=  10	,!241									&
		s21_ext			=   4	,!245									&
		s21_prov		=   4	,!249									&
		!													&
		s21_netterm		=  18	,!267	net								&
		s21_ots_Ckt		=  18	,!285	ckt								&
		s21_bc			=  12	,!297	project/bc							&
		s21_xtra		=  15	,!312	no form label							&
		!													&
		s21_notes		=  68	,!380									&
		s21_dis_align		=   0	,!380 (sub-total 2 device)						&
		!													&
		!	xtra info section										&
		!													&
		s21_name_alt		=  25	,! 25									&
		s21_tel_alt		=  10	,! 35									&
		s21_ext_alt		=   4	,! 39									&
		s21_remote_sys		=  10	,! 49									&
		s21_misc		=  68	,!117									&
		s21_xis_align		=   0	,!117 (sub-total 3 xtra)						&
		!													&
		!	future data section (for emergency new field use)						&
		!													&
		s21_fut_data		= 185	,! 185									&
		s21_fds_align		= 0	,! 185 (sub-total 4 future)						&
		!													&
		!	vendor specific info section									&
		!													&
		s21_vendor_data		= 300	,!300									&
		s21_vsi_align		= 0	,!300 (sub-total 5 vendor)						&
		!													&
		s21_whole_align		= 0	 !  0
	map (TblSnap21)													&
	    string				 !++++-- running total							&
		fill$			= 167	,!167									&
		s21_part1a_align	=   0	,!167		----------------- sub-total for PART1a			&
		s21_tbl_hold_cc		=   2	,!169 }	cc								&
		s21_tbl_hold		=  10	,!179 }	yymmddhhmm							&
		s21_tbl_resume_cc	=   2	,!181 }	cc								&
		s21_tbl_resume		=  10	,!191 }	yymmddhhmm -- we don't care if these '}' change			&
		s21_tbl_clear_cc	=   2	,!193 }	cc								&
		s21_tbl_clear		=  10	,!203 }	yymmddhhmm							&
		s21_tbl_fn_ttr		=   1	,!204 }									&
		s21_tbl_ttr		=   6	,!210 }	_99:59								&
						 !									&
		s21_tbl_onsite_cc	=   2	,!212								line 5	&
		s21_tbl_onsite		=  10	,!222 yymmdd hhmm							&
		s21_tbl_close8		=   8	,!230 ccyymmdd								&
		s21_tbl_due_cc		=   2	,!232 cc								&
		s21_tbl_due		=  10	,!242 yymmdd hhmm							&
		s21_tbl_apmt_cc		=   2	,!244 cc								&
		s21_tbl_apmt		=  10	,!254 yymmdd hhmm							&
		s21_eta_cc		=   2	,!256									&
		s21_eta			=  10	,!266									&
						!									&
		s21_tbl_contract	=  15	,!281			15>					line 5	&
		s21_tbl_sla_obj		=   4	,!285			19>						&
		s21_tbl_pro_obj		=   4	,!289			23>						&
		s21_loading_obj		=   4	,!293			39>---- sub total for PART1b			&
		s21_part1b_align	=   0	,!293									&
		fill$			= 125	,!418	this fill tops off to	sub-total 1 (main)			&
		s21_tis_align		=   0	,!418									&
		fill$			= 380	,!380				sub-total 2 (device)			&
		s21_dis_align		=   0	,!370									&
		fill$			= 117	,!117				sub-total 3 (xtra)			&
		s21_xis_align		=   0	,!117									&
		fill$			=  185	,!185				sub-total 4 (future)			&
		s21_fds_align		=   0	,!185									&
		fill$			= 300	,!300				sub-total 5 (vendor)			&
		s21_vsi_align		=   0	,!300									&
						 !									&
		s21_whole_align		=   0	 !
	map (TblSnap21)													&
	    string				 !++++-- running total							&
		fill$			= 155	,!155									&
		s21_tbl_create_12	=  12	,!167	ccyymmddhhmm		end of PART1a				&
		s21_part1a_align	=   0	,!167		----------------- sub-total for PART1a			&
		s21_tbl_hold_12		=  12	,!179	ccyymmddhhmm							&
		s21_tbl_resume_12	=  12	,!191	ccyymmddhhmm							&
		s21_tbl_clear_12	=  12	,!203	ccyymmddhhmm							&
		s21_tbl_fn_ttr		=   1	,!204									&
		s21_tbl_ttr		=   6	,!210						for alignment		&
						 !									&
		s21_tbl_onsite_12	=  12	,!222	ccyymmddhhmm							&
		s21_tbl_close8		=   8	,!230	ccyymmdd				for alignment		&
		s21_tbl_due_12		=  12	,!242	ccyymmdd hhmm							&
		s21_tbl_apmt_12		=  12	,!254	ccyymmdd hhmm							&
		s21_eta_12		=  12	,!266									&
						 !									&
		s21_tbl_contract	=  15	,!281						for alignment		&
		s21_tbl_sla_obj		=   4	,!285						for alignment		&
		s21_tbl_pro_obj		=   4	,!289						for alignment		&
		s21_loading_obj		=   4	,!293									&
		s21_tbl_weight		=   4	,!297						for alignment		&
		s21_we			=   8	 !306						for alignment
	map (TblSnap21)													&
	    string				 !++++-- running total							&
		s21_whole_record	=1400	,!1400									&
		s21_whole_align		= 0	 !  0
	map (TblSnap21)													&
	    string													&
		!													&
		!	trouble info section										&
		!													&
		fill$			= 418	,! 418									&
		s21_tis_align		=   0	,! 418 (sub-total 1 main)						&
		!													&
		!	device info section										&
		!													&
		s21_dev_data		= 380	,! 380				used in COS testing			&
		s21_dis_align		=   0	,! 380 (sub-total 2 device)						&
		!													&
		!	xtra info section										&
		!													&
		fill$			= 117	,! 117									&
		s21_xis_align		=   0	,! 117 (sub-total 3 xtra)						&
		!													&
		!	future data section (for emergency new field use)						&
		!													&
		s21_fut_data		= 185	,! 185									&
		s21_fds_align		= 0	,! 185 (sub-total 4 future)						&
		!													&
		!	vendor specific section										&
		!													&
		s21_vendor_data		= 300	,!300									&
		s21_vsi_align		= 0	,!300 (sub total 5 vendor)						&
		!													&
		s21_whole_align		= 0	 !  0
	!======================================================================
	!
	!	--- define the Trouble Data record (prefix: d21_) ---
	!
	map (TblData21)													&
	    string													&
		d21_tbl_ticket		=   9	,!  9 AB0123CTL								&
		d21_tbl_dept2		=   3	 ! 12 WAT (referred to group)		(to enforce alignment)		!
	!-marker-mysql-start
	!-marker-mysql-database:replicant
	!-marker-mysql-table:ticket
	map (TblData21)													&
	    string													&
		!													&
		!	trouble section (main part)									&
		!													&
		d21_tbl_serial		=   6	,!  6 AB0123							line 1	&
		d21_tbl_dept1		=   3	,!  9 CTL (creator group)						&
		d21_tbl_dept2		=   3	,! 12 WAT (referred to group)						&
	        d21_tbl_report		=  50	,! 62									&
		!													&
		d21_tbl_xref		=  15	,! 77 cross-ref info						line 2	&
		d21_tbl_type		=   2	,! 79 ticket type							&
		d21_tbl_Orig		=   1	,! 80 original flag							&
		d21_tbl_grp		=   3	,! 83 teck group	who						&
		d21_tbl_ini		=   3	,! 86 tech initials	who						&
		d21_tbl_sup		=   1	,! 87 supplement flag							&
		d21_tbl_act		=   1	,! 88 activity flag							&
		d21_tbl_mat		=   1	,! 89 material flag							&
		d21_tbl_ana		=   4	,! 93 analysis flag							&
		d21_tbl_cause		=   2	,! 95 manager check							&
		!													&
		d21_tbl_status		=   2	,! 97 trouble status						line 3	&
		d21_tbl_severity	=   1	,! 98 trouble severity							&
		d21_tbl_hv		=   1	,! 99 high-value flag							&
		d21_tbl_pri		=   2	,!101 action log priority						&
		d21_tbl_interface	=   1	,!102 interface								&
		d21_tbl_cc		=   2	,!104 customer contact							&
		d21_tbl_at		=   1	,!105 attached text flag						&
		d21_tbl_action		=  50	,!155 action summary							&
		!													&
		d21_tbl_create_cc	=   2	,!157 cc							line 4	&
		d21_tbl_create		=  10	,!167 yymmdd hhmm							&
		d21_part1a_align	=   0	,!167		----------------- sub-total for PART1a			&
		d21_tbl_hold_cc		=   2	,!169 cc		+						&
		d21_tbl_hold		=  10	,!179 yymmdd hhmm	+-- these offset values must match below	&
		d21_tbl_resum_cc	=   2	,!181 cc		+						&
		d21_tbl_resume		=  10	,!191 yymmdd hhmm	+						&
		d21_tbl_clear_cc	=   2	,!193 cc		+						&
		d21_tbl_clear		=  10	,!203 yymmdd hhmm	+						&
		d21_tbl_fn_ttr		=   1	,!204			+						&
		d21_tbl_ttr		=   6	,!210 _99:59		+						&
		!													&
		d21_tbl_onsite_cc	=   2	,!212								line 5	&
		d21_tbl_onsite		=  10	,!222 yymmdd hhmm							&
		d21_tbl_close8		=   8	,!230 ccyymmdd								&
		d21_tbl_due_cc		=   2	,!232 cc								&
		d21_tbl_due		=  10	,!242 yymmdd hhmm							&
		d21_tbl_apmt_cc		=   2	,!244 cc								&
		d21_tbl_apmt		=  10	,!254 yymmdd hhmm							&
		d21_eta_cc		=   2	,!256									&
		d21_eta			=  10	,!266									&
		!													&
		d21_tbl_contract	=  15	,!281			15>					line 6	&
		d21_tbl_sla_obj		=   4	,!285			19>						&
		d21_tbl_pro_obj		=   4	,!289			23>						&
		d21_loading_obj		=   4	,!293			39>---- sub total for PART1b			&
		d21_tbl_weight		=   4	,!297									&
		d21_we			=   8	,!305									&
		d21_part1b_align	=   0	,!305									&
		!													&
		d21_network_number	=   8	,!313								line 7	&
		d21_activity_number	=   4	,!317									&
		d21_cost_center		=   6	,!323									&
		d21_wbs_number		=   7	,!330									&
		d21_general_ledger	=   7	,!337									&
		d21_ion			=   7	,!344									&
		!													&
		d21_acct		=   4	,!348								line 9	&
		d21_loc			=   6	,!354									&
		d21_org			=   8	,!362									&
		d21_est			=   7	,!369									&
		d21_toe			=   3	,!372									&
		d21_pin			=   9	,!381									&
		!													&
		d21_tbl_act_charges	=  11	,!392								line 8	&
		d21_tbl_mat_charges	=  11	,!403									&
		d21_tbl_total_charges	=  12	,!415					 				&
		!													&
		d21_tbl_src		=   3	,!418									&
		d21_tis_align		=   0	,!418 (sub-total 1 main)						&
		!													&
		!	device info section										&
		!													&
		!	Note: No longer the same as map "DevData11"							&
		!													&
		d21_station		=  15	,! 15									&
		d21_assoc_system	=  15	,! 30									&
		d21_spare06a		=   6	,! 36									&
		d21_tbl_depta		=   3	,! 39									&
		!													&
		d21_device_serial	=  15	,! 54									&
		d21_device_name		=  20	,! 74									&
		d21_clli		=  12	,! 86									&
		!													&
		d21_company		=  30	,!116									&
		d21_spare18		=  18	,!134									&
		d21_postal		=  10	,!144									&
		!													&
		d21_flr			=   5	,!149									&
		d21_address		=  37	,!186									&
		d21_city		=  20	,!206	 								&
		!													&
		d21_name		=  25	,!231									&
		d21_tel			=  10	,!241									&
		d21_ext			=   4	,!245									&
		d21_prov		=   4	,!249									&
		!													&
		d21_netterm		=  18	,!267	net								&
		d21_ots_Ckt		=  18	,!285	ckt								&
		d21_bc			=  12	,!297	project/bc							&
		d21_xtra		=  15	,!312	no form label							&
		!													&
		d21_notes		=  68	,!380									&
		d21_dis_align		=   0	,!380 (sub-total 2 device)						&
		!													&
		!	xtra info section										&
		!													&
		d21_name_alt		=  25	,! 25									&
		d21_tel_alt		=  10	,! 35									&
		d21_ext_alt		=   4	,! 39									&
		d21_remote_sys		=  10	,! 49									&
		d21_misc		=  68	,!117									&
		d21_xis_align		=   0	,!117 (sub-total 3 xtra)						&
		!													&
		!	future data section  (for emergency new field use)						&
		!													&
		d21_pein		= 10	,! 10									&
		d21_pein_alt		= 10	,! 20									&
		d21_tbl_deptxv1		=  3	,! 23	xtra-view-1							&
		d21_tbl_deptxv2		=  3	,! 26	xtra-view-2							&
		d21_tbl_deptxv3		=  3	,! 29	xtra-view-3							&
		d21_tbl_deptxv4		=  3	,! 32	xtra-view-4							&
		d21_fut_data		= 153	,!185									&
		d21_fds_align		= 0	,!185 (sub-total 4 future)						&
		!													&
		!	vendor specific section										&
		!													&
		d21_vendor_data1	= 25	,! 25 (cgi - productCategorizationTier1)				&
		d21_vendor_data2	= 25	,! 50 (cgi - productCategorizationTier2)				&
		d21_vendor_data3	= 25	,! 75 (cgi - productCategorizationTier3)				&
		d21_vendor_data4	= 25	,!100 (cgi - operationalCategorizationTier1)				&
		d21_vendor_data5	= 25	,!125 (cgi - operationalCategorizationTier2)				&
		d21_vendor_data6	= 25	,!150 (cgi - operationalCategorizationTier3)				&
		d21_vendor_data7	= 25	,!175 (cgi - from DICE)							&
		d21_vendor_data8	= 25	,!200 (cgi - REQ/uest number - might be blank)				&
		d21_vendor_data9	= 25	,!225 (cgi - INC/ident number)						&
		d21_vendor_data10	= 25	,!250 (cgi - TAS/k number)						&
		d21_vendor_data11	= 25	,!275 (cgi - cgi_status (so we don't close twice))			&
		d21_vendor_data12	= 25	,!300 (cgi - request number)						&
		d21_vsi_align		= 0	,!300 (sub-total 5 vendor)						&
		!													&
		d21_whole_align		= 0	 !  0
	!-marker-mysql-stop
	map (TblData21)													&
	    string				 !++++-- running total							&
		fill$			= 167	,!167									&
		d21_part1a_align	=   0	,!167		----------------- sub-total for PART1a			&
		d21_tbl_hold_cc		=   2	,!169 }	cc								&
		d21_tbl_hold		=  10	,!179 }	yymmddhhmm							&
		d21_tbl_resume_cc	=   2	,!181 }	cc								&
		d21_tbl_resume		=  10	,!191 }	yymmddhhmm -- we don't care if these '}' change			&
		d21_tbl_clear_cc	=   2	,!193 }	cc								&
		d21_tbl_clear		=  10	,!203 }	yymmddhhmm							&
		d21_tbl_fn_ttr		=   1	,!204 }									&
		d21_tbl_ttr		=   6	,!210 }	_99:59								&
		fill$			=  56	,!266									&
		d21_tbl_contract	=  15	,!281			15>				line 6		&
		d21_tbl_sla_obj		=   4	,!285			19>						&
		d21_tbl_pro_obj		=   4	,!289			23>						&
		d21_loading_obj		=   4	,!293			39>---- sub total for PART1b			&
		d21_tbl_weight		=   4	,!297									&
		d21_tbl_we		=   8	,!305									&
		d21_part1b_align	=   0	,!305									&
		fill$			= 113	,!418	this fill tops off to	sub-total 1 (main)			&
		d21_tis_align		=   0	,!418									&
		fill$			= 380	,!				sub-total 2 (device)			&
		d21_dis_align		=   0	,!									&
		fill$			= 117	,!				sub-total 3 (xtra)			&
		d21_xis_align		=   0	,!									&
		fill$			= 185	,!				sub-total 4 (future)			&
		d21_fds_align		=   0	,!									&
		fill$			= 300	,!				sub-total 5 (vendor)			&
		d21_vis_align		=   0	,!									&
		!													&
		d21_whole_align		=   0	 !  0
	map (TblData21)													&
	    string				 !++++-- running total							&
		fill$			= 155	,!155									&
		d21_tbl_create_12	=  12	,!167	ccyymmddhhmm		end of PART1a				&
		d21_part1a_align	=   0	,!167		----------------- sub-total for PART1a			&
		d21_tbl_hold_12		=  12	,!179	ccyymmddhhmm							&
		d21_tbl_resume_12	=  12	,!191	ccyymmddhhmm							&
		d21_tbl_clear_12	=  12	,!203	ccyymmddhhmm							&
		d21_tbl_fn_ttr		=   1	,!204									&
		d21_tbl_ttr		=   6	,!210						for alignment		&
						 !									&
		d21_tbl_onsite_12	=  12	,!222	ccyymmddhhmm							&
		d21_tbl_close8		=   8	,!230	ccyymmdd				for alignment		&
		d21_tbl_due_12		=  12	,!242	ccyymmdd hhmm							&
		d21_tbl_apmt_12		=  12	,!254	ccyymmdd hhmm							&
		d21_eta_12		=  12	,!266									&
						 !									&
		d21_tbl_contract	=  15	,!281						for alignment		&
		d21_tbl_sla_obj		=   4	,!285						for alignment		&
		d21_tbl_pro_obj		=   4	,!289						for alignment		&
		d21_loading_obj		=   4	,!293									&
		d21_tbl_weight		=   4	,!297						for alignment		&
		d21_we			=   8	 !305						for alignment
	map (TblData21)													&
	    string				 !++++-- running total							&
		d21_whole_record	=1400	,!1400									&
		d21_whole_align		=   0	 !
	map (TblData21)													&
	    string													&
		!													&
		!	trouble info section										&
		!													&
		fill$			= 418	,! 418									&
		d21_tis_align		=   0	,! 418 (sub-total 1 main)						&
		!													&
		!	device info section										&
		!													&
		d21_dev_data		= 380	,! 380				used in COS testing			&
		d21_dis_align		=   0	,! 380 (sub-total 2 device)						&
		!													&
		!	xtra info section										&
		!													&
		fill$			= 117	,! 117									&
		d21_xis_align		=   0	,! 117 (sub-total 3 xtra)						&
		!													&
		!	future data section (for emergency new field use)						&
		!													&
		d21_pein		= 10	,! 10									&
		d21_pein_alt		= 10	,! 20									&
		d21_tbl_deptxv1		=  3	,! 23	xtra-view-1							&
		d21_tbl_deptxv2		=  3	,! 26	xtra-view-2							&
		d21_tbl_deptxv3		=  3	,! 29	xtra-view-3							&
		d21_tbl_deptxv4		=  3	,! 32	xtra-view-4							&
		d21_fut_data		= 153	,!185									&
		d21_fds_align		=  0	,!185 (sub-total 4 future)						&
		!													&
		!	vendor specific section										&
		!													&
		d21_vendor_data		= 300	,!300									&
		d21_vsi_align		=   0	,!300 (sub-total 5 vendor)						&
		!													&
		d21_whole_align		=   0	 !
	declare long constant k_wdb_last_vend_specific   = 11	! 0-11=12 subscripts
	map (TblData21)													&
	    string													&
		!													&
		!	trouble info section										&
		!													&
		fill$			= 418	,! 418									&
		d21_tis_align		=   0	,! 418 (sub-total 1 main)						&
		!													&
		!	device info section										&
		!													&
		fill$			= 380	,! 380									&
		d21_dis_align		=   0	,! 380 (sub-total 2 device)						&
		!													&
		!	xtra info section										&
		!													&
		fill$			= 117	,! 117									&
		d21_xis_align		=   0	,! 117 (sub-total 3 xtra)						&
		!													&
		!	future data section (for emergency new field use)						&
		!													&
		fill$			= 185	,! 185									&
		d21_fds_align		=  0	,! 185 (sub-total 4 future)						&
		!													&
		!	vendor specific section										&
		!													&
		d21_vendor_data(k_wdb_last_vend_specific)	= 25	,!0-11=12*25=300				&
		d21_vsi_align					=  0	,!300 (sub-total 5 vendor)			&
		!													&
		d21_whole_align		=   0				 !
	!
	!	[...snip...]
	!
    %end %if