Sample fetch array program
The following sample program shows how to perform the
steps in Using a fetch array. It uses separate
functions to initialize, print, and free the sqlda structure.
These functions are described in the following sections.
#include <windows.h>
#include
#include
EXEC SQL include sqlda.h;
EXEC SQL include locator.h;
EXEC SQL include sqltypes.h;
#define BLOBSIZE 32275 /* using a predetermined length for blob */
EXEC SQL begin declare section;
long blobsize; /* finding the maximum blob size at runtime */
EXEC SQL end declare section;
/*********************************************************************
* Function: init_sqlda()
* Purpose: With the sqlda pointer that was returned from the DESCRIBE
* statement, function allocates memory for the fetch arrays
* in the sqldata fields of each column. The function uses
* FetArrSize to determine the size to allocate.
* Returns: < 0 for error
* > 0 error with messagesize
*********************************************************************/
int init_sqlda(struct sqlda *in_da, int print)
{
int i, j,
row_size=0,
msglen=0,
num_to_alloc;
struct sqlvar_struct *col_ptr;
ifx_loc_t *temp_loc;
char *type;
if (print)
printf("columns: %d. \n", in_da->sqld);
/* Step 1: determine row size */
for (i = 0, col_ptr = in_da->sqlvar; i < in_da->sqld; i++, col_ptr++)
{
/* The msglen variable holds the sum of the column sizes in the
* database; these are the sizes that DESCRIBE returns. This
* sum is the amount of memory that ESQL/C needs to store
* one row from the database. This value is <= row_size. */
msglen += col_ptr->sqllen; /* get database sizes */
/* calculate size for C data: string columns get extra byte added
* to hold null terminator */
col_ptr->sqllen = rtypmsize(col_ptr->sqltype, col_ptr->sqllen);
/* The row_size variable holds the sum of the column sizes in
* the client application; these are the sizes that rtypmsize()
* returns. This sum is amount of memory that the client
* application needs to store one row. */
row_size += col_ptr->sqllen;
if(print)
printf("Column %d size: %d\n", i+1, col_ptr->sqllen);
}
if (print)
{
printf("Total message size = %d\n", msglen);
printf("Total row size = %d\n", row_size);
}
EXEC SQL select max(length(cat_descr)) into :blobsize from catalog;
/* Step 2: set FetArrSize global variable to number of elements
* in fetch array; this function calculates the FetArrSize
* value that can fit into the existing fetch buffer.
* If FetBufSize is not set (equals zero), the code assigns a
* default size of 4096 bytes (4 kilobytes). Alternatively, you
* could set FetArrSize to the number elements you wanted to
* have and let ESQL/C size the fetch buffer. See the text in
* "Allocating Memory for the Fetch Arrays" for more information.*/
if (FetArrSize <= 0) /* if FetArrSize not yet initialized */
{
if (FetBufSize == 0) /* if FetBufSize not set */
FetBufSize = 4096; /* default FetBufSize */
FetArrSize = FetBufSize/msglen;
}
num_to_alloc = (FetArrSize == 0)? 1: FetArrSize;
if (print)
{
printf("Fetch Buffer Size %d\n", FetBufSize);
printf("Fetch Array Size: %d\n", FetArrSize);
}
/* set type in sqlvar_struct structure to corresponding C type */
for (i = 0, col_ptr = in_da->sqlvar; i < in_da->sqld; i++,
col_ptr++)
{
switch(col_ptr->sqltype)
{
case SQLCHAR:
type = "char ";
col_ptr->sqltype = CCHARTYPE;
break;
case SQLINT:
case SQLSERIAL:
type = "int ";
col_ptr->sqltype = CINTTYPE;
break;
case SQLBYTES:
case SQLTEXT:
if (col_ptr->sqltype == SQLBYTES)
type = "blob ";
else
type = "text ";
col_ptr->sqltype = CLOCATORTYPE;
/* Step 3 (TEXT & BLOB only): allocate memory for sqldata
* that contains ifx_loc_t structures for TEXT or BYTE column */
temp_loc = (ifx_loc_t *)malloc(col_ptr->sqllen * num_to_alloc);
if (!temp_loc)
{
fprintf(stderr, "blob sqldata malloc failed\n");
return(-1);
}
col_ptr->sqldata = (char *)temp_loc;
/* Step 4 (TEXT & BLOB only): initialize ifx_loc_t structures to
hold blob values in a user-defined buffer in memory */
byfill( (char *)temp_loc, col_ptr->sqllen*num_to_alloc ,0);
for (j = 0; j< num_to_alloc; j++, temp_loc++)
{
/* blob data to go in memory */
temp_loc->loc_loctype = LOCMEMORY;
/* assume none of the blobs are larger than BLOBSIZE */
temp_loc->loc_bufsize = blobsize;
temp_loc->loc_buffer = (char *)malloc(blobsize+1);
if (!temp_loc->loc_buffer)
{
fprintf(stderr, "loc_buffer malloc failed\n");
return(-1);
}
temp_loc->loc_oflags = 0; /* clear flag */
} /* end for */
break;
default: /* all other data types */
fprintf(stderr, "not yet handled(%d)!\n", col_ptr->sqltype);
return(-1);
} /* switch */
/* Step 5: allocate memory for the indicator variable */
col_ptr->sqlind = (short *)malloc(sizeof(short) * num_to_alloc);
if (!col_ptr->sqlind)
{
printf("indicator malloc failed\n");
return -1;
}
/* Step 6 (other data types): allocate memory for sqldata. This
* function
* casts the pointer to this memory as a (char *). Subsequent
* accesses to the data would need to cast it back to the data
* type that corresponds to the column type. See the print_sqlda()
* function for an example of this casting. */
if (col_ptr->sqltype != CLOCATORTYPE)
{
col_ptr->sqldata = (char *) malloc(col_ptr->sqllen *
num_to_alloc);
if (!col_ptr->sqldata)
{
printf("sqldata malloc failed\n");
return -1;
}
if (print)
printf("column %3d, type = %s(%3d), len=%d\n", i+1, type,
col_ptr->sqltype, col_ptr->sqllen);
}
} /* end for */
return msglen;
}
/**********************************************************************
* Function: print_sqlda
* Purpose: Prints contents of fetch arrays for each column that the
* sqlda structure contains. Current version only implements
* data types found in the blobtab table. Other data types
* would need to me implemented to make this function complete.
**********************************************************************/
void print_sqlda(struct sqlda *sqlda, int count)
{
void *data;
int i, j;
ifx_loc_t *temp_loc;
struct sqlvar_struct *col_ptr;
char *type;
char buffer[512];
int ind;
char i1, i2;
/* print number of columns (sqld) and number of fetch-array elements
*/
printf("\nsqld: %d, fetch-array elements: %d.\n", sqlda->sqld,
count);
/* Outer loop: loop through each element of a fetch array */
for (j = 0; j < count; j ++)
{
if (count > 1)
{
printf("record[%4d]:\n", j);
printf("col | type | id | len | ind | rin | data ");
printf("| value\n");
printf("--------------------------------------------");
printf("------------------\n");
}
/* Inner loop: loop through each of the sqlvar_struct structures */
for (i = 0, col_ptr = sqlda->sqlvar; i < sqlda->sqld; i++, col_ptr++)
{
data = col_ptr->sqldata + (j*col_ptr->sqllen);
switch (col_ptr->sqltype)
{
case CFIXCHARTYPE:
case CCHARTYPE:
type = "char";
if (col_ptr->sqllen > 40)
sprintf(buffer, " %39.39s<..", data);
else
sprintf(buffer, "%*.*s", col_ptr->sqllen,
col_ptr->sqllen, data);
break;
case CINTTYPE:
type = "int";
sprintf(buffer, " %d", *(int *) data);
break;
case CLOCATORTYPE:
type = "byte";
temp_loc = (ifx_loc_t *)(col_ptr->sqldata +
(j * sizeof(ifx_loc_t)));
sprintf(buffer, " buf ptr: %p, buf sz: %d, blob sz: %d",
temp_loc->loc_buffer,
temp_loc->loc_bufsize, temp_loc->loc_size);
break;
default:
type = "??????";
sprintf(buffer, " type not implemented: ",
"can't print %d", col_ptr->sqltype);
break;
} /* end switch */
i1 = (col_ptr->sqlind==NULL) ? 'X' :
(((col_ptr->sqlind)[j] != 0) ? 'T' : 'F');
i2 = (risnull(col_ptr->sqltype, data)) ? 'T' : 'F';
printf("%3d | %-6.6s | %3d | %3d | %c | %c | ",
i, type, col_ptr->sqltype, col_ptr->sqllen, i1, i2);
printf("%8p |%s\n", data, buffer);
} /* end for (i=0...) */
} /* end for (j=0...) */
}
/**********************************************************************
* Function: free_sqlda
* Purpose: Frees memory used by sqlda. This memory includes:
* o loc_buffer memory (used by TEXT & BYTE)
* o sqldata memory
* o sqlda structure
**********************************************************************/
void free_sqlda(struct sqlda *sqlda)
{
int i,j, num_to_dealloc;
struct sqlvar_struct *col_ptr;
ifx_loc_t *temp_loc;
for (i = 0, col_ptr = sqlda->sqlvar; i < sqlda->sqld; i++,
col_ptr++)
{
if ( col_ptr->sqltype == CLOCATORTYPE )
{
/* Free memory for blob buffer of each element in fetch array */
num_to_dealloc = (FetArrSize == 0)? 1: FetArrSize;
temp_loc = (ifx_loc_t *) col_ptr->sqldata;
for (j = 0; j< num_to_dealloc; j++, temp_loc++)
{
free(temp_loc->loc_buffer);
}
}
/* Free memory for sqldata (contains fetch array) */
free(col_ptr->sqldata);
}
/* Free memory for sqlda structure */
free(sqlda);
}
void main()
{
int i = 0;
int row_count, row_size;
EXEC SQL begin declare section;
char *db = "stores7";
char *uid = "odbc";
char *pwd = "odbc";
EXEC SQL end declare section;
/********************************************************************
* Step 1: declare an sqlda structure to hold the retrieved column
* values
********************************************************************/
struct sqlda *da_ptr;
EXEC SQL connect to :db user :uid using :pwd;
if ( SQLCODE < 0 )
{
printf("CONNECT failed: %d\n", SQLCODE);
exit(0);
}
/* Prepare the SELECT */
EXEC SQL prepare selct_id from 'select catalog_num, cat_descr from
catalog';
if ( SQLCODE < 0 )
{
printf("prepare failed: %d\n", SQLCODE);
exit(0);
}
/********************************************************************
* Step 2: describe the prepared SELECT statement to allocate memory
* for the sqlda structure and the sqlda.sqlvar structures
* (DESCRIBE can allocate sqlda.sqlvar structures because
* prepared statement is a SELECT)
********************************************************************/
EXEC SQL describe selct_id into da_ptr;
if ( SQLCODE < 0 )
{
printf("describe failed: %d\n", SQLCODE);
exit(0);
}
/********************************************************************
* Step 3: initialize the sqlda structure to hold fetch arrays for
* columns
********************************************************************/
row_size = init_sqlda(da_ptr, 1);
/* declare and open a cursor for the prepared SELECT */
EXEC SQL declare curs cursor for selct_id;
if ( SQLCODE < 0 )
{
printf("declare failed: %d\n", SQLCODE);
exit(0);
}
EXEC SQL open curs;
if ( SQLCODE < 0 )
{
printf("open failed: %d\n", SQLCODE);
exit(0);
}
while (1)
{
/********************************************************************
* Step 4: perform fetch to get "FetArrSize" array of rows from
* the database server into the sqlda structure
********************************************************************/
EXEC SQL fetch curs using descriptor da_ptr;
/* Reached last set of matching rows? */
if ( SQLCODE == SQLNOTFOUND )
break;
/********************************************************************
* Step 5: obtain the values from the fetch arrays of the sqlda
* structure; use sqlca.sqlerrd[2] to determine number
* of array elements actually retrieved.
********************************************************************/
printf("\n===============\n");
printf("FETCH %d\n", i++);
printf("===============");
print_sqlda(da_ptr, ((FetArrSize == 0) ? 1 : sqlca.sqlerrd[2]));
/********************************************************************
* Step 6: repeat the FETCH until all rows have been fetched (SQLCODE
* is SQLNOTFOUND
********************************************************************/
}
/********************************************************************
* Step 7: Free resources:
* o statement id, selct_id
* o select cursor, curs
* o sqlda structure (with free_sqlda() function)
* o delete sample table and its rows from database
********************************************************************/
EXEC SQL free selct_id;
EXEC SQL close curs;
EXEC SQL free curs;
free_sqlda(da_ptr);
}