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 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. Table 12-2 “LONG Column Descriptor” shows the format of the LONG column descriptor.




	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 2³¹-1	49 through 52
Reserved for Internal Use		53 through 96

Parsing LONG Column Descriptors

Record structures cannot be declared as host variables unless they are to be used in BULK operations. To parse the LONG column descriptor returned by a SELECT or FETCH statement, copy it to a record like the one shown below.

   (* Use case 0 when you don't need to break down the descriptor.     *)

   (* Use case 1 when you want to access a portion of the descriptior. *)



   type

     desc_type = packed record

	         case integer of

	         0:(lfhv   : packed array [1..96] of char);

                 1:(name   : packed array [1..44] of char;

                       opt    : 0..255;

                       outdev : 0..255;

                       indev  : 0..255;

                       xxx    : 0..255;

                       size   : integer;

                       intern : packed array [1..44] of char);

                    end;

Using LONG Columns with a SELECT Command

When you use 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.

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

Parsing LONG Column Descriptors

Using LONG Columns with a SELECT Command

Using LONG Columns with a Dynamic FETCH Command