ASA Programming Guide
  Embedded SQL Programming
    Sending and retrieving long values

Retrieving LONG data

This section describes how to retrieve LONG values from the database. For background information, see Sending and retrieving long values.

The procedures are different depending on whether you are using static or dynamic SQL.

To receive a LONG VARCHAR or LONG BINARY value (static SQL)

1. Declare a host variable of type DECL_LONGVARCHAR or DECL_LONGBINARY, as appropriate.

2. Retrieve the data using FETCH, GET DATA, or EXECUTE INTO. Adaptive Server Anywhere sets the following information:

   • indicator variable    The indicator variable is negative if the value is NULL, 0 if there is no truncation, and is the positive untruncated length in bytes up to a maximum of 32767.

     For more information, see Indicator variables.

   • stored_len    This DECL_LONGVARCHAR or DECL_LONGBINARY field holds the number of bytes retrieved into the array. It is never greater than array_len.

   • untrunc_len    This DECL_LONGVARCHAR or DECL_LONGBINARY field holds the number of bytes held by the database server. It is at least equal to the stored_len value. It is set even if the value is not truncated.

To receive a value into a LONGVARCHAR or LONGBINARY structure (dynamic SQL)

1. Set the sqltype field to DT_LONGVARCHAR or DT_LONGBINARY as appropriate.

2. Set the sqldata field to point to the LONGVARCHAR or LONGBINARY structure.

   You can use the LONGVARCHARSIZE( n ) or LONGBINARYSIZE( n ) macros to determine the total number of bytes to allocate to hold n bytes of data in the array field.

3. Set the array_len field of the LONGVARCHAR or LONGBINARY structure to the number of bytes allocated for the array field.

4. Retrieve the data using FETCH, GET DATA, or EXECUTE INTO. Adaptive Server Anywhere sets the following information:

   • * sqlind    This sqlda field is negative if the value is NULL, 0 if there is no truncation, and is the positive untruncated length in bytes up to a maximum of 32767.

   • stored_len    This LONGVARCHAR or LONGBINARY field holds the number of bytes retrieved into the array. It is never greater than array_len.

   • untrunc_len    This LONGVARCHAR or LONGBINARY field holds the number of bytes held by the database server. It is at least equal to the stored_len value. It is set even if the value is not truncated.

The following code snippet illustrates the mechanics of retrieving LONG VARCHAR data using dynamic embedded SQL. It is not intended to be a practical application:

#define DATA_LEN 128000
void get_test_var()
/*****************/
{
    LONGVARCHAR *longptr;
    SQLDA *sqlda;
    SQLVAR *sqlvar;

    sqlda = alloc_sqlda( 1 );
    longptr = (LONGVARCHAR *)malloc(
                 LONGVARCHARSIZE( DATA_LEN ) );
    if( sqlda == NULL || longptr == NULL ) {
        fatal_error( "Allocation failed" );
    }

    // init longptr for receiving data
    longptr->array_len = DATA_LEN;

    // init sqlda for receiving data
    // (sqllen is unused with DT_LONG types)
    sqlda->sqld = 1;   // using 1 sqlvar
    sqlvar = &sqlda->sqlvar[0];
    sqlvar->sqltype = DT_LONGVARCHAR;
    sqlvar->sqldata = longptr;

    printf( "fetching test_var\n" );
    EXEC SQL PREPARE select_stmt FROM 'SELECT test_var';
    EXEC SQL EXECUTE select_stmt INTO DESCRIPTOR sqlda;
    EXEC SQL DROP STATEMENT select_stmt;
    printf( "stored_len: %d, untrunc_len: %d,
       1st char: %c, last char: %c\n",
        longptr->stored_len,
        longptr->untrunc_len,
        longptr->array[0],
        longptr->array[DATA_LEN-1] );
    free_sqlda( sqlda );
    free( longptr );
}