Reference Manual
   CHAPTER 9. SQL Statements     

GET DATA statement [ESQL]

Function 

To get string or binary data for one column on the current row of a cursor. GET DATA is usually used to fetch LONG BINARY or LONG VARCHAR fields. See SET statement.

Syntax 

GET DATA cursor-name COLUMN column-num OFFSET start-offset
... [ WITH TEXTPTR ]
... USING DESCRIPTOR sqlda-name
| INTO host-variable [, ... ] |

Parameters 

cursor-name: identifier, or host-variable

column-num: integer or host-variable

start-offset: integer or host-variable

sqlda-name: identifier

Permissions 

The cursor must be opened and positioned on a row, using FETCH.

Side effects 

None.

See also 

FETCH statement

READTEXT statement

Description 

Get a piece of one column value from the row at the current cursor position. The value of column-num starts at one, and identifies which column's data is to be fetched. That column must be of a string or binary type.

The start-offset indicates the number of bytes to skip over in the field value. Normally, this would be the number of bytes previously fetched. The number of bytes fetched on this GET DATA statement is determined by the length of the target host variable.

The indicator value for the target host variable is a short integer, so it cannot always contain the number of bytes truncated. Instead, it contains a negative value if the field contains the NULL value, a positive value (NOT necessarily the number of bytes truncated) if the value is truncated, and zero if a non-NULL value is not truncated.

If the WITH TEXTPTR clause is given, a text pointer is retrieved into a second indicator variable or into the second field in the SQLDA. This text pointer can be used with the Transact-SQL READ TEXT and WRITE TEXT statements.

The total length of the data is returned in the SQLCOUNT field of the SQLCA structure.

Standards and compatibility 

• SQL/92     Vendor extension.

• Sybase     Not supported by Open Client/Open Server. An alternative is the Transact-SQL READTEXT statement.

Example 

• The following example uses GET DATA to fetch a binary large object (often called a blob).

  EXEC SQL BEGIN DECLARE SECTION;
  DECL_BINARY(1000) piece;
  short ind;
  long offset;
  EXEC SQL END DECLARE SECTION;
  int size;
  /* Open a cursor on a long varchar field */
  EXEC SQL DECLARE big_cursor CURSOR FOR
  SELECT long_data FROM some_table
  WHERE key_id = 2;
  EXEC SQL OPEN big_cursor;
  EXEC SQL FETCH big_cursor INTO :piece;
  for( offset = 0; ; offset += piece.len ) {
     EXEC SQL GET DATA big_cursor COLUMN 1
     OFFSET :offset INTO :piece:ind;
     /* Done if the NULL value */
     if( ind < 0 ) break;
     write_out_piece( piece );
     /* Done when the piece was not truncated */
     if( ind == 0 ) break;
  }
  EXEC SQL CLOSE big_cursor;