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 = (4 bytes) 1
* t = (11 bytes) 'joe's place'
* b = (5 bytes) 700508110938526354
*
* tuple 0: got
* i = (4 bytes) 2
* t = (8 bytes) 'ho there'
* b = (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 = PQfnumber(res, "i");
t_fnum = PQfnumber(res, "t");
b_fnum = PQfnumber(res, "b");
for (i = 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 = PQgetvalue(res, i, i_fnum);
tptr = PQgetvalue(res, i, t_fnum);
bptr = 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 = ntohl(*((uint32_t *) iptr));
bval = *((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 = (%d bytes) %d\n",
PQgetlength(res, i, i_fnum), ival);
printf(" t = (%d bytes) '%s'\n",
PQgetlength(res, i, t_fnum), tptr);
printf(" b = (%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;
/*
* If the user supplies a parameter on the command line, use it as the
* conninfo string; otherwise default to setting dbname=postgres and
using
* environment variables or defaults for all other connection
parameters.
*/
if (argc > 1)
conninfo = argv[1];
else
conninfo = "dbname = postgres";
/* Make a connection to the database */
conn = PQconnectdb(conninfo);
/* Check to see that the backend connection was successfully made */
if (PQstatus(conn) != 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] = "joe's place";
res = PQexecParams(conn,
"SELECT * FROM test1 WHERE t = $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) != 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 = htonl((uint32_t) 2);
/* Populate outdata */
outdata.ti1 = 1;
outdata.ti2 = 0;
outdata.ti3 = 20; /* array contains int8 OID=20 */
outdata.ti4 = 2; /* nelems */
outdata.ti5 = 1; /* lbound */
outdata.ti6 = 8; /* sizeof(int8) */
outdata.da1 = 700508110938526354ll;
outdata.ti7 = 8; /* sizeof(int8) */
outdata.da2 = -8679563850315317972ll;
/* Set up parameter arrays for PQexecParams */
paramTypes[0] = 1016; /* int8[] OID */
paramValues[0] = (char *) &outdata;
paramLengths[0] = sizeof(outdata);
paramFormats[0] = 1; /* binary */
res = PQexecParams(conn,
"SELECT * FROM lookup_tokens($1)",
1, /* one param */
paramTypes, /* int8[] OID */
paramValues,
paramLengths,
paramFormats,
1); /* ask for binary
results */
if (PQresultStatus(res) != 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=4 -I /my/postgresql/include native_binarray_dspam.c
-L/my/postgresql/lib -R/my/postgresql/lib -lpq
I needed to use the "-fpack-struct=4" option to keep the alignment
from 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:
>
> -------- Original-Nachricht --------
> > Datum: Sun, 12 Sep 2010 01:52:04 +0400
> > Von: Dmitriy Igrishin <[email protected]>
> > An: Steve <[email protected]>
> > CC: [email protected]
> > Betreff: Re: [SQL] Question about PQexecParams
>
> > Hey Steve,
> >
> > 2010/9/11 Steve <[email protected]>
> >
> > > 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 want
> > 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 = htonl(value1);
> > > paramValues[0] = (char *)&ivalue1;
> > > paramTypes[0] = INT4OID;
> > > paramLengths[0] = sizeof(ivalue1);
> > > paramFormats[0] = 1;
> > >
> > > int32_t ivalue2 = htonl(value2);
> > > paramValues[1] = (char *)&ivalue2;
> > > paramTypes[1] = INT4OID;
> > > paramLengths[1] = sizeof(ivalue2);
> > > paramFormats[1] = 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.
> >
> > >
> > > Probably I have to set the query to be:
> > > SELECT * FROM lookup_data($1,$2,{$3})
> > >
> > Incorrect.
> >
> > >
> > > Or:
> > > SELECT * FROM lookup_data($1,$2,$3)
> > >
> > Correct.
> >
> Thanks.
>
>
> > 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 type),
> > e.g.
> > SELECT * FROM lookup_data($1, $2, $3::bigint[])
> >
> Thanks.
>
>
> > > 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 and
> > > paramLengths to be sizeof one value multiplied by the amount of elements
> > in
> > > the array.
> > >
> > Please note, that in this case, you must pass to paramValues[2] a textual
> > 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.
> >
> 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 to
> 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 typeid.
> In my case INT8OID aka 20)) and then followed by the content of the array.
> And here I have a problem. I don't know how that data following the header
> should look like? I think that each value is in a block of 8 bytes (converted
> 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 blocks and
> adds so
> mething they call "dims" and "lbound". I have no clue what that is? I think
> the PostgreSQL function "array_recv()" is responsible for the format but I
> can not find any documentation about the format of a binary array
> representation. Maybe you know a place where I can read about how to send an
> array of int64_t to the PostgreSQL backend in binary?
>
> 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 btw
> make the task ultra easy).
>
> 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?
>
>
> >
> > >
> > > 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.
> >
> Yes. You helped me a bit. But I am still not there where I want/need to be.
>
>
> > Regards,
> > Dmitriy
> >
> // Steve
> --
> Neu: GMX De-Mail - Einfach wie E-Mail, sicher wie ein Brief!
> Jetzt De-Mail-Adresse reservieren: http://portal.gmx.net/de/go/demail
>
> --
> Sent via pgsql-sql mailing list ([email protected])
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-sql
>
--
Sent via pgsql-sql mailing list ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql
