Kbase 18757: How to Connect to SQL-92 Server Using ODBC and SQL Explorer
| Autor |
Progress Software Corporation - Progress |
| Acesso |
Público |
| Publicação |
21/01/2009 |
|
Status: Verified
GOAL:
How to connect to a SQL-92 server from SQL Explorer and ODBC?
GOAL:
How do I set up SQL-92 server?
GOAL:
How do I test SQL-92 client connections?
GOAL:
How do I use SQL explorer to test SQL-92 connections?
GOAL:
How do I use ODBC test to test SQL-92 configurations?
GOAL:
How do I use WinSQL to test SQL-92 configurations?
FACT(s) (Environment):
Windows NT 32 Intel/Windows 2000
Progress 9.x
OpenEdge 10.x
FIX:
This solution provides an example of how to connect to an SQL-92 server using the SQL 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 Microsoft's Access or Microsoft's ODBC Test program (The ODBC Test utility can be obtained from the Microsoft Data Access SDK, which comes with Visual Studio or can be downloaded from the following Web site: http://www.msdn.microsoft.com) or WinSQL (downloadable from 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 _sqlsrv2 (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 data source:
1) Go to Start > Control Panel > ODBC.
2) Press the "Add" button under the User DSN tab.
3) Select Merant 3.60 32-bit Progress SQL-92 v9.1D 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 server 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.
12) 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. .