When I’m converting an OpenEdge database to a MS SQL database there are a few issues to consider.
One important one is that natice OE databases allow users to “overfill” database fields e.g. if I have a field called employee.name defined as CHARACTER FORMAT “x(20)” but have a name which is 30 characters then that is no problem.
In MSSQL however, that will fail.
So when we convert our database Progress supply “dbtool” which trawls through the content of each field checking for overfilling. It will report the fields then ask to change the internal SQL Width value which I then use when migrating the database.
Unfortunately this tool doesn’t handle database array fields so I’ve amended that procedure and posted it below for reference.
/* Program to Verify / Change SQL Width Sizes within a Progress
database.
This program should be run BEFORE any Schema Migration Process
: Dave Walker - June 2010
:
: The Program will add 50% to the width if it's exceeded
*/
FORM
_file-name LABEL "Processing File"
_field-name
WITH FRAME xx SIDE-LABELS 1 COL WIDTH 78.
DEFINE TEMP-TABLE sqlw
FIELD fileName AS CHARACTER FORMAT "x(15)"
FIELD fieldName AS CHARACTER FORMAT "x(15)"
FIELD fieldsize as INTEGER FORMAT ">>>9" LABEL "Field Size"
FIELD sz AS INTEGER FORMAT ">>>9"
field changesize AS INTEGER FORMAT ">>>9" LABEL "Change to"
INDEX ix IS PRIMARY UNIQUE fileName fieldName.
RUN FIND_fields.
FOR EACH sqlw NO-LOCK :
DISPLAY sqlw WITH TITLE "Change SQL-WIDTH for this fields" .
END.
DEFINE VARIABLE vlOK AS LOGICAL.
IF CAN-FIND( FIRST sqlw NO-LOCK ) THEN
MESSAGE "Do you wish to update the database?" VIEW-AS ALERT-BOX BUTTONS YES-NO
UPDATE vlok.
ELSE
MESSAGE "Dataserver schema was validated for using WIDTH method." VIEW-AS ALERT-BOX.
IF vlok THEN
DO TRANSACTION:
FOR EACH _file NO-LOCK WHERE NOT _hidden,
EACH _field OF _file WHERE _data-type = "CHARACTER",
EACH sqlw NO-LOCK WHERE FILENAME = _file-name AND fieldName =
_field-name:
ASSIGN _width = changesize.
END.
END.
PROCEDURE FIND_fields:
DEFINE VARIABLE vFieldList AS CHARACTER NO-UNDO.
DEFINE VARIABLE vSizeList AS CHARACTER NO-UNDO.
DEFINE VARIABLE vFieldType AS CHARACTER NO-UNDO.
DEFINE VARIABLE vFieldName AS INTEGER NO-UNDO.
DEFINE VARIABLE hBuffer AS HANDLE NO-UNDO.
DEFINE VARIABLE hQuery AS HANDLE NO-UNDO.
DEFINE VARIABLE hField AS HANDLE NO-UNDO.
DEFINE VARIABLE c1 AS INTEGER NO-UNDO.
DEFINE VARIABLE VarSize AS INTEGER NO-UNDO.
DEFINE VARIABLE lv-extent-width AS INTEGER NO-UNDO.
DEFINE VARIABLE lv-tempdec AS DECIMAL FORMAT "->>>>>>>>9.99<<" NO-UNDO.
FOR EACH _file WHERE NOT _hidden
/*AND _file._file-name BEGINS "os_program" */
NO-LOCK:
ASSIGN vFieldList = ''
vSizeList = ''.
FOR EACH _field OF _file NO-LOCK WHERE _data-type = "CHARACTER":
ASSIGN vFieldList = vFieldList + ',' + _field-name
vSizeList = vSizeList + ',' + STRING(INTEGER(_width /*- 2*/))
vFieldType = vFieldType + "," + _field._data-type.
END.
IF vFieldList = "" THEN
NEXT.
/* Ignore the 1st comma in the lists */
ASSIGN vFieldList = SUBSTRING(vFieldList,2)
vSizeList = SUBSTRING(vSizeList,2)
vFieldType = SUBSTRING(vFieldType,2).
/* DISPLAY vFieldList FORMAT "x(70)" SKIP */
/* vSizeList FORMAT "x(70)" */
/* vFieldType FORMAT "x(70)". */
/* Création du buffer de parcours des enregistrements */
CREATE BUFFER hBuffer FOR TABLE _file-name.
CREATE QUERY hQuery.
hQuery:SET-BUFFERS(hBuffer).
hQuery:QUERY-PREPARE("FOR EACH " + _file-name + " NO-LOCK ").
hQuery:QUERY-OPEN().
DISPLAY _file-name WITH FRAME xx .
IF hQuery:GET-FIRST()THEN
REPEAT:
hBuffer = hQuery:GET-BUFFER-HANDLE(1).
DO vFieldName = 1 TO NUM-ENTRIES(vFieldList):
ASSIGN hField = hBuffer:BUFFER-FIELD(ENTRY(vFieldName,vFieldList))
VarSize = 0 .
/* CHecking valid data for numeric input */
/* IF LOOKUP(HField:DATA-TYPE, "integer,recid") <> 0 THEN */
/* */
/* DO: */
/* */
/* */
/* */
/* /* message showing field info */ */
/* */
/* ASSIGN lv-tempdec = DECIMAL(STRING(hField:BUFFER-VALUE)) NO-ERROR. */
/* */
/* DISPLAY ENTRY(vFieldName,vFieldList) HField:DATA-TYPE hField:BUFFER-VALUE lv-tempdec. PAUSE. */
/* */
/* IF ERROR-STATUS:ERROR THEN */
/* MESSAGE "vFieldName : " ENTRY(vFieldName,vFieldList) SKIP */
/* "Datatype : " HField:DATA-TYPE SKIP */
/* "Full Width : " INTEGER(ENTRY(vFieldName,vSizeList)) SKIP */
/* */
/* "HField:EXTENT : " HField:EXTENT SKIP */
/* "c1 : " c1 SKIP */
/* "Extent Width : " lv-extent-width SKIP */
/* "Variable Size : " VarSize SKIP(1) */
/* "Field Value : " hField:BUFFER-VALUE */
/* VIEW-AS ALERT-BOX INFO BUTTONS OK. */
/* */
/* */
/* */
/* END. */
/* If we aren't dealing with a character string then ignore the length checks */
IF ENTRY(vFieldName,vFieldType) <> "CHARACTER" THEN
NEXT.
/* If we are dealing with extents then we need some special code.. */
IF HField:EXTENT > 0 THEN
DO:
DO c1 = 1 TO HField:EXTENT :
/* because the Progress Meta Schema only has one field which
stores the lengths of fields there is only one width value
for extents. this means that extent widths are the TOTAL
width for all the extents - e.g. width 50 for a field with
extent 5 would mean width 10 for each field (less 2 it seems
for some reason)
So we apply this logic for the extent fields we encounter */
ASSIGN lv-extent-width = (INTEGER(ENTRY(vFieldName,vSizeList))
/ HField:EXTENT) - 2.
IF (LENGTH(hField:BUFFER-VALUE(c1)) >
lv-extent-width) AND
( LENGTH(hField:BUFFER-VALUE(c1)) >
VarSize )
THEN DO :
/* Here we calculate what the Variable size is..
For extents we should multiply it up by the number of extents */
VarSize = (LENGTH(hField:BUFFER-VALUE(c1)) + 2) * HField:EXTENT.
/* message showing extent errors */
/* MESSAGE "vFieldName : " ENTRY(vFieldName,vFieldList) SKIP */
/* "Full Width : " INTEGER(ENTRY(vFieldName,vSizeList)) SKIP */
/* "HField:EXTENT : " HField:EXTENT SKIP */
/* "c1 : " c1 SKIP */
/* "Extent Width : " lv-extent-width SKIP */
/* "Variable Size : " VarSize SKIP */
/* */
/* LENGTH(hField:BUFFER-VALUE(c1)) ">" lv-extent-width SKIP */
/* */
/* VIEW-AS ALERT-BOX INFO BUTTONS OK. */
END.
END.
END.
/* Normal field definition */
ELSE
DO:
IF LENGTH(hField:BUFFER-VALUE) > INTEGER(ENTRY(vFieldName,vSizeList)) THEN
DO:
VarSize = LENGTH(hField:BUFFER-VALUE).
END.
END.
/* If the variable size is greater than 0 then this means some fields
have content greater than their field definition */
IF Varsize > 0 THEN
DO:
FIND sqlw WHERE FILENAME = _file-name AND
fieldName = ENTRY(vFieldName,vFieldList)
NO-ERROR.
IF NOT AVAILABLE(sqlw) THEN
DO:
CREATE sqlw.
ASSIGN FILENAME = _file-name
fieldName = ENTRY(vFieldName,vFieldList)
fieldsize = INTEGER(ENTRY(vFieldName,vSizeList)).
END.
IF sz < varsize THEN
ASSIGN sz = varsize
changesize = varsize + (varsize / 2).
END.
END.
hQuery:GET-NEXT().
IF hQuery:QUERY-OFF-END
THEN LEAVE.
END.
hQuery:QUERY-CLOSE.
DELETE OBJECT hQuery.
DELETE OBJECT hBuffer.
END. /* FOR EACH _file... */
END PROCEDURE.
