Date: Thu, 30 Sep 2010 12:12:54 -0500
From: Kenneth Marshall <ktm@rice.edu>
To: Steve <steeeeeveee@gmx.net>
Cc: pgsql-sql@postgresql.org
Subject: Re: Question about PQexecParams
Message-ID: <20100930171253.GI14003@aart.is.rice.edu>
References: <20100911124423.75020@gmx.net>
	<AANLkTikZjB5M=CMVt-bNEnWS+bVN0MSdod_KZKKAZmnJ@mail.gmail.com>
	<20100922100715.123530@gmx.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
Content-Transfer-Encoding: quoted-printable
In-Reply-To: <20100922100715.123530@gmx.net>
User-Agent: Mutt/1.5.16 (2007-06-09)

Hi Steve,

I am going to follow-up to this post so that the results will
be available to other PostgreSQL users. Here is a simple
example program for binary transmission of an int8 array
using native libpq functions and not the libpqtypes:

-----------native_binarray_dspam.c------------------
/*
 *
 *
 * native_binarray_dspam.c
 *		Test out-of-line parameters and binary I/O for dspam using
 *              native libpq functions.
 *
 * Before running this, populate a database with the following commands
 * (provided in src/test/examples/dspam.sql):
 *
 * CREATE TABLE test1 (i int4, t text, b bigint);
 *
 * INSERT INTO test1 values (1, 'joe''s place', 700508110938526354);
 * INSERT INTO test1 values (2, 'ho there', -8679563850315317972);
 *
 * The expected output is:
 *
 * tuple 0: got
 *	i =3D (4 bytes) 1
 *	t =3D (11 bytes) 'joe's place'
 *	b =3D (5 bytes) 700508110938526354
 *
 * tuple 0: got
 *	i =3D (4 bytes) 2
 *	t =3D (8 bytes) 'ho there'
 *	b =3D (5 bytes) -8679563850315317972
 *
 *  Here is the code for the test version of lookup_tokens():
 *
 * create function lookup_tokens(bigint[])
 *  returns setof test1
 *  language plpgsql stable
 * as '
 * declare
 *   v_rec record;
 * begin
 *   for v_rec in select * from test1
 *     where b in (select $1[i]
 *         from generate_series(array_lower($1,1),array_upper($1,1)) s(i))
 *   loop
 *     return next v_rec;
 *   end loop;
 *   return;
 * end;';
 *
 */
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sys/types.h>
#include "libpq-fe.h"

/* for ntohl/htonl */
#include <netinet/in.h>
#include <arpa/inet.h>

typedef struct testbinary
{
	int ti1; /* ndims for array */
	int ti2; /* array has NULLs */
	int ti3; /* OID for int8 */
	int ti4; /* numelem in array */
	int ti5; /* lbound of array */
	int ti6; /* size of first int8 */
	long long da1; /* first long long element */
	int ti7; /* size of second int8 */
	long long da2; /* second long long element */
} testbinary;

static void
exit_nicely(PGconn *conn)
{
	PQfinish(conn);
	exit(1);
}

/*
 * This function prints a query result that is a binary-format fetch from
 * a table defined as in the comment above.  We split it out because the
 * main() function uses it twice.
 */
static void
show_binary_results(PGresult *res)
{
	int			i,
				j;
	int			i_fnum,
				t_fnum,
				b_fnum;

	/* Use PQfnumber to avoid assumptions about field order in result */
	i_fnum =3D PQfnumber(res, "i");
	t_fnum =3D PQfnumber(res, "t");
	b_fnum =3D PQfnumber(res, "b");

	for (i =3D 0; i < PQntuples(res); i++)
	{
		char	   *iptr;
		char	   *tptr;
		char	   *bptr;
		long long			bval;
		int			blen;
		int			ival;

		/* Get the field values (we ignore possibility they are null!) */
		iptr =3D PQgetvalue(res, i, i_fnum);
		tptr =3D PQgetvalue(res, i, t_fnum);
		bptr =3D PQgetvalue(res, i, b_fnum);

		/*
		 * The binary representation of INT4 is in network byte order, which
		 * we'd better coerce to the local byte order.
		 */
		ival =3D ntohl(*((uint32_t *) iptr));
		bval =3D *((long long *) bptr);

		/*
		 * The binary representation of TEXT is, well, text, and since libpq
		 * was nice enough to append a zero byte to it, it'll work just fine
		 * as a C string.
		 *
		 * The binary representation of BIGINT is a long long.
		 */
		printf("tuple %d: got\n", i);
		printf(" i =3D (%d bytes) %d\n",
			   PQgetlength(res, i, i_fnum), ival);
		printf(" t =3D (%d bytes) '%s'\n",
			   PQgetlength(res, i, t_fnum), tptr);
		printf(" b =3D (%d bytes) %lld\n",
			   PQgetlength(res, i, b_fnum), bval);
	}
}

int
main(int argc, char **argv)
{
	const char *conninfo;
	PGconn	   *conn;
	PGresult   *res;
	const char *paramValues[1];
	int			paramTypes[1];
	int			paramLengths[1];
	int			paramFormats[1];
	uint32_t	binaryIntVal;
	testbinary	outdata;
=09

	/*
	 * If the user supplies a parameter on the command line, use it as the
	 * conninfo string; otherwise default to setting dbname=3Dpostgres and usi=
ng
	 * environment variables or defaults for all other connection parameters.
	 */
	if (argc > 1)
		conninfo =3D argv[1];
	else
		conninfo =3D "dbname =3D postgres";

	/* Make a connection to the database */
	conn =3D PQconnectdb(conninfo);

	/* Check to see that the backend connection was successfully made */
	if (PQstatus(conn) !=3D CONNECTION_OK)
	{
		fprintf(stderr, "Connection to database failed: %s",
				PQerrorMessage(conn));
		exit_nicely(conn);
	}

	/*
	 * The point of this program is to illustrate use of PQexecParams() with
	 * out-of-line parameters, as well as binary transmission of data.
	 *
	 * This first example transmits the parameters as text, but receives the
	 * results in binary format.  By using out-of-line parameters we can avoid
	 * a lot of tedious mucking about with quoting and escaping, even though
	 * the data is text.  Notice how we don't have to do anything special with
	 * the quote mark in the parameter value.
	 */

	/* Here is our out-of-line parameter value */
	paramValues[0] =3D "joe's place";

	res =3D PQexecParams(conn,
					   "SELECT * FROM test1 WHERE t =3D $1",
					   1,		/* one param */
					   NULL,	/* let the backend deduce param type */
					   paramValues,
					   NULL,	/* don't need param lengths since text */
					   NULL,	/* default to all text params */
					   1);		/* ask for binary results */

	if (PQresultStatus(res) !=3D PGRES_TUPLES_OK)
	{
		fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));
		PQclear(res);
		exit_nicely(conn);
	}

	show_binary_results(res);

	PQclear(res);

	/*
	 * In this second example we transmit an integer parameter in binary form,
	 * and again retrieve the results in binary form.
	 *
	 * Although we tell PQexecParams we are letting the backend deduce
	 * parameter type, we really force the decision by casting the parameter
	 * symbol in the query text.  This is a good safety measure when sending
	 * binary parameters.
	 */

	/* Convert integer value "2" to network byte order */
	binaryIntVal =3D htonl((uint32_t) 2);

	/* Populate outdata */
	outdata.ti1 =3D 1;
	outdata.ti2 =3D 0;
	outdata.ti3 =3D 20; /* array contains int8 OID=3D20 */
	outdata.ti4 =3D 2;  /* nelems */
	outdata.ti5 =3D 1;  /* lbound */
	outdata.ti6 =3D 8;  /* sizeof(int8) */
	outdata.da1 =3D 700508110938526354ll;
	outdata.ti7 =3D 8;  /* sizeof(int8) */
	outdata.da2 =3D -8679563850315317972ll;

	/* Set up parameter arrays for PQexecParams */
	paramTypes[0] =3D 1016; /* int8[] OID */
	paramValues[0] =3D (char *) &outdata;
	paramLengths[0] =3D sizeof(outdata);
	paramFormats[0] =3D 1;		/* binary */

	res =3D PQexecParams(conn,
					   "SELECT * FROM lookup_tokens($1)",
					   1,		/* one param */
					   paramTypes,	/* int8[] OID */
					   paramValues,
					   paramLengths,
					   paramFormats,
					   1);		/* ask for binary results */

	if (PQresultStatus(res) !=3D PGRES_TUPLES_OK)
	{
		fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));
		PQclear(res);
		exit_nicely(conn);
	}

	show_binary_results(res);

	PQclear(res);

	/* close the connection to the database and cleanup */
	PQfinish(conn);

	return 0;
}
-----------native_binarray_dspam.c------------------

Here is the command that I used to compile/link the program:

gcc -fpack-struct=3D4  -I /my/postgresql/include native_binarray_dspam.c -L=
/my/postgresql/lib -R/my/postgresql/lib -lpq

I needed to use the "-fpack-struct=3D4" option to keep the alignment
=66rom being padded in the structure to 8 bytes, which messed up the
binary encoding of the array. Please let me know if you have any
questions.

Regards,
Ken

On Wed, Sep 22, 2010 at 12:07:15PM +0200, Steve wrote:
>=20
> -------- Original-Nachricht --------
> > Datum: Sun, 12 Sep 2010 01:52:04 +0400
> > Von: Dmitriy Igrishin <dmitigr@gmail.com>
> > An: Steve <steeeeeveee@gmx.net>
> > CC: pgsql-sql@postgresql.org
> > Betreff: Re: [SQL] Question about PQexecParams
>=20
> > Hey Steve,
> >=20
> > 2010/9/11 Steve <steeeeeveee@gmx.net>
> >=20
> > > Hello list,
> > >
> > > I would like to call a function from my C application by using libpq =
and
> > > PQexecParams. My problem is that I don't know how to specify that I w=
ant
> > to
> > > send an array to the function.
> > >
> > > Assume the function is called lookup_data and takes the following
> > > parameters: lookup_data(integer,integer,bigint[])
> > >
> > > I would like to specify the OID with my query. How would I do that?
> > Assume
> > > I would like to query 3 values for bigint:
> > >
> > > const char *paramValues[5];
> > > Oid paramTypes[5];
> > > int paramLengths[5];
> > > int paramFormats[5];
> > >
> > > int32_t ivalue1 =3D htonl(value1);
> > > paramValues[0]  =3D (char *)&ivalue1;
> > > paramTypes[0]   =3D INT4OID;
> > > paramLengths[0] =3D sizeof(ivalue1);
> > > paramFormats[0] =3D 1;
> > >
> > > int32_t ivalue2 =3D htonl(value2);
> > > paramValues[1]  =3D (char *)&ivalue2;
> > > paramTypes[1]   =3D INT4OID;
> > > paramLengths[1] =3D sizeof(ivalue2);
> > > paramFormats[1] =3D 1;
> > >
> > > etc...
> > >
> > > How would I tell libpq that the next 3 values are an array of bigint?
> > >
> > > I tried to use INT8OID and specify the query like below but that did =
not
> > > work:
> > > SELECT * FROM lookup_data($1,$2,{$3,$4,$5})
> > >
> > Incorrect.
> >=20
> > >
> > > Probably I have to set the query to be:
> > > SELECT * FROM lookup_data($1,$2,{$3})
> > >
> > Incorrect.
> >=20
> > >
> > > Or:
> > > SELECT * FROM lookup_data($1,$2,$3)
> > >
> > Correct.
> >
> Thanks.
>=20
>=20
> > You may specify a data type by OID (1016 for bigint[],
> > please refer to
> > http://www.postgresql.org/docs/9.0/static/catalog-pg-type.html
> > to obtain information about types) or attach an explicit cast to a
> > parameter symbol to force treating it as bigint[] (or any specified typ=
e),
> > e.g.
> > SELECT * FROM lookup_data($1, $2, $3::bigint[])
> >=20
> Thanks.
>=20
>=20
> > > But what would I set for paramTypes? How can I say that the values are
> > an
> > > array of bigint? I assume that I can set paramValues to be an array a=
nd
> > > paramLengths to be sizeof one value multiplied by the amount of eleme=
nts
> > in
> > > the array.
> > >
> > Please note, that in this case, you must pass to paramValues[2] a textu=
al
> > representation
> > of bigint[], e.g. '{1,2,3}'.
> > Its not necessary to specify a length of text-format parameters (its
> > ignored).
> > The length is essential only if you transmit data in a binary format.
> >=20
> Really? I must use a textual representation of the array? Why?
> I searched the Internet up and down and as far as I can tell, there is a =
possibility to send the array in binary. I have to add a special header to =
the array and do off course that host to network translation and then I can=
 send the array in binary. Unfortunately I can not find enough information =
about the format of the whole header + array. The header looks to be easy t=
o create (just 3 times 4 bytes for 1) number of dimensions (aka ndims), 2) =
if the array has null elements (aka hassnull), 3) array element oid (aka ty=
peid. In my case INT8OID aka 20)) and then followed by the content of the a=
rray. And here I have a problem. I don't know how that data following the h=
eader should look like? I think that each value is in a block of 8 bytes (c=
onverted from host to network). But I am not sure (the examples I have seen=
 are all for int4 and not for bigint). I am confused by the two examples I =
have found so far. One of them is dividing those 8 bytes into two 4 bytes b=
locks and adds so
>  mething they call "dims" and "lbound". I have no clue what that is? I th=
ink the PostgreSQL function "array_recv()" is responsible for the format bu=
t I can not find any documentation about the format of a binary array repre=
sentation. Maybe you know a place where I can read about how to send an arr=
ay of int64_t to the PostgreSQL backend in binary?
>=20
> I know that I could go the textual representation path, but I really want=
 to send the data in binary. And I don't want/can libpqtypes (which would b=
tw make the task ultra easy).
>=20
> Maybe I can not see the forest because of the trees but I really can not =
find any documentation how to create a correct struct representing an array=
 datatype. Can it be that this part is not documented at all?
>=20
>=20
> >=20
> > >
> > > I am somehow lost and don't know how to call the function and pass an
> > array
> > > to libpq.
> > >
> > > Can any one help me with this?
> > >
> > Hope this helps.
> >=20
> Yes. You helped me a bit. But I am still not there where I want/need to b=
e.
>=20
>=20
> > Regards,
> > Dmitriy
> >
> // Steve
> --=20
> Neu: GMX De-Mail - Einfach wie E-Mail, sicher wie ein Brief! =20
> Jetzt De-Mail-Adresse reservieren: http://portal.gmx.net/de/go/demail
>=20
> --=20
> Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>=20