Programmers toolkit - C api sprintf-like mysql_query()

Printer-friendly version

I've recently taken up writing some C code to access mysql databases. One tedious part of that process is the preparation of query statements (not "prepared statement" queries, however) using program-supplied values.

Typically, to me, that means

  1. computing (or guessing) the size of the final SQL statement,
  2. calling malloc() to allocate a temporary buffer of that computed size,
  3. calling sprintf() on a query string like "INSERT name, number VALUES ('%s',%d) INTO table" to plug in the program-supplied values and populate the final SQL statement into the buffer, then
  4. executing mysql_query() on the resulting string to actually perform the query, and finally
  5. calling free() to release the temporary buffer.

Yes, I know that I could use a "prepared statement", but for the code I'm writing, that would be overkill. However, the malloc()/sprintf()/mysql_query()/free() sequence occurs often enough (and there doesn't seem to be an equivalent mysql_*() API) that I encapsulated it into a single function.

So, for your enjoyment, edification, and use, I give you

formatted_mysql_query()
/*
** this code is released into the public domain
** Lew Pitcher (lew.pitcher@digitalfreehold.ca) 2017-07-02
*/

#include <mysql/my_global.h>
#include <mysql/mysql.h>

#include <stdio.h>
#include <stdlib.h>
#include <stdarg.h>

int formatted_mysql_query(MYSQL *dbm_handle, char *fquery, ...)
{
  int retcode = 0;

  va_list       ap,     /* our va_list */
                pp;     /* vsnprintf va_list */
  size_t        querysize;
  char          dummy[1],
                *query = NULL;

  va_start(ap,fquery);

  va_copy(pp,ap);
  querysize = 2 + vsnprintf(dummy,sizeof(dummy),fquery,pp);

  if (query = malloc(querysize))
  {
    va_copy(pp,ap);
    vsnprintf(query,querysize,fquery,pp);

    retcode = mysql_query(dbm_handle,query);
    free(query);
  }
  else retcode = -1;            /* NOT a mysql error code */

  va_end(ap);

  return retcode;
}


formatted_mysql_query() inserts user-specified values into an SQL query, then executes the resulting query via mysql_query()

Accepts
a MYSQL * pointer to mysql dbm handle
a char * pointer to a sprintf-formatted query string, and
other arguments as required to satisfy the the query string formatting characters
Updates
the MYSQL * dbm_handle with query results (on success), and
database tables as required
Returns
(int) -1 to indicate a malloc() failure,
(int) 0 to indicate mysql_query() success, or
or some other integer value, which indicates a mysql_query() error
Example usage
  MYSQL *dbm_handle;
  int rc;

  char *name = "the name"
  int number = 6;

  rc = formatted_mysql_query(dbm_handle,
                             "INSERT name, number VALUES ('%s',%d) INTO table",
                             name, number);
  switch (rc)
  {
    case -1:
      puts("query failed - malloc() failure");
      break;
    case 0:
      puts("query succeeded")
      break;
    default:
      printf("query failed - mysql failure %d",rc);
      break;
  }

AttachmentSize
File formatted_mysql_query.c677 bytes
Articles: