Using An OpenEdge Database as a Repository for Enterprise Architect

The standard repository for Enterprise Architect uses the JET database engine. This makes it accessible with Access and SQL, but those working with ABL may prefer to use an OpenEdge database and ABL, being more familiar and more powerful. An OpenEdge database is required for some of the ABL to UML tools available here.

Prerequisites

The following discussion assumes the use of EA 7.0. Whether it works with anything earlier is not known to me, but the tools developed and published here will generally be based on the latest version of both OpenEdge and Enterprise Architect. Even if the target system is an earlier version, these tools will generally not be back-ported. Note that you must have the Corporate Edition of EA to use an alternate database for a repository.

Before EA can be configured to use OpenEdge as a Repository the following Software Components need to be installed and configured:
* MDAC 2.6 or 2.7
* Install Progress OpenEdge 10.0B03.
EA has also been tested by me personally against OpenEdge 10.1B02 and PSC indicates it works with 10.1B01, but not 10.1B unpatched. If you are using 10.1B, you must have installed Service Pack 1 at a minimum. See Solution P120293 in the KnowledgeBase. There has been some prior testing against version 10.0B01, but there are known to be problems with all 10.1A versions.

Once these preconditions have been met the following process should be used to create the EA OpenEdge repository:

Create an Empty Repository Database

Using appropriate OpenEdge tools, create an empty database.

Start a server for this database with an appropriate -S parameter so that you can connect to it with SQL and make an appropriate entry in /etc/services and/or C:\Windows\system32\drivers\etc\services.

