Checking a Progress / OpenEdge Database for MSSQL conversion

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.

Leave a Comment

Your email address will not be published. Required fields are marked *