Easysoft SQI-Unisys LINC Developer Driver User Guide - Connection

Connecting to the Easysoft SQI-Unisys LINC Developer Driver

Having installed the Easysoft SQI-Unisys LINC Developer Driver, you can immediately demonstrate a connection to an LDA III Runtime Database.

This section contains exercises to demonstrate the Easysoft SQI-Unisys LINC Developer Driver connecting to and amending data in two LDA III data sources.

Although the exercises were created with and make reference to Microsoft Access, they can be carried out with any ODBC-compliant SQL tool.

Chapter Guide

Prerequisites

This section assumes you have the Sample_LDAIII User DSN connected to the Easysoft sample database.

You should have received a SAMPLE database when you obtained LDA III from Unisys.

Make sure that you:

The Sample_LDAIII User DSN, created as part of the Easysoft SQI-Unisys LINC Developer Driver, is to be used with the following changes:

 Refer to "Creating a data source" on page 38 for more information on how DSNs are configured.

Both the sample model supplied with LDA III and the sample database supplied with the Easysoft SQI-Unisys LINC Developer Driver contain the following tables:

Connecting to the source data Database

1.  Start Microsoft Access, create a blank database and then save it as db_lda.mdb.

 A Database window is displayed, which currently has no tables. It is now necessary to link to the tables containing the source data.

2.  Select File > Get External Data > Link Tables to begin connecting to the sample database and its tables, installed as part of the Easysoft SQI-Unisys LINC Developer Driver.

 This displays the Link window, in which the appropriate database can be selected.

3.  From the Files of type drop-down list, choose ODBC Databases.

 The Select Data Source dialog box is displayed:

Figure 18: The Select Data Source dialog box

4.  Click the Machine Data Source Tab, select Sample_LDAIII and click OK.

 The Link Tables window displays the following list of tables:

 


NB

The list of tables displayed is not a complete listing, because the Ispec profile option was chosen in the Sample_LDAIII DSN.


5.  Click Select All to select all the tables shown, then click OK.

 This will then link all the displayed source tables, before returning to the Database window, where the destination tables may then be selected.

Connecting to the Sample LDA III Runtime Database

1.  Select File > Get External Data > Link Tables to begin connecting to the sample LDA III Runtime database.

 This displays the Link window, in which the appropriate database can be selected.

2.  From the Files of type drop-down list, choose ODBC Databases.

 The Select Data Source window is displayed.

3.  Click the Machine Data Source Tab, select LDA_DSN and click OK.

 The Link Tables window is displayed, which due to the Ispec profile option being chosen in this DSN, will contain the same number of tables as displayed for the previous DSN used.

 For more information please refer to "Determining Profile Record Selection Criteria" on page 42.

4.  Click Select All to select all the tables shown and then click OK.

 This will then link all the destination tables into the database before returning to the Database window.


NB

The LDA_DSN table names selected will have the suffix 1 applied to them, because the table names are identical in both databases.


Figure 19: db_lda tables in the Database window

5.  Select the SAMPLE_PROD table and click Open.

 The data within the SAMPLE_PROD table is displayed:

Figure 20: Sample data within the SAMPLE_PROD table

6.  Select the SAMPLE_PROD1 table and click Open.

 An empty SAMPLE_PROD1 table is displayed:

Figure 21: The sample linked SAMPLE_PROD1 table empty

 Appropriate SQL statements must now be used in order to populate the empty table with the data from the populated table.


NB

SQL statements will not be generated for use with SAMPLE_DOX and SAMPLE_PAUDT since, although the tables need to exist, no records exist in these source database tables.

In addition SAMPLE_EVENT is not used, since this table is used to track specific events within the LDA III Runtime, such as a stock transaction, where a sale is made or goods are received.


Preparing to enter SQL Statements

Although the process of populating the empty tables may be done by either the Microsoft Access Query Wizards, the Select Query window in Design View or by using drag-and-drop, it is also possible to use SQL statements to perform this task:

1.  Click on the Queries tab and click New.

 The New Query window is displayed.

2.  In order to not use query wizards select Design View and then click OK.

3.  Click Close.

 The Select Query window is displayed, showing that no tables or fields are currently defined.

4.  Select View > SQL View to display the Select Query window in SQL mode, which now allows SQL statements to be entered directly.

Entering SQL Statements

The empty tables must now be populated from the source data tables by using the SQL statement INSERT INTO.


NB

In order for these statements to work, the sequence of column names used in the SQL statements must be the same sequence as they occur in both source and destination tables. Additionally all columns must be accounted for in the statement, regardless of whether that column is to be populated or not.


1.  By default the statement SELECT; is displayed, which should be deleted and replaced with:

 INSERT INTO sample_cust1 (credlimit, [cust-type], customer, deladd1, deladd2, deladd3, maint, nam, postadd1, postadd2, postadd3, salesrep)

 SELECT credlimit, [cust-type], customer, deladd1, deladd2, deladd3, "A" AS maint, nam, postadd1, postadd2, postadd3, salesrep

 FROM sample_cust;

 


NB

The source MAINT column data has not been selected, because data is being appended to the LDA III Runtime Database table and therefore the MAINT status flag is set to A, regardless of its value in the source table.


2.  Select File > Save and name the file Populate_CUST.

 The remaining queries can then be created.

3.  Create the following statement and save as Populate_PROD.

 INSERT INTO sample_prod1 (maint, nam, product, reordlev, sellprice, unitsale)

 SELECT "A" AS maint, nam, product, reordlev, sellprice, unitsale

 FROM sample_prod;

4.  Create the following statement and save as Populate_REPT.

 INSERT INTO sample_rept1 (maint, report, reptcode)

 SELECT "A" AS maint, report, reptcode

 FROM sample_rept;

5.  Create the following statement and save as Populate_SREP.

 INSERT INTO sample_srep1 (area, maint, nam, salesrep)

 SELECT area, "A" AS maint, nam, salesrep

 FROM sample_srep;

6.  Create the following statement and save as Populate_VEND.

 INSERT INTO sample_vend1 (maint, nam, postadd1, postadd2, postadd3, vendor)

 SELECT "A" AS maint, nam, postadd1, postadd2, postadd3, vendor

 FROM sample_vend;

7.  Create the following statement and save as Populate_VPROD.

 INSERT INTO sample_vprod1 (rowid, comments, maint, product, vendor)

 SELECT rowid, comments, "A" AS maint, product, vendor

 FROM sample_vprod;

 You will then need to implement the SQL statements created, in order to begin populating the empty tables with the test data.

Implementing SQL Statements

1.  To implement the SQL statement and populate the empty table, select Populate_CUST and click Open.

 If confirmation messages are enabled, Microsoft Access will ask for confirmation to run the append query and modify the relevant table.

2.  Click Yes to continue.

 The system will ask for further confirmation, indicating the number of rows that are about to be appended.

3.  Click Yes to continue.

4.  Select:

5.   Click Open.

6.  Exit Microsoft Access by selecting File > Exit.

 You can now use the test data residing in the LDA III Runtime Database.

Using the LDA Runtime Database Test Data

1.  Select Start > Programs > LDA III > Runtime.

 The LDA III Runtime window is displayed.

2.  Select File > Open Session.

 The Select a LINC System window is displayed.

3.  Select the SAMPLE system name, and click OK.

 This will open a session to allow you to begin testing your LINC system, with the test data now residing in the LDA Runtime Database.