Permalink
Switch branches/tags
Nothing to show
Find file Copy path
Fetching contributors…
Cannot retrieve contributors at this time
106 lines (83 sloc) 2.9 KB
-* prototype of logic to replicate "HOLD FORMAT HYPERSTAGE"
-* call this with INPUTFILE parameter
-DEFAULT &INPUTFILE = 'NAME_OF_INPUT_FILE';
-PROMPT &INPUTFILE ;
-* you might need to dynamically set the Hyperstage database owner
-SET &OWNER = 'dbowner.';
-SET &HYPERFILE = 'HYPERSTAGE_' || &INPUTFILE ;
-* defaults for the upcoming -READFILE
-DEFAULTH &COLTYPE = '';
-DEFAULTH &LENGTH = '';
-DEFAULTH &SCALE = '';
-DEFAULTH &NULLS = '';
-DEFAULTH &NAME = '';
TABLE FILE SYSCOLUM
WHERE TBNAME IS '&INPUTFILE';
PRINT NAME COLTYPE LENGTH SCALE NULLS
BY TBNAME
BY SEGNO
BY COLNO
ON TABLE HOLD AS TEMPINFO FORMAT ALPHA
END
-RUN
-SET &NBRCOLS = &LINES;
-* create the SQL output file
FILEDEF SQLOUTPUT DISK TEMPSQL.SQL
-RUN
-* create the temporary master for reading SQL output file
FILEDEF CREATEMFD DISK TEMPMFD.MAS
-RUN
-WRITE CREATEMFD FILE=TEMPMFD, SUFFIX=FIX, $
-WRITE CREATEMFD SEGNAME=SEG1, SEGTYPE=S0, $
-WRITE CREATEMFD FIELD=SQLLINE, ALIAS=SQLLINE, USAGE=A120, ACTUAL=A120, $
-CLOSE CREATEMFD
CHECK FILE TEMPMFD
-RUN
-* generate SQL CREATE statements
-WRITE SQLOUTPUT
-WRITE SQLOUTPUT -- load input file (&INPUTFILE) into Hyperstage version (&HYPERFILE)
-WRITE SQLOUTPUT
-WRITE SQLOUTPUT -- initialize structure for &HYPERFILE
-WRITE SQLOUTPUT CREATE OR REPLACE TABLE &OWNER&HYPERFILE (
-* loop through columns of input file, generating SQL CREATE column statement for each
-SET &COUNTER = 0;
-REPEAT READLOOP &NBRCOLS TIMES
-SET &COUNTER = &COUNTER + 1;
-READFILE TEMPINFO
-SET &SQLCOLTYPE = DECODE &COLTYPE ('DOUBLE' 'DECIMAL' 'CHAR' 'CHAR' 'VARCHAR' 'VARCHAR' 'PACKED' 'DECIMAL'
- 'INTEGER' 'INTEGER' 'DATE' 'DATE' ELSE 'UNKNOWN');
-SET &LJ_LENGTH = LJUST(&LENGTH.LENGTH, '&LENGTH.EVAL', 'A&LENGTH.LENGTH');
-SET &LJ_SCALE = LJUST(&SCALE.LENGTH, '&SCALE.EVAL', 'A&SCALE.LENGTH');
-SET &MISSING = IF &NULLS IS 'Y' THEN 'NULL' ELSE '';
-SET &FORMAT = IF &SQLCOLTYPE IS 'DECIMAL' THEN &SQLCOLTYPE || '(' || &LJ_LENGTH || ',' || &LJ_SCALE || ')' ELSE
- IF &SQLCOLTYPE IS 'VARCHAR' OR 'CHAR' THEN &SQLCOLTYPE || '(' || &LJ_LENGTH || ')' ELSE
&SQLCOLTYPE;
-SET &COMMA = IF &COUNTER EQ &NBRCOLS THEN '' ELSE ',';
-SET &SQLLINE = &NAME || (' ' | &FORMAT || (' ' | &MISSING) || &COMMA );
-WRITE SQLOUTPUT &SQLLINE
-READLOOP
-WRITE SQLOUTPUT )
-WRITE SQLOUTPUT ENGINE=BRIGHTHOUSE ;
-WRITE SQLOUTPUT
-WRITE SQLOUTPUT -- load data file into &HYPERFILE
-WRITE SQLOUTPUT LOAD DATA INFILE '&INPUTFILE...FTM'
-WRITE SQLOUTPUT INTO TABLE &OWNER&HYPERFILE ;
-CLOSE SQLOUTPUT
-* testing: show the generated SQL (in real world, would run it here)
FILEDEF TEMPMFD DISK TEMPSQL.SQL
TABLE FILE TEMPMFD
PRINT SQLLINE AS ''
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT PDF
ON TABLE SET WIDTH 200
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = IBFS:/EDA/EDASERVE/_EDAHOME/ETC/endeflt.sty,
ORIENTATION=LANDSCAPE,
$
ENDSTYLE
END
-RUN
-GOTO EXIT_PROC
-EXIT_PROC