Data Access for ISAM User Manual - Demonstration

Worked example of connecting to ISAM data

This section demonstrates connecting to local ISAM data from an ODBC-compliant application on your Windows or Unix machine.

If you are implementing cross-platform access to your ISAM data using the Easysoft ODBC-ODBC Bridge or the Easysoft JDBC-ODBC Bridge, please refer to the documentation provided with those products for an example of connecting to remote data.

Chapter Guide

Demonstration on Windows

This section provides a worked example of using Microsoft Access to connect to the sample ISAM data provided with Easysoft Data Access for ISAM.

This worked example assumes that:

To obtain a list of customer order shipping details and display the results in Microsoft Access:

1.  Run Microsoft Access.

2.  Create a new blank database.

3.  Display the Tables tab on the database window.

4.  Right-click in the empty window, then select Link Tables.

 The Link dialog box is displayed.

5.  From the Files of Type drop-down list box, select ODBC Databases ().

 The Select Data Source dialog box is displayed.

6.  Click the Machine Data Source tab.

7.  Select the data source that connects to the sample data.

 The default name for this data source is Sample_Isam, but you may have given it an alternative name.

8.  Click OK.

 The Link Tables dialog box is displayed.

9.  Click Select All to link all the tables into the data base and then click OK.

 After a few moments, the tables are listed in the Tables tab of the database window in Microsoft Access.

10.  Click the Queries tab in the database window and then click New to create a new query.

11.  On the New Query wizard, click Design View and then click OK.

12.  On the Show Table dialog box, select the CUSTOMER table and then click Add.

 The table is added to the Select Query window.

13.  Select the ORDERS table and then click Add.

14.  Select the LINEITEM table and then click Add.

15.  Click Close to close the Select Table dialog box.

16.  Resize the tables in the Select Query window until you can see all the column names in full.

 The Select Query window will look something like:

Figure 28: Tables on the Microsoft Access Select Query screen

17.  Create the following joins:

Figure 29: Joins on the Microsoft Access Select Query screen

18.  From the CUSTOMER, ORDERS and LINEITEM tables, drag each of the following columns in turn and release them in the columns in the lower half of the Select Query window:

 C_NAME from the CUSTOMER table

 O_ORDERDATE from the ORDERS table

 L_QUANTITY from the LINEITEM table

 L_SHIPDATE from the LINEITEMtable

 O_ORDERKEY from the ORDERS table

 The Select Query window will look something like:

Figure 30: A Query on the Microsoft Access Select Query screen

19.  Click the Close box to close the Select Query window. When asked if you want to save the query, click Yes.

 The Save As dialog box is displayed.

20.  Enter a name for the query then click OK.

 The query is now listed on the Queries tab of the database window.

21.  Double-click the query to open it.

 A list of customer order shipping details is displayed:

Figure 31: Column details on the Microsoft Access Select Query screen

Close Microsoft Access unless you want to continue using it for another task.

Demonstration on Unix

This section provides a worked example of using interactive SQL queries to connect to the sample ISAM data provided with Easysoft Data Access for ISAM.


NB

This demonstration uses a script called "demosql" to run an SQL script, as described in "Querying a data source on Unix" on page 61. This demonstration may be ignored if the instructions in that section have already been understood.


This worked example assumes that:

To query the sample data source using demosql:

1.  Change into the usr/local/easysoft/isam/bin directory.

2.  Type:

 ./demosql

 If an "invalid user" or "invalid password" error is displayed, check that the correct user name and password are specified in the script.

 If a line beginning "conn =" is displayed, then the connection has been made and you can type an SQL statement to query the data source.

3.  To display a list of tables in the data source, type:

 select * from info_schema.tables;

4.  To display a list of customer names and their account balances, type:

 select "C_NAME", "C_ACCTBAL" from CUSTOMER;

5.  To display a list of order numbers and their total prices, type:

 select "O_ORDERKEY", "O_TOTALprice" from ORDERS;

6.  To display a list of customer order shipping details, type:

 SELECT "C_NAME", "O_ORDERDATE", "L_QUANTITY", "L_SHIPDATE", "O_ORDERKEY"
from CUSTOMER, ORDERS, LINEITEM
where "C_CUSTKEY"="O_CUSTKEY" and "O_ORDERKEY"="L_ORDERKEY";

 If the returned data scrolls out of view, press <Pause> and <Enter> (if using a Windows terminal) or <Ctrl-S> and <Ctrl-Q> (if using Unix xterm) to halt and then continue the listing on the screen.

 To finish your SQL session, press <Enter> to return to the system prompt.