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
shared memory as specified.
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
shared memory.
|
| |
|
 | 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 Device | File name or
shared memory ID
| 1 through 44 |
| Output Device Options | 0 = no output specified 1 = overwrite 2 = append 3 = wildcard 4 = overwrite and wildcard 5 = append and wildcard | 45 |
| Output Device Type | 0 = no device specified 1 = file 2 = shared memory | 46 |
| Input Device Type | 0 = no device specified 1 = file 2 = shared memory | 47 |
| Reserved for Internal Use | | 48 |
| Size in Bytes of LONG Column Data | 1 to 231-1 | 49 through 52 |
| 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.
.
.
.
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.
