Note: This is an entry from my old website. This is a piece of code that I have written in 2007. Although it is still being used in my research group, it is being used on systems with Matlab v7.0, Microsoft Visual Studio .NET 2003 and PostgreSQL v8.1.5. I have never tried to run the program using newer versions of any of these software and make no claim or provide any guarantee that it will work as provided or at all. Please use caution in using this code.
A few months ago I was assigned an interesting problem of providing connectivity between PostgreSQL and Matlab. After some research I found that the best way would be the Matlab Database Toolbox. The problem with that solution was that we need to provide the connectivity for about 10 computers and the toolbox was priced at about USD 125. So purchasing per seat or even 10 floating licenses would mean spending around USD 1000 (With any academic volume discounts available, I guess). So I set out to find alternatives and after some more searching around I found that writing a MEX function is the best way to include C (or Fortran for that matter) code into Matlab. I was already using libpq to connect to this database in my C/C++ programs, so I ventured to try if MEX function could be written to do the job.
The setup
The setup consisted of 10 systems. The database server is a Linux machine running Ubuntu Dapper Drake, and all the clients are Windows machines running Windows XP with Matlab 7.0 and Microsoft Visual Studio .NET 2003 installed on them.
Introduction to MEX files
MEX stands for Matlab EXecutable. MEX provides a way to call C or Fortran functions from within Matlab. A very good resource (probably the best) on MEX function is [1].
A MEX file has two main components:
- #include “mex.h”
Every C/C++ MEX-file must include mex.h. This is necessary to use the mx* and mex* routines. - mexFunction gateway
C/C++ MEX functions do not have a main() function, instead the entry point is the mexFunction(). It’s syntax is always
void mexFunction(int nlhs, mxArray *plhs[], int nrhs, const mxArray *phrs[]){ }
where,
mexFunction – Name of the entry point to the program (same for every MEX-file, like “main” in C/C++)
nlhs – Number of output mxArrays (Left Hand Side)
plhs – Array of poitns to expected output mxArrays
nrhs – Number of input mxArrays (Right Hand Side)
prhs – Array of pointers to input mxArrays. The input data is read-only and should not be altered by the mexFunction.
Apart from these two essential things you can use all the mx* and mex* functions and include all the normal include files from C/C++ or your special libraries. A more detailed discussion can be found at [1].
Introduction to libpq
libpq is the C API to PostgreSQL and consists of a set of library functions that allow client programs to pass queries to a PostgreSQL backend server and to receive the result of these queries.
An introduction to libpq can be found at [2].
Setting up the PostgreSQL Database Server
I setup a PostgreSQL server on Ubuntu Dapper Drake. The simplest way to install the database server is through apt-get.
sudo apt-get install postgresql-8.1
This would install PostgreSQL-8.1, creates a user ‘postgres’ and a database ‘postgres’. For this example, I would be using the default database user and database.
For more information on installing from source, creating new databases, creating and adding new users to the database visit [3].
C Program for checking libpq
To check the functionality offered by libpq and to check whether the database server and libpq are working as expected, I wrote a C program and used MSVS 2003 to compile it. For compiling the program you need ‘libpq-fe.h’, ‘libpq.dll’ and other associated dlls. For that I downloaded the binary version of PostgreSQL for windows from [4] and added the required directories to VC++ path.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 | #include <stdio.h> #include <stdlib.h> #include <string.h> #include <stdarg.h> #include "libpq-fe.h" /* This is required for using the goodies from libpq */ PGconn *psql; /* The actual connection variable */ int main (int argc, char **argv) { int i, j; PGresult *result; /*Variable to store the result from the database query */ /* Initialize the connection */ /* Makes connection to the host with specified IP address as user postgres and connects to the database postgres */ psql = PQconnectdb("hostaddr='192.168.100.25' dbname='postgres' user = 'postgres'"); if(!psql) { /* Check whether the connection went fine or not */ fprintf(stderr, "libpq error: PQconnectdb returned NULL.\n\n"); exit(1); } if(PQstatus(psql) != CONNECTION_OK) { /* Check the status of the connection */ fprintf(stderr, "libpq error : PQstatus(psql) != CONNECTION_OK \n\n"); exit(2); } result = PQexec (psql, "SELECT * FROM test;"); /* PQexec queries the server psql with the query that is passed to it */ if(!result || !(j=PQntuples(result))) { /* Check if there is a result from the query. If yes then store the number of rows returned */ fprintf(stderr, "libpq error: no rows returned or bad result set \n\n"); PQfinish(psql); /* If no result was returned, close the connection */ exit(3); } for(i=0; i< j; i++) { printf("Name: %s\n", PQgetvalue(result, i,0)); /*PQgetvalue returns the value at the given index from the result */ printf("ID: %s\n", PQgetvalue(result, i, 1)); } PQclear(result); /* Clear the result - Always a good practise */ PQfinish(psql); /* Close the connection */ } |
Now compile and execute this program. If everything was done right, you will see the contents of the table ‘test’ from the database ‘postgres’.
Since we now know that libpq setup is working properly, we can proceed to the mex function.
The MEX function
Programming MEX functions is actually easy one you are through with a few examples. You will realize that the time taken to setup the Matlab environment is much less than the time required to setup a Visual Studio project.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 | #include <stdio.h> #include <stdlib.h> #include <string.h> #include <stdarg.h> #include "libpq-fe.h" /* Include this for the goodies from libpq */ #include "mex.h" /* Include this for MEX stuff */ #include "matrix.h" #define MAXERROR 500 //Size of the maximum error string /* * There is no main function in the MEX files. The entry point is instead the mexFunction. The * mexFunction takes four parameters: * nlhs - An int variable which holds the number of left hand side variables (vararg_out) in the * MATLAB function call * plhs - A mxArray pointer which holds the left hand side output values (output_args) in the * MATLAB function call * nrhs - An int variable which holds the number of right hand side variables (vararg_in) in the * MATLAB function call * prhs - A mxArray pointer which holds the right hand side input values (input_args) in the * MATLAB function call */ void mexFunction(int nlhs, mxArray *plhs[], int nrhs, const mxArray *prhs[]){ PGconn *psql; // The actual connection variable PGresult *result; // A Variable to hold the result char colBuffer[33]; int i, j, tuples, fields, buflen, status; int *dims = mxCalloc(1, sizeof(int)); //Use mxCalloc to allocate memory in mex functions char *errMsg = mxCalloc(MAXERROR, sizeof(char)); char *conn, *quer; int position = 1; mxArray *outArray; //An array to store the output values from pq_query function if(nrhs !=2) //Check for the number of inputs - This program requires two inputs - connectioninfo and query mexErrMsgTxt("Two Input Arguments Required."); else if(nlhs >1) //Check for the number of outputs - This program outputs only one output mexErrMsgTxt("Too many output arguments."); if((mxIsChar(prhs[0]) != 1) && (mxIsChar(prhs[1]) != 1)) //Check the type of the inputs mexErrMsgTxt("Inputs must be string."); // Inputs must be Strings //Calculates the length of the first rhs argument buflen = (mxGetM(prhs[0])*mxGetN(prhs[0])) + 1; conn = mxCalloc(buflen, sizeof(char)); // Allocate memory for char* conn status = mxGetString(prhs[0], conn, buflen); //Store the value of first rhs argument into conn if(status !=0) //Check whether successful mexWarnMsgTxt("Not Enough memory. String 'conn' is truncated."); buflen = (mxGetM(prhs[1])* mxGetN(prhs[1])) + 1; //Calculates the length of the second rhs argument quer = mxCalloc(buflen, sizeof(char)); //Allocate memory for char *quer status = mxGetString(prhs[1], quer, buflen); //Store the value of the second rhs argument into quer if(status !=0) //Check whether successful mexWarnMsgTxt("Not Enough Memory. String 'quer' is truncated."); /* Initiate the connection */ psql = PQconnectdb(conn); if(!psql){ //Check whether the connection went fine or not mexErrMsgTxt("libpq error: PQconnectdb returned null\n\n"); } if(PQstatus(psql) != CONNECTION_OK){ //Check the connection status mexErrMsgTxt("libpq error: PQstatus(psql) != CONNECTION_OK\n\n"); } result = PQexec (psql,quer); // Use PQexec to query the database /* check for null rows - if yes */ if(!result || !(tuples = PQntuples(result)) || !(fields = PQnfields(result))){ errMsg = PQerrorMessage(psql); PQfinish(psql); mexPrintf("%s", errMsg); mexErrMsgTxt("libpq error: No rows returned or bad result set\n\n"); } dims[0] = tuples * fields + 1; outArray = mxCreateCellArray(1, dims); itoa(fields, colBuffer, 10); mxSetCell(outArray,0, mxCreateString(colBuffer)); for(i=0; i < tuples; i++) for(j=0; j < fields; j++) /* copy the contents of Result in to mxArray outArray */ mxSetCell(outArray, position++, mxCreateString(PQgetvalue(result, i, j))); plhs[0] = outArray; /*Assign the outArray to the left hand side argument in the MATLABfunction call */ return; } |
Save this program as, say libpqmex.c
To compile the program at Matlab command prompt type
mex libpqmex.c libpq.lib
If a C compiler is not already selected, you will be presented with the various compilers available on your system. I selected MS VC++ .net 2003. Select the compiler you want. This will compile your program and link it to libpq.lib and create libpqmex.mex32 (previous versions of Matlab used to create a dll, say libpqmex.dll). Now you can call the MEX function as you would all any other Matlab function
connInfo = ['hostaddr=''192.168.100.25'' dbname=''postgres'' user=''postgres'''];
In the above line all are single quotes. this will save the string into connInfo.
queryString = ['SELECT * FROM test'];
This will save your query into queryString. This can be modified into any query you want. Now call the function.
result = libpqmex(connInfo, queryString);
This should save the output from the database into the variable result.
Now the MEX function returns the result in one single vector so I wrote a .m file to convert this into a cell array containing the result in the form of a table. Although this can be done in the MEX function itself, I decided to keep things simple.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 | function [queryResult] = getFromdb(hostname, database, username, query) % Sample Function call - result = querydb('192.168.100.25', 'postgres', 'postgres', 'SELECT * from test2'); % hostname = '192.168.100.25'; % database = 'postgres'; % username = 'postgres'; % Use the information provided by the user to create a connection string connInfo = ['host = ''' hostname '''' ' dbname = ''' database '''' ' user = ''' username '''']; % Get the result. The result will be a single column array containing % all the values returned from the query. resultFromdb = getFromdbmex(connInfo, query); numResultCols = str2num(resultFromdb{1}); queryResult = {}; % Convert the result to an cell Matrix with rows and columns for i = 1:numResultCols queryResult(:,i) = resultFromdb(i+1:numResultCols:end); end |
That concludes this article.
References