Thread: Question about PQexecParams
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 howto 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}) Probably I have to set the query to be: SELECT * FROM lookup_data($1,$2,{$3}) Or: SELECT * FROM lookup_data($1,$2,$3) 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 paramValuesto be an array and paramLengths to be sizeof one value multiplied by the amount of elements in the array. 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? // Steve -- GMX DSL SOMMER-SPECIAL: Surf & Phone Flat 16.000 für nur 19,99 Euro/mtl.!* http://portal.gmx.net/de/go/dsl
Hey Steve,
2010/9/11 Steve <steeeeeveee@gmx.net>
Regards,
Dmitriy
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. 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[])
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[])
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.
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.
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.
Regards,
Dmitriy
-------- 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 > Hey Steve, > > 2010/9/11 Steve <steeeeeveee@gmx.net> > > > 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 haveto add a special header to the array and do off course that host to network translation and then I can send the arrayin binary. Unfortunately I can not find enough information about the format of the whole header + array. The headerlooks to be easy to create (just 3 times 4 bytes for 1) number of dimensions (aka ndims), 2) if the array has nullelements (aka hassnull), 3) array element oid (aka typeid. In my case INT8OID aka 20)) and then followed by the contentof the array. And here I have a problem. I don't know how that data following the header should look like? I thinkthat each value is in a block of 8 bytes (converted from host to network). But I am not sure (the examples I have seenare all for int4 and not for bigint). I am confused by the two examples I have found so far. One of them is dividingthose 8 bytes into two 4 bytes blocks and adds something they call "dims" and "lbound". I have no clue what thatis? I think the PostgreSQL function "array_recv()" is responsible for the format but I can not find any documentationabout the format of a binary array representation. Maybe you know a place where I can read about how to sendan 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/canlibpqtypes (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 structrepresenting 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
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 libpqfunctions.** 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 testversion 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]* fromgenerate_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 */intti5; /* lbound of array */int ti6; /* size of first int8 */long long da1; /* first long long element */int ti7; /* sizeof 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. Wesplit 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 byteorder. */ 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 byteto 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 settingdbname=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 transmissionof 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 bycasting 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 <dmitigr@gmail.com> > > An: Steve <steeeeeveee@gmx.net> > > CC: pgsql-sql@postgresql.org > > Betreff: Re: [SQL] Question about PQexecParams > > > Hey Steve, > > > > 2010/9/11 Steve <steeeeeveee@gmx.net> > > > > > 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 haveto add a special header to the array and do off course that host to network translation and then I can send the arrayin binary. Unfortunately I can not find enough information about the format of the whole header + array. The headerlooks to be easy to create (just 3 times 4 bytes for 1) number of dimensions (aka ndims), 2) if the array has nullelements (aka hassnull), 3) array element oid (aka typeid. In my case INT8OID aka 20)) and then followed by the contentof the array. And here I have a problem. I don't know how that data following the header should look like? I thinkthat each value is in a block of 8 bytes (converted from host to network). But I am not sure (the examples I have seenare all for int4 and not for bigint). I am confused by the two examples I have found so far. One of them is dividingthose 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()" isresponsible for the format but I can not find any documentation about the format of a binary array representation. Maybeyou 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/canlibpqtypes (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 correctstruct 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 (pgsql-sql@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-sql >
Hey Kenneth,<br /><br />Thank you for solution. But sorry, personally, I don't clearly<br />understand the benefits of thiscode compared with using<br />simple array literals or even array constructors...<br />Conversion "overheads" from text?Doubtfully...<br clear="all" /><br />-- <br />// Dmitriy.<br /><br /><br />
On Thu, Sep 30, 2010 at 10:30:16PM +0400, Dmitriy Igrishin wrote: > Hey Kenneth, > > Thank you for solution. But sorry, personally, I don't clearly > understand the benefits of this code compared with using > simple array literals or even array constructors... > Conversion "overheads" from text? Doubtfully... > > -- > // Dmitriy. There are three benefits: - reduces the CPU overhead in both the client and the DB server for converting to/from ASCII numbers - decreases the amount of network traffic by more than 1/2 - binary transmission of query parameters helps protect against SQL injection attacks Obviously, this is not as important for low performance systems, with the possible exception of preventing SQL injection attacks, but on high performance systems, minimizing the CPU overhead due to data conversions is very useful indeed. Whether or not someone chooses to use it would need to be evaluated on a case-by-case basis. I posted the code because the details on how to use binary array transmission needed close examination of the documentation as well as the database sources. If you are using PostgreSQL 8.3 or higher, and can use an add on library, libpqtypes provides a very nice and very useful API for managing binary parameter transmission. I really hope that it could be included in the core PostgreSQL to help others avoid the need to troll through the grotty inner-workings of the database to figure out how to do this using just libpq. Cheers, Ken
Hey Kenneth,
--
// Dmitriy.
There are three benefits:
- reduces the CPU overhead in both the client and the DB server
for converting to/from ASCII numbers
You solution based on PL/pgSQL function. I am doubt that execution
of PL/pgSQL parser (loadable module, which aren't built into the
PostgreSQL server) would be cheaper than such conversion.
Furthermore, what if I want to SELECT from table which contains
fields of different types, including arrays? How proposed PL/pgSQL
function (which returns SETOF) can be using in this case?
of PL/pgSQL parser (loadable module, which aren't built into the
PostgreSQL server) would be cheaper than such conversion.
Furthermore, what if I want to SELECT from table which contains
fields of different types, including arrays? How proposed PL/pgSQL
function (which returns SETOF) can be using in this case?
- decreases the amount of network traffic by more than 1/2
Agree.
- binary transmission of query parameters helps protect against
SQL injection attacks
PQexecParams() was invented to protect users from SQL injections
by separating parameters from SQL command and escaping then
as well. Furthermore, there is no way to execute more than one
command with PQexecParams() (according to the doc, this provides
an extra protection from SQL injections! :-)
by separating parameters from SQL command and escaping then
as well. Furthermore, there is no way to execute more than one
command with PQexecParams() (according to the doc, this provides
an extra protection from SQL injections! :-)
Obviously, this is not as important for low performance systems,
with the possible exception of preventing SQL injection attacks,
but on high performance systems, minimizing the CPU overhead
due to data conversions is very useful indeed.
On the other hand, programming of such binary transmissions, IMO,
it very tedious and error prone that is not acceptable for highly reliable
systems.
it very tedious and error prone that is not acceptable for highly reliable
systems.
Whether or not someone chooses to use it would need to be
evaluated on a case-by-case basis. I posted the code because
the details on how to use binary array transmission needed
close examination of the documentation as well as the database
sources. If you are using PostgreSQL 8.3 or higher, and can
use an add on library, libpqtypes provides a very nice and
very useful API for managing binary parameter transmission.
I really hope that it could be included in the core PostgreSQL
to help others avoid the need to troll through the grotty
inner-workings of the database to figure out how to do this
using just libpq.
Thanks for sharing!
--
// Dmitriy.