Re: Using libpq, floats and binary data

zz

On Wed, Sep 15, 2010 at 12:02 PM, Michael Hull
<mikehulluk(at)googlemail(dot)com> wrote:
> Hi Guys,
> I have jsut started to use the libpq interface to postgre, and was
> wondering if someone with some experience could explain the 'best
> practices' :)
>
> I am preparing a query, then submitting it with binary data. 6 values
> are ints, and one is a float.
>
> For the ints, I understand that since I need network byte ordering, I
> have to pump them through htonl()
> The same is also true for the floats I assume, so I have dones some
> dirty,dirty casting, but this feels like a source for problems later
> on.
> It does the correct thing, but is there a better way of doing it??
>
>
> Mike
>
>
> char qString[] = "INSERT INTO raw_results (ca,kf,ks,lk,na,iinj,
> AP1height) VALUES( $1::int4, $2::int4, $3::int4, $4::int4, $5::int4,
> $6::int4, $7::float4)";
>
> PGresult* pPreparedQuery = PQprepare(pDB, "InsertQ",qString, nParams, NULL);
>
>
>
>
> float AP1Height = 3.141;
> /* TROUBLE AHEAD! */
> if( sizeof( float) != sizeof(int32_t) ) throw MyException();
> int32_t AP_int = * ( (int32_t*) &AP1Height );
> int32_t AP_intSwapped = htonl(AP_int);
> float AP1HeightSwapped = *( (float*) &AP_intSwapped);
> /* TROUBLE OVER */
>
>
> int32_t ca= htonl(100);
> int32_t kf= htonl(200);
> int32_t ks= htonl(300);
> int32_t lk= htonl(400);
> int32_t na= htonl(500);
> int32_t iinj= htonl(600);
>
> const char* values[nParams] = {
>                                (char*)&ca,
>                                (char*)&kf,
>                                (char*)&ks,
>                                (char*)&lk,
>                                (char*)&lk,
>                                (char*)&iinj,
>                                (char*)&AP1HeightSwapped,
>                        };
> int lengths[nParams] = {
>                sizeof(ca),
>                sizeof(kf),
>                sizeof(ks),
>                sizeof(lk),
>                sizeof(na),
>                sizeof(iinj),
>                sizeof(AP1Height),
>                };
>
> int binary[nParams]={1,1,1,1,1, 1,1};
>
> PGresult* res = PQexecPrepared( pDB, "InsertQ", nParams, values,
> lengths,binary,0);

there is a much better way -- libpqtypes:
http://libpqtypes.esilo.com/

char qString[] = "INSERT INTO raw_results
(ca,kf,ks,lk,na,iinj,AP1height) VALUES( %int4, %int4, %int4, %int4,
%int4, %int4, %float4)";

res = PQexecf(conn, qString, ca, kf, ks, lk, na, iinj, AP1Height);

getting data out is similarly easy.

merlin