MySQL :: MySQL 8.4 C API Developer Guide :: 6.4.12 mysql_stmt_fetch() (original) (raw)

6.4.12 mysql_stmt_fetch()

Description

mysql_stmt_fetch() returns the next row in the result set. It can be called only while the result set exists; that is, after a call tomysql_stmt_execute() for a statement such as SELECT that produces a result set.

mysql_stmt_fetch() returns row data using the buffers bound bymysql_stmt_bind_result(). It returns the data in those buffers for all the columns in the current row set and the lengths are returned to thelength pointer. All columns must be bound by the application before it callsmysql_stmt_fetch().

mysql_stmt_fetch() typically occurs within a loop, to ensure that all result set rows are fetched. For example:

int status;

while (1)
{
  status = mysql_stmt_fetch(stmt);

  if (status == 1 || status == MYSQL_NO_DATA)
    break;

  /* handle current row here */
}

/* if desired, handle status == 1 case and display error here */

By default, result sets are fetched unbuffered a row at a time from the server. To buffer the entire result set on the client, callmysql_stmt_store_result() after binding the data buffers and before callingmysql_stmt_fetch().

If a fetched data value is a NULL value, the *is_null value of the correspondingMYSQL_BIND structure contains TRUE (1). Otherwise, the data and its length are returned in the*buffer and *length elements based on the buffer type specified by the application. Each numeric and temporal type has a fixed length, as listed in the following table. The length of the string types depends on the length of the actual data value, as indicated by data_length.

In some cases, you might want to determine the length of a column value before fetching it withmysql_stmt_fetch(). For example, the value might be a long string orBLOB value for which you want to know how much space must be allocated. To accomplish this, use one of these strategies:

• Before invokingmysql_stmt_fetch() to retrieve individual rows, passSTMT_ATTR_UPDATE_MAX_LENGTH tomysql_stmt_attr_set(), then invokemysql_stmt_store_result() to buffer the entire result on the client side. Setting the STMT_ATTR_UPDATE_MAX_LENGTH attribute causes the maximal length of column values to be indicated by the max_length member of the result set metadata returned bymysql_stmt_result_metadata().
• Invoke mysql_stmt_fetch() with a zero-length buffer for the column in question and a pointer in which the real length can be stored. Then use the real length withmysql_stmt_fetch_column().

real_length= 0;  
bind[0].buffer= 0;  
bind[0].buffer_length= 0;  
bind[0].length= &real_length  
mysql_stmt_bind_result(stmt, bind);  
mysql_stmt_fetch(stmt);  
if (real_length > 0)  
{  
  data= malloc(real_length);  
  bind[0].buffer= data;  
  bind[0].buffer_length= real_length;  
  mysql_stmt_fetch_column(stmt, bind, 0, 0);  
}  

Return Values

MYSQL_DATA_TRUNCATED is returned when truncation reporting is enabled. To determine which column values were truncated when this value is returned, check theerror members of theMYSQL_BIND structures used for fetching values. Truncation reporting is enabled by default, but can be controlled by callingmysql_options() with theMYSQL_REPORT_DATA_TRUNCATION option.

Errors

• CR_COMMANDS_OUT_OF_SYNC
  Commands were executed in an improper order.
  Althoughmysql_stmt_fetch() can produce this error, it is more likely to occur for the_following_ C API call ifmysql_stmt_fetch() is not called enough times to read the entire result set (that is, enough times to returnMYSQL_NO_DATA).
• CR_OUT_OF_MEMORY
  Out of memory.
• CR_SERVER_GONE_ERROR
  The MySQL server has gone away.
• CR_SERVER_LOST
  The connection to the server was lost during the query.
• CR_UNKNOWN_ERROR
  An unknown error occurred.
• CR_UNSUPPORTED_PARAM_TYPE
  The buffer type is MYSQL_TYPE_DATE,MYSQL_TYPE_TIME,MYSQL_TYPE_DATETIME, orMYSQL_TYPE_TIMESTAMP, but the data type is not DATE,TIME,DATETIME, orTIMESTAMP.
• All other unsupported conversion errors are returned frommysql_stmt_bind_result().

Example

The following example demonstrates how to fetch data from a table usingmysql_stmt_result_metadata(),mysql_stmt_bind_result(), andmysql_stmt_fetch(). (This example expects to retrieve the two rows inserted by the example shown in Section 6.4.11, “mysql_stmt_execute()”.) Themysql variable is assumed to be a valid connection handler.

#define STRING_SIZE 50

#define SELECT_SAMPLE "SELECT col1, col2, col3, col4 \
                       FROM test_table"

MYSQL_STMT    *stmt;
MYSQL_BIND    bind[4];
MYSQL_RES     *prepare_meta_result;
MYSQL_TIME    ts;
unsigned long length[4];
int           param_count, column_count, row_count;
short         small_data;
int           int_data;
char          str_data[STRING_SIZE];
bool          is_null[4];
bool          error[4];

