I've written a little C mex file for MATLAB to interface to the my
PostgreSQL database. Currently, I have a compiled version for SGI IRIX
6.5.4, but I think this should be simple enough to port to any machine.
Currently, the mex file just brings data back as ASCII. I hope to
eventually add some ability for binary cursors and possibly even
lower-level functions like PQexec and PQntuples. But considering my list
of things to do that may take a while.
I've tried to compile it for Linux, however MATLAB was compiled on Linux
4.2 and so the mex compiler won't work properly under my RH 6.0. There
is a workaround by getting the version 5.0 C libraries for Linux and
compiling it with them. However, I am hoping that the Mathworks will
eventually compile the program on 6.0 so that I don't have to do the
workaround.
I hope people get some use out of it. Let me know if you find ways to
improve it. I think it would be a nice little interface to add to the
PostgreSQL distribution someday.
-Tony
/* psql.c
* MATLAB mex file which reads from the PostgreSQL database
*
* Usage: [a, b, c, ...] = psql(database_name_string, query_string);
*
* Example: [monkey, arm, cell] = psql('db01', 'select distinct monkey, arm, cell from cell');
*
* Tony Reina
* Motor Control Lab
* The Neurosciences Institute
* San Diego, CA
*
* Created: 1 Oct 1999
* Last Update: 1 Oct 1999 GAR
* To compile use the CMEX compiler:
* cmex mat_psql.c $SQL -output psql.mexsg
*/
#include <strings.h>
#include "mex.h"
#include "libpq-fe.h"
void mexFunction( int nlhs,
mxArray *plhs[],
int nrhs,
const mxArray *prhs[] )
{
char *db_name, *query_string;
char *buffer;
char *output_string[8192];
unsigned int buffer_length;
int i, j, status, number_of_tuples, number_of_fields;
/* SQL database variables */
/* ---------------------- */
char *pghost, *pgport, *pgoptions, *pgtty;
PGconn *conn;
PGresult *res;
/* Verify that there are 11 variables passed into the function */
/* rhs = right-hand side = input variables */
if (nrhs != 2) {
mexErrMsgTxt("ERROR! I need two input arguments: database name and query string.");
}
buffer_length = mxGetM(prhs[0]) * mxGetN(prhs[0]) + 1;
db_name = mxCalloc(buffer_length, sizeof(char));
status = mxGetString(prhs[0], db_name, buffer_length);
if (status != 0)
mexErrMsgTxt("Could not perform the database query because of input error!");
buffer_length = mxGetM(prhs[1]) * mxGetN(prhs[1]) + 1;
query_string = mxCalloc(buffer_length, sizeof(char));
status = mxGetString(prhs[1], query_string, buffer_length);
if (status != 0)
mexErrMsgTxt("Could not perform the database query because of input error!");
/* Initialize database variables */
/* ============================= */
pghost = NULL; /* host name of the backend server */
pgport = NULL; /* port of the backend server */
pgoptions = NULL; /* special options to start up the backend server */
pgtty = NULL; /* debugging tty for the backend server */
/* Open a connection to the database */
/* ================================= */
conn = PQsetdb (pghost, pgport, pgoptions, pgtty, db_name);
if (PQstatus (conn) == CONNECTION_BAD)
{
printf ("Connection to database '%s' failed.\n", db_name);
printf ("%s\n", PQerrorMessage (conn));
PQfinish(conn);
mexErrMsgTxt("Could not perform the database query because of connection error!");
} /* if (PQstatus(conn) == CONNECTION_BAD) */
res = PQexec(conn, query_string);
number_of_tuples = PQntuples(res);
if (number_of_tuples == 0) {
PQclear(res);
PQfinish(conn);
mexErrMsgTxt("Could not perform the database query because no data returned for query!");
}
number_of_fields = PQnfields(res);
if (nlhs > number_of_fields) {
printf("Too many output variables for the desired query.\n"
"I will only fill the first %d variables.\n", number_of_fields);
nlhs = number_of_fields;
}
else if (nlhs < number_of_fields) {
printf("Too few output variables for the desired query.\n"
"The last %d columns (fields) in the query will be dropped.\n",
number_of_fields - nlhs);
}
/* This declares the memory space in MATLAB for the left-handed arguments:
* coefficients, work, and error. These are the output variables.
*/
for (i = 0; i < nlhs; i++) {
for (j = 0; j < number_of_tuples; j++)
output_string[j] = PQgetvalue(res, j, i);
plhs[i] = mxCreateCharMatrixFromStrings(number_of_tuples, (const char **)output_string);
}
PQclear(res);
PQfinish(conn);
}