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

** this code is released into the public domain
** Lew Pitcher ( 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;


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

  if (query = malloc(querysize))

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


  return retcode;

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

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
the MYSQL * dbm_handle with query results (on success), and
database tables as required
(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");
    case 0:
      puts("query succeeded")
      printf("query failed - mysql failure %d",rc);

File formatted_mysql_query.c677 bytes