MySQL C API Handle TEXT field

When one uses Prepared Statements in MySQL C API to handle TEXT field result, one has to specify the length of the string for an out binding:

 MYSQL_BIND    out_bind;
 char          str_data[STRING_SIZE]; 
 my_bool       is_null;
 my_bool       error;

 ....
 /* STRING COLUMN */
 out_bind.buffer_type = MYSQL开发者_高级运维_TYPE_STRING;
 out_bind.buffer = str_data;
 out_bind.buffer_length = STRING_SIZE;
 out_bind.is_null= &is_null;
 out_bind.length= &length;
 out_bind.error= &error;

 mysql_stmt_bind_result(statement, out_bind)

In the given example STRING_SIZE is the known constant, but how to be with TEXT fields where data length can vary from small sizes to megabytes?

Is there standard approaches for this?

The manual page for mysql_stmt_fetch says:

In some cases you might want to determine the length of a column value before fetching it with mysql_stmt_fetch(). ... To accomplish this, you can use these strategies:

• Before invoking mysql_stmt_fetch() to retrieve individual rows, pass STMT_ATTR_UPDATE_MAX_LENGTH to mysql_stmt_attr_set(), then invoke mysql_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 by mysql_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 with mysql_stmt_fetch_column().

You might also like to read the manual page for mysql_stmt_bind_result

I had the same issue. I have solved this problem like pmg saids in first point, using STMT_ATTR_UPDATE_MAX_LENGTH setting, here is my code :

MYSQL_STMT    *stmt;
MYSQL_BIND    bind[1];
MYSQL_BIND    bind_result[1];

// _con your mysql connection 
stmt = mysql_stmt_init(_con);
if (!stmt)
{
  fprintf(stderr, " mysql_stmt_init(), out of memory\n");
  exit(0);
}

char* aQuery = (char*) "'your query'";
if (mysql_stmt_prepare(stmt, aQuery, strlen(aQuery)))
{
  fprintf(stderr, " mysql_stmt_prepare(), INSERT failed\n");
  fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
  exit(0);
}

    // Here fill binded parameters (here a string)
memset(bind, 0, sizeof(bind));

const char* aStr = ioType.c_str();
long unsigned int aSize = ioType.size();

bind[0].buffer_type= MYSQL_TYPE_STRING;
bind[0].buffer= (char *) aStr;
bind[0].buffer_length= 2048;
bind[0].is_null= 0;
bind[0].length= &aSize;

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

    // Reauest meta data information
MYSQL_RES* aRes = mysql_stmt_result_metadata(stmt);

    // Set STMT_ATTR_UPDATE_MAX_LENGTH attribute
my_bool aBool = 1;
mysql_stmt_attr_set(stmt, STMT_ATTR_UPDATE_MAX_LENGTH, &aBool);

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

if (mysql_stmt_store_result(stmt)) {
  fprintf(stderr, " mysql_stmt_execute(), 1 failed\n");
  fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
  exit(0);
}       

// Retrieving meta data information
MYSQL_FIELD* aField = &aRes->fields[0];

fprintf(stdout, " field %s \n",aField->name);
fprintf(stdout, " field length %d \n",(int) aField->length);
fprintf(stdout, " field max length %d \n", (int) aField->max_length);


int totalrows = mysql_stmt_num_rows(stmt);
fprintf(stdout, " fetched %d description\n",totalrows);
fprintf(stdout, " field count %d \n",(int) aRes->field_count);

long unsigned int aMaxSize;
char* aBuffer = (char*) malloc(aField->max_length);

memset (bind_result, 0, sizeof (bind_result));
bind_result[0].buffer_type= MYSQL_TYPE_BLOB;
bind_result[0].is_null= 0;
bind_result[0].buffer= (char *) aBuffer;
bind_result[0].buffer_length= aField->max_length;
bind_result[0].length= &aMaxSize;

mysql_stmt_bind_result(stmt, bind_result);

std::string aStrData;
while(!mysql_stmt_fetch(stmt))
{
    fprintf(stdout, " size %d\n", (int) aMaxSize);
    aStrData = std::string(aBuffer,aMaxSize);
    fprintf(stdout, " data %s\n", aStrData.c_str());
}

free(aBuffer);

mysql_free_result(aRes);

if (mysql_stmt_close(stmt))
{
  fprintf(stderr, " failed while closing the statement\n");
  fprintf(stderr, " %s\n", mysql_stmt_error(stmt));
  exit(0);
}

Hope this helps !