Re: Question about PQexecParams - Mailing list pgsql-sql

-------- 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