Programmers toolkit - C api sprintf-like mysql_query()

From: Lew Pitcher <lew.pitcher_at_digitalfreehold.ca>
Date: Sun, 02 Jul 2017 12:26:08 -0400
Message-ID: <ojb6js$oer$1_at_dont-email.me>

[Quoted] [Quoted] I've recently taken up writing some C code to access mysql databases. One [Quoted] 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 using sprintf() on a query string like   "INSERT name, number VALUES ('%s',%d) INTO table" to plug in the program-supplied values, then executing mysql_query() on the resulting string to actually perform the query.

Yes, I know that I could use a "prepared statement", but for the code I'm writing, that would be overkill. However, the sprintf()/mysql_query() 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().

• cut -------- /* ** this code is released into the public domain ** Lew Pitcher (lew.pitcher_at_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;
}

• cut --------

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

Accepts:		MYSQL *		points to mysql dbm handle
			char *		points to sprintf-formatted query
			...		arguments as required to satisfy the
					the sprintf-formatted query string

Updates:		MYSQL *		dbm_handle is updated with query
					results (on success)
			database tables as required

Returns:		(int) -1	indicating malloc() failure
			(int) 0		indicating mysql_query() success
			(int) other	indicating mysql_query() error

Example usage:
  int rc;

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

  MYSQL *dbm_handle;

  rc = formatted_mysql_query(dbm_handle,

			     "INSERT nam,num 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;

  }

I hope this makes someone's life easier.

-- 
Lew Pitcher
"In Skills, We Trust"
PGP public key available upon request

Received on Sun Jul 02 2017 - 18:26:08 CEST