Re: Dynamic sql program using libpq
От | Christoph Haller |
---|---|
Тема | Re: Dynamic sql program using libpq |
Дата | |
Msg-id | 3E004C46.161F824D@rodos.fzk.de обсуждение исходный текст |
Ответ на | Dynamic sql program using libpq ("Prashanth - Kamath" <prashanth_kamath1@rediffmail.com>) |
Список | 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; }
В списке pgsql-sql по дате отправления: