HP 3000 Manuals HP 3000 Manuals
Retrieving LONG Column Data with a SELECT, FETCH, or REFETCH Command
The following syntax represents the available subset when your select
list includes one or more LONG columns. Remember, a LONG column can be
referenced only in a select list and/or a host variable declaration.
{* }
SELECT [ALL] {[Owner.]Table.* }
{CorrelationName.* }
{CorrelationName.ColumnName}
[,...] [INTO HostVariableDeclaration] FROM
{[Owner.]FromTableName [CorrelationName]} [,...]
As we noted earlier, the concept of how LONG column data is retrieved
differs from that of standard columns. The LONG column descriptor
(rather than the data itself) is selected or fetched into a host
variable. In the case of a dynamic FETCH command, the LONG column
descriptor information goes to the data buffer. In any case, the LONG
column data is written to a file or random heap space.
When the following SELECT command is executed, :HostPartPic will contain
the LONG column descriptor information for column PartPicture. LONG
column data will go to the output device specified when column
PartPicture was last inserted or updated.
SELECT PartNumber, PartPicture
INTO :HostPartNum, :HostPartPic
FROM PartsTable
WHERE PartNumber = 200
Using the LONG Column Descriptor
ALLBASE/SQL does not swap LONG column data into or out of a host
variable. Instead a 96-byte descriptor is available to your program at
select or fetch time. It contains LONG column information for your
program for which you must declare an appropriate host variable.
For example, if you do not know the output device type and its name or
address, you obtain this information from the descriptor. Then open the
appropriate file or call the operating system to access random heap
space.
NOTE The LONG column descriptor must be declared whether or not you
access its contents in your code.
Table 12-2. LONG Column Descriptor
-----------------------------------------------------------------------------------------------
| | | |
| Description | Possible Binary Values | Byte Range |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| Name or Address of Output | File name or heap address | 1 through 44 |
| Device | | |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| Output Device Options | | 45 |
| | 0 = no output specified | |
| | 1 = overwrite | |
| | 2 = append | |
| | 3 = wildcard | |
| | 4 = overwrite and wildcard | |
| | 5 = append and wildcard | |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| Output Device Type | | 46 |
| | 0 = no device specified | |
| | 1 = file | |
| | 3 = random heap space | |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| Input Device Type | | 47 |
| | 0 = no device specified | |
| | 1 = file | |
| | 3 = random heap space | |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| Reserved for Internal Use | | 48 |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| Size in Bytes of LONG Column | 1 to 231-1 (or 2,147,483,647) per LONG | 49 through 52 |
| Data | column per row. Standard column data | |
| | is restricted to 3996 bytes maximum. | |
| | | |
-----------------------------------------------------------------------------------------------
| | | |
| Reserved for Internal Use | | 53 through 96 |
| | | |
-----------------------------------------------------------------------------------------------
Example LONG Column Descriptor Declaration.
* Use this when you don't need to break down the descriptor.
01 LONG-COLUMN-DESCRIPTOR.
* Here n equals the number of consecutive LONG columns *
* you are referencing. *
05 EACH-ROW OCCURS n TIMES.
10 DESCRIPTOR-INFO PIC X(96).
* Use this when you want to access a portion of the descriptor.
01 LONG-COLUMN-DESCRIPTORS.
* Here n equals the number of consecutive LONG columns *
* you are referencing. *
05 EACH-ROW OCCURS n TIMES.
10 OUTPUT-DEVICE-NAME PIC X(44).
10 OUTPUT-DEVICE-OPTION PIC X.
10 OUTPUT-DEVICE-TYPE PIC X.
10 INPUT-DEVICE-TYPE PIC X.
10 FILLER PIC X.
10 SIZE-IN-BYTES PIC S9(9) COMP.
10 FILLER PIC X(44).
Using LONG Columns with a BULK SELECT Command
The following code segments illustrate a declaration for the BULK SELECT
command with long columns. Should an error occur before completion of
the BULK SELECT command, any operating system files written before the
error occurred remain on the system, and LONG column descriptors written
to a host variable array remain. It is your responsibility to remove
such files as appropriate.
Example.
.
.
.
EXEC SQL BEGIN DECLARE SECTION END-EXEC.
01 PARTSARRAY.
05 EACH-ROW OCCURS 25 TIMES.
10 PARTNAME PIC X(10).
10 PARTNAMEIND SQLIND.
10 PARTNUMBER PIC S9(9) COMP.
10 PARTNUMBERIND SQLIND.
10 PARTPICTURE PIC X(96).
10 PARTPICTUREIND SQLIND.
10 PARTMODULE PIC X(96).
10 PARTMODULEIND SQLIND.
01 STARTINDEX PIC S9(4) COMP.
01 NUMBEROFROWS PIC S9(4) COMP.
EXEC SQL END DECLARE SECTION END-EXEC.
.
.
.
Using LONG Columns with a Dynamic FETCH Command
If you have the need to dynamically retrieve LONG column data, the
sqlrowbuf column of the sqlda, as always, contains the address of the
data buffer. However, the data buffer, rather than containing LONG
column data, holds the 96-byte LONG column descriptor.
The sqltype field of the format array holds a data type ID number of 15
for a LONG BINARY column and 16 for a LONG VARBINARY column. And the
sqltotallen and sqlvallen columns will always contain a value of 96
(indicating the length of the descriptor).
When a NULL is fetched as the LONG column value, no external files are
created, and the associated indicator variable for the LONG column
descriptor is set to -1.