Gammon Forum

#include <mysql/mysql.h>

MYSQL               mysqlconn;
MYSQL_RES           *result;
MYSQL_ROW           row;

extern int          sql_working;

/* declarations */
#define MY_SERVER   "localhost"
#define MY_USER     "YOUR USER"
#define MY_PWD      "YOUR PASSWORD"
#define MY_DB       "YOUR DATABASE"
#define MY_SOCK     NULL
#define MY_PORT     3306
#define MY_FLAG     0

#define bool        int
#define TRUE        1
#define FALSE       0

void load_mysql     (void);
int MySQLQuery      (char * query);
bool open_db        (void);
void close_db       (void);
---------

Now you need the handler functions..
Make a mysql.c file and put..

(Any suggestions? Did I do this right? It seems to work..)

---------
/*
 * load_mysql()
 *
 * Opens the database, provides errors, etc
 */
void load_mysql(void)
{
    if (!open_db() )
    {
        logf("**** MYSQL ****: Could not open Database.\n");
        logf("**** MYSQL ****: Reason:\n%s\n", mysql_error(&mysqlconn));
        exit (1);
        return;
    }
    logf("**** MYSQL ****: Connection open and working.\n");
    sql_working = 1;
    return;
}

/*
 * MySQLQuery()
 *
 * Inserts a raw query to the SQL server
 * returns true if it works, false if it fails
 */
int MySQLQuery(char * query)
{
    if (mysql_real_query(&mysqlconn, query, strlen(query)))
    {
        logf("**** MYSQL ****: Error:\n%s\n", mysql_error(&mysqlconn));
        return 0;
    }
    mysql_free_result(result);
    return 1;
}
    
/*
 * open_db()
 *
 * Opens a connection to the SQL database
 */
bool open_db(void)
{
    if (!mysql_init(&mysqlconn))
    {
        logf("**** MYSQL ****: Unable to open database!\n");
        return FALSE;
    }
    if ((mysql_real_connect(&mysqlconn, MY_SERVER, MY_USER, MY_PWD, MY_DB, MY_PORT, MY_SOCK, MY_FLAG)) == NULL)
    {
        logf("**** MYSQL ****: open_db() failed during opening!\n");
        mysql_close(&mysqlconn);
        return FALSE;
    }
    return TRUE;
}

/*
 * close_db()
 *
 * Closes connection to the SQL server
 */
void close_db(void)
{
    logf("**** MYSQL ****: close_db(): closed\n");
    mysql_close(&mysqlconn);
    return;
}

sprintf( xmysql->xrowdata[sqlrow][column], "%s", row[column - 1] ); /* ... */

char xrowdata[1024][1024][1024];

char xrowdata[1024][1024];

typedef struct mysql_xdata {
    char ***xrowdata;
    int xnumrows;
    int xnumcols;
    char xmysqldebugbuf[1024];
} XMYSQL;

xmysql = (XMYSQL *) malloc(sizeof(XMYSQL));
xmysql->xrowdata = (char ***) malloc(sizeof(char **) * row_count);

/* output fields of each row */
while ( ( row = mysql_fetch_row( res ) ) != NULL )
{
    column = 0;
    xmysql->xrowdata[sqlrow] = (char **) malloc(sizeof(char *) * res->field_count);
    while ( column < res->field_count )
    {
        xmysql->xrowdata[sqlrow][column] = (char *) malloc(sizeof(char) * (1 + strlen(row[column])));
        strcpy(xmysql->xrowdata[sqlrow][column], row[column]);
        column++;
        totalcols++;
    }
    sqlrow++;
}

struct mysql_xrow;
typedef struct mysql_xrow {
    struct mysql_xrow *next;
    struct mysql_xrow *prev;
    char **xdata;
}

typedef struct mysql_xdata {
    struct mysql_xrow *xrowdata_head;
    struct mysql_xrow *xrowdata_tail;
    int xnumrows;
    int xnumcols;
    char xmysqldebugbuf[1024];
} XMYSQL;

xmysql = (XMYSQL *) malloc(sizeof(XMYSQL));
xmysql->xrowdata_head = NULL;
xmysql->xrowdata_tail = NULL;

/* output fields of each row */
while ( ( row = mysql_fetch_row( res ) ) != NULL )
{
    struct mysql_xrow *xrow;
    column = 0;
    xrow = (struct mysql_xrow *) malloc(sizeof(struct mysql_xrow));
    xrow->xdata = (char **) malloc(sizeof(char *) * res->field_count);
    while ( column < res->field_count )
    {
        xrow->xdata[column] = (char *) malloc(sizeof(char) * (1 + strlen(row[column])));
        strcpy(xrow->xdata[column], row[column]);
        column++;
        totalcols++;
    }
    sqlrow++;
    if (NULL == xmysql->xrowdata_tail) {
        xmysql->xrowdata_head = xmysql->xrowdata_tail = xrow
        xrow->next = xrow->prev = NULL;
        continue;
    }
    xrow->prev = xmysql->xrowdata_tail;
    xrow->prev->next = xrow;
    xrow->next = NULL;
    xmysql->xrowdata_tail = xrow;
}

struct mysql_xrow /* There shouldn't be a typedef on this line. */
{
struct mysql_xrow *next;
struct mysql_xrow *prev;
char **xdata;
}; /* and there should be a semicolon here :-/ */

void free_xmysql(XMYSQL *xmysql)
{
    struct mysql_xrow *xrow = NULL;
    short col = 0;
    if (NULL == xmysql)
        return;
    while (NULL != xmysql->xrowdata_tail)
    {
        xrow = xmysql->xrowdata_tail;
        xmysql->xrowdata_tail = xrow->prev;
        xrow->prev->next = NULL;
        xrow->prev = NULL;
        col = xmysql->xnumcols;
        while (0 < col)
        {
            free(xrow->xdata[--col]);
            xrow->xdata[col] = NULL; /* redundant, but good practice */
        }
        free(xrow->xdata);
        xrow->xdata = NULL; /* redundant, but good practice */
    }
    /* if tail is NULL, then there are no entries in the list so head is invalid, and should be NULL. */
    xmysql->xrowdata_head = NULL;
    free(xmysql);
    return;
}