Skip to main content

SQL setup for use with EA

  • warning: Illegal string offset 'files' in /home/cintegri/public_html/modules/upload/upload.module on line 281.
  • warning: Illegal string offset 'files' in /home/cintegri/public_html/modules/upload/upload.module on line 281.
Printer-friendly version

Different people have different ideas of how they want to set up databases for SQL access, but in case this is an area unfamiliar to someone trying to setup an OpenEdge database for use with Enterprise Architect, the following describes one approach.

Note that I always set up my databases on a Unix box where I have my own database management scripts, even though the tools I will be using it with are on a Windows platform, so adaptation of the following to Windows is left as an exercise for the reader.

When one first creates the database, it will have no entries in the _User table. Some people like it that way because they manage security in other ways, but to me it seems like it presents a lot of problems, particularly with SQL access, because there are many cases where a tool *really* wants a user id and password. So, my normal practice is to use the Database Administration tools to define three users, one for the database creator, typically root for me, one for sysprogress, which is the default SQL administrator, and one for a generic SQL user, typically named for the tool which will be using this connection, e.g., something like EA in this case. All of these get login names, appropriate descriptions, and passwords.

To use the database with SQL, you will need to have a server running with a -S parameter with a service name and you will have to define the service name in the /etc/services or /Windows/system32/drivers/etc/services files as needed.

Then, with a server running using
$DLC/bin/sqlexp $DBNAME -S $SERVICE -user sysprogress -password xxxxxxxx
I will get a SQL session and run two commands:
grant DBA to EA;
grant RESOURCE to EA;
Follow this with:
to commit the work. To confirm that this has all worked properly, use
select * from sysprogress.sysdbauth;
to list the authorizations and you should see all three users with both priviledges.

With that much setup, you should then be able to define a connection to the database from OpenEdge Architect using the appropriate host name, service name, and the generic userid and password created above.

To use it with Enterprise Architect, you also need to define it as an ODBC datasource. That takes the same information, but you have to use the service number, not the name for reasons known only to Microsoft. This process is described in detail in another subpage.