Attached are some new functions that extend the libpq api to make calling the parameterized interfaces easier, especially when making binary calls. IMO, this fills one of the two big missing parts of the libpq api when making binary database calls, the other being client side handling of complex structures (arrays, etc).
The code covers two major areas of functionality and isolated for separate inclusion: * PGparam (param.c) * get/set functions for the pgresult (result_ext.c) We are happy with both pieces but they can be adopted separately or not at all. The attached code is basically a cleaned up version of wrappers put in place in our own applications, plus a functional test. The major ideas were: * introduce a new opaque structure, PGparam, that handles some of the more difficult aspects of memory management associated with binary calls. * remove the requirement of client side code having to do byte swapping * make binary calls as efficient as possible, with a minimal amount of memory allocations * introduce, as much as possible, no additional portability issues or additional dependencies to the libpq api. Here are the interesting and/or possibly controversial pieces: * For portability purposes, we had the 64 bit integer put function take a pointer where the other putters take value types. We couldn't think of any other way to do it because there is not 64 bit portable integer type in libpq. * The synchronous execution functions (for example PQparamExec), takes a pointer to a result and return error status, which is _not_ how the other flavors of Exec operate, but is very convenient however. If you pass in NULL the result is discarded for you. We are stuck on this approach, but we like it. * The getters check the returned type oid to make sure it is sane. For this reason, we have to include catalog/pg_type.h and postgres.h to get to the OID defines (these are not exposed to the interface however). I don't see a reason why this is not ok. The 64 bit integer is handled as a pointer in the get/set functions because as far as we can tell there is no 64 bit integer type we can count on without introducing compatibility issues. We considered putting the PGparam struct into the PGconn structure. In this case, a PGconn pointer would be passed to the PQparamXXX functions instead of a PGparam, and would lazy allocate the structure and free it on PQfinish. We are curious for opinions on this. Writing credits to myself and Andrew Chernow. If this proposal is accepted, we will write all the documentation and make suitable changes necessary for inclusion, presumably for the 8.4 release. To compile the changes see the attached makefile. What we would really like is to use the backend input and output functions for data types, rather than reimplementing this within the client ... ie pqformat.c and similar files. For this reason, we did not re-implement get/put functions for the geometric types (we thought about it), etc. Merging the client and the server marshaling may require some abstraction of the server so formatting functions can be called from the client api. Hopefully this will open up the binary interfaces to more developers. For certain types of queries, binary calls can be a huge win in terms of efficiency. merlin
makefile
Description: Binary data
#include <stdlib.h>
#include <string.h>
#include "pg.h"
#include "libpq-int.h"
/* Supports 250 columns worth of params. If more are needed,
* memory is allocated ... very rare case.
*/
#define COLSTACKSIZE 4096
#define CHKPARAMPTR(p) do{ \
if(!(p)) \
{ \
errno = EINVAL; \
strcpy((p)->errmsg, libpq_gettext("PGparam pointer is NULL")); \
return 0; \
} \
}while(0)
#define PARAM_ARRAY_DECL \
char _stackbuffer[COLSTACKSIZE]; \
char *buf = _stackbuffer; \
char **vals = NULL; \
int *lens = NULL; \
int *fmts = NULL
#define PARAM_ARRAY_ASSIGN do{ \
if(param) \
{ \
int n = (int)((sizeof(void *) * param->vcnt) + \
((sizeof(int) * 2) * param->vcnt)); \
if(n > COLSTACKSIZE) \
{ \
buf = (char *)malloc(n); \
if(!buf) \
{ \
printfPQExpBuffer(&conn->errorMessage, \
libpq_gettext("cannot allocate parameter column arrays\n")); \
return 0; \
} \
} \
vals = (char **)buf; \
lens = (int *)(buf + (sizeof(void *) * param->vcnt)); \
fmts = lens + param->vcnt; \
for(n=0; n < param->vcnt; n++) \
{ \
vals[n] = param->vals[n].data; \
lens[n] = param->vals[n].datal; \
fmts[n] = param->vals[n].format; \
} \
} \
}while(0)
#define PARAM_ARRAY_FREE do{ \
if(buf != _stackbuffer) \
free(buf); \
}while(0)
typedef struct
{
int ptrl;
void *ptr;
int datal;
char *data;
int format;
} PGvalue;
struct pg_param
{
int vcnt;
int vmax;
PGvalue *vals;
int slabsize;
char *slab;
char errmsg[128];
};
PGparam *PQparamCreate(void)
{
return (PGparam *)calloc(1, sizeof(PGparam));
}
void PQparamReset(PGparam *param)
{
if(param)
param->vcnt = 0;
}
char *PQparamErrorMessage(PGparam *param)
{
if(!param)
return libpq_gettext("PGparam pointer is NULL\n");
return param->errmsg;
}
void PQparamClear(PGparam *param)
{
if(param)
{
int i;
for(i=0; i < param->vmax; i++)
if(param->vals[i].ptr)
free(param->vals[i].ptr);
if(param->vals)
free(param->vals);
if(param->slab)
free(param->slab);
free(param);
}
}
int PQparamExec(
PGconn *conn,
const char *command,
PGparam *param,
int resultFormat,
PGresult **resultp)
{
PGresult *res;
PARAM_ARRAY_DECL;
if(resultp)
*resultp = NULL;
PARAM_ARRAY_ASSIGN;
res = PQexecParams(
conn,
command,
param ? param->vcnt : 0,
(const Oid *)NULL,
(const char * const *)vals,
(const int *)lens,
(const int *)fmts,
resultFormat);
PARAM_ARRAY_FREE;
if(!res)
return 0;
/* If caller doesn't want result, clear it. */
if(!resultp)
PQclear(res);
else
*resultp = res;
return 1;
}
int PQparamExecPrepared(
PGconn *conn,
const char *stmtName,
PGparam *param,
int resultFormat,
PGresult **resultp)
{
PGresult *res;
PARAM_ARRAY_DECL;
if(resultp)
*resultp = NULL;
PARAM_ARRAY_ASSIGN;
res = PQexecPrepared(
conn,
stmtName,
param ? param->vcnt : 0,
(const char * const *)vals,
(const int *)lens,
(const int *)fmts,
resultFormat);
PARAM_ARRAY_FREE;
if(!res)
return 0;
/* If caller doesn't want result, clear it. */
if(!resultp)
PQclear(res);
else
*resultp = res;
return 1;
}
int PQparamSend(
PGconn *conn,
const char *command,
PGparam *param,
int resultFormat)
{
int r;
PARAM_ARRAY_DECL;
PARAM_ARRAY_ASSIGN;
r = PQsendQueryParams(
conn,
command,
param ? param->vcnt : 0,
(const Oid *)NULL,
(const char * const *)vals,
(const int *)lens,
(const int *)fmts,
resultFormat);
PARAM_ARRAY_FREE;
return r;
}
int PQparamSendPrepared(
PGconn *conn,
const char *stmtName,
PGparam *param,
int resultFormat)
{
int r;
PARAM_ARRAY_DECL;
PARAM_ARRAY_ASSIGN;
r = PQsendQueryPrepared(
conn,
stmtName,
param ? param->vcnt : 0,
(const char * const *)vals,
(const int *)lens,
(const int *)fmts,
resultFormat);
PARAM_ARRAY_FREE;
return r;
}
int PQparamPutChar(PGparam *param, unsigned char ch)
{
CHKPARAMPTR(param);
return PQparamPut(param, (const void *)&ch, 1, 1);
}
int PQparamPutInt2(PGparam *param, unsigned short i2)
{
CHKPARAMPTR(param);
i2 = htons(i2);
return PQparamPut(param, (const void *)&i2, 2, 1);
}
int PQparamPutInt4(PGparam *param, unsigned int i4)
{
CHKPARAMPTR(param);
i4 = htonl(i4);
return PQparamPut(param, (const void *)&i4, 4, 1);
}
int PQparamPutInt8(PGparam *param, void *i8p)
{
unsigned int v[2];
unsigned int *i8;
CHKPARAMPTR(param);
if(!i8p)
{
errno = EINVAL;
strcpy(param->errmsg,
libpq_gettext("PQparamPutInt8: int64 ptr argument cannot be null"));
return 0;
}
i8 = (unsigned int *)i8p;
if(1 != htonl(1))
{
v[0] = htonl(i8[1]);
v[1] = htonl(i8[0]);
}
else
{
v[0] = i8[0];
v[1] = i8[1];
}
return PQparamPut(param, (const void *)&v, 8, 1);
}
int PQparamPutFloat(PGparam *param, float f)
{
void* vp = &f;
return PQparamPutInt4(param, *(unsigned int *)vp);
}
int PQparamPutDouble(PGparam *param, double d)
{
return PQparamPutInt8(param, &d);
}
int PQparamPutString(PGparam *param, const char *str, int strl)
{
CHKPARAMPTR(param);
if(str && strl < 0)
strl = (int)strlen(str);
return PQparamPut(param, str, strl, 0);
}
int PQparamPut(
PGparam *param,
const void *data,
int datal,
int format)
{
PGvalue *v;
CHKPARAMPTR(param);
if(param->vcnt == param->vmax)
{
PGvalue *vals;
int vmax = param->vmax ? (param->vmax * 3) / 2 : 16;
if(!(vals = (PGvalue *)realloc(param->vals, sizeof(PGvalue) * vmax)))
{
strcpy(param->errmsg,
libpq_gettext("cannot grow parameter param"));
return 0;
}
memset(vals + param->vcnt, 0, (vmax - param->vcnt) * sizeof(PGvalue));
param->vmax = vmax;
param->vals = vals;
}
v = ¶m->vals[param->vcnt];
if(!data)
datal = 0;
if(v->ptrl < datal)
{
char *ptr = (char *)realloc(v->ptr, datal);
if(!ptr)
{
strcpy(param->errmsg,
libpq_gettext("cannot enlarge parameter data pointer"));
return 0;
}
v->ptrl = datal;
v->ptr = ptr;
}
param->vcnt++;
v->datal = datal;
v->format = format;
v->data = data ? (char *)memcpy(v->ptr, data, datal) : NULL;
return 1;
}
#ifndef __PG_H__
#define __PG_H__
#include <libpq-fe.h>
#ifdef __cplusplus
extern "C" {
#endif
/* #########################################
* PGparam API
*/
typedef struct pg_param PGparam;
PGparam *PQparamCreate(void);
/* Resets a PGparam for use again. */
void PQparamReset(PGparam *param);
char *PQparamErrorMessage(PGparam *param);
/* Frees a PGparam */
void PQparamClear(PGparam *param);
int PQparamExec(
PGconn *conn,
const char *command,
PGparam *param,
int resultFormat,
PGresult **resultp);
int PQparamExecPrepared(
PGconn *conn,
const char *stmtName,
PGparam *param,
int resultFormat,
PGresult **resultp);
int PQparamSend(
PGconn *conn,
const char *command,
PGparam *param,
int resultFormat);
int PQparamSendPrepared(
PGconn *conn,
const char *stmtName,
PGparam *param,
int resultFormat);
int PQparamPutChar(PGparam *param, unsigned char ch);
int PQparamPutInt2(PGparam *param, unsigned short i2);
int PQparamPutInt4(PGparam *param, unsigned int i4);
int PQparamPutInt8(PGparam *param, void *i8p);
int PQparamPutFloat(PGparam *param, float f);
int PQparamPutDouble(PGparam *param, double d);
/* If strl < 0, the length of str will be calculated using strlen(). */
int PQparamPutString(PGparam *param, const char *str, int strl);
int PQparamPut(
PGparam *param,
const void *data,
int datal,
int format);
/* #########################################
* PGresult Extension API
*/
int PQgetchar(
const PGresult *res,
int tup_num,
int field_num,
unsigned char *charp);
int PQgetint2(
const PGresult *res,
int tup_num,
int field_num,
unsigned short *int2p);
int PQgetint4(
const PGresult *res,
int tup_num,
int field_num,
unsigned int *int4p);
int PQgetint8(
const PGresult *res,
int tup_num,
int field_num,
void *int8p);
#define PQgetfloat(res, tup_num, field_num, floatp) \
PQgetint4(res, tup_num, field_num, (unsigned int *) ((void*) floatp))
#define PQgetdouble(res, tup_num, field_num, doublep) \
PQgetint8(res, tup_num, field_num, (void *)(doublep))
#ifdef __cplusplus
}
#endif
#endif
#include "postgres.h"
#include "catalog/pg_type.h" /* For xxxOID constants */
#include <stdlib.h>
#include <string.h>
#include "pg.h"
#include "libpq-int.h"
int PQgetchar(
const PGresult *res,
int tup_num,
int field_num,
unsigned char *charp)
{
if(!charp || !res || PQftype(res, field_num) != CHAROID)
{
errno = EINVAL;
return 0;
}
if(PQfformat(res, field_num) == 0)
{
unsigned int v;
errno = 0;
v = (unsigned int)strtol(PQgetvalue(res, tup_num, field_num), NULL, 10);
if(v == 0 && errno)
return 0;
if(v > UCHAR_MAX)
{
errno = ERANGE;
return 0;
}
*charp = (unsigned char)v;
return 1;
}
/* binary format conversion */
*charp= *(unsigned char *)PQgetvalue(res, tup_num, field_num);
return 1;
}
int PQgetint2(
const PGresult *res,
int tup_num,
int field_num,
unsigned short *i2p)
{
if(!i2p || !res || PQftype(res, field_num) != INT2OID)
{
errno = EINVAL;
return 0;
}
if(PQfformat(res, field_num) == 0)
{
unsigned int v;
errno = 0;
v = (unsigned int)strtol(PQgetvalue(res, tup_num, field_num), NULL, 10);
if(v == 0 && errno)
return 0;
if(v > USHRT_MAX)
{
errno = ERANGE;
return 0;
}
*i2p = (unsigned short)v;
return 1;
}
/* binary format conversion */
*i2p = ntohs(*(unsigned short *)PQgetvalue(res, tup_num, field_num));
return 1;
}
int PQgetint4(
const PGresult *res,
int tup_num,
int field_num,
unsigned int *i4p)
{
Oid ftype = PQftype(res, field_num);
char *val = PQgetvalue(res, tup_num, field_num);
if(!i4p || !res || (ftype != INT4OID && ftype != FLOAT4OID))
{
errno = EINVAL;
printf("failed getint4\n");
return 0;
}
val = PQgetvalue(res, tup_num, field_num);
if(PQfformat(res, field_num) == 0)
{
if(ftype == INT4OID)
{
unsigned int v;
errno = 0;
v = (unsigned int)strtol(val, NULL, 10);
if(v == 0 && errno)
return 0;
*i4p = v;
}
else
{
float f;
errno = 0;
f = (float)strtod(val, NULL);
if(f == 0 && errno)
return 0;
*(float *)i4p = f;
}
return 1;
}
/* binary format conversion */
*i4p = ntohl(*(unsigned int *)val);
return 1;
}
int PQgetint8(
const PGresult *res,
int tup_num,
int field_num,
void *i8p)
{
unsigned int *v;
unsigned int *i8;
char *val;
Oid ftype = PQftype(res, field_num);
if(!i8p || !res || (ftype != INT8OID && ftype != FLOAT8OID))
{
errno = EINVAL;
return 0;
}
val = PQgetvalue(res, tup_num, field_num);
if(PQfformat(res, field_num) == 0)
{
if(ftype == INT8OID)
{
#if defined(HAVE_LONG_LONG_INT_64) && defined(HAVE_STRTOLL)
uint64 v;
errno = 0;
v = (uint64)strtoll(val, NULL, 10);
if(v == 0 && errno)
return 0;
*(uint64 *)i8p = v;
#else
unsigned long v;
errno = 0;
v = (unsigned long)strtol(val, NULL, 10);
if(v == 0 && errno)
return 0;
*(unsigned long *)i8p = v;
#endif
}
else
{
double d;
errno = 0;
d = strtod(val, NULL);
if(d == 0 && errno)
return 0;
*(double *)i8p = d;
}
return 1;
}
/* binary format conversion */
i8 = (unsigned int *)i8p;
v = (unsigned int *)val;
i8 = (unsigned int *)i8p;
if(1 != htonl(1))
{
i8[0] = ntohl(v[1]);
i8[1] = ntohl(v[0]);
}
else
{
i8[0] = v[0];
i8[1] = v[1];
}
return 1;
}
#include "pg.h"
#include <stdlib.h>
#include <string.h>
#include <limits.h>
#if defined(_WIN32) || defined(_WIN64)
# define U64FMT "%I64u"
typedef unsigned __int64 myuint64_t;
#else
# define U64FMT "%llu"
typedef unsigned long long myuint64_t;
#endif
/*
CREATE TABLE test_param
(
b "char",
s smallint,
i int,
l bigint,
t text,
f float4,
d float8,
t2 text
);
*/
int main(void)
{
float f;
double d = 0;
char *text;
unsigned char i8 = 0;
unsigned short i16 = 0;
unsigned int i32 = 0;
myuint64_t i64;
PGconn *conn;
PGresult *res;
PGparam *param;
ExecStatusType status;
char command[] = "INSERT INTO test_param VALUES "
"($1, $2, $3, $4, $5, $6, $7, $8)";
conn = PQconnectdb("hostaddr=127.0.0.1 user=postgres");
if(PQstatus(conn) != CONNECTION_OK)
{
printf("connection failure\n");
return 1;
}
/* clear test table */
res = PQexec(conn, "DELETE FROM test_param");
PQclear(res);
/* creates a PGparam. This can be used for multiple executions as
* long as PQparamReset() is called prior to putting param values.
*/
param = PQparamCreate();
PQparamPutChar(param, UCHAR_MAX);
PQparamPutInt2(param, USHRT_MAX);
PQparamPutInt4(param, UINT_MAX);
/* must pass a pointer to 64-bit value. frontend pq lib has no
* 64-bit type exposed.
*/
i64 = ULLONG_MAX;
PQparamPutInt8(param, &i64);
/* -1 means calculate the strlen */
PQparamPutString(param, "This is a string", -1);
PQparamPutFloat(param, 111.234f);
PQparamPutDouble(param, 11111111.234567);
/* SQL NULL value for text column */
PQparamPutString(param, NULL, 0);
/* execute the query using the provided params */
PQparamExec(conn, command, param, 1, &res);
/* check result */
status = PQresultStatus(res);
if(status != PGRES_COMMAND_OK && status != PGRES_TUPLES_OK)
printf("%s", PQresultErrorMessage(res));
PQclear(res);
/* don't need this any more. If it was needed, PGparamReset() would
* need to be called before using it again.
*/
PQparamClear(param);
/* execute a query in binary format with no params. NOTE: a PQparam
* without any values (a param count of 0) has the same effect as
* passing a NULL PGparam. See PQparamReset(), which sets the
* internal param count to 0.
*/
PQparamExec(conn, "SELECT * FROM test_param", NULL, 1, &res);
status = PQresultStatus(res);
if(status != PGRES_COMMAND_OK && status != PGRES_TUPLES_OK)
{
printf("%s", PQresultErrorMessage(res));
PQclear(res);
return 1;
}
/* process results */
PQgetchar(res, 0, 0, &i8);
PQgetint2(res, 0, 1, &i16);
PQgetint4(res, 0, 2, &i32);
PQgetint8(res, 0, 3, &i64);
text = PQgetvalue(res, 0, 4);
PQgetfloat(res, 0, 5, &f);
PQgetdouble(res, 0, 6, &d);
printf("i8 = %d\ni16 = %d\ni32 = %u\ni64 = " U64FMT "\n"
"str = '%s'\nfloat = %.3f\ndouble = %lf\n",
i8, i16, i32, i64, text, f, d);
if(PQgetisnull(res, 0, 7))
printf("nullstr is NULL\n");
else
printf("nullstr WAS NOT NULL!!\n");
PQclear(res);
PQfinish(conn);
return 0;
}
---------------------------(end of broadcast)--------------------------- TIP 5: don't forget to increase your free space map settings
