Hi we needed to acces a postgres db from octave, and tried the database-1.01 package from octave-forge, and didnt like it: it is slow, creates 108 global variables, and crashes :
After installing it globally: octave 1> pkg load database octave 2> clear -a panic: Segmentation fault -- stopping myself... attempting to save variables to `octave-core'... save to `octave-core' complete http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=523085 After some digging, it seems that swig is the cause of this, and we cannot fix this. So we made our own tools (inspired by pgnumdata 1999 D.Eddelbuettel) and would like to put them in octave-forge. Currently we only read from postgresdb, we will add 'insert' very soon, as we need this too. Any comments are welcome. Alain. ------------------------- Makefile ----------------------------------- # build oct files MAKEFLAGS=s all: echo "Building pgsql oct files..." rm -f *.oct mkoctfile pg_lib.cc -lpq --output pg_lib.oct ln -s pg_lib.oct pgOpen.oct ln -s pg_lib.oct pgEnd.oct ln -s pg_lib.oct pgCell.oct ln -s pg_lib.oct pgMatrix.oct echo "done." rm -f *.o clean: rm -f *.o rm -f *.oct ------------------------ End of Makefile ------------------------------ -------------------------- pg_lib.cc ---------------------------------- /* * pg_lib.cc -- octave functions to retrieve data from Postgresql 8 * * Copyright (C) 2009 Alain Baeckeroot <alain.baecker...@laposte.net> * * This is a direct adaptation to postgres 8.3 of pgnumdata 1.1 : * pgnumdata 1.1 Copyright (C) 1999 Dirk Eddelbuettel <e...@debian.org> */ // // This program is free software; you can redistribute it and/or modify // it under the terms of the GNU General Public License as published by // the Free Software Foundation; either version 2 of the License, or // (at your option) any later version. // // This program is distributed in the hope that it will be useful, // but WITHOUT ANY WARRANTY; without even the implied warranty of // MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the // GNU General Public License for more details. // // You should have received a copy of the GNU General Public License // along with this program; if not, write to the Free Software // Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA. // // #include <octave/oct.h> #include <octave/Cell.h> #include <postgresql/libpq-fe.h> /***************************************************************************************/ /************** octave function to retrieve any data from Postgresql 8 ****************/ DEFUN_DLD(pgCell, args, nargout, \ "usage : C = pgCell( conn, select_command )\n\n\ (long int) conn is obtained by 'conn = pgOpen(...)'\n\ (string) select_command = \"select n1, n2, n3 from mytable limit 10\"\n\ returns a Cell\n\ see also pgOpen pgMatrix\n") { const char *conninfo; PGconn *conn; const char *sqlcmd; PGresult *res; long iconn; std::string tmp; octave_value_list retval; // list of return values int nargs = args.length(); // number of arguments supplied if (nargs != 2) { // if not enough arguments, show message usage("Try 'help pgCell' for info"); return retval; // and return empty } iconn = args(0).long_value(); // get the info to connect to the database conn = (PGconn *) iconn; // set the pointer to this address tmp = args(1).string_value(); // get SQL command "select numbers from ..." sqlcmd = tmp.c_str(); /* Check to see that the backend connection was successfully made */ if (PQstatus(conn) != CONNECTION_OK) { error("Connection to database failed: %s \n", PQerrorMessage(conn)); PQfinish(conn); return retval; // ... then return empty } // pass SQL argument as char*, and check if executed allright res = PQexec(conn, sqlcmd); // Check that the query was OK if ( (PQresultStatus(res) != PGRES_TUPLES_OK) && (PQresultStatus(res) != 2) ) { error("Query failed failed: %s", PQerrorMessage(conn)); PQclear(res); // avoid memory leaks return retval; // ... then return empty } long k = PQnfields(res); // we know that query was executed, long n = PQntuples(res); if (n == 0) { // but did we get any data ? error("Query returned no data"); return retval; // ... then return empty } Cell C (n, k); // now we know there's data, so let's assign it for (long i = 0; i < n; i++) // loop over all elements for (long j = 0; j < k; j++) C (i,j) = PQgetvalue(res, i, j); /* cleanup */ PQclear(res); retval(0) = C; // set the return vector and return retval; // give it back to the caller } /***************************************************************************************/ /********** octave function to retrieve *numerical* data from Postgresql 8 *************/ DEFUN_DLD(pgMatrix, args, nargout, \ "usage : X = pgMatrix( conn, select_command )\n\ (long int) conn is obtained by 'conn = pgOpen(...)'\n\ (string) select_command = \"select n1, n2, n3 from mytable limit 10\"\n\ returns a Matrix\n\ see also pgOpen pgCell\n") { const char *conninfo; PGconn *conn; const char *sqlcmd; PGresult *res; long iconn; std::string tmp; octave_value_list retval; // list of return values int nargs = args.length(); // number of arguments supplied if (nargs != 2) { // if not enough arguments, show message usage("Try 'help pgMatrix' for info"); return retval; // and return empty } iconn = args(0).long_value(); // get the info to connect to the database conn = (PGconn *) iconn; // set the pointer to this address tmp = args(1).string_value(); // get SQL command "select numbers from ..." sqlcmd = tmp.c_str(); /* Check to see that the backend connection was successfully made */ if (PQstatus(conn) != CONNECTION_OK) { error("Connection to database failed: %s \n", PQerrorMessage(conn)); PQfinish(conn); return retval; // ... then return empty } // pass SQL argument as char*, and check if executed allright res = PQexec(conn, sqlcmd); // Check that the query was OK if (PQresultStatus(res) != PGRES_TUPLES_OK) { error("Query failed : %s", PQerrorMessage(conn)); PQclear(res); // avoid memory leaks return retval; // ... then return empty } long k = PQnfields(res); // we know that query was executed, long n = PQntuples(res); if (n == 0) { // but did we get any data ? error("Query returned no data"); return retval; // ... then return empty } Matrix X(n, k); // now we know there's data, so let's assign it for (long i = 0; i < n; i++) // loop over all elements, and convert to doubles for (long j = 0; j < k; j++) sscanf(PQgetvalue(res, i, j), "%lf", &X(i, j)); /* cleanup */ PQclear(res); retval(0) = X; // set the return vector and return retval; // give it back to the caller } /***************************************************************************************/ /************** octave function to open a connection to postgres 8.3 db ****************/ DEFUN_DLD(pgOpen, args, nargout, "Returns the address of the connection pointer\n\ usage : conn = pgOpen( \"user=foo dbname=thedb password=secret ...\")\n\ see also pgEnd, pgMatrix ...\n" ) { const char *conninfo; PGconn *conn; std::string tmp; octave_value_list retval; // list of return values int nargs = args.length(); // number of arguments supplied if (nargs != 1) { // if not enough arguments, show message usage("Try 'help pgOpen' for info"); return retval; // and return empty } tmp = args(0).string_value(); // get info to connect to the database // "dbname=foodb user=foo password=secret ..." conninfo = tmp.c_str(); /* Make a connection to the database */ conn = PQconnectdb(conninfo); /* Check to see that the backend connection was successfully made */ if (PQstatus(conn) == CONNECTION_OK) { // read the address of the pointer (octave does not have pointer) retval(0) = (long) ( conn ); } else { error("Connection to database failed: %s \n", PQerrorMessage(conn)); PQfinish(conn); retval(0) = -1; } return retval; // give it back to the caller } /***************************************************************************************/ /************* octave function to close a connection with Postgresql 8.3 ***************/ DEFUN_DLD(pgEnd, args, nargout, "pgEnd( conn )\n\ Ends the connection opened by : conn = pgOpen(...)\n\ Returns error code (negative value)\n") { PGconn *conn; octave_value_list retval; // list of return values int nargs = args.length(); // number of arguments supplied if (nargs != 1) { // if not enough arguments, show message usage("Try 'help pgEnd' for info"); retval(0) = -1; return retval; // return negative err code } conn = (PGconn *) args(0).long_value(); // get info to connect to the database // either segfault or finish ! /* Close the connection to the database */ PQfinish(conn); return retval; // Return empty is ok. } /***************************************************************************************/ -----------------------------End of pg_lib.cc ------------------------------------------ ------------------------------------------------------------------------------ Register Now & Save for Velocity, the Web Performance & Operations Conference from O'Reilly Media. Velocity features a full day of expert-led, hands-on workshops and two days of sessions from industry leaders in dedicated Performance & Operations tracks. Use code vel09scf and Save an extra 15% before 5/3. http://p.sf.net/sfu/velocityconf _______________________________________________ Octave-dev mailing list Octave-dev@lists.sourceforge.net https://lists.sourceforge.net/lists/listinfo/octave-dev
