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 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 11-2 LONG Column Descriptor

Description	Possible Binary Values	Byte Range
Name or Address of Output Device	File name or random heap address	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 3 = random heap space	46
Input Device Type	0 = no device specified 1 = file 3 = random heap space	47
Reserved for Internal Use		48
Size in Bytes of LONG Column Data	1 to 2³¹-1 (or 2,147,483,647) per LONG column per row. Standard column data is restricted to 3996 bytes maximum.	49 through 52
Reserved for Internal Use		53 through 96

Example LONG Column Descriptor Declaration

   C     Use this when you don't need to break down the descriptor.        

   

         CHARACTER*96 LongDesc 

   

   C     Use this when you want to access a portion of the descriptor.        

   

         CHARACTER*44 OutputDevName

         CHARACTER    OutDevOption 

         CHARACTER    OutDevType 

         CHARACTER    InDevType 

         CHARACTER    UnusedA

         INTEGER      ByteLength

         CHARACTER*44 UnusedB

Using LONG Columns with a SELECT Command

Should an error occur before completion of the 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.

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.

Retrieving LONG Column Data with a SELECT, FETCH, or REFETCH Command

Technical documentation

» Table of Contents

» Index

Using the LONG Column Descriptor

Example LONG Column Descriptor Declaration

Using LONG Columns with a SELECT Command

Using LONG Columns with a Dynamic FETCH Command