C demo programs

You can use directly the MySQL API to write C programs interacting with the server. See the MySQL reference pages. Examples are the UDFs functions described in the course.

Tutorial
MySQL C API programming tutorial

Here are two minimalist examples.
The mysql_get_client_info() below requests information about the MySQL client version. Note how no actual DB connection is performed:

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

int main(int argc, char **argv)
{
  printf("MySQL client version: %s\n", mysql_get_client_info());
  exit(0);
}

And this code performs a simple SELECT query printing the result:

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

int main(int argc, char **argv)
{
  MYSQL *con = mysql_init(NULL);
  mysql_real_connect(con, "localhost", "mpeusr", "mpe2018pass", "mpe2018db", 0, NULL, 0);
  mysql_query(con, "SELECT * FROM Messier");
  MYSQL_RES *result = mysql_store_result(con);

  int num_fields = mysql_num_fields(result);  // number of columns
  MYSQL_ROW row;
  while ((row = mysql_fetch_row(result))) {   // for each returned row
      for(int i = 0; i < num_fields; i++)
          printf("%s ", row[i] ? row[i] : "NULL");
      printf("\n");
  }

  mysql_free_result(result);
  mysql_close(con);
  exit(0);
}

For convenience, the tar file soft_dirs.tar.gz contains teh whole Soft tree structure.
If. not done yet, download it in the ~/mpe2018 directory (or another you prefer) and untar it:

shell> cd ~/mpe2018
shell> wget https://ross2.oas.inaf.it/imprs-db/soft_dirs.tar.gz
shell> tar zxvf soft_dirs.tar.gz

Here we show some simple examples making use of a custom set of wrapper functions stored in the file my_db.c (see the source code). Note that you could need to edit the Makefile to set the correct path for the CFITSIO library include and lib directories. They are assumed to be in /usr/local/cfitsio.
On Mac OS with you can install it with
shell> sudo port install cfitsio

On Linux (assuming Debian/Ubuntu):
shell> sudo apt install libcfitsio-dev

shell>
  cd Soft/C/dbIO
  make all

This is the Makefile:

CC   ?= gcc
FITSDIR = /usr/local/cfitsio

CFLAGS= -I./ -I$(FITSDIR)/include `mysql_config --cflags` -Wall -O2
LDFLAGS= `mysql_config --libs`
LIBCFITSIO= $(CFLAGS) -L$(FITSDIR)/lib -lcfitsio

EXAMPLES = getcat getcat_radec2_BSC gsc23tdb
default: getcat
all: $(EXAMPLES)

getcat: getcat.o my_db.o
getcat_radec2_BSC: getcat_radec2_BSC.o getcat_buildq_BSC.o praws_BSC.o deg_dec.o deg_ra.o my_db.o

gsc23tdb.o: gsc23tdb.c my_db.o
gsc23tdb: gsc23tdb.o my_db.o
        $(CC) -o $@ $@.o my_db.o $(LDFLAGS) -L$(FITSDIR)/lib -lcfitsio

clean:
        rm -f *.o $(EXAMPLES)

# Dependencies
getcat.o getcat_radec2_BSC.o gsc23tdb.o: my_db.h MyServer.h

Three demo programs will be compiled: getcat, getcat_radec2_BSC, gsc23tdb. The first one is just a query executor, the second is meant to perform queries specific for the BSC catalogue printing the output accordingly. Again the default connection information are read from the include file MyServer.h. Here is the getcat.c source code (reduced for convenience):

#include <stdlib.h>
#include <stdio.h>
#include <unistd.h>
#include <string.h>
#include "my_db.h"
#include "MyServer.h"  // edit this for your case

// Version ID string
static char* VERID="Ver 0.1a, 03-12-2014, LN@INAF-IASF";

int
main(int argc,char *argv[]) {
  unsigned int i, j;
  int ret;
  char *p;

  if (argc != 2) { 
    printf("%s  %s\n\n", argv[0], VERID);
    printf("Usage:   %s \"SQL_query\"\n\n", argv[0]);
    return(1);
  }

  ret = db_init(0);
  if (!ret) { printf("Can't set CONNECT_TIMEOUT for MySQL connection.");  return(1); }

  ret = db_connect(0, SERVER_IP,SERVER_USER,SERVER_PWD,SERVER_DB);
  if (!ret) { printf("DB error: %s\n", db_error(0));  return(1); }

  ret = db_query(0, argv[1]);
  if (!ret) { printf("DB error: %s\n", db_error(0));  return(1); }

  if (db_return_row(0)) { 
    for(j = 0; j < db_num_fields(0); j++)
      printf("%s\t", db_fieldname(0, j));
    
    printf("\n");
    
    for(j = 0; j < db_num_fields(0); j++) {
      p = db_fieldname(0, j);
      for(i = 0; i < strlen(p); i++)
	printf("-");
      
      printf("\t");
    }
    printf("\n");
    
    for (j = 0; j < db_num_rows(0); j++) {
      for(i = 0; i < db_num_fields(0); i++)
	printf("%s\t", db_data(0, j, i));
      
      printf("\n");
    }
  }
  else 
    printf("\n%d rows affected\n", db_num_rows(0));

  db_close(0);
  return(0);
}

Let's use them (for the moment we disregard the last one):

shell>
  ./getcat

./getcat  Ver 0.1a, 03-12-2014, LN@INAF-IASF

Usage:   ./getcat "SQL_query"
...

  ./getcat "describe ucac2_initial"
  ./getcat "SELECT COUNT(*) from ucac2_initial"
  ./getcat "select RAmas,DECmas,Amm from ucac2_initial where (htm6=32768 and DECmas > -30000)"

  ./getcat_radec2_BSC 20 -10 500
  ./getcat_radec2_BSC 140 40 500