There was a post back in April which addressed this exact item. The
original author of the post was Gary Biggs. Here is the part about the
record number, known in Image/SQL as the Tuple ID (TID):
<SNIP>
>Recalcitrant Tables
>
>Some tables contain no combination of columns that will yield a unique
key.
>
>Some possible solutions include:
>
>" Adding a date/time stamp
>" Serialize (number transactions), possibly using a TPI Index
>" Use a Structural View to map the TID (Tuple ID) function to map the
> Tuple Id (or Image Record Address) to a column that insures uniqueness
>
>Strucutural Views
>
>A structural view is an Allbase view used to expand upon the column
>definitions of an IMAGE/SQL or Allbase table. They are used to recreate
the
>complex field structures found in COGNOS structures or COBOL levels.
>
>Thus, this simple IMAGE/SQL structure (as seen in QUERY):
>
>SET NAME:
> PAYMENT-HISTORY,DETAIL
>
> ITEMS:
> PATRON-NUMBER, Z6 <<SEARCH ITEM>>
> BATCH-NUMBER, Z8 <<SEARCH ITEM>>
> BATCH-SEQUENCE, I1
> DEPOSIT-DATE, K2
> TRANS-TYPE, K1
> AMOUNT, I2
> CAMPAIGN, Z2
> FISCAL-YEAR, Z2
> STATUS, X2
> OPER-BATCH-TYPES, X2
> SOURCE-CODE, X4
> PAYMENT-DESCR, X10
>
>CAPACITY: 120078 ENTRIES: 89722
>
>-------------------------------------------------------------------------
-----
--
>-------
>
>Can be expanded into a more useful form by creating the following view:
>
>
>Create view desktop.payment_history(
> Amount,
> Batch_Number,
> Batch_Sequence,
> Batch_Type,
> Campaign,
> Deposit_Date,
> Fiscal_Year,
> Operation_Type,
> Patron_Number,
> Payment_Descr,
> Perform_Key,
> Price_key,
> Production,
> Unique_key,
> Season,
> Series,
> Source_Code,
> Status,
> Trans_Type)
> As Select (Amount,
> Batch_Sequence,
> substring(Oper_Batch_Types,1,1),
> Campaign,
> cast(cast(Deposit_Date,CHAR(6),),
> Date, YYMMDD ),
> Fiscal_Year,
> substring(Oper_batch_types,2,1),
> Patron_Number,
> Payment_Descr,
> substring(payment_descr,1,8),
> substring(payment_descr,1,10),
> substring(payment_descr,1,6),
> cast(TID(),char(32)),
> substring(payment_descr,7,2),
> source_code,
> Status,
> Trans_Type
> from PACT.PAYMENT_HISTORY;
>-------------------------------------------------------------------------
-----
--
>----
>
>Notice how the ALLBASE/SQL substring function is used to recreate the
much
>more complex record structure found in traditional 3rd and 4th
generation
>languages. Field order is no longer a concern in SQL, so to improve the
>usability of the view, all data items are alphabetized to allow the
>effective use of drop down menus and scroll able pick lists with GUI
based
>tools. The cast function is used to convert a 32bit integer date stored
as
>YYMMDD to a form that SQL and ODBC understand.
>
>This table also suffers from a common problem with non-SQL based data
>storage, there is no combination of fields that will absolutely identify
a
>row as unique. To accomplish this task, we can cast the TID (Tuple
ID)function
>as a char column. Each row now has a unique serial number. Thus,
>
>create unique index Unique_idx on
> desktop.patrons(patron_number,unique_key);
>
>now satisfies the unique index requirement of the JET Database Engine
and
>we can Insert, Update and Delete on this table. Again, for best
>performance, you should create the unique index using the IMAGE, TPI or
Allbase
>index that results in the shortest chained retrieval.
>
Hope this helps.
John Spivey
Litespec Optical Fiber, LLC
Research Triangle Park, NC
[log in to unmask]
----------
From: Richard Trapp RAT[SMTP:[log in to unmask]]
Sent: Thursday, July 11, 1996 4:30 PM
To: Multiple recipients of list HP3000-L
Subject: ODBC access to IMAGE/SQL Row Id?
Anybody know of a way to get the record number for a detail set via
SQL/ODBC? (Or is this why we need Birket's ODBC driver to make it a
column?)
Rich
|
