Status: Verified
GOAL:
How to setup an ODBC connection in Progress 8.x?
GOAL:
How to setup an ODBC connection in Progress 9.x?
GOAL:
How to setup ODBC connection for Local OIB/OID?
GOAL:
How to configure ODBC using the OIB/OID?
GOAL:
Configure ODBC with SQL-89
GOAL:
Configure ODBC with oibroker and oidriver
GOAL:
How to configure ODBC for SQL-89 connections to a database?
GOAL:
How to make SQL-89 connections to a database using ODBC?
GOAL:
How to configure the OIB and OID to handle SQL-89 connections to a database?
FACT(s) (Environment):
Progress 8.x
Progress 9.x
All Supported Operating Systems
FIX:
This solution refers to connectivity of a SQL-89 driver to a Progress Database.
SQL-92 ODBC drivers use a native connection and do not need the OIBroker to handle the connection.
To setup a SQL-89 ODBC connection to the Progress database the PC client has to have client networking version 8.2x, 8.3x or 9.x installed locally (or pointing to a network installation). It will connect to a database on an WinNT, Win2000 or UNIX machine using one of the following MERANT ODBC drivers:
MERANT 3.50 32-BIT PROGRESS
MERANT 3.60 32-BIT PROGRESS
MERANT 3.70 32-BIT PROGRESS
MERANT 4.00 32-BIT PROGRESS
Step by step details:
Part 1. The Database - WinNT or Win2000
Part 1 refers to having a database running on a WinNT or Win2000 server, and includes steps that need to be performed on this WinNT or Win2000 server. If your database is on a UNIX machine, go to Part 2.
A. Your database is probably already up and running. Make a note of how it was started. It is probably something like:
_mprosrv.exe databasename -H hostname -S servicename1 -N TCP
Hostname is the name of the WinNT or Win2000 server machine.
Servicename1 is an entry in the services file that is associated with a port number.
B. Starting the OIBroker
The OIBroker is a listener that waits for a 3rd party ODBC client (i.e. your non-progress application) to call to it.
At that point, the OIBroker spawns an OIDriver to complete the connection to the database. You only need one OIBroker running on your system. It doesn't matter how many databases you will need to connect to or how many drivers will need
to be started.
(NOTE: Most configurations will start the OIBroker on the same machine as the database and will therefore make a direct connection (shared memory connection) to the database. There are a few situations that require the OIBroker to run on a different machine, or to make a remote connection to the database.
If your situation requires such a configuration, go to Part 5. Alternate configurations for more information before continuing)
You will need to set the following environment variables.
NOTE:
In the following instructions all references to dlc as a directory, refer to the Progress installation directory.
DLC=<path to dlc>
PROMSGS=<path to dlc>\promsgs
IDLC=<path to dlc>
IPROMSGS=<path to dlc>\promsgs
prooibrk=<path to dlc>\bin\oibrkr32.exe
prooidrv=<path to dlc>\bin\oidrvr32.exe
where <path to dlc> is replaced by the actual path. To set them, go to the System Icon in the Control Panel and click on the Environment tab. Set them as System Environment Variables -NOT the User Environment Variables.
Now, make another entry in the services file, associated with a different port number for the OIBroker, for example servicename2. (NOTE: This SAME EXACT entry will need to be made to the services file on the PC client machine also. See Part 3 below)
To actually start the OIBroker use the following command:
<path to dlc>\bin\oibrkr32.exe -SV -S servicename2 -H hostname -N TCP
where <path to dlc> is replaced by the actual path.
You can verify that it started successfully by starting the NT task manager and verifying that there is an oibrkr32 process in the process list.
C. Shutting down the OIBroker
**DO NOT shut the OIBroker down now**
The instructions are given here for completeness.
<path to dlc>\bin\_mprshut.exe -SV -H hostname -S servicename2 -N tcp
where the -H, -S values are the same ones used when the OIBroker was started.
Go to Part 3.
Part 2. T.he Database Machine - UNIX
Part 2 refers to having a database running on a UNIX machine, and includes steps that need to be performed on this UNIX machine. If your database is on an NT server, go to Part 1.
A. Your database is probably already up and running. Make a note of how it was started. It is probably something like:
_mprosrv databasename -H hostname -S servicename1 -N tcp
Hostname is the name of the UNIX machine. Servicename1 is an entry in the services file that is associated with a port number. (The services file is usually located in /etc.)
B. Starting the OIBroker
The OIBroker is a listener that waits for a 3rd party ODBC client to call to it. At that point, the OIBroker spawns an OIDriver to complete the connection to the database. You only need one OIBroker running on your system. It doesn't matter
how many databases you will need to connect to or how many drivers will need to be started.
(NOTE: Most configurations will start the OIBroker on the same machine as the database and will therefore make a direct connection (shared memory connection) to the database. There are a few situations that require the OIBroker to run on a different machine, or to make a remote connection to the database.
If your situation requires such a configuration, go to Part 5. Alternate configurations for more information before continuing)
You will need to set the following environment variables.
NOTE:
In the following instructions all references to DLC as a directory, refer to the Progress installation directory.
DLC=<path to dlc>;export DLC
PROMSGS=<path to dlc>/promsgs;export PROMSGS
IDLC=<Path to DLC>; export IDLC
IPROMSGS=<Path to DLC>/promsgs; export IPROMSGS
PROOIBRK=<path to dlc>/bin/_prooibk;export PROOIBRK
PROOIDRV=<path to dlc>/bin/_prooidv;export PROOIDRV
where <path to dlc> is replaced by the actual path.
Now, make another entry in the services file, associated with a different port number for the OIBroker, for example servicename2. (NOTE: This SAME EXACT entry will need to be made to the services file on the PC client machine also. See Part 3 below)
To actually start the OIBroker use the following command from the dlc/bin directory:
<path to dlc>/bin/_prooibk -SV -S servicename2 -H hostname -N tcp
where <path to dlc> is replaced by the actual path. You can verify that it started successfully by using the following command:
ps -ef |grep _prooibk
On AIX, the process doesn't show when you grep the process itself, try:
ps -ef |grep servicename2
C. Shutting down the OIBroker
**DO NOT shut the OIBroker down now**
The instructions are given here for completeness.
<path to dlc>/bin/_mprshut -SV -H hostname -S servicename2 -N tcp
where the -H, -S values are the same ones used when the OIBroker was started.
Part 3. The PC Client
A. You need to set the following environment variables on the PC client:
NOTE:
In the following instructions all references to dlc as a directory, refer to the Progress installation directory.
DLC=<path to dlc>
PROMSGS=<path to dlc>\promsgs
IDLC=<path to dlc>
IPROMSGS=<path to dlc>\promsgs
where <path to dlc> is replaced by the actual path.
If the PC client is Windows 95, 98 or Me the above variables should be set in the autoexec.bat file using the set command, and then then reboot the pc.
If the PC client is WinNT or Win2000, go to the System Icon in the contr.ol panel and click on the Environment tab. Set them as system environment variables - NOT the User Environment Variables.
NOTE: With the release of the "MERANT 4.00 32-BIT PROGRESS" ODBC driver the PATH environmnet variable MUST include:
<path to dlc>\bin
B. Make an entry in the services file on this PC client machine, for the OIBroker. (See Solution P13195, "Host, Protocol and Services files on Windows" for more information on the services file) It has to be exactly the same as the entry made on the database machine for the OIBroker, i.e. same name,and port number.
Part 4. Configuring the ODBC Datasource
You will now define your ODBC Datasource using the ODBC Administrator. The ODBC Administrator can be accessed from the control panel by using the ODBC-32 icon.
One datasource will be needed for each database you will connect to. Therefore if you intend to use your non-progress application to connect to three Progress databases, you will need three distinct datasources.
Within the administrator, click Add, and select the MERANT 3.70 (3.50 or 3.60) 32-BIT PROGRESS driver. Do NOT select the SQL-92 driver. Click Finish and begin configuring the new datasource.
A. General Tab
You will only need to fill in 2 fields here; the datasource name and the database name. Assign a name to the datasource - it can be anything you like. Then, type the name of the database in the database name field (for example, sports).
B. OID/OIB Options Tab
The three entries on this screen, are the same three network parameters you used when starting the OIBroker in Part 1 or 2 above.
Protocol will be TCP.
Service Name is the same -S value used when starting the OIBroker servicename2 from the example.
Host name is the name of the machine that the OIBroker is running on.
C. Database Options Tab
This screen refers to how the OIDriver will connect with the database. Because the OIBroker (and OIDrivers spawned) are on the same machine as the database, you will use DIRECT for Database Access.
Database Path refers to the path on the server machine where the database is located.
If the database resides on an WinNT or Win2000 machine in directory c:\progress\dbs then the database path here will be c:\progress\dbs\
*** BE SURE TO PUT THE trailing \ on the database path! ***
If the database resides on a UNIX machine in directory /usr/progress/dbs then the database path here will be /usr/progress/dbs/
*** BE SURE TO PUT THE trailing / on the database path! ***
Operating system can be set to ignore for all platforms.
D. Advanced Tab
There is nothing on this form that is required to be filled in.
Click OK. This completes the definition of the ODBC Datasource and you should now be able to use your non-progress application to connect to the Progress database via the ODBC.
Part 5. Alternate Configurations
If you have completed Parts 1-4 above, your configuration is complete, and you should not go forward with the directions that follow.
Most configurations will start the OIBroker on the same machine as the database and will therefore make a direct connection (shared memory connection) to the database. There are a few situations that require the OIBroker to run on a
different machine, or to make a remote connection to the database.
The specifics of these situations will not be discussed here.
After reviewing the specifics below, you should start back at Part 1, and proceed through Parts 1-4 making the noted changes below.
RONG>A. If you need to have your OIBroker running on a machine other than the database machine:
Part 1B (or 2B) will be performed on the "different machine".
For example, if the OIBroker has to run on the pc client the environment variables prooibrk and prooidrv need be set (appropriately for the version of windows), the service name for the OIBroker needs be made to the services file on the pc, and then the OIBroker can be started.
This scenario will then require that the OIBroker make a remote connection to the database, so follow the steps in B below.
B. If the OIBroker (& OIDriver) needs to make a remote connection to the database, Part 4C above would read as follows:
Database Options Tab
This screen refers to how the OIDriver will connect with the database. Because the OIBroker (and OIDrivers spawned) are not on the same machine as the database, you will use VIA SERVER for Database Access, and you will specify the network protocols used when starting the database:
Protocol will be TCP.
Service Name is the same -S value used when starting the database (servicename1 from the example).
Host Name will be the -H value used when starting the database.
.