The FETCH statement can be modified to fetch more than one row at a time, which may improve performance. This is called a wide fetch or an array fetch.
SQL Anywhere also supports wide puts and inserts. See PUT statement [ESQL] and EXECUTE statement [ESQL].
To use wide fetches in embedded SQL, include the fetch statement in your code as follows:
EXEC SQL FETCH ... ARRAY nnn |
where ARRAY nnn is the last item of the FETCH statement. The fetch count nnn can be a host variable. The number of variables in the SQLDA must be the product of nnn and the number of columns per row. The first row is placed in SQLDA variables 0 to (columns per row) - 1, and so on.
Each column must be of the same type in each row of the SQLDA, or a SQLDA_INCONSISTENT error is returned.
The server returns in SQLCOUNT the number of records that were fetched, which is always greater than zero unless there is an error or warning. On a wide fetch, a SQLCOUNT of one with no error condition indicates that one valid row has been fetched.
The following example code illustrates the use of wide fetches. You can also find this code in samples-dir\SQLAnywhere\esqlwidefetch\widefetch.sqc.
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include "sqldef.h"
EXEC SQL INCLUDE SQLCA;
EXEC SQL WHENEVER SQLERROR { PrintSQLError();
goto err; };
static void PrintSQLError()
{
char buffer[200];
printf( "SQL error %d -- %s\n",
SQLCODE,
sqlerror_message( &sqlca,
buffer,
sizeof( buffer ) ) );
}
static SQLDA * PrepareSQLDA(
a_sql_statement_number stat0,
unsigned width,
unsigned *cols_per_row )
/* Allocate a SQLDA to be used for fetching from
the statement identified by "stat0". "width"
rows are retrieved on each FETCH request.
The number of columns per row is assigned to
"cols_per_row". */
{
int num_cols;
unsigned row, col, offset;
SQLDA * sqlda;
EXEC SQL BEGIN DECLARE SECTION;
a_sql_statement_number stat;
EXEC SQL END DECLARE SECTION;
stat = stat0;
sqlda = alloc_sqlda( 100 );
if( sqlda == NULL ) return( NULL );
EXEC SQL DESCRIBE :stat INTO sqlda;
*cols_per_row = num_cols = sqlda->sqld;
if( num_cols * width > sqlda->sqln )
{
free_sqlda( sqlda );
sqlda = alloc_sqlda( num_cols * width );
if( sqlda == NULL ) return( NULL );
EXEC SQL DESCRIBE :stat INTO sqlda;
}
// copy first row in SQLDA setup by describe
// to following (wide) rows
sqlda->sqld = num_cols * width;
offset = num_cols;
for( row = 1; row < width; row++ )
{
for( col = 0;
col < num_cols;
col++, offset++ )
{
sqlda->sqlvar[offset].sqltype =
sqlda->sqlvar[col].sqltype;
sqlda->sqlvar[offset].sqllen =
sqlda->sqlvar[col].sqllen;
// optional: copy described column name
memcpy( &sqlda->sqlvar[offset].sqlname,
&sqlda->sqlvar[col].sqlname,
sizeof( sqlda->sqlvar[0].sqlname ) );
}
}
fill_s_sqlda( sqlda, 40 );
return( sqlda );
err:
return( NULL );
}
static void PrintFetchedRows(
SQLDA * sqlda,
unsigned cols_per_row )
{
/* Print rows already wide fetched in the SQLDA */
long rows_fetched;
int row, col, offset;
if( SQLCOUNT == 0 )
{
rows_fetched = 1;
}
else
{
rows_fetched = SQLCOUNT;
}
printf( "Fetched %d Rows:\n", rows_fetched );
for( row = 0; row < rows_fetched; row++ )
{
for( col = 0; col < cols_per_row; col++ )
{
offset = row * cols_per_row + col;
printf( " \"%s\"",
(char *)sqlda->sqlvar[offset].sqldata );
}
printf( "\n" );
}
}
static int DoQuery(
char * query_str0,
unsigned fetch_width0 )
{
/* Wide Fetch "query_str0" select statement
* using a width of "fetch_width0" rows" */
SQLDA * sqlda;
unsigned cols_per_row;
EXEC SQL BEGIN DECLARE SECTION;
a_sql_statement_number stat;
char * query_str;
unsigned fetch_width;
EXEC SQL END DECLARE SECTION;
query_str = query_str0;
fetch_width = fetch_width0;
EXEC SQL PREPARE :stat FROM :query_str;
EXEC SQL DECLARE QCURSOR CURSOR FOR :stat
FOR READ ONLY;
EXEC SQL OPEN QCURSOR;
sqlda = PrepareSQLDA( stat,
fetch_width,
&cols_per_row );
if( sqlda == NULL )
{
printf( "Error allocating SQLDA\n" );
return( SQLE_NO_MEMORY );
}
for( ;; )
{
EXEC SQL FETCH QCURSOR INTO DESCRIPTOR sqlda
ARRAY :fetch_width;
if( SQLCODE != SQLE_NOERROR ) break;
PrintFetchedRows( sqlda, cols_per_row );
}
EXEC SQL CLOSE QCURSOR;
EXEC SQL DROP STATEMENT :stat;
free_filled_sqlda( sqlda );
err:
return( SQLCODE );
}
void main( int argc, char *argv[] )
{
/* Optional first argument is a select statement,
* optional second argument is the fetch width */
char *query_str =
"select GivenName, Surname from Employees";
unsigned fetch_width = 10;
if( argc > 1 )
{
query_str = argv[1];
if( argc > 2 )
{
fetch_width = atoi( argv[2] );
if( fetch_width < 2 )
{
fetch_width = 2;
}
}
}
db_init( &sqlca );
EXEC SQL CONNECT "DBA" IDENTIFIED BY "sql";
DoQuery( query_str, fetch_width );
EXEC SQL DISCONNECT;
err:
db_fini( &sqlca );
} |
| Send feedback about this page via email or DocCommentXchange | Copyright © 2008, iAnywhere Solutions, Inc. - SQL Anywhere 11.0.0 |