Hi all,
For those interested in the GNU Report Generator GRG,
I have extended this
utility to support the Postgres SQL database.
To get this to work, you first need to get the
latest GRG code ( e.g., The current release of GRG can be got from:
ftp://ftp.dai.ed.ac.uk/pub/daidb/grg/grg-1.43.tar.gz
or goto the home page at:
http://www.dai.ed.ac.uk/daidb/grg/
)
I have included below the postgres.c file which
does the interfacing. Unfortunately I was unable to
get the configure script to correctly work
with the new postgres file.
To get this to work, postgres.c must be included
in src/sql.c, the postgres include directory must
also be specified in the compile line, and
libpq.a must also be linked in.
Have fun,
Cheers
Anthony
--
--------------------------------------------------------
Dr Anthony Symons | Phone: +61-73-259-2365
CiTR Pty Ltd | Fax: +61-73-259-2259
John Oxley Centre |
339 Coronation Drive | Email: a.symons@citr.com.au
MILTON |
QLD 4064 AUSTRALIA | Postal: PO Box 1643
--------------------------------------------------------
/****************************************************************************/
/* @(#)ingres.sc 1.4 Copyright (C) University of Edinburgh, 12 Nov 1997.
*
* GRG - GNU Report Generator
*
* Copyright (C) University of Edinburgh, 1993-1997. All rights reserved.
* The Author, Tim Edward Colles, has exercised his right to be identified
* as such under the Copyright, Designs and Patents Act 1988.
*
* 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, 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.
*/
/*
* GRG INGRES HANDLER
*/
#include <stdio.h>
/*#include <postgres.h>*/
#include <libpq-fe.h>
/*#include <catalog/pg_type.h>*/
#include <ctype.h>
#define BOOLOID 16
#define BYTEAOID 17
#define CHAROID 18
#define NAMEOID 19
#define INT2OID 21
#define INT28OID 22
#define INT4OID 23
#define TEXTOID 25
#define OIDOID 26
#define FLOAT4OID 700
#define FLOAT8OID 701
#define UNKNOWNOID 705
#define CASHOID 790
#define BPCHAROID 1042
#define VARCHAROID 1043
#define DATEOID 1082
#define TIMEOID 1083
#define DATETIMEOID 1184
#define TIMESPANOID 1186
#define TIMESTAMPOID 1296
/*
* Run variable 'select' statement on an Ingres RDBMS and load as database
*/
int
gppSQLOpen(nm,qry,da) char *nm, *qry; dbase **da;
{
int i, j, ptr = 0;
int tuple;
dbase *db;
char fname[32];
char dbname[51];
char stmt_buffer[4096];
PGresult * qry_res = NULL;
PGconn * connection = NULL;
strncpy(dbname,texphysdb,50);
if (!strcmp(qry,"")) {
strcpy(qry,"SELECT * FROM ");
strcat(qry,nm);
}
strcpy(stmt_buffer,qry);
/* make connection */
if ( NULL == (connection = PQsetdb(NULL, /* host*/
NULL, /* pg port */
NULL, /* options */
NULL, /* pgtty */
dbname)
))
{
/* did not connect */
fprintf(stderr,"%s:%d: gppSQLOpen could not make connection to %s \n",
__FILE__,__LINE__,
dbname);
return(db_FOP);
}
if (CONNECTION_BAD == PQstatus(connection))
{
fprintf(stderr,"%s:%d: gppSQLOpen could not make connection %s\n",
__FILE__,__LINE__,
PQerrorMessage(connection));
return(db_FOP);
}
/* now execute the sql query */
if (NULL == (qry_res = PQexec(connection, stmt_buffer)))
{
fprintf(stderr,"%s:%d: gppSQLOpen got null result %s \n",
__FILE__,__LINE__,
PQerrorMessage(connection));
PQfinish(connection);
return(db_FOP);
}
/* was the query ok? */
if (!( PGRES_COMMAND_OK == PQresultStatus(qry_res)
|| PGRES_TUPLES_OK == PQresultStatus(qry_res))
)
{
fprintf(stderr,"%s:%d: gppSQLOpen query failed %s\n",
__FILE__,__LINE__,
PQerrorMessage(connection));
PQclear(qry_res);
PQfinish(connection);
return(db_FOP);
}
if (!(db = (dbase *) calloc(1,sizeof(dbase)))) return(db_MEM);
*da = db;
db->dname = nm;
db->dfile = NULL;
db->dnfld = PQnfields(qry_res);
db->dnrec = 1;
db->dtrec = 0;
if (!(db->dhead = (db_head *) calloc(1,sizeof(db_head)))) return(db_MEM);
db->dhead->db_nrecs = db->dtrec;
if (!(db->dfrec = (db_field *) calloc(db->dnfld,sizeof(db_field))))
return(db_MEM);
for (i = 0; i < PQnfields(qry_res); i++)
{
char * qry_fname = PQfname(qry_res,i);
if (texnamcol)
{
for (j = 0; j < strlen(qry_fname) && j < 10; j++)
{
db->dfrec[i].fl_fname[j] = toupper(qry_fname[j]);
}
db->dfrec[i].fl_fname[j] = '\0';
}
else
{
sprintf(fname,"U%03d",i+1);
strcpy(db->dfrec[i].fl_fname,fname);
}
switch(PQftype(qry_res, i))
{
case INT2OID:
case INT4OID:
case INT28OID:
case BOOLOID:
case OIDOID:
/* int types */
db->dfrec[i].fl_fsize = 12;
db->dfrec[i].fl_ftype = 'N';
break;
case CASHOID:
case FLOAT4OID:
case FLOAT8OID:
/* float types */
db->dfrec[i].fl_fsize = 14;
db->dfrec[i].fl_ftype = 'N';
break;
case BYTEAOID:
case CHAROID:
case NAMEOID:
case TEXTOID:
case VARCHAROID:
case DATEOID:
case TIMEOID:
case DATETIMEOID:
case TIMESPANOID:
case TIMESTAMPOID:
db->dfrec[i].fl_fsize = PQfsize(qry_res, i);
db->dfrec[i].fl_ftype = 'C';
break;
default:
db->dfrec[i].fl_fsize = PQfsize(qry_res, i);
db->dfrec[i].fl_ftype = 'C';
break;
}
}
/* now check if any had a size -1 */
for (i = 0; i < db->dnfld; i++)
if (db->dfrec[i].fl_fsize < 0)
{
/* find the maximum field size */
int k, mx = 1 /* some default? */, tmp;
for(k = 0; k < PQntuples(qry_res); k++)
{
tmp = PQgetlength(qry_res,k,i);
if (tmp > mx)
mx = tmp;
}
db->dfrec[i].fl_fsize = mx;
}
for (i = 0; i < db->dnfld; i++)
db->dnrec += db->dfrec[i].fl_fsize;
if (!(db->drrec = (char *) realloc(NULL,1)))
return(db_MEM);
for (tuple = 0; tuple < PQntuples(qry_res); tuple ++)
{
db->dtrec++;
if (!(db->drrec = (char *) realloc(db->drrec,db->dtrec*db->dnrec+1)))
return(db_MEM);
for (i = 0; i < PQnfields(qry_res); i++)
{
char * qry_data = PQgetvalue(qry_res,tuple,i);
if (NULL == qry_data)
{
sprintf(&db->drrec[ptr],"%-.*s",db->dfrec[i].fl_fsize,texnull);
}
else
{
sprintf(&db->drrec[ptr],"%*s",db->dfrec[i].fl_fsize,
qry_data);
}
ptr += db->dfrec[i].fl_fsize;
}
db->drrec[ptr++] = ' ';
}
db->drrec[ptr-1] = 0x1A;
db->dhead->db_nrecs = db->dtrec;
PQclear(qry_res);
PQfinish(connection);
return(0);
}
/****************************************************************************/