/* Prepare a SELECT query to fetch data from test_table */
stmt = mysql_stmt_init(mysql);
if (!stmt)
{
  fprintf(stderr, " mysql_stmt_init(), out of memory\n");
  exit(0);
}
if (mysql_stmt_prepare(stmt, SELECT_SAMPLE, strlen(SELECT_SAMPLE)))
{
  fprintf(stderr, " mysql_stmt_prepare(), SELECT failed\n");
  fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
  exit(0);
}
fprintf(stdout, " prepare, SELECT successful\n");

/* Get the parameter count from the statement */
param_count= mysql_stmt_param_count(stmt);
fprintf(stdout, " total parameters in SELECT: %d\n", param_count);

if (param_count != 0) /* validate parameter count */
{
  fprintf(stderr, " invalid parameter count returned by MySQL\n");
  exit(0);
}

/* Execute the SELECT query */
if (mysql_stmt_execute(stmt))
{
  fprintf(stderr, " mysql_stmt_execute(), failed\n");
  fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
  exit(0);
}

/* Fetch result set meta information */
prepare_meta_result = mysql_stmt_result_metadata(stmt);
if (!prepare_meta_result)
{
  fprintf(stderr,
         " mysql_stmt_result_metadata(), \
           returned no meta information\n");
  fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
  exit(0);
}

/* Get total columns in the query */
column_count= mysql_num_fields(prepare_meta_result);
fprintf(stdout,
        " total columns in SELECT statement: %d\n",
        column_count);

if (column_count != 4) /* validate column count */
{
  fprintf(stderr, " invalid column count returned by MySQL\n");
  exit(0);
}

/* Bind the result buffers for all 4 columns before fetching them */

memset(bind, 0, sizeof(bind));

/* INTEGER COLUMN */
bind[0].buffer_type= MYSQL_TYPE_LONG;
bind[0].buffer= (char *)&int_data;
bind[0].is_null= &is_null[0];
bind[0].length= &length[0];
bind[0].error= &error[0];

/* STRING COLUMN */
bind[1].buffer_type= MYSQL_TYPE_STRING;
bind[1].buffer= (char *)str_data;
bind[1].buffer_length= STRING_SIZE;
bind[1].is_null= &is_null[1];
bind[1].length= &length[1];
bind[1].error= &error[1];

/* SMALLINT COLUMN */
bind[2].buffer_type= MYSQL_TYPE_SHORT;
bind[2].buffer= (char *)&small_data;
bind[2].is_null= &is_null[2];
bind[2].length= &length[2];
bind[2].error= &error[2];

/* TIMESTAMP COLUMN */
bind[3].buffer_type= MYSQL_TYPE_TIMESTAMP;
bind[3].buffer= (char *)&ts;
bind[3].is_null= &is_null[3];
bind[3].length= &length[3];
bind[3].error= &error[3];

/* Bind the result buffers */
if (mysql_stmt_bind_result(stmt, bind))
{
  fprintf(stderr, " mysql_stmt_bind_result() failed\n");
  fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
  exit(0);
}

/* Now buffer all results to client (optional step) */
if (mysql_stmt_store_result(stmt))
{
  fprintf(stderr, " mysql_stmt_store_result() failed\n");
  fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
  exit(0);
}

/* Fetch all rows */
row_count= 0;
fprintf(stdout, "Fetching results ...\n");
while (!mysql_stmt_fetch(stmt))
{
  row_count++;
  fprintf(stdout, "  row %d\n", row_count);

  /* column 1 */
  fprintf(stdout, "   column1 (integer)  : ");
  if (is_null[0])
    fprintf(stdout, " NULL\n");
  else
    fprintf(stdout, " %d(%ld)\n", int_data, length[0]);

  /* column 2 */
  fprintf(stdout, "   column2 (string)   : ");
  if (is_null[1])
    fprintf(stdout, " NULL\n");
  else
    fprintf(stdout, " %s(%ld)\n", str_data, length[1]);

  /* column 3 */
  fprintf(stdout, "   column3 (smallint) : ");
  if (is_null[2])
    fprintf(stdout, " NULL\n");
  else
    fprintf(stdout, " %d(%ld)\n", small_data, length[2]);

  /* column 4 */
  fprintf(stdout, "   column4 (timestamp): ");
  if (is_null[3])
    fprintf(stdout, " NULL\n");
  else
    fprintf(stdout, " %04d-%02d-%02d %02d:%02d:%02d (%ld)\n",
                     ts.year, ts.month, ts.day,
                     ts.hour, ts.minute, ts.second,
                     length[3]);
  fprintf(stdout, "\n");
}

/* Validate rows fetched */
fprintf(stdout, " total rows fetched: %d\n", row_count);
if (row_count != 2)
{
  fprintf(stderr, " MySQL failed to return all rows\n");
  exit(0);
}

/* Free the prepared result metadata */
mysql_free_result(prepare_meta_result);


/* Close the statement */
if (mysql_stmt_close(stmt))
{
  /* mysql_stmt_close() invalidates stmt, so call          */
  /* mysql_error(mysql) rather than mysql_stmt_error(stmt) */
  fprintf(stderr, " failed while closing the statement\n");
  fprintf(stderr, " %s\n", mysql_error(mysql));
  exit(0);
}