-- DROP PROCEDURE get_precision;
CREATE PROCEDURE get_precision( iColLength INT ) RETURNING CHAR( 300 );
	DEFINE iPrecision INT;
	IF iColLength >= 256 THEN
		LET iPrecision = iColLength / 256;
		IF iColLength - iPrecision * 256 = 255 THEN
			RETURN iPrecision;
		ELSE
			RETURN iPrecision || ', ' || iColLength - iPrecision * 256;
		END IF;
	ELSE
		RETURN iColLength;
	END IF;
END PROCEDURE;

-- DROP PROCEDURE get_qualifier;
CREATE PROCEDURE get_qualifier( iCode INT ) RETURNING CHAR( 300 );
	IF iCode = 0 THEN RETURN 'YEAR';
	ELIF iCode = 2 THEN RETURN 'MONTH';
	ELIF iCode = 4 THEN RETURN 'DAY';
	ELIF iCode = 8 THEN RETURN 'MINUTE';
	ELIF iCode = 10 THEN RETURN 'SECOND';
	ELIF iCode = 11 THEN RETURN 'FRACTION( 1 )';
	ELIF iCode = 12 THEN RETURN 'FRACTION( 2 )';
	ELIF iCode = 13 THEN RETURN 'FRACTION( 3 )';
	ELIF iCode = 14 THEN RETURN 'FRACTION( 4 )';
	ELIF iCode = 15 THEN RETURN 'FRACTION( 5 )';
	ELSE RETURN '???';
	END IF;
END PROCEDURE;

-- DROP PROCEDURE get_from_to;
CREATE PROCEDURE get_from_to( iColLength INT ) RETURNING CHAR( 300 );
	DEFINE iLen, iFrom, iTo INT;
	DEFINE cFromTo CHAR( 300 );
	LET iLen = iColLength / 256;
	LET iFrom = ( iColLength - iLen * 256 ) / 16;
	LET iTo = iColLength - iLen * 256 - iFrom * 16;
	LET cFromTo = get_qualifier( iFrom );
	RETURN SUBSTR( cFromTo, 1, LENGTH( cFromTo ) ) || ' TO ' || get_qualifier( iTo );
END PROCEDURE;

-- DROP PROCEDURE get_reserve;
CREATE  PROCEDURE get_reserve( iColLength INT ) RETURNING CHAR( 300 );
	DEFINE iPrecision INT;
	IF iColLength >= 256 THEN
		LET iPrecision = iColLength / 256;
		RETURN iColLength - iPrecision * 256 || ', ' || iPrecision;
	ELSE
		RETURN iColLength;
	END IF;
END PROCEDURE;

