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