Gets string or binary data for one column of the current row of a cursor.
GET DATA cursor-name COLUMN column-num OFFSET start-offset [ WITH TEXTPTR ] { USING DESCRIPTOR sqlda-name | INTO hostvar, ... }
cursor-name : identifier | hostvar
column-num : integer | hostvar
start-offset : integer | hostvar
sqlda-name : identifier
COLUMN clause The value of column-num starts at one, and identifies the column whose data is to be fetched. That column must be of a string or binary type.
OFFSET clause 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.
WITH TEXTPTR clause If the WITH TEXTPTR clause is given, a text pointer is retrieved into a second host 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 text pointer is a 16-bit binary value, and can be declared as follows:
DECL_BINARY( 16 ) textptr_var; |
The WITH TEXTPTR clause can only be used with long data types (LONG BINARY, LONG VARCHAR, TEXT, IMAGE). If you attempt to use it with another data type, the error INVALID_TEXTPTR_VALUE is returned.
The total length of the data is returned in the SQLCOUNT field of the SQLCA structure.
USING DESCRIPTOR clause The sqlda-name specifies the SQLDA (SQL Descriptor Area) that receives the fetched data. The USING DESCRIPTOR clause provides a dynamic method of specifying host variables to receive fetched data.
INTO clause Use the INTO clause to specify the host variable that receives the fetched data. The indicator value for the target host variable is of type a_sql_len, which is currently a 16-bit value, so it is not always large enough to 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.
Similarly, if a LONG VARCHAR, LONG NVARCHAR, or LONG BINARY host variable is used with an offset greater than zero, the untrunc_len field does not accurately indicate the size before truncation.
Get a piece of one column value from the row at the current cursor position. The cursor must be opened and positioned on a row, using FETCH.
GET DATA is usually used to fetch LONG BINARY or LONG VARCHAR fields.
None.
None.
SQL/2008 Vendor extension.
The following example uses GET DATA to fetch a binary large object (also called a BLOB).
EXEC SQL BEGIN DECLARE SECTION; DECL_BINARY(1000) piece; short ind; 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; |
Discuss this page in DocCommentXchange.
|
Copyright © 2012, iAnywhere Solutions, Inc. - SQL Anywhere 12.0.1 |