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:
- my first foray in .oct file creation, C++, and APIs
- 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
