Re: Dynamic sql program using libpq - Mailing list pgsql-sql

>
> 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

/*-------------------------------------------------------------------------
 *
 * 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 */
#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;
}