Kbase P49: How to connect to a SQL-92 server using the Progress Explore
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
4/15/2003 |
|
Solution ID: P49
GOAL:
How to connect to a SQL92 server using the Progress Explorer and ODBC.
GOAL:
How do I set up my SQL92 server using the Progress Explorer and ODBC.
FACT(s) (Environment):
Windows NT 32 Intel/Windows 2000
FACT(s) (Environment):
Progress 9.x
SYMPTOM(s):
MERANT ODBC drivers for Progress
FIX:
This solution provides an example of how to connect to an SQL-92 server using the Progress Explorer and ODBC.
The information is intended to help you connect in order to test issues and become more familiar with this type of connection and not to cover every scenario.
This solution assumes that you have MSAccess or Microsoft's ODBC Test program (downloadable from ftp.merant) or WinSQL (downloadable from imran. http://www.imranweb.com) available to use as a front end for your ODBC access.
The following six main steps are discussed:
- Starting the SQL-92 server,
- Connecting to the server via the SQL Explorer.
- Setting up the ODBC data sources.
- Connecting to SQL-92 server via ODBC using WinSQL.
- Connecting to SQL-92 server via ODBC using MSAccess.
- Connecting to SQL-92 server via ODBC using Microsoft
ODBCtest utility.
Starting the SQL-92 server:
Follow these steps to start the server:
1) Set your PATH and DLC environment variables in your Start > Settings > Control Panel> System > environment area. For a default installation, the DLC is set to <drive>:\Program Files\Progress, and the path includes the DLC\bin directory.
If you do not do this, you might have trouble finding _sqlsrv (part of the process that is spawned when a connection is made).
2) Create a Progress sports2000 database and connect to it with the Progress client in single user mode.
3) For this test, create a user 'sysprogress' with password 'x' via the Data Admin > Security > Edit User list menu option.
4) Exit out of the client.
5) Shell out to the MsDOS window and CD to your working directory (for example, the default working dir for Version 9.x is drive:\progress\wrk).
6) Start a server for sports2000 as follows:
proserve sports2000 -N tcp -H localhost -S demosv (or whatever service name or port number you choose)
Connect to the SQL-92 server with SQL Explorer:
Follow these test the connection to SQL Explorer before you use the ODBC:
1) Go to Start > Progress V9 > SQL Explorer Tool.
2) Drop down the File menu and select connect.
3) Fill in the dialog box as follows:
host: localhost
service/port: demosv (or the port #)
database: sports2000 (don't include the path)
user: sysprogress
pw: x
4) Press the "connect" button.
5) Type into the box:
select * from pub.customer
6) Press the "Execute" button.
You should see all the customers listed.
Setting up the ODBC data sources:
The ODBC from MS Access connection must be tested. Follow these steps to set up the ODBC data source in the ODBC Manager:
1) Go to Start > Control Panel > ODBC.
2) Press the "Add" button under the User DSN tab.
3) Select Intersolv 3.11 32-bit Progress Driver (MERANT).
4) Enter information in the dialog box as follows:
data source name: testsql92 (or you choose)
description: optional
host: localhost
port: demosv (or port number)
Database: sports2000 (do NOT include the path)
User ID: sysprogress
5) You can press the "Test Connect" button here to try it out.
You are prompted for the password.
Password: x
6) If the password works, press OK.
Connecting to the SQL-92 server with WinSQL:
Follow these steps:
1) Run the WinSQL application.
2) Fill in the ODBC Data Source dialog box as follows:
Data source name: testsql92
User ID: sysprogress
PW: x
3) Type in the query:
select * from pub.customer
4) Press the green triangular button to see the results of the query.
Connecting to the SQL-92 ser.ver with Microsoft Access:
Try the ODBC connection with the following steps:
1) Start the front-end application you want to use with the ODBC connection (use MsAccess in this example).
2) Create a new database using the blank database at the Microsoft Access dialog box.
3) Press OK.
4) Press create.
5) Press New.
6) Click on Link table.
7) Drop down 'Files of type' combo-box in the link dialog box and select "ODBC databases".
8) Select the testsql92 datasource and press OK.
9) You connect via ODBC to the Progress Sports2000 database.
10) There is a list of tables (such as pub.benefits). Select the tables that you want to have access to and press OK.
11) Here is a list of the tables you want to access. You can click on them to see the data in the tables. Shut down MsAccess.
Connecting to the SQL-92 server with Microsoft's ODBCTEST utility:
Follow these steps:
1) Run ODBCte32.
2) Pull down the Conn menu (connect).
3) Select your DSN (testsql92).
4) Enter the UserID and password (sysprogress and x).
5) Press OK.
6) In the lower window, you should see the status of your connect. In the upper box, you can type in SQL code such as:
select * from pub.customer
7) Select SQLExecDirect from the Stmt menu.
8) In the lower window you should see the status of the execution of that SQL code. To see the RESULTS of the query, pull down the Results and select "Get Data All" to see the customer records that were fetched.
9) Close down the ODBCtest32 application..