HP 3000 Manuals HP 3000 Manuals
Programming with TID Data Access
Each row (tuple) in an ALLBASE/SQL table is stored at a database address
on disk. This unique address is called the tuple identifier or TID. When
using a SELECT statement, you can obtain the TID of any row. In turn,
you can use this TID to specify the target row for a SELECT, UPDATE, or
DELETE statement. TID functionality provides the fastest possible data
access to a single row at a time (TID access) in conjunction with maximum
coding flexibility. The following options are available:
* Rapid read and write access to a specific row without the use of a
cursor (less overhead).
* Rapid update and delete capability based on TIDs returned by a
nested query, a union query, a join query, or a query specifying
sorted data.
Other ALLBASE/SQL functionality provides a method of processing a
multiple row query result sequentially, one row at a time. This involves
the use of a cursor with the UPDATE WHERE CURRENT, DELETE WHERE CURRENT,
and REFETCH commands which internally utilize TID access. See the
ALLBASE/SQL Reference Manual for more details.
The nature of your applications will determine how valuable TID
functionality can be to you. It could be most useful for applications
designed for interactive users and applications that must update a set of
related rows as a group. See the programming examples at the end of this
chapter.
A TID function and host variable data type are provided. The TID
function is used in the select list and/or the WHERE clause of a SELECT
statement and in the WHERE clause of an UPDATE or DELETE statement. The
new host variable data type is used in an application program to hold
data input to and output from the TID function.
Understanding TID Function Input and Output
The next sections describe how TID output is accessed via a select list
and how you provide TID input via a WHERE clause. Topics discussed are
as follows:
* Using the TID Function in a Select List.
* Using the TID Function in a WHERE Clause.
* Declaring TID Host Variables.
* Understanding the SQLTID Data Format.
Using the TID Function in a Select List.
When using the TID function in a select list, specify it as you would a
column name. In an application, you could use a statement like the
following:
SELECT TID(), VendorNumber, VendorName, PhoneNumber
INTO :TidHostVar, :VendorNumber,
:VendorName, :PhoneNumber;
FROM Purchdb.Vendors
WHERE VendorName = :VendorName
The resulting TID and column data is placed in the host variables,
TidHostVar, VendorNumber, VendorName, PhoneNumber.
The next example illustrates how to obtain TID values for qualifying rows
of a two table join. Correlation names are used.
SELECT TID(sp), TID(o)
INTO :SupplyPriceTID, :OrdersTID,
FROM PurchDB.SupplyPrice sp,
PurchDB.Orders o
WHERE sp.VendorNumber = :VendorNumber
AND o.VendorNumber = :VendorNumber
Using the TID Function in a WHERE Clause.
When using the TID function in a WHERE clause, you provide an input
parameter. For application programs, this parameter can be specified as
a host variable, a constant, or a question mark (?) representing a
dynamic parameter. The input parameter is a constant. For example:
DELETE FROM PurchDB.Parts WHERE TID() = 3:3:30;
In an application, you could use a statement like the following to verify
the data integrity of a previously accessed row:
SELECT PartNumber, PartName, SalesPrice
INTO :PartNumber, :PartName, :SalesPrice
FROM PurchDB.Parts
WHERE TID() = :PartsTID
You might use the following statement in an application to update a row:
UPDATE PurchDB.Parts
SET PartNumber = :PartNumber,
PartName = :PartName,
SalesPrice = :SalesPrice
WHERE TID() = :PartsTID
Declaring TID Host Variables.
Host variables for TID function input and output must be declared in your
application as SQLTID host variables. You would declare an SQLTID host
variable as follows:
TIDVarName : SQLTID;
Understanding the SQLTID Data Format.
The data in SQLTID host variables has its own unique format which is not
compatible with any other ALLBASE/SQL data type. It is not necessary to
know the internal format of SQLTID data to use the TID function. The
information in this section is provided in case you require the TID value
to be broken into its components.
For instance, you might want to know the page numbers of all TID's in a
table in order to analyze data distribution. To do this, you must parse
the SQLTID host variable.
ALLBASE/SQL does allow you to unload SQLTID data. However, you cannot
use the LOAD command to load TID data back into a table. The TID is a
unique identifier generated internally by ALLBASE/SQL, and cannot be
assigned by users.
An SQLTID host variable consists of eight bytes of binary data and has
the following format:
Table 13-4. SQLTID Data Internal Format
---------------------------------------------------------------------------------------------
| | |
| Content | Byte Range |
| | |
---------------------------------------------------------------------------------------------
| | |
| Version Number | 1 through 2 |
| | |
---------------------------------------------------------------------------------------------
| | |
| File Number | 3 through 4 |
| | |
---------------------------------------------------------------------------------------------
| | |
| Page Number | 5 through 7 |
| | |
---------------------------------------------------------------------------------------------
| | |
| Slot Number | 8 |
| | |
---------------------------------------------------------------------------------------------
The SQLTID version number is an optional input parameter. If not
specified, the version number defaults to 0. If you do specify the
version, it must always be 0. If a version other than 0 is specified, no
rows will qualify for the operation.
TID function application output always contains a version number of 0.