-- DROP PROCEDURE get_tab_def;
CREATE PROCEDURE get_tab_def( cTabName CHAR( 18 ) ) RETURNING CHAR( 32000 );
	DEFINE iColNo, iColType, iColLength INT;
	DEFINE cColName CHAR( 18 );
	DEFINE cCrLf CHAR( 2 );
	DEFINE cCreateTable CHAR( 32000 );
	DEFINE cPrecision, cFromTo, cReserve CHAR( 300 );
	SELECT data[ 69, 70 ] INTO cCrLf FROM sysprocbody WHERE datakey = 'T' AND procid = 1 AND seqno = 1;
	LET cCreateTable = 'CREATE TABLE ' || SUBSTR( cTabName, 1, LENGTH( cTabName ) ) || ' (' || cCrLf;
	FOREACH
		SELECT colno, colname, coltype, collength INTO iColNo, cColName, iColType, iColLength
		FROM systables t, syscolumns c WHERE t.tabid = c.tabid AND t.tabname = cTabName ORDER BY 1
		LET cCreateTable = SUBSTR( cCreateTable, 1, LENGTH( cCreateTable ) ) || '	' || cColName || '*';
		IF iColType IN ( 0, 256 ) THEN LET cCreateTable = SUBSTR( cCreateTable, 1, LENGTH( cCreateTable ) -1 ) || '	CHAR( ' || iColLength || ' ),' || cCrLf;
		ELIF iColType IN ( 1, 257 ) THEN LET cCreateTable = SUBSTR( cCreateTable, 1, LENGTH( cCreateTable ) -1 ) || '	SMALLINT,' || cCrLf;
		ELIF iColType IN ( 2, 258 ) THEN LET cCreateTable = SUBSTR( cCreateTable, 1, LENGTH( cCreateTable ) -1 ) || '	INTEGER,' || cCrLf;
		ELIF iColType IN ( 3, 259 ) THEN LET cCreateTable = SUBSTR( cCreateTable, 1, LENGTH( cCreateTable ) -1 ) || '	FLOAT,' || cCrLf;
		ELIF iColType IN ( 4, 260 ) THEN LET cCreateTable = SUBSTR( cCreateTable, 1, LENGTH( cCreateTable ) -1 ) || '	SMALLFLOAT,' || cCrLf;
		ELIF iColType IN ( 5, 261 ) THEN
			LET cPrecision = get_precision( iColLength );
			LET cCreateTable = SUBSTR( cCreateTable, 1, LENGTH( cCreateTable ) -1 ) || '	DECIMAL( ' || SUBSTR( cPrecision, 1, LENGTH( cPrecision ) ) || ' ),' || cCrLf;
		ELIF iColType IN ( 6, 262 ) THEN LET cCreateTable = SUBSTR( cCreateTable, 1, LENGTH( cCreateTable ) -1 ) || '	SERIAL,' || cCrLf;
		ELIF iColType IN ( 7, 263 ) THEN LET cCreateTable = SUBSTR( cCreateTable, 1, LENGTH( cCreateTable ) -1 ) || '	DATE,' || cCrLf;
		ELIF iColType IN ( 8, 264 ) THEN
			LET cPrecision = get_precision( iColLength );
			LET cCreateTable = SUBSTR( cCreateTable, 1, LENGTH( cCreateTable ) -1 ) || '	MONEY( ' || SUBSTR( cPrecision, 1, LENGTH( cPrecision ) ) || ' ),' || cCrLf;
		ELIF iColType IN ( 10, 266 ) THEN
			LET cFromTo = get_from_to( iColLength );
			LET cCreateTable = SUBSTR( cCreateTable, 1, LENGTH( cCreateTable ) -1 ) || '	DATETIME ' || SUBSTR( cFromTo, 1, LENGTH( cFromTo ) ) || ',' || cCrLf;
		ELIF iColType IN ( 11, 267 ) THEN LET cCreateTable = SUBSTR( cCreateTable, 1, LENGTH( cCreateTable ) -1 ) || '	BYTE,' || cCrLf;
		ELIF iColType IN ( 12, 268 ) THEN LET cCreateTable = SUBSTR( cCreateTable, 1, LENGTH( cCreateTable ) -1 ) || '	TEXT,' || cCrLf;
		ELIF iColType IN ( 13, 269 ) THEN
			LET cReserve = get_reserve( iColLength );
			LET cCreateTable = SUBSTR( cCreateTable, 1, LENGTH( cCreateTable ) -1 ) || '	VARCHAR( ' || SUBSTR( cReserve, 1, LENGTH( cReserve ) ) || ' ),' || cCrLf;
		ELIF iColType IN ( 14, 270 ) THEN
			LET cFromTo = get_from_to( iColLength );
			LET cCreateTable = SUBSTR( cCreateTable, 1, LENGTH( cCreateTable ) -1 ) || '	INTERVAL ' || SUBSTR( cFromTo, 1, LENGTH( cFromTo ) ) || ',' || cCrLf;
		ELIF iColType IN ( 15, 271 ) THEN LET cCreateTable = SUBSTR( cCreateTable, 1, LENGTH( cCreateTable ) -1 ) || '	NCHAR( ' || iColLength || ' ),' || cCrLf;
		ELIF iColType IN ( 16, 272 ) THEN
			LET cReserve = get_reserve( iColLength );
			LET cCreateTable = SUBSTR( cCreateTable, 1, LENGTH( cCreateTable ) -1 ) || '	NVARCHAR( ' || SUBSTR( cReserve, 1, LENGTH( cReserve ) ) || ' ),' || cCrLf;
		ELSE LET cCreateTable = SUBSTR( cCreateTable, 1, LENGTH( cCreateTable ) -1 ) || '	???' || cCrLf;
		END IF;
	END FOREACH;
	RETURN SUBSTR( cCreateTable, 1, LENGTH( cCreateTable ) - 3 ) || cCrLf || ');';
END PROCEDURE;