MySQL C API Handle TEXT field

Question

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?

Ugh! str_data by itself (after decaying) is a char *. Why are you converting to char *? :) — pmg
– pmg, Commented Jul 7, 2011 at 18:16
You are right. It is just a rudiment that stayed here after moving from a real code to this simplified example. Never mind. — Dmitry Romanov
– Dmitry Romanov, Commented Jul 7, 2011 at 18:26
I dont remember exactly, but you get this in return structures — Ulterior
– Ulterior, Commented Jul 7, 2011 at 18:27
Like out_bind.length in this example? ;) But you have to do the binding prior calling the statement that means that you have to allocate memory prior too. And you have to know the amount of memory to allocate. We have kind of complicated querry and do it twice would be kind of ugly solution... I believe... — Dmitry Romanov
– Dmitry Romanov, Commented Jul 7, 2011 at 18:32
How about using mysql builtin functions to return size of a field with OCTET_LENGTH() in the same query, so you can first allocate enough data and then actually read it in one pass --- select count(*) as 'documents', sum(octet_length(document_data)) as total_size, avg(octet_length(document_data)) as avg_size from DOCUMENTS; — Ulterior
– Ulterior, Commented Jul 7, 2011 at 18:42

pmg · Accepted Answer · 2011-07-07 19:02:59Z

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

JBV06 · Accepted Answer · 2014-03-19 19:30:59Z

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 !

I came upon this after a lot of trial and error as I have my code structured a bit different. I call mysql_stmt_bind_result() before mysql_stmt_store_result() as I was following examples from the MySQL manual. For example, page 2688 under mysql_stmt_fetch() has it that way but then the call to mysql_stmt_fetch() and STMT_ATTR_UPDATE_MAX_LENGTH set returns nothing, no rows at all. So I had to modify the bindings for affected field with actual size, call mysql_stmt_bind_result() again and then it worked. Weird.

Collectives™ on Stack Overflow

MySQL C API Handle TEXT field

2 Answers 2

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related