// pgnumdata --  Octave function to retrieve numerical data from Postgresql
//
// Copyright (C) 1999 Dirk Eddelbuettel <edd@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.
//
// $Id: pgnumdata.cc,v 1.1 1999/08/22 18:36:22 edd Exp $

#include <config.h>		// defines Octave configuration
#include <oct-obj.h>		// defines octave_value_list
#include <defun-dld.h>		// defines the function header

#include <libpq++.H>		// PostGreSQL; needs -lpq++

DEFUN_DLD (pgnumdata, args, nargout, 
 "X = pgnumdata( db, select_command)\n\n\
Retrieve numerical data from `db' using `select_command'\n\n\
The columns that are to be retrieved _must_ be numerical as the data is\n\
returned as an Octave matrix. The following trick can be used to convert\n\
dates with PostGresSQL.\n\n\
First, add an SQL function such as the following to your database:\n\n\
     create function date2text (date) returns text \n\
     as 'select substr(text($1),1,4) ||\n\
         substr(text($1),6,2) ||\n\
         substr(text($1),9,2)'  \n\
     language 'sql';\n
This uses the ISO DateStyle and requires that you either set the ISO DateStyle\n\
explicitly in the query as shown below, or select it in the postmaster.init file.\n\n\
Second, in Octave, a query can be executed as follows (where we explicitly specify\n\
the ISO DateStyle)\n\n\
     X = pgnumdata(\"biz\", [\"set DateStyle to 'ISO';\", \\\n\
                           \"select date2text(date), price from sales\", \\\n\
                           \"order by date\"]);\n\n\
See also: pgtextdata.\n")
{

  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 pgnumdata' for info");
    return retval;		// and return empty
  }

  string dbbase = "dbname=";	// beginning PostGreSQL connect string
  string db     = args(0).string_value(); // get name of database
  string sqlcmd = args(1).string_value(); // get SQL command

  // form `dbname=foodb' argument; then convert to char* and pass to PostGreSQL
  PgDatabase DB((dbbase+db).c_str()); 

  if ( DB.ConnectionBad() ) {	// if backend connection not successful ...
    error("Connection to database failed.\n");
    return retval;		// ... then return empty
  }

  // pass SQL argument as char*, and check if executed allright
  if ( ! DB.ExecTuplesOk(sqlcmd.c_str())) {
    error("Query failed");
    return retval;
  }

  long k = DB.Fields();		// we know that query was executed, 
  long n = DB.Tuples();		

  if (n == 0) {			// but did we get any data ?
    error("Query returned no data");
    return retval;
  }

  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(DB.GetValue(i,j), "%lf", &X(i, j));

  retval(0) = X;		// set the return vector and
  return retval;		// give it back to the caller
}

// Local Variables: 
// mode: C++ 
// compile-command: "mkoctfile pgnumdata.cc -I/usr/include/postgresql -lpq++" 
// End: