Over the past months, there has been a great deal of controversy=20
regarding the ability to update IMAGE/SQL tables via the HP ALLBASE=20
ODBC/API and Microsoft Access version 2.0. Here is one method that=20
enables you to do it.
You must be running IMAGE/SQL version G.1.05 or higher. This is the=20
version that is available with MPE/iX 5 Express 3. This version is=20
required to properly register the Image detail data set keys with the=20
Allbase SQL optimizer. Prior to this release, no detail data set key=20
information was available to the optimizer and serial reads were=20
employed. This resulted in very poor performance when attempting to=20
update these data sets.
If you intend to update Image Key Items with MS Access, you must enable=20
Critical Item Update. To do this, log on to the group and account which=20
contains your IMAGE Database. With the database closed issue the=20
following commands:
:run dbutil.pub.sys
> set (mydbname) CIUPDATE =3D ON
> exit
:
The problem with MS Access and updates is that the ODBC API does not=20
transmit key information when the tables are attached. Access expects to=20
find a =91Unique Key=92 for every row in a table. With an IMAGE master data=
=20
set, this is the one and only item declared as a key on the master. =20
With Image detail data sets, there is no corresponding structure unless=20
there is a key that is unique because of its usage. In essence, we have=20
to create unique keys for detail data sets from existing Image elements.
Here is how you do it:
Creating MS ACCESS unique indexes for IMAGE master data sets
=20
Here is the IMAGE definition for a simple manual master.
SET NAME:
TRANS-MASTER,MANUAL
ITEMS:
TRANS-TYPE, K1 <<KEY ITEM>>
DESCRIPTION, X40
=20
CAPACITY: 101 ENTRIES: 64
When we attach this data set in MS Access via IMAGE/SQL we get a table=20
like this (as seen in Access design view on the attached table):
Table: DB_TRANS_MASTER
* Field Name Data Type Description
=20
TRANS_TYPE Number=20
DESCRIPTION Text
You can open the table, but you can=92t update it. To inform Access about=
=20
its uniqueness, we need to use a SQL Specific Query to declare=20
TRANS_TYPE as a key.
So in Access,
1) Create a new query without selecting any tables or queries from the=20
list presented.
2) From the menu bar choose Query, then SQL Specific, then Data=20
Definition.
3) This will bring up a blank Data Definition Query Box:
4) Enter the following Data Definition Query:
create unique index TRANS_INDEX on DB_TRANS_MASTER(TRANS_TYPE);
=20
This declares a Unique Index TRANS_INDEX on the TRANS-TYPE field of the=20
IMAGE master data set TRANS-MASTER.
5) Save and Run the Query.
Now opening the Attached Table displays:
Table: DB_TRANS_MASTER
* Field Name Data Type Description
=20
* TRANS_TYPE Number=20
DESCRIPTION Text
Where the asterisk indicates a key symbol. TRANS_TYPE is now known to be=20
an unique index and you can update the table.
Creating MS ACCESS unique indexes for IMAGE detail data sets
=20
Detail Data sets can be handled in a similar fashion:=20
SET NAME:
CUSTOMERS,DETAIL
ITEMS:
CUSTOMER-NUMBER, Z6 <<SEARCH ITEM>>
ALPHA-KEY, X10 <<SEARCH ITEM>>
NAME, X40
COMPANY-NAME, X40
TITLE, X40
ADDRESS-1, X30
ADDRESS-2, X30
ADDRESS-3, X30
CITY, X26
STATE, X2
ZIP-PLUS-4, X10
MAINT-CODE, Z2
MAINT-DATE, I2
CAPACITY: 2506 ENTRIES: 1917
In this case, CUSTOMER_NUMBER is known (but not enforced by Image) to be=20
unique. CUSTOMER_NUMBER is a serially incrementing counter that is used=20
by one and only one record in this data set. A detail data set was used=20
in this instance to allow lookups by the customer=92s last name as found=20
in ALPHA-KEY. Hence, we will have only one customer #1 but may have many=20
with the last name of BIGGS.
As with the manual master in the above example we issue a data=20
definition query as follows:
create unique index CUST_INDEX on DB_CUSTOMERS(CUSTOMER_NUMBER);
=20
This declares a Unique Index CUST_INDEX on the CUSTOMER_NUMBER field of=20
the IMAGE detail data set CUSTOMERS.
As with the manual master, DB_CUSTOMERS can now be updated with Access.=20
For speed, you need to make sure that the item you are declaring as=20
unique is also an IMAGE key.
Creating MS ACCESS Virtual (Multi-Column) Unique Indexes for IMAGE=20
detail data sets
Most details do not have the simple organization of the above data set.=20
There is, however, a way we can create a unique key from multiple=20
fields:
Lets look at this IMAGE Detail Data Set:
SET NAME:
SALES,DETAIL
ITEMS:
CUSTOMER-NUMBER, Z6 <<SEARCH ITEM>>
BATCH-NUMBER, Z8 <<SEARCH ITEM>>
BATCH-SEQUENCE, I1
ORDER-LINE-NUM, I1
TRANS-TYPE, I1
PRODUCT, X8
QUANTITY, I1
DISCOUNT-CODE, X4
DISCOUNT-AMNT, I2
SALES-AMOUNT, I2
DATE-ORDERED, I2
DATE-SHIPPED, I2
CAPACITY: 30044 ENTRIES: 16196
Here a customer may have many orders, and each order may have many=20
products. An order is composed of entries with the same BATCH-NUMBER and=20
BATCH-SEQUENCE and multiple line items with 1 product and quantity per=20
line. The intersection of BATCH-NUMBER, BATCH-SEQUENCE and ORDER-LINE-
NUMBER defines a unique record in this data set.=20
Thus, if we use the QUERY statement
find BATCH-NUMBER =3D X and BATCH-SEQUENCE =3D Y and ORDER-LINE-NUM =3D Z
we should always retrieve only 1 record.
With this type of relationship, we can use a MS Access Data Definition=20
query of the form:
Create unique index ORDER_LINE_ITEM on =20
SALES(BATCH_NUMBER,BATCH_SEQUENCE,ORDER_LINE_NUM);
=20
By combining the three fields, we can now update the table rows with MS=20
ACCESS. Again, for speed, at least one of the columns used to create the=20
unique index should be a key in the corresponding IMAGE detail data set.=20
And the shorter the chain, the faster the update.=20
There are some data sets where no existing elements can be combined to form=
=20
a unique key. In these cases, you may have to consider the addition of a=20
date time/stamp, sequential counter or the use of record address/tuple=20
id in combination with an existing IMAGE key. Most of us just need to=20
rethink the way we design databases to accommodate this tool.
This is a very powerful technique. With its careful use and good data=20
design principles, MS Access becomes an excellent tool for updating=20
IMAGE databases. Just don=92t forget to save your data definition queries=
=20
in the event you have to detach and reattach the tables.
For more information, please see the MS Access HELP Topic:
Creating a Data Definition Query
Gary L. Biggs, Interex SIG Allbase Chair
Performing Technologies, LLC.
[log in to unmask]
2/15/96
=20
|
