Kbase P57768: How to determine the record size within Progress and compare it to the defined length of the column
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
4/10/2008 |
|
Status: Unverified
GOAL:
How to determine the record size within Progress and compare it to the defined length of the column in foreign Database?
GOAL:
How to determine the record size within Progress and compare it to the defined length of the column in Oracle database?
GOAL:
How to determine the record size within Progress and compare it to the defined length of the column in MS SQL Server Database?
FACT(s) (Environment):
MS SQL DataServer
Oracle DataServer
Oracle DataServer
FIX:
The following code will report the following items.
NAME - column name
DATATYPE -datatype
FORMAT -display format within Progress
MAX-SIZE -Maximum size of the record in Progress
TOT FIELD SZ -Format size times the number of extents defined for field
If (at anytime) the MAX-SIZE is larger than the FORMAT, then you will have problems loading data into ORACLE.
-------------------CODE---------------------------
DEF VAR X AS INT.
DEF VAR SIZE AS INT.
DEFINE TEMP-TABLE results
FIELD t_id AS INTEGER LABEL "ID"
FIELD t_NAME AS CHAR LABEL "NAME" FORMAT "X(32)"
FIELD t_extent AS INT LABEL "EXTENTS"
FIELD t_type AS CHAR LABEL "DATATYPE"
FIELD t_FORMAT AS CHAR LABEL "FORMAT" FORMAT "X(20)"
FIELD t_size AS INT LABEL "MAX-SIZE"
INDEX id IS UNIQUE PRIMARY t_id.
DEFINE VARIABLE i AS INTEGER.
DEFINE VARIABLE qh AS WIDGET-HANDLE.
DEFINE VARIABLE bh AS WIDGET-HANDLE.
DEFINE VARIABLE fh AS WIDGET-HANDLE.
DEFINE VARIABLE TOTAL_size AS INT LABEL "TOTAL SIZE " INITIAL 0.
DEFINE VARIABLE m_size AS INT LABEL "TOT FIELD SZ".
DEFINE VARIABLE l_size AS INT.
CREATE BUFFER bh FOR TABLE "customer".
CREATE QUERY qh.
qh:SET-BUFFERS(bh).
qh:QUERY-PREPARE("for each customer").
qh:QUERY-OPEN.
qh:GET-FIRST.
DISPLAY bh:NAME.
REPEAT X = 1 TO bh:num-fields.
/* IF bh:BUFFER-FIELD(x):DATA-TYPE <> "character" THEN
NEXT.*/
CREATE results.
ASSIGN t_id = X
t_name = bh:BUFFER-FIELD(x):NAME
t_format = bh:BUFFER-FIELD(x):FORMAT
t_type = bh:BUFFER-FIELD(x):DATA-TYPE
t_ext = bh:BUFFER-FIELD(x):EXTENT
t_size = 0.
END.
DO WHILE NOT qh:QUERY-OFF-END:
REPEAT i = 1 TO bh:NUM-FIELDS.
/*IF bh:BUFFER-FIELD(i):DATA-TYPE <> "character" THEN
NEXT.*/
fh = bh:BUFFER-FIELD(i).
FIND results WHERE t_id = i.
IF fh:EXTENT = 0 THEN DO:
l_size = length(fh:BUFFER-VALUE).
IF fh:DATA-TYPE = "decimal" THEN
l_size = l_size - 1. /* remove decimal separator */
IF results.t_size < l_size THEN
results.t_size = l_size.
END.
ELSE DO:
REPEAT X = 1 TO fh:EXTENT:
l_size = length(fh:BUFFER-VALUE[X]).
IF fh:DATA-TYPE = "decimal" THEN
l_size = l_size - 1. /* remove decimal separator */
IF results.t_size < l_size THEN
results.t_size = l_size.
END.
END.
END.
qh:GET-NEXT.
END.
DELETE WIDGET bh.
OUTPUT TO log4.txt.
FOR EACH results.
IF t_extent = 0 THEN
m_size = t_size.
ELSE
m_size = (t_size * t_extent).
TOTAL_size = TOTAL_size + m_size.
DISP t_name t_type t_format t_size m_size WITH WIDTH 150.
END.
DISPLAY TOTAL_size.
OUTPUT CLOSE.
----------------END OF CODE---------------------------