Re: fast read of binary data

On 22.11.2012 09:54, Eildert Groeneveld wrote:
> ok, I did have a look at the libpq librar, and you are right, there is a
> way to obtain binary data from the backend through the  PQexecParams
>
>      res = PQexecParams(conn,
>                         "DECLARE myportal CURSOR FOR select genotype_bits
> from v_genotype_data",
>                         0,       /* zero 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 */
>
> genotype_bits is defined as bit varying in the backend. When writing the
> results:
>      for (i = 0; i<  PQntuples(res); i++)
>      {
>          for (j = 0; j<  nFields; j++)
>        fwrite(PQgetvalue(res, i, j),100000,1,f);
>      }
>
> it is clear that the results are NOT in binary format:
> eg(eno,snp): od -b junk |head
> 0000000 061 060 061 060 061 060 061 060 061 060 061 060 061 060 061 060

You need to ask for binary results when you execute the FETCH
statements. Asking for binary results on the DECLARE CURSOR statement
has no effect, as DECLARE CURSOR doesn't return any results; it's the
FETCH that follows that returns the result set.

- Heikki