Use the Data Administration Tool to create users for use by EA. I typically setup users for sysprogress (the SQL administrator, the user who created the database, and a generic user for use by the appropriate tool, e.g., EA in this case. All should get passwords as many ODBC functions seem to insist on them.

Use sqlexp to assign the needed permissions. See the discussion in the subpage below about setting up the database for use by SQL, unless you are already familiar with this process.

Do one of the following two steps to load the repository schema:

SQL Schema Load
From a proenv equivalent, run the OpenEdge Basemodel.sql (54 Kb) script file to create all of the required tables/indexes etc. Remove the "_.txt" extensions before use. To run the script, use a command like the following:

sqlexp -db EAEmpty -S EAEmpty -user xxx -password yyy -infile OpenEdge_BaseModel.sql

Note that you will see a lot of errors from the DROP TABLE commands at the beginning where no such table exists.

.df Schema Load
The equivalent of the SQL schema load is also provided as an attached .df file which can be loaded using standard database administration tools. Note that this approach is not supported by Sparx, but has worked for some people who have encountered difficulties with the SQL scripts. Sparx may create more current scripts. To use this script, you need to include the FullKeywordForget.txt file to the -k parameter for the session. The smaller KeywordForget.txt file is required for writing code against some parts of the schema.

One may want to back up this database before proceeding, but note that it is not yet provided with all of the data you will want in order for it to serve as the base for all future projects.

Prepare Database for Use

Start a server for the database, if you don't already have one running. In the server start up parameters for the repository, include a parameter for -SQLStmtCache. I am currently using 10000. That might be more than is needed, but 1000 was definitely not enough.

Create a ODBC entry for the database using the instructions in the subpage below.

Create an Empty EA Project

You will now have an empty database, you can use the data transfer tool to transfer an existing model to the server, to achieve this use the following steps:

Please note: it is recommended that you run the Data Integrity tool on your basic project you wish to transfer to OpenEdge. This will ensure data is 'clean' before uploading. To ensure the integrity of a .EAP file open the .EAP file in EA and select Tools | Data Management | Data Integrity.

Then

  • Open EA and then select Tools | Data Management | Project Transfer
  • In the Dialog provide select .EAP to DBMS.
  • For the fields:
    • Source Model: Select the .EAP file that you want to transfer from;
    • Target Model: Select the OpenEdge ODBC Data Source in the DBMS connection tool. Select the Microsoft OLE Provider for ODBC Drivers in the Data Link properties dialog, then press the Next button.
    • On the Connections tab, use data Source name dropdown menu to select the ODBC data source created earlier, supply the user name and password, check allow saving password, and use the dropdown to select the default Catalog name.
    • At this point, you may use Test Connection to check your parameters.
    • On the All tab of the Data Link Properties dialog, enter “DefaultSchema=PUB” for Extended Properties.
    • Press the OK button.
    • You will receive another logon to the driver. Provide a password.
  • Press the Transfer Data button, this will begin the process. Click OK to the warning.
  • Once this process is completed the .EAP model will be transfered to a OpenEdge repository.

Note: when entering the Extended Properties DefaultSchema=PUB, don't go back and test the connection - you will loose this setting (a Microsoft quirk!).

Opening the New Project in EA

This process is covered in the EA help file under the topic "Connect to a Progress OpenEdge Repository" and is detailed in the subpage below.

Saving a Base Project

Rather than go through this whole process repeatedly, it is useful to back up a copy at one or more suitable points. Depending on the work you are doing, you will probably want some basic things in every project such as the OE datatypes, possibly the stereotypes for the Profile from this site, etc. There is no particular magic about this data ... it is simply data in tables so once you figure out what you need, get it added to an empty project and then back up that database. The transfer process described here is only required for converting from one database form to another. To create new projects from a base, you simply need to make a copy of the database and go through the connection dialog in the subpage to connect to it for the first time.

Transferring back to an EAP file

In exchanging models with others, it may be desirable at some point to transfer your OpenEdge repository back to an EAP file, e.g., for use by someone off site or for viewing with the free EA viewer. This transfer is easily accomplished by following the same instructions for the transfer, except reversing the source and target databases (probably selecting a new target name, since you don't want to overwrite your EAP base.

Notes

Sparx says "If you wish to enable user security, when prompted enter {F08113BA-8B4F-41df-8F01-46DF2C35D249} as the key to enable security." I haven't found that prompt yet.

EA help files refer to this process as "upsizing" a model. It always consists of *transferring* an existing model from the built-in MS JET database to the OpenEdge database. So, even if you are starting a new project, you need to create an empty project with the desired components and transfer that to the OpenEdge repository. If one was going to do this repeatedly, it would seem sensible to create the empty JET project once, transfer it to OpenEdge once, and then same a copy of that database for starting future new projects. Similarly, if one had some standard model elements which were generally required, e.g., those for the ABL to UML tools found on this site, then it is desirable to either have those in the project one transfers from or to add them to the model before saving the repository to use as a base for future projects.

Material adapted from OpenEdge Scripts on the Registered Users portion of the Sparx website. To get to that page, use the Registered Users link at the top of any Sparx website page, log in, and select OpenEdge Scripts under the Corporate Resources on the box in the left column.

Also note that PSC has published a very detailed howto guide covering this material: OpenEdge RDBMS as Model Repository. The material in this document does not differ materially from what is presented here, but is very detailed step by step if you are not familiar with these operations.

The "_modified" script below is a new one provided by Sparx Systems. It changes the date fields to datetime for better accuracy and changes the CHAR(1) to CHARACTER so that they don't map to the FIXCHAR datatype in OE, which can't be accessed from ABL. No BLOBs though. Apparently, that doesn't work. The TMH script is one with some further enhancements of my own. This will be developed further and sent back to Sparx.

Work is underway to explore changing the default field format for strings to something longer than "X(8)" and the possibility of moving away from all caps table and field names.

For services related to this material, see my website.


AttachmentSize
OpenEdge_BaseModel.sql_.txt52.92 KB
OpenEdge_BaseModel_modified.sql_.txt53.59 KB
OpenEdge_BaseModel_TMH.sql_.txt53.69 KB
EA_20080226.df_.txt142.87 KB
FullKeywordForget.txt63 bytes
KeywordForget.txt15 bytes