OpenEdge / Progress 4GL Notes
Setting up Webspeed with IIS 7.5 Windows 7
The following instructions can be used to create a basic setup for OpenEdge (10.2b) Webspeed development.
The information has been gleaned from the following source – Progress Knowledgebase, ProgressTalk.com, The OpenEdge Hive and also the manuals! Thanks to all who put info online, especially Scott Auge who has posted some very informative videos.
Continue Reading
View all registered OCX and DLLs
Now and again I have problems with registered .ocx and .dll files.
I found a program that will display and allow you to edit / register / unregister the files.
It’s called RegDllView and you can find it here. http://www.nirsoft.net/utils/registered_dll_view.html
Remember to run it as an Administrator if you’re using Windows 7.
Progress / OpenEdge 10.x License Update
When attempting to update the license details the installer checks for already running instances of Progress… but finds itself and stops!
To stop this change the startup target on the Update Icon from:
c:installationdirsetup.exe -u
to
c:installationdirsetup.exe u
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.
Updating a Progress and MSSQL database
When migrating a Progress database to a MSSQL database I use the following parameters…
I connect to the database using a differing logical name. Because our apps are compiled using the logical name of the database we need to supply this logical name to the Schema Holder.
Progress / OpenEdge Incremental df
When you want to create an incremental df between two databases you should;
- connect to the newer database (appdb)
- connect to the older database with diff logical name (oldappdb)
- ensure the current working database is the newer (appdb)
- create the incremental db from there
Job done! test
Flickr Photos
