MATLAB mex file for PostgreSQL - Mailing list pgsql-hackers

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);


}