Compile with the command: gcc c-example1.c -lpq

Complete list of C API functions used by PostgreSQL: http://www.postgresql.org/docs/8.3/static/libpq.html

//#++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ //#+ Mumps Compiler Run-Time Support Functions //#+ Copyright (c) 2001, 2002, 2008 by Kevin C. O'Kane //#+ okane@cs.uni.edu //#+ //#+ This library is free software; you can redistribute it and/or //#+ modify it under the terms of the GNU Lesser General Public //#+ License as published by the Free Software Foundation; either //#+ version 2.1 of the License, or (at your option) any later version. //#+ //#+ This library 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 //#+ Lesser General Public License for more details. //#+ //#+ You should have received a copy of the GNU Lesser General Public //#+ License along with this library; if not, write to the Free Software //#+ Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA //#+ //#+ http://www.cs.uni.edu/~okane //#+ //#++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ /* rdbms.c - Mumps Runtime Library */ /* compile with: gcc rdbms-if.c -lpq */ #include <stdio.h> #include <stdlib.h> #include <string.h> #include <postgresql/libpq-fe.h> int sql(char * Connection, char *Command) { /*#++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ *#+ PostgreSQL Interface Section *#++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/ PGconn *p1; PGresult *p2; int i, j, nt = 0, nf = 0; p1 = PQconnectdb(Connection); if (p1 == NULL) { printf("*** Null connect\n"); return -1; } if (PQstatus(p1)==CONNECTION_OK) printf("connection ok\n"); if (PQstatus(p1)==CONNECTION_STARTED) printf("connection started\n"); if (PQstatus(p1)==CONNECTION_MADE) printf("connection made\n"); if (PQstatus(p1)==CONNECTION_AWAITING_RESPONSE) printf("connection waitng\n"); if (PQstatus(p1)==CONNECTION_BAD) printf("connection bad\n"); if (PQstatus(p1)==CONNECTION_AUTH_OK) printf("connection auth ok\n"); if (PQstatus(p1)==CONNECTION_SETENV) printf("connection setenv\n"); p2 = PQexec(p1, Command); if (p2 == NULL) { printf("*** Null result\n"); printf("%s\n", PQerrorMessage(p1)); return -1; } if (PQresultStatus(p2) == PGRES_TUPLES_OK) { /*#+=================================================================== *#+ get the results. *#+=================================================================*/ nt = PQntuples(p2); nf = PQnfields(p2); for (i = 0; i < nt; i++) { char val[1024]; char *item; int ii; strcpy(val, ""); for (j = 0; j < nf; j++) { item = PQgetvalue(p2, i, j); strcat(val, item); strcat(val, " "); } printf("*** %s\n",val); } } PQfinish(p1); printf("exit\n"); return nt; /*#++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ *#++++++++++++ End of PostgreSQL Interface +++++++++++++++++++++++++++++ *#++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/ } int main() { sql ("user=okane host=tuatha.cs.uni.edu dbname=medical", "select * from labs where ptid='1002';"); return EXIT_SUCCESS; } ---------- output: okane@neamh:~/DataBases/114$ a.out connection ok *** 1002 1999-Jul-11 12:10 Glucose 10 *** 1002 1999-Jul-12 12:11 Glucose 12 *** 1002 1999-Jul-13 12:12 Glucose 14 *** 1002 1999-Jul-14 12:13 Glucose 16 *** 1002 1999-Jul-15 12:14 Glucose 18 *** 1002 1999-Jul-16 12:15 Glucose 20 *** 1002 1999-Jul-17 12:16 Glucose 22 *** 1002 1999-Jul-18 12:17 Glucose 24 *** 1002 1999-Jul-19 12:18 Glucose 16 *** 1002 1999-Jul-11 12:10 Hct 10 *** 1002 1999-Jul-12 12:11 Hct 12 *** 1002 1999-Jul-13 12:12 Hct 14 *** 1002 1999-Jul-14 12:13 Hct 16 *** 1002 1999-Jul-15 12:14 Hct 18 *** 1002 1999-Jul-16 12:15 Hct 20 *** 1002 1999-Jul-17 12:16 Hct 22 *** 1002 1999-Jul-18 12:17 Hct 24 *** 1002 1999-Jul-19 12:18 Hct 16 *** 1002 1999-Jul-11 12:10 Hgb 10 *** 1002 1999-Jul-12 12:11 Hgb 12 *** 1002 1999-Jul-13 12:12 Hgb 14 *** 1002 1999-Jul-14 12:13 Hgb 16 *** 1002 1999-Jul-15 12:14 Hgb 18 *** 1002 1999-Jul-16 12:15 Hgb 20 *** 1002 1999-Jul-17 12:16 Hgb 22 *** 1002 1999-Jul-18 12:17 Hgb 24 *** 1002 1999-Jul-19 12:18 Hgb 16 Another example. The C program below captures QUERY_STRING with the code in cgi.c (shown below). It looks for the value of 'query=' from QUERY_STRING and then passes the right hand side to the SQL interface which prints the results. cgi.c populates pairs of pointers in RESULTS[] that point (1) to a string giving the left hand side of the QUERY_STRING parameter and (2) a pointer to the left hand side value. //#++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ //#+ Mumps Compiler Run-Time Support Functions //#+ Copyright (c) 2001, 2002, 2008 by Kevin C. O'Kane //#+ okane@cs.uni.edu //#+ //#+ This library is free software; you can redistribute it and/or //#+ modify it under the terms of the GNU Lesser General Public //#+ License as published by the Free Software Foundation; either //#+ version 2.1 of the License, or (at your option) any later version. //#+ //#+ This library 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 //#+ Lesser General Public License for more details. //#+ //#+ You should have received a copy of the GNU Lesser General Public //#+ License along with this library; if not, write to the Free Software //#+ Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA //#+ //#+ http://www.cs.uni.edu/~okane //#+ //#++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ /* compile with: gcc xxx.c -lpq */ #include <stdio.h> #include <stdlib.h> #include <string.h> #include <postgresql/libpq-fe.h> int sql(char * Connection, char *Command) { /*#++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ *#+ PostgreSQL Interface Section *#++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/ PGconn *p1; PGresult *p2; int i, j, nt = 0, nf = 0; p1 = PQconnectdb(Connection); if (p1 == NULL) { printf("*** Null connect\n"); return -1; } if (PQstatus(p1)==CONNECTION_OK) printf("connection ok\n"); if (PQstatus(p1)==CONNECTION_STARTED) printf("connection started\n"); if (PQstatus(p1)==CONNECTION_MADE) printf("connection made\n"); if (PQstatus(p1)==CONNECTION_AWAITING_RESPONSE) printf("connection waitng\n"); if (PQstatus(p1)==CONNECTION_BAD) printf("connection bad\n"); if (PQstatus(p1)==CONNECTION_AUTH_OK) printf("connection auth ok\n"); if (PQstatus(p1)==CONNECTION_SETENV) printf("connection setenv\n"); p2 = PQexec(p1, Command); if (p2 == NULL) { printf("*** Null result\n"); printf("%s\n", PQerrorMessage(p1)); return -1; } if (PQresultStatus(p2) == PGRES_TUPLES_OK) { /*#+=================================================================== *#+ get the results. *#+=================================================================*/ nt = PQntuples(p2); nf = PQnfields(p2); for (i = 0; i < nt; i++) { char val[1024]; char *item; int ii; strcpy(val, ""); for (j = 0; j < nf; j++) { item = PQgetvalue(p2, i, j); strcat(val, item); strcat(val, " "); } printf("*** %s\n",val); } } PQfinish(p1); printf("exit\n"); return nt; /*#++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ *#++++++++++++ End of PostgreSQL Interface +++++++++++++++++++++++++++++ *#++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++*/ } int main() { int i,L,M; char Query[1024]; #include "cgi.c" printf("Content-type: text/html \n\n"); printf("<html><body><pre>"); for (i=0; i<RX; i++) { if (strcmp(RESULTS[i],"query")==0) break; } if (i == RX) { printf("No query found </body></html>\n"); return EXIT_FAILURE; } printf("Query is: ***%s***<p>\n", RESULTS[i+1]); sql ("user=okane host=tuatha.cs.uni.edu dbname=medical", RESULTS[i+1]); printf("</pre></html></body>"); return EXIT_SUCCESS; } where cgi.c is: //#++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ //#+ Mumps Compiler Run-Time Support Functions //#+ Copyright (c) 2001, 2008 by Kevin C. O'Kane //#+ okane@cs.uni.edu //#+ //#+ This library is free software; you can redistribute it and/or //#+ modify it under the terms of the GNU Lesser General Public //#+ License as published by the Free Software Foundation; either //#+ version 2.1 of the License, or (at your option) any later version. //#+ //#+ This library 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 //#+ Lesser General Public License for more details. //#+ //#+ You should have received a copy of the GNU Lesser General Public //#+ License along with this library; if not, write to the Free Software //#+ Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA 02111-1307 USA //#+ //#+ http://www.cs.uni.edu/~okane //#+ http://www.omahadave.com //#+ //#++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++ /*-------------------------------------------- decode arguments passed to processor from command line or environment ----------------------------------------------*/ #define QS_MAX 2048 #define QS_MAX1 2047 #define STR_MAX 4096 /**************************************************************************** ******* ******* install default program to execute init.mps or decode shell variable ******* ****************************************************************************/ char * RESULTS[100]; int RX=0; { char _ftmp[STR_MAX]; if (getenv("REMOTE_ADDR")!=NULL) { char t[QS_MAX]; strncpy(t,(const char *)getenv("REMOTE_ADDR"),QS_MAX1); RESULTS[RX]=(char *)malloc(sizeof(t)+1); strcpy(RESULTS[RX],t); RX++; } if (getenv("REMOTE_HOST")!=NULL) { char t[QS_MAX]; strncpy(t,(const char *)getenv("REMOTE_HOST"),QS_MAX1); RESULTS[RX]=(char *)malloc(sizeof(t)+1); strcpy(RESULTS[RX],t); RX++; } if (getenv("QUERY_STRING")!=NULL) { char t[QS_MAX],t1[1024]; int i,j,k,m,n,p,dup=0; strncpy(t,(const char *)getenv("QUERY_STRING"),QS_MAX1); /* find end */ for (i=0; t[i]!='&' && t[i]!=0 && i<QS_MAX; i++); if (i==QS_MAX) t[0]='\0'; RESULTS[RX]=(char *)malloc(sizeof(t)+1); strcpy(RESULTS[RX],t); RX++; if (t[0]=='\0') goto cgi_exit; /*************************** convert plus signs to blanks ***************************/ for (i=0; t[i]!=0 && i<QS_MAX; i++) if (t[i]=='+') t[i]=' '; nxtarg: /* find end */ for (i=0; t[i]!='&' && t[i]!=0 && i<QS_MAX; i++); if (t[i]==0) k=0; else k=1; t[i]=0; for (j=0; t[j]!='=' && j<QS_MAX; j++); if (j==QS_MAX) goto cgi_exit; t[j]=0; /*************************** copy right hand side to _ftmp ***************************/ for (n=0,m=j+1; t[m]!=0; m++) { if (t[m]!='%') { _ftmp[n]=t[m]; n++; continue; } /************** first hex digit **************/ if (t[m+1]>='a'&&t[m+1]<='z')t[m+1]=t[m+1]-32; if (t[m+1]>='0'&&t[m+1]<='9') p=(t[m+1]-'0')*16; else p=(t[m+1]-'A'+10)*16; /*************** second hex digit ***************/ if (t[m+2]>='a'&&t[m+2]<='z')t[m+2]=t[m+2]-32; if (t[m+2]>='0'&&t[m+2]<='9') p=p+(t[m+2]-'0'); else p=p+(t[m+2]-'A'+10); _ftmp[n]=p; /* insert value into output string */ n++; m=m+2; } _ftmp[n]=0; { RESULTS[RX]=(char *)malloc(sizeof(t)+1); strcpy(RESULTS[RX],t); RX++; RESULTS[RX]=(char *)malloc(sizeof(_ftmp)+1); strcpy(RESULTS[RX],_ftmp); RX++; } /********************************** not at end yet - shift input string **********************************/ if (k) { for (k=i+1; t[k]!=0 && k<QS_MAX; k++) t[k-(i+1)]=t[k]; t[k-(i+1)]=0; goto nxtarg; } } cgi_exit: ; } where query.html is: <html> <body> <font size=+2> Worst General Hospital Database Reports <p> <hr> <form method=get action="cgi-bin/example2.cgi"> Enter SQL query: <input type=text name=query value="" size=50> &nbsp; &nbsp; &nbsp; <input type=submit value="Display Results"> </form> </body> </html> screen caps: