Message-ID: <3E004C46.161F824D@rodos.fzk.de>
Date: Wed, 18 Dec 2002 11:21:59 +0100
From: Christoph Haller <ch@rodos.fzk.de>
MIME-Version: 1.0
To: pgsql-sql@postgresql.org
Cc: prashanth_kamath1@rediffmail.com
Subject: Re: Dynamic sql program using libpq
Content-Type: multipart/mixed; boundary="------------CC76AA878C9CCC3BCEA709BA"

This is a multi-part message in MIME format.
--------------CC76AA878C9CCC3BCEA709BA
Content-Type: text/plain; charset=iso-8859-2
Content-Transfer-Encoding: 7bit

>
> I am able to find couple
> of examples for dynamic sql programming through ecpg. But i want to
> do dynamic sql programming through libpq.
> If anyone has programs doing the dynamic sql programmming using the
> libpq libraries please mail to me.
>
Attached you'll find my encapsulated library of postgres functions,
which I use for all queries resp. commands.
Regards, Christoph


--------------CC76AA878C9CCC3BCEA709BA
Content-Type: text/plain; charset=iso-8859-2; name="libpq-myfe.h"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline; filename="libpq-myfe.h"

/*-------------------------------------------------------------------------
 *
 * libpq-myfe.h 
 *
 *
 * Christoph Haller, D.T.I. 
 *
 * Created Sep 2001 
 *
 *-------------------------------------------------------------------------
 */
#include "libpq-fe.h"

#ifndef LIBPQ_MYFE_H
#define LIBPQ_MYFE_H

int PGSQL_command(PGconn *thisconnec, PGresult *thisresul, 
		  char *thiscommand);
int PGSQL_process(PGconn *thisconnec, PGresult **thisresul, 
		  char *thiscommand);
int PGSQL_begin(PGconn *thisconnec);

int PGSQL_declare_cursor1(PGconn *thisconnec, PGresult *thisresul, 
			  char *thisselect);
int PGSQL_fetch_all1(PGconn *thisconnec, PGresult **thisresul);
int PGSQL_close_cursor1(PGconn *thisconnec, PGresult *thisresul);

int PGSQL_commit(PGconn *thisconnec);
int PGSQL_rollback(PGconn *thisconnec);

int PGSQL_declare_cursor2(PGconn *thisconnec, PGresult *thisresul, 
			  char *thisselect);
int PGSQL_fetch_all2(PGconn *thisconnec, PGresult **thisresul);
int PGSQL_close_cursor2(PGconn *thisconnec, PGresult *thisresul);

int PGSQL_declare_bincsr1(PGconn *thisconnec, PGresult *thisresul, 
			  char *thisselect);
int PGSQL_fetch_binall1(PGconn *thisconnec, PGresult **thisresul);
int PGSQL_close_bincsr1(PGconn *thisconnec, PGresult *thisresul);

int PGSQL_binprocess(PGconn *thisconnec, PGresult **thisresul,
		     char *thiscommand);

#endif	 /* LIBPQ_MYFE_H */

--------------CC76AA878C9CCC3BCEA709BA
Content-Type: text/plain; charset=iso-8859-2; name="libpq-myfe.c"
Content-Transfer-Encoding: 7bit
Content-Disposition: inline; filename="libpq-myfe.c"

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include "libpq-myfe.h"

char *strdup(const char *s);

#ifdef PostgreSQL_6_5_3
const char *PGSQL_UNIQUE_INDEX_VIOLATED = /* PostgreSQL 6.5.3 */
"ERROR:  Cannot insert a duplicate key into a unique index" ;
#endif /* PostgreSQL_6_5_3 */
const char *PGSQL_UNIQUE_INDEX_VIOLATED = /* PostgreSQL 7.1.2 */
"ERROR:  Cannot insert a duplicate key into unique index " ;

/*
** procedure:	trim_sequence 
** purpose:	delete space sequences within a given string 
** 		delete spaces only within the given length 
** 		the given string does not have to be null terminated 
**		this function is secure:
**		- one space always remains 
**		- no space, no action 
**		- several space sequences are processed 
**		- deleting stops if a null character is found 
**		  and the given length is not reached 
**
** parameters:	seq		- the string to trim 
**		seq_len		- the length of string to trim 
** returns:	the trimmed string 
*/
#  if defined(__STDC__) || defined(__cplusplus)
char *trim_sequence(char *seq,size_t seq_len)
#  else /* __STDC__ || __cplusplus */
char *trim_sequence(seq,seq_len)
   char *seq;size_t seq_len;
#  endif /* __STDC__ || __cplusplus */
{
   char space=' ';		/* the trim character */
   char *first=seq;		/* ptr to the 1st space */
   char *last;			/* ptr to the last space */
   size_t seq_pos=0;		/* index within sequence */
   
   /* while not end of sequence ... */
   while(seq_pos<seq_len) {
      /* find first space */
      /* if there is no space break from loop */
      if(!(first=strchr(first,space))) break;
      /* assume last space to be next char after first */
      last=++first;
      /* while sequence not terminated by null and
	 current char evaluates to space and
	 sequence end not reached -> continue search */
      while(last&&*last==space&&last-seq<seq_len) last++;
      /* if there is more than one space */
      if(last>first) {
	 *first=NULL; /* set temporary termination */
	 strcat(seq,last); /* append the following subsequence */
      }
      seq_pos=last-seq; /* update sequence index */
   }
   
   return seq; /* return the trimmed sequence */
} /* trim_sequence() */

int PGSQL_command(PGconn *thisconnec, PGresult *thisresul, 
			  char *thiscommand)
{
   int result = EXIT_SUCCESS;
   
   trim_sequence(thiscommand, strlen(thiscommand));
   
   thisresul = PQexec(thisconnec, thiscommand);
   if (!thisresul || PQresultStatus(thisresul) != PGRES_COMMAND_OK)
   {
      fprintf(stderr, "'%s' command failed\n%s\n", thiscommand, 
	      PQresultErrorMessage(thisresul));
      result = -EXIT_FAILURE;
   }
   else
   {
      result = atoi(PQcmdTuples(thisresul));
   }
   /* PQclear PGresult whenever it is no longer needed to avoid memory leaks */
   PQclear(thisresul);
   return result;
}

int PGSQL_process(PGconn *thisconnec, PGresult **thisresul, 
			  char *thiscommand)
{
   int result = EXIT_SUCCESS;
   
   trim_sequence(thiscommand, strlen(thiscommand));
   
   *thisresul = PQexec(thisconnec, thiscommand);
   if (!*thisresul || PQresultStatus(*thisresul) != PGRES_TUPLES_OK)
   {
      fprintf(stderr, "'%s' command failed\n%s\n", thiscommand, 
	      PQresultErrorMessage(*thisresul));
      result = -EXIT_FAILURE;
   }
   else
   {
      result = PQntuples(*thisresul);
   }
   /* PQclear PGresult whenever it is no longer needed to avoid memory leaks */
   /*
   PQclear(thisresul);
   The calling function has to PQclear PGresult 
   use PGSQL_process to select 
   */
   return result;
}

int PGSQL_begin(PGconn *thisconnec)
{
   int result = EXIT_SUCCESS;
   PGresult *thisresul;
   
   /* start a transaction block */
   thisresul = PQexec(thisconnec, "BEGIN");
   if (!thisresul || PQresultStatus(thisresul) != PGRES_COMMAND_OK)
   {
      fprintf(stderr, "BEGIN command failed\n");
      result = -EXIT_FAILURE;
   }
   /* PQclear PGresult whenever it is no longer needed to avoid memory leaks */
   PQclear(thisresul);
   return result;
}

int PGSQL_declare_cursor1(PGconn *thisconnec, PGresult *thisresul, 
			  char *thisselect)
{
   int result = EXIT_SUCCESS;
   char *thiscommand = (char *)malloc( 32 + strlen(thisselect) );
   
   trim_sequence(thisselect, strlen(thisselect));
   
   sprintf(thiscommand, "DECLARE mycursor CURSOR FOR %s", thisselect);
   thisresul = PQexec(thisconnec, thiscommand);
   if (!thisresul || PQresultStatus(thisresul) != PGRES_COMMAND_OK)
   {
      fprintf(stderr, "DECLARE CURSOR command \n%s\nfailed \n%s\n", 
	      thisselect, PQresultErrorMessage(thisresul));
      result = -EXIT_FAILURE;
   }
   /* PQclear PGresult whenever it is no longer needed to avoid memory leaks */
   PQclear(thisresul);
   free(thiscommand);
   return result;
}

int PGSQL_fetch_all1(PGconn *thisconnec, PGresult **thisresul)
{
   int result = EXIT_SUCCESS;
   
   *thisresul = PQexec(thisconnec, "FETCH ALL in mycursor");
   if (!*thisresul || PQresultStatus(*thisresul) != PGRES_TUPLES_OK)
   {
      fprintf(stderr, "FETCH ALL command didn't return tuples properly\n");
      result = -EXIT_FAILURE;
   }
   return result;
}

int PGSQL_close_cursor1(PGconn *thisconnec, PGresult *thisresul)
{
   int result = EXIT_SUCCESS;
   thisresul = PQexec(thisconnec, "CLOSE mycursor");
   PQclear(thisresul);
   return result;
}

int PGSQL_commit(PGconn *thisconnec)
{
   int result = EXIT_SUCCESS;
   PGresult *thisresul;
   thisresul = PQexec(thisconnec, "COMMIT");
   PQclear(thisresul);
   return result;
}

int PGSQL_rollback(PGconn *thisconnec)
{
   int result = EXIT_SUCCESS;
   PGresult *thisresul;
   thisresul = PQexec(thisconnec, "ROLLBACK");
   PQclear(thisresul);
   return result;
}

int PGSQL_declare_cursor2(PGconn *thisconnec, PGresult *thisresul, 
			  char *thisselect)
{
   int result = EXIT_SUCCESS;
   char *thiscommand = (char *)malloc( 32 + strlen(thisselect) );
   
   trim_sequence(thisselect, strlen(thisselect));
   
   sprintf(thiscommand, "DECLARE mycursor2 CURSOR FOR %s", thisselect);
   thisresul = PQexec(thisconnec, thiscommand);
   if (!thisresul || PQresultStatus(thisresul) != PGRES_COMMAND_OK)
   {
      fprintf(stderr, "DECLARE CURSOR command \n%s\nfailed \n%s\n", 
	      thisselect, PQresultErrorMessage(thisresul));
      result = -EXIT_FAILURE;
   }
   /* PQclear PGresult whenever it is no longer needed to avoid memory leaks */
   PQclear(thisresul);
   free(thiscommand);
   return result;
}

int PGSQL_fetch_all2(PGconn *thisconnec, PGresult **thisresul)
{
   int result = EXIT_SUCCESS;
   
   *thisresul = PQexec(thisconnec, "FETCH ALL in mycursor2");
   if (!*thisresul || PQresultStatus(*thisresul) != PGRES_TUPLES_OK)
   {
      fprintf(stderr, "FETCH ALL command didn't return tuples properly\n");
      result = -EXIT_FAILURE;
   }
   return result;
}

int PGSQL_close_cursor2(PGconn *thisconnec, PGresult *thisresul)
{
   int result = EXIT_SUCCESS;
   thisresul = PQexec(thisconnec, "CLOSE mycursor2");
   PQclear(thisresul);
   return result;
}

int PGSQL_declare_bincsr1(PGconn *thisconnec, PGresult *thisresul, 
			  char *thisselect)
{
   int result = EXIT_SUCCESS;
   char *thiscommand = (char *)malloc( 32 + strlen(thisselect) );
   
   trim_sequence(thisselect, strlen(thisselect));
   
   sprintf(thiscommand, "DECLARE mybcursor BINARY CURSOR FOR %s", thisselect);
   thisresul = PQexec(thisconnec, thiscommand);
   if (!thisresul || PQresultStatus(thisresul) != PGRES_COMMAND_OK)
   {
      fprintf(stderr, "DECLARE BINARY CURSOR command \n%s\nfailed \n%s\n", 
	      thisselect, PQresultErrorMessage(thisresul));
      result = -EXIT_FAILURE;
   }
   /* PQclear PGresult whenever it is no longer needed to avoid memory leaks */
   PQclear(thisresul);
   free(thiscommand);
   return result;
}

int PGSQL_fetch_binall1(PGconn *thisconnec, PGresult **thisresul)
{
   int result = EXIT_SUCCESS;
   
   *thisresul = PQexec(thisconnec, "FETCH ALL in mybcursor");
   if (!*thisresul || PQresultStatus(*thisresul) != PGRES_TUPLES_OK)
   {
      fprintf(stderr, "FETCH ALL BINARY command didn't return tuples properly\n");
      result = -EXIT_FAILURE;
   }
   return result;
}

int PGSQL_close_bincsr1(PGconn *thisconnec, PGresult *thisresul)
{
   int result = EXIT_SUCCESS;
   thisresul = PQexec(thisconnec, "CLOSE mybcursor");
   PQclear(thisresul);
   return result;
}

int PGSQL_binprocess(PGconn *thisconnec, PGresult **thisresul, 
			  char *thiscommand)
{
   int result = EXIT_SUCCESS;
   
   result = PGSQL_declare_bincsr1(thisconnec, *thisresul, thiscommand);
   
   if (result < EXIT_SUCCESS)
   {
      return result;
   }
   
   result = PGSQL_fetch_binall1(thisconnec, thisresul);
   
   if (result < EXIT_SUCCESS)
   {
      return result;
   }
   /*
   PQclear(thisresul);
   The calling function has to PQclear PGresult 
   use PGSQL_binprocess to select 
   */
   
   {
      /* use a local PGresult to close the cursor because 
         the PGresult parameter 'thisresul' is needed by 
	 the calling function 
      */
      PGresult   *local_pgresult;
      result = PGSQL_close_bincsr1(thisconnec, local_pgresult);
   }
   
   return result;
}


--------------CC76AA878C9CCC3BCEA709BA--