Connecting MySQL to GNU Octave in Ubuntu

In my little world, this is, officially, A Very Big Deal (tm).  
MySQL features a rich API.  GNU Octave has a database package. 
Unfortunately I don't know API from DPI, and Octave's db package pretty much doesn't work for anybody (including me), according to my google results.


So I wrote my own.. it was:

1. my first foray in .oct file creation, C++, and APIs
2. successful!  thanks largely to the extensive help available all over the internet

Regardless, I'm disproportionately pleased with myself.  


Note you'll need both octave and MySQL header files, and my goal was to get a .oct that could retrieve data FROM the database, which is different than stuffing data TO the database.


The code below takes in four strings, queries MySQL, and if the query was successful, returns a cell variable to octave.

/*
 * SQL2m.cpp
 *
 *  Created on: Nov 5, 2011
 *      Author: nzvyyx
 */


#include "/usr/include/mysql/mysql.h"
#include "octave/oct.h"
#include "octave/Cell.h"
#include <string>
using namespace std;


#define NUM_INPUTS_REQUIRED (4) // 0 is file name, 1: user name, 2: password, 3: database name, 4: SQL query


#define  NoError 0
#define  Error_NoInputs (NoError + 1)
#define  Error_SQL_QueryError (Error_NoInputs + 1)
#define  Error_UnexpectedTermination (Error_SQL_QueryError + 1)
#define  Error_UserNameNotAString (Error_UnexpectedTermination + 1)
#define  Error_UserPasswordNotAString (Error_UserNameNotAString + 1)
#define  Error_DatabaseNotAString (Error_UserPasswordNotAString + 1)
#define  Error_SQL_Query_NotAString (Error_DatabaseNotAString + 1)
#define  Error_NoOutputs (Error_SQL_Query_NotAString + 1)
#define  Error_SQL_CnxnFailed (Error_NoOutputs + 1)


#define  *ErrorMsgs[] = {
    ">> SUCCESS <<",
    "not enough inputs, expected (name, password, db_name, SQL_query)",
    "SQL query error",
    "unexpected program exit",
    "non-character user name",
    "non-character user password",
    "non-character database name",
    "non-character SQL query",
    "no output assigned",
    "SQL failed to connect",
    NULL
};


char *server = "localhost";
char *username = NULL;
char *password = NULL;
char *database = NULL;
char *SQL_query = NULL;
string str;
MYSQL *conn;
MYSQL_RES *res;
MYSQL_ROW row;
unsigned long long totalrows, numfields;
int _n;
octave_idx_type _row, _col;
octave_value_list  retval;


DEFUN_DLD (SQL2m, args, nargout,
        "[SQL_result] = SQL2m(database, user_name, user_password, SQL_query)")
{
  if (nargout < 1)
  {
    retval(0) = ErrorMsgs[Error_NoOutputs];
    return retval;
  }
  if (args.length() < NUM_INPUTS_REQUIRED)
  {
    retval(0) = ErrorMsgs[Error_NoInputs];
    return retval;
  }
  for (_n = 0; _n < NUM_INPUTS_REQUIRED; _n++)
  {
    if (!(args(_n).is_string()))
    {
      retval(0) = ErrorMsgs[Error_UserNameNotAString + _n];
      return retval;
    }
  }


  str = args(0).string_value();
  username = new char[str.size() + 1];
  strcpy(username, str.c_str());
  str = args(1).string_value();
  password = new char[str.size() + 1];
  strcpy(password, str.c_str());
  str = args(2).string_value();
  database = new char[str.size() + 1];
  strcpy(database, str.c_str());
  str = args(3).string_value();
  SQL_query = new char[str.size() + 1];
  strcpy(SQL_query, str.c_str());


  conn = mysql_init(NULL);


  if (NULL == mysql_real_connect(conn, server,
      username, password, database, 0, NULL, 0))
  {
    retval(0) = ErrorMsgs[Error_SQL_CnxnFailed];
    return retval;
  }


  if (0 != mysql_query(conn, SQL_query))
  {
    retval(0) = ErrorMsgs[Error_SQL_QueryError];
    return retval;
  }


  res = mysql_store_result(conn);


  totalrows = mysql_num_rows(res);
  numfields = mysql_num_fields(res);
  Cell DATA(totalrows, numfields);


  _row = 0;
  while (row = mysql_fetch_row(res))
  {
    for (_col = 0; _col < numfields; _col++)
    {
      DATA(_row, _col) = octave_value(row[_col]);
    }
    _row++;
  }


  mysql_free_result(res);
  mysql_close(conn);


  return octave_value(DATA);
}

Here's a simple octave .m file to build the .oct file  & test for  results.

You'll need to a MySQL running, and the dummy placeholders for user name, password, etc need functional values from you.

% BuildAndTestSQL2m.m

clear all; clc; 

% build required MySQL API:

% $ sudo apt-get install libmysqlclient-dev

%

% directories given by command line:

% $ mysql_config --cflags

% and

% $ mysql_config --libs

mkoctfile  -I/usr/include/mysql  -L/usr/lib/mysql -lmysqlclient  SQL2m.cpp 

disp("change this script to specify your username, password, database name and SQL query !")

username = 'user';   

password = 'password';

dbname = 'dbname';

SQL_query = 'select field1, field2 from table';

[status, output] = system('rm SQL2m_tests.txt');

diary SQL2m_tests.txt

help SQL2m

% test with combinations of insufficient argument counts

SQL2m()

results = SQL2m()

results = SQL2m(username)

results = SQL2m(username, password)

results = SQL2m(username, password, dbname)

% this should return a cell array - success!

results = SQL2m(username, password, dbname, SQL_query)

diary off