How to set up Cold Fusion 4.0 with a Progress 9.1b Database
My Cold Fusion server connects to a Progress database about 1500 miles away in another country. It’s kinda slow, but it works. It took me about a week to research/trial/error and figure out all this.
1. Install Progress on the same server with Cold Fusion. Install the whole thing.
2. Install The Merant ODBC driver.
3. Set the environment variables
(%DLC% = the path to the folder where Progress is installed. Mine is at e:\Program Files\Progress so the first variable looks like this
DLC= e:\Program Files\Progress)
Set the Environment Variables as follows:
DLC=%DLC%
IDLC=%DLC%
PROMSGS=%DLC%\promsgs(or wherever this file is located)
IPROMSGS=%DLC%\promsgs(or wherever this file is located)
4. Add the services for the database and the OIB broker to the winnt\system32\drivers\etc\services file like this
xiob 20000/TCP *entry for the OIB broker port
xdb 22000/TCP *entry for the database port
Look in the services file on the database server for the correct entries for your server.
5. In the Windows Control Panel, open ODBC drivers.
Click the System DSN tab.
Click “Add”
Select the “MERANT 3.70 32-BIT PROGRESS” driver
Click “Finish”
The configuration screen will appear.
In the General tab, Data Source Name – Give it any name.
For Database Name – Type the name of the database you want to connect to
In the OID/OIB Options, choose TCP.
Service Name – I use the port number of the OIB broker; same as found in the services file on the database host machine.
Host Name – I use the IP address of the machine hosting the database.
In the Database Options tab, choose “Via Server”
Protocol –TCP
Service name – The port number of the database; same as found in the services file on the database host machine.
Host Name - – I use the IP address of the machine hosting the database.
In the Advanced tab, no checkboxes are checked.
The only thing I changed was to add the –cache line to Database Connection Options, Additional Options. You may not need this if your database machine is nearby. See Using –cache.
6. Click the “Apply” button.
7. Click the “Test Connect” button.
8. If you get the error 126, do the following.
Locate the esql32.dll in the progress\bin folder.
Copy it to the winnt\system32 folder and rename it to prosql32.dll.
Try the Test Connect again.
9. You should get a test connect successful message. If it took a long time you need to use the –cache option in the ODBC setup.
10. If you get a 1432 error, you need to set up the broker service on your Cold Fusion machine. If not, skip this step.
To add the broker service-
Add the following Environment Variables
prooibrk= path to the oibrkr32.exe file, like c:\Program Files\Progress\bin\oibrkr32.exe
prooidrv= path to the oidrv32.exe file, like c:\Program Files\Progress\bin\oidrv32.exe
Set up a service that starts the broker with the following command line:
c:\Program Files\Progress\bin\oibrkr32.exe –SV –S xoib –h xdb –N TCP
Substitute for xoib, the name you entered in the services file for the oib broker, and for xdb, the name of the database. Setup the service to login as a system account and to start on boot.
Start the service.
11. Try the Test Connect. It should work. If it is slow (minutes to respond) use the –cache option in the ODBC configuration.
12. Using the –cache option. If your connection is very slow, I mean minutes to respond, then you need to add the –cache option to the Database Configuration.
First, you need a copy of the cache file. To get this, from the Progress Procedure Editor and connected to the database, run the following command –
SAVE CACHE CURRENT xdb to c:\wherever\xdb.csh.
In Control Panel, Data Sources (ODBC), Select the previously configured data source and click “Configure”.
On the Advanced tab under Database Connection Options, in the Additional Options line add : -cache c:\wherever\xdb.csh.
Your database connection should be much faster now. My Test Connect went from 3.5 minutes to about 4 seconds after adding the –cache option.
13. Now try a Verify Database from your Cold Fusion admin page. You may need to add user name and password info here.
14. Notes on using Cold Fusion with a Progress database.
a) Be specific in queries. Don’t use SELECT *. Very slow.
b) You can’t use AS in the query for a column name.
c) Column names with a hyphen are a pain. Don’t use them if you have a choice.
d) If you are stuck with hyphenated column names, to display them in CFOUTPUT use the following format
#myQuery[“stupid-name”][1]#
The [1] is the record count from the query. Won’t work without it.
e) If you need a looped output, use the CurrentRow variable in place of the 1.Like this-
(Angle brackets left out for display)
CFLOOP QUERY="myQuery"
CFOUTPUT
#myQuery["stupid-name"][CurrentRow]#<br>
/CFOUTPUT
/CFLOOP
My Cold Fusion server connects to a Progress database about 1500 miles away in another country. It’s kinda slow, but it works. It took me about a week to research/trial/error and figure out all this.
1. Install Progress on the same server with Cold Fusion. Install the whole thing.
2. Install The Merant ODBC driver.
3. Set the environment variables
(%DLC% = the path to the folder where Progress is installed. Mine is at e:\Program Files\Progress so the first variable looks like this
DLC= e:\Program Files\Progress)
Set the Environment Variables as follows:
DLC=%DLC%
IDLC=%DLC%
PROMSGS=%DLC%\promsgs(or wherever this file is located)
IPROMSGS=%DLC%\promsgs(or wherever this file is located)
4. Add the services for the database and the OIB broker to the winnt\system32\drivers\etc\services file like this
xiob 20000/TCP *entry for the OIB broker port
xdb 22000/TCP *entry for the database port
Look in the services file on the database server for the correct entries for your server.
5. In the Windows Control Panel, open ODBC drivers.
Click the System DSN tab.
Click “Add”
Select the “MERANT 3.70 32-BIT PROGRESS” driver
Click “Finish”
The configuration screen will appear.
In the General tab, Data Source Name – Give it any name.
For Database Name – Type the name of the database you want to connect to
In the OID/OIB Options, choose TCP.
Service Name – I use the port number of the OIB broker; same as found in the services file on the database host machine.
Host Name – I use the IP address of the machine hosting the database.
In the Database Options tab, choose “Via Server”
Protocol –TCP
Service name – The port number of the database; same as found in the services file on the database host machine.
Host Name - – I use the IP address of the machine hosting the database.
In the Advanced tab, no checkboxes are checked.
The only thing I changed was to add the –cache line to Database Connection Options, Additional Options. You may not need this if your database machine is nearby. See Using –cache.
6. Click the “Apply” button.
7. Click the “Test Connect” button.
8. If you get the error 126, do the following.
Locate the esql32.dll in the progress\bin folder.
Copy it to the winnt\system32 folder and rename it to prosql32.dll.
Try the Test Connect again.
9. You should get a test connect successful message. If it took a long time you need to use the –cache option in the ODBC setup.
10. If you get a 1432 error, you need to set up the broker service on your Cold Fusion machine. If not, skip this step.
To add the broker service-
Add the following Environment Variables
prooibrk= path to the oibrkr32.exe file, like c:\Program Files\Progress\bin\oibrkr32.exe
prooidrv= path to the oidrv32.exe file, like c:\Program Files\Progress\bin\oidrv32.exe
Set up a service that starts the broker with the following command line:
c:\Program Files\Progress\bin\oibrkr32.exe –SV –S xoib –h xdb –N TCP
Substitute for xoib, the name you entered in the services file for the oib broker, and for xdb, the name of the database. Setup the service to login as a system account and to start on boot.
Start the service.
11. Try the Test Connect. It should work. If it is slow (minutes to respond) use the –cache option in the ODBC configuration.
12. Using the –cache option. If your connection is very slow, I mean minutes to respond, then you need to add the –cache option to the Database Configuration.
First, you need a copy of the cache file. To get this, from the Progress Procedure Editor and connected to the database, run the following command –
SAVE CACHE CURRENT xdb to c:\wherever\xdb.csh.
In Control Panel, Data Sources (ODBC), Select the previously configured data source and click “Configure”.
On the Advanced tab under Database Connection Options, in the Additional Options line add : -cache c:\wherever\xdb.csh.
Your database connection should be much faster now. My Test Connect went from 3.5 minutes to about 4 seconds after adding the –cache option.
13. Now try a Verify Database from your Cold Fusion admin page. You may need to add user name and password info here.
14. Notes on using Cold Fusion with a Progress database.
a) Be specific in queries. Don’t use SELECT *. Very slow.
b) You can’t use AS in the query for a column name.
c) Column names with a hyphen are a pain. Don’t use them if you have a choice.
d) If you are stuck with hyphenated column names, to display them in CFOUTPUT use the following format
#myQuery[“stupid-name”][1]#
The [1] is the record count from the query. Won’t work without it.
e) If you need a looped output, use the CurrentRow variable in place of the 1.Like this-
(Angle brackets left out for display)
CFLOOP QUERY="myQuery"
CFOUTPUT
#myQuery["stupid-name"][CurrentRow]#<br>
/CFOUTPUT
/CFLOOP
