accessing a MySQL db

4 sample C utility programs that came with MySQL

1. connect_test.c
2. select_test.c
3. insert_test.c
4. list_test.c

They came with the disclaimer below.

 /*     connect_test.c    A program to connect to a database.
  * 
  * This program is free software; you can redistribute it and/or modify
  * it under the terms of the GNU General Public License as published by
  * the Free Software Foundation; either version 2 of the License, or
  * (at your option) any later version.

  * This program is distributed in the hope that it will be useful,
  * but WITHOUT ANY WARRANTY; without even the implied warranty of
  * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
  * GNU General Public License for more details.  
  */

#include 
#include 
#include "mysql.h"

static void change_user(MYSQL *socket, const char *user, const char *password,
                                       const char *db,my_bool warning)
{
  if (mysql_change_user(socket,user,password,db) != warning)
  {
    fprintf(stderr,"Couldn't change user to: user: '%s', password: '%s', db: '%s':  Error: %s\n",
       user, password ? password : "", db ? db : "",
       mysql_error(socket));
  }
}


int main(int argc, char **argv)
{
  MYSQL *socket;

  if (!(socket=mysql_init(0)))
  {
    fprintf(stderr,"Couldn't initialize mysql struct\n");
    exit(1);
  }
  mysql_options(socket,MYSQL_READ_DEFAULT_GROUP,"connect");
  if (!mysql_real_connect(socket,NULL,NULL,NULL,NULL,0,NULL,0))
  {
    fprintf(stderr,"Couldn't connect to engine!\n%s\n",mysql_error(socket));
    perror("");
    exit(1);
  }

  if (mysql_select_db(socket,"test"))
  {
    fprintf(stderr,"Couldn't select database test: Error: %s\n",
	    mysql_error(socket));
  }

  change_user(socket,"test_user","test_user","test",0);
  change_user(socket,"test",NULL,"test",0);
  change_user(socket,"test_user",NULL,"test",1);
  change_user(socket,"test_user",NULL,NULL,1);
  change_user(socket,"test_user","test_user","mysql",1);

  mysql_close(socket);
  exit(0);
  return 0;
}

A program to select[display] data from a table.
select_test.c

 /*    select_test.c     A program to select[display] data from a table.     */

#if defined(_WIN32) || defined(_WIN64)
#include 
#endif
#include 
#include 
#include "mysql.h"

#define SELECT_QUERY "select name from test where num = %d"

int main(int argc, char **argv)
{
  int	count, num;
  MYSQL mysql,*socket;
  MYSQL_RES *res;
  char	qbuf[160];

  if (argc != 3)
  {
    fprintf(stderr,"usage : select_test  \n\n");
    exit(1);
  }

  mysql_init(&mysql);
  if (!(socket = mysql_real_connect(&mysql,NULL,0,0,argv[1],0,NULL,0)))
  {
    fprintf(stderr,"Couldn't connect to engine!\n%s\n\n",mysql_error(&mysql));
    perror("");
    exit(1);
  }

  count = 0;
  num = atoi(argv[2]);
  while (count < num)
  {
    sprintf(qbuf,SELECT_QUERY,count);
    if(mysql_query(socket,qbuf))
    {
      fprintf(stderr,"Query failed (%s)\n",mysql_error(socket));
      exit(1);
    }
    if (!(res=mysql_store_result(socket)))
    {
      fprintf(stderr,"Couldn't get result from %s\n",
	      mysql_error(socket));
      exit(1);
    }
#ifdef TEST
    printf("number of fields: %d\n",mysql_num_fields(res));
#endif
    mysql_free_result(res);
    count++;
  }
  mysql_close(socket);
  exit(0);
  return 0;      /* Keep some compilers happy */
}

A program to perform an insert into a table.
insert_test.c

 /*    insert_test.c     A program to perform an insert into a table.        */

#include 
#include 
#include "mysql.h"

#define INSERT_QUERY "insert into test (name,num) values ('item %d', %d)"

int main(int argc, char **argv)
{
  int	count,num;
  MYSQL *socket,mysql;
  char	qbuf[160];

  if (argc != 3)
  {
    fprintf(stderr,"usage : insert_test  \n\n");
    exit(1);
  }

  mysql_init(&mysql);
  if (!(socket = mysql_real_connect(&mysql,NULL,NULL,NULL,argv[1],0,NULL,0)))
  {
    fprintf(stderr,"Couldn't connect to engine!\n%s\n",mysql_error(&mysql));
    perror("");
    exit(1);
  }

  num = atoi(argv[2]);
  count = 0;
  while (count < num)
  {
    sprintf(qbuf,INSERT_QUERY,count,count);
    if(mysql_query(socket,qbuf))
    {
      fprintf(stderr,"Query failed (%s)\n",mysql_error(socket));
      exit(1);
    }
    count++;
  }
  mysql_close(socket);
  exit(0);
  return 0;
}

A program to list the tables in a database.
list_test.c

 /*     list_test.c      A program to list the tables in a database.         */

#ifdef __WIN__
#include 
#endif
#include 
#include 
#include "mysql.h"

#define SELECT_QUERY "select name from test where num = %d"


int main(int argc, char **argv)
{
  int	count, num;
  MYSQL mysql,*socket;
  MYSQL_RES *res;
  char	qbuf[160];

  if (argc != 2)
  {
    fprintf(stderr,"usage : select_test \n\n");
    exit(1);
  }

  if (!(socket = mysql_connect(&mysql,NULL,0,0)))
  {
    fprintf(stderr,"Couldn't connect to engine!\n%s\n\n",mysql_error(&mysql));
    perror("");
    exit(1);
  }

  if (mysql_select_db(socket,argv[1]) < 0)
  {
    fprintf(stderr,"Couldn't select database %s!\n%s\n",argv[1],
	    mysql_error(socket));
    exit(1);
  }

  if (!(res=mysql_list_dbs(socket,NULL)))
  {
    fprintf(stderr,"Couldn't list dbs!\n%s\n",mysql_error(socket));
    exit(1);
  }
  mysql_free_result(res);
  if (!(res=mysql_list_tables(socket,NULL)))
  {
    fprintf(stderr,"Couldn't list tables!\n%s\n",mysql_error(socket));
    exit(1);
  }
  mysql_free_result(res);

  mysql_close(socket);
  exit(0);
  return 0;
}