#############################################################
# Convert coltype/length into an SQL descriptor string
#############################################################
FUNCTION col_cnvrt(coltype, collength)
DEFINE coltype, collength, NONULL SMALLINT,
SQL_strg CHAR(40),
tmp_strg CHAR(4)
LET coltype = coltype + 1 # datatype[] is offset by one
LET NONULL = coltype/256 # if > 256 then is NO NULLS
LET coltype = coltype MOD 256 # lose the NO NULLS determinator
LET SQL_strg = datatype[coltype] # basic datatype
CASE coltype
WHEN 1# char
LET tmp_strg = collength using "<<<<"
LET SQL_strg = SQL_strg clipped, " (", tmp_strg clipped, ")"
# SQL syntax supports float(n) - Informix ignores this
# WHEN 4 # float
# LET SQL_strg = SQL_strg clipped, " (", ")"
WHEN 6 # decimal
LET SQL_strg = SQL_strg clipped, " (", fix_nm(collength,0) clipped, ")"
# Syntax supports serial(starting_no) - starting_no is unavaliable
# WHEN 7 # serial
# LET SQL_strg = SQL_strg clipped, " (", ")"
WHEN 9 # money
LET SQL_strg = SQL_strg clipped, " (", fix_nm(collength,0) clipped, ")"
WHEN 11 # datetime
LET SQL_strg = SQL_strg clipped, " ", fix_dt(collength) clipped
WHEN 14 # varchar
LET SQL_strg = SQL_strg clipped, " (", fix_nm(collength,1) clipped, ")"
WHEN 15 # interval
LET SQL_strg = SQL_strg clipped, " ", fix_dt(collength) clipped
END CASE
IF NONULL THEN
LET SQL_strg = SQL_strg clipped, " NOT NULL"
END IF
RETURN SQL_strg
END FUNCTION
##############################################################
# Turn collength into two numbers - return as string
##############################################################
FUNCTION fix_nm(num,tp)
DEFINE num integer,
tp smallint,
strg CHAR(8),
i, j SMALLINT,
strg1, strg2 char(3)
LET i = num / 256
LET j = num MOD 256
LET strg1 = i using "<<&"
LET strg2 = j using "<<&"
IF tp = 0 THEN
IF j > i THEN
LET strg = strg1 clipped
ELSE
LET strg = strg1 clipped, ", ", strg2 clipped
END IF
ELSE# varchar is just the opposite
IF i = 0 THEN
LET strg = strg2 clipped
ELSE
LET strg = strg2 clipped, ", ", strg1 clipped
END IF
END IF
RETURN strg
END FUNCTION
##############################################################
# Turn collength into meaningful date info - return as string
##############################################################
FUNCTION fix_dt(num)
DEFINE num integer,
i, j, k, len SMALLINT,
strg CHAR(30)
LET i = (num mod 16) + 1# offset again
LET j = ((num mod 256) / 16) + 1# offset again
LET k = num / 256# length of value
# If this is an interval then life gets interesting, 'k' is
# the length of the entire string. So a YEAR TO DAY is
# YYYYMMDD or 8. A DAY(3) TO MINUTE is DDDHHMM or 7.
# We don't know how long the first one is, but
# we can work it out by computing the 'should
# be length' of the string and then adding/subtracting
# the result from the 'should be length' of
# the major element.
#
# Keep in mind ---> YYYYMMDDHHMMSSFFFFF
# vs. j = 1 2 3 4 5 678901
#
# I was just working an algorithm to do this, 4
# notepads, 90 minutes, and 50 lines into it I realized
# that I was creating something impossible to test or
# maintain. Therefore I am opting for something a lot simpler.
#
# In the globals I have created an ARRAY of RECORD with
# start and end points for the major and minor pieces.
# By subtracting the START point of the
# major element from the END point of the minor element
# I get the 'should be length'
#
LET len = intvray[i].end_point - intvray[j].start_point
# len should match k.e.g.:
#DAY(5) TO MINUTE ==> k = 9, len = 6
#YEAR(6) TO HOUR ==> k = 12, len = 14
LET len = k - len # add len to the major
IF len = 0 OR j > 11 THEN # is the default
# 12 on have the precision already coded
LET strg = datetype[j] clipped, " TO ", datetype[i] clipped
ELSE# isn't the default
# uh-oh, how long IS the default major?
LET k = intvray[j].end_point - intvray[j].start_point
# add in the extra
LET k = k + len
LET strg = datetype[j] clipped, "(", k using "<<", ")", " TO ",
datetype[i] clipped
END IF
RETURN strg
END FUNCTION
#############################################
Required global definitions:
DEFINE datatype ARRAY[40] OF CHAR(20), # for coltype conversions
datetype ARRAY[16] OF CHAR(11),
intvray ARRAY[16] OF RECORD
start_point SMALLINT,
end_point SMALLINT
END RECORD
LET datatype[1] = "CHAR"
LET datatype[2] = "SMALLINT"
LET datatype[3] = "INTEGER"
LET datatype[4] = "FLOAT"
LET datatype[5] = "SMALLFLOAT"
LET datatype[6] = "DECIMAL"
LET datatype[7] = "SERIAL"
LET datatype[8] = "DATE"
LET datatype[9] = "MONEY"
LET datatype[10] = "UNKNOWN"
LET datatype[11] = "DATETIME"
LET datatype[12] = "BYTE"
LET datatype[13] = "TEXT"
LET datatype[14] = "VARCHAR"
LET datatype[15] = "INTERVAL"
LET datatype[16] = "NCHAR"
LET datatype[17] = "NVARCHAR"
LET datatype[18] = "UNKNOWN"# little room for growth
LET datatype[19] = "UNKNOWN"
LET datatype[20] = "UNKNOWN"
LET datetype[1] = "YEAR"
LET intvray[1].start_point = 1
LET intvray[1].end_point = 5# offset by one for easy math
LET datetype[3] = "MONTH"
LET intvray[3].start_point = 5
LET intvray[3].end_point = 7
LET datetype[5] = "DAY"
LET intvray[5].start_point = 7
LET intvray[5].end_point = 9
LET datetype[7] = "HOUR"
LET intvray[7].start_point = 9
LET intvray[7].end_point = 11
LET datetype[9] = "MINUTE"
LET intvray[9].start_point = 11
LET intvray[9].end_point = 13
LET datetype[11] = "SECOND"
LET intvray[11].start_point = 13
LET intvray[11].end_point = 15
LET datetype[12] = "FRACTION(1)"
LET intvray[12].start_point = 15
LET intvray[12].end_point = 16
LET datetype[13] = "FRACTION(2)"
LET intvray[13].start_point = 16
LET intvray[13].end_point = 17
LET datetype[14] = "FRACTION(3)"
LET intvray[14].start_point = 17
LET intvray[14].end_point = 18
LET datetype[15] = "FRACTION(4)"
LET intvray[15].start_point = 18
LET intvray[15].end_point = 19
LET datetype[16] = "FRACTION(5)"
LET intvray[16].start_point = 19
LET intvray[16].end_point = 20
|
更多内容请看系统优化大全、系统安全设置、系统安装手册专题，或进入讨论组讨论。
