libpq binary transfer of the numeric data type - II - Mailing list pgsql-interfaces

Hi! 

Has anyone had success with fetching numeric data in binary format?
I tried to follow Tom's advice below and try to reverse engineer from 
recv/send function. It seemes the end structure that user will 
get is of Numeric type (see struct below)

typedef struct NumericData
{SE_INT32    varlen;    /* Variable size (std varlena header) */SE_INT16    n_weight;    /* Weight of 1st digit
*/USHORT   n_sign_dscale;    /* Sign + display scale */char        n_data[1];        /* Digits (really array of
 
NumericDigit) */
} NumericData;

typedef NumericData *Numeric; 

However when I cast the result from PQgetValue() to a Numeric structure
I don't seem to get the right value in structure. Varlen is usually
512 and dscale 768. Also the n_data doesn't seem to have right values.
I admit I am not too familiar with the conversion process but I am 
trying follow the same logic that is there in numeric.c that converts
NumericVar data to a string before convering to double value. Am I
missing something here?

I cannot fetch the numeric column in text format since I have a
binary column in the same query and fetching binary data in text
format doesn't work very well as it truncates the data when it 
sees a null terminator. Doc mentions that even though underlying 
protocol supports fetching individual columns in different format 
there is no provision for it right now. Is there any work around 
to this limitation? If that is possible I will be more than happy 
to fetch numeric data only in text format.

warm regards,
Brijesh Shrivastav



-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, June 14, 2004 12:37 PM
To: Eliot Simcoe
Cc: pgsql-interfaces@postgresql.org
Subject: Re: [INTERFACES] libpq binary transfer of the numeric data type



Eliot Simcoe <esimcoe@mac.com> writes:
> it has been a struggle using the binary I/O method supported by 
> PQexecParams() for both parameter passing and result retrieval due to 
> the lack of documentation on the internal data representations used by 
> PostgreSQL,

As of 7.4 the "binary" representation used on-the-wire is NOT
necessarily the same as the server internal representation; it is
big-endian for one thing, and the layout is simplified where possible.
The documentation is still somewhat lacking; best bet is to read the
source code for the send/recv routines for the data types you are
interested in.

> and after reading a number of posts from 1999 frowning upon 
> its usage I am aware of the issues associate with this approach.

Anything dated 1999 is not very relevant to the approach we are actually
using today.

> Could someone please explain to me what the varlen field is used for? 

varlen is not present in any of the 7.4 on-the-wire formats.  According
to numeric_recv, * External format is a sequence of int16's: * ndigits, weight, sign, dscale, NumericDigits.

Some other relevant comments are
* The value represented by a NumericVar is determined by the sign, weight,* ndigits, and digits[] array.* Note: the
firstdigit of a NumericVar's value is assumed to be multiplied* by NBASE ** weight.    Another way to say it is that
thereare weight+1* digits before the decimal point.  It is possible to have weight < 0.
 
* dscale, or display scale, is the nominal precision expressed as number* of digits after the decimal point (it must
alwaysbe >= 0 at present).* dscale may be more than the number of physically stored fractional
 
digits,* implying that we have suppressed storage of significant trailing zeroes.* It should never be less than the
numberof stored digits, since that
 
would* imply hiding digits that are present.  NOTE that dscale is always
expressed* in *decimal* digits, and so it may correspond to a fractional number of* base-NBASE digits --- divide by
DEC_DIGITSto convert to NBASE digits.
 

Personally I would suggest using text format instead of binary, at least
for "numeric"-type values.  There really is not enough gain from dealing
with the binary format to justify doing the work and dealing with
possible future incompatibilities.  Binary format makes sense to me for
simple integers and maybe for floats.
        regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?
              http://www.postgresql.org/docs/faqs/FAQ.html