Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 57C489FB43B for ; Mon, 9 Jul 2007 19:46:12 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 56947-10 for ; Mon, 9 Jul 2007 19:46:08 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from web32613.mail.mud.yahoo.com (web32613.mail.mud.yahoo.com [68.142.207.240]) by postgresql.org (Postfix) with SMTP id 4E1B79FB430 for ; Mon, 9 Jul 2007 19:46:08 -0300 (ADT) Received: (qmail 88120 invoked by uid 60001); 9 Jul 2007 22:46:07 -0000 DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=s1024; d=yahoo.com; h=X-YMail-OSG:Received:Date:From:Subject:To:In-Reply-To:MIME-Version:Content-Type:Content-Transfer-Encoding:Message-ID; b=4OwS0B4Vksj9P0U/GryD9zUCd5lh1yoiGOvF+KgJRoGn/FIJlvThLkV/YVOMQ4T39g1wfB2aXNV7YAX/dZ5K9tiWS8h7fMmvLgL4kPlNMJ/UiTK5uxEWfHhtqoL+h5r1mpZqx6BrAE6x7ID2ng0jv5Q23edCSp9ig5vFPZO6620=; X-YMail-OSG: GZm40bIVM1ndIyBmZ2hiLgsRTY2YcqNpf1UqNMvj6T9kvwxWikBdT.5J9UH609anRphUJsntQVroUQpcE34waSWZlnU2xlkgyTl0 Received: from [71.6.39.21] by web32613.mail.mud.yahoo.com via HTTP; Mon, 09 Jul 2007 15:46:06 PDT Date: Mon, 9 Jul 2007 15:46:06 -0700 (PDT) From: Richard Frankland Subject: Re: libpq: PQexecParams, binaryFormat and float To: "Sun, Way" , pgsql-interfaces@postgresql.org In-Reply-To: <7FBC16D72C0ABF45A88EAEB9ED3464C48CC172@sausatlexch01.corp.sa.net> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="0-1778365493-1184021166=:69903" Content-Transfer-Encoding: 8bit Message-ID: <959570.69903.qm@web32613.mail.mud.yahoo.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.757 tagged_above=0 required=5 tests=BAYES_00=-2.599, DNS_FROM_RFC_ABUSE=0.2, DNS_FROM_RFC_POST=1.708, DNS_FROM_RFC_WHOIS=1.447, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200707/1 X-Sequence-Number: 6585 --0-1778365493-1184021166=:69903 Content-Type: text/plain; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Hi, This is my first reply/post to postgres forums. I hope I'm doing it OK. If not, please help. So, onto how I see the problem and a possible solution. As Tom Lane pointed out. Using a "union" is another solution to this problem. Here is a simple testing program and output: #include /* for ntohl/htonl */ #include void show_hex (void * vptr) { unsigned char * xptr; int k; /* display each byte in heX */ xptr = (unsigned char *)vptr; for (k=0; k<4; k++) { printf("%.2X ", *(xptr+k)); } printf("\n"); } int main(int argc, char **argv) { float starting_value = 1.23; float loadavg; unsigned long reversed_float; /* prove we really are starting with 1.23 */ printf( "%f \n", starting_value); show_hex(&starting_value); /* see man page for return type of htonl() */ reversed_float = htonl(starting_value); show_hex(&reversed_float); /* implicit conversion shown explicitly (i.e. has no effect) */ reversed_float = htonl((long)starting_value); show_hex(&reversed_float); /* and another implicit conversion */ loadavg = reversed_float; //htonl(starting_value); show_hex(&loadavg); /* again, shown explicitly */ loadavg = (float)(unsigned long)16777216; //htonl(starting_value); show_hex(&loadavg); loadavg = (float)(unsigned long)htonl((long)starting_value); //htonl(starting_value); show_hex(&loadavg); /* show what was stored into loadavg */ printf( "%f \n", loadavg); /* show this is the same as the original problem */ loadavg = htonl(1.23); printf( "%f \n", loadavg); /* A (not the only one) correct solution */ /* Casting a pointer: */ /* address of source */ /* | */ /* pointer to UNSIGNED type | */ /* | | */ /* dereference pointer | | */ /* | | | */ /* | | | */ /* | | | */ reversed_float = htonl(*(unsigned long *)&starting_value); show_hex(&reversed_float); /* * paramValues[0] = (unsigned char *) &reversed_float; */ } ------------------------------------------------------ $ ./tst 1.230000 A4 70 9D 3F 00 00 00 01 00 00 00 01 00 00 80 4B 00 00 80 4B 16777216.000000 16777216.000000 3F 9D 70 A4 $ "Sun, Way" wrote: Hi, I'm having trouble inserting a float into pg using binary format. Hope someone on the list can help me point out what I'm doing wrong.... float loadavg; loadavg = htonl(1.23); paramValues[0] = (char *) &loadavg paramLengths[0] = sizeof(loadavg); paramFormats[0] = 1; res = PQexecParams(conn, "INSERT INTO cpu (loadavg) VALUES ($1::real)", 1, NULL, paramValues, paramLengths, paramFormats, 1); From psql, select returns the value as 1.20765e-38 instead of 1.23.....:( Thanks Way --------------------------------- - - - - - Appended by Scientific Atlanta, a Cisco company - - - - - This e-mail and any attachments may contain information which is confidential, proprietary, privileged or otherwise protected by law. The information is solely intended for the named addressee (or a person responsible for delivering it to the addressee). If you are not the intended recipient of this message, you are not authorized to read, print, retain, copy or disseminate this message or any part of it. If you have received this e-mail in error, please notify the sender immediately by return e-mail and delete it from your computer. --------------------------------- Boardwalk for $500? In 2007? Ha! Play Monopoly Here and Now (it's updated for today's economy) at Yahoo! Games. --0-1778365493-1184021166=:69903 Content-Type: text/html; charset=iso-8859-1 Content-Transfer-Encoding: 8bit Hi, This is my first reply/post to postgres forums. I hope I'm doing it OK.
If not, please help.

So, onto how I see the problem and a possible solution.
As Tom Lane pointed out. Using a "union" is another solution to this problem.

Here is a simple testing program and output:

#include <stdio.h>
/* for ntohl/htonl */
#include <netinet/in.h>

void show_hex (void * vptr) {
    unsigned char * xptr;
    int k;
    /* display each byte in heX */
    xptr = (unsigned char *)vptr;
    for (k=0; k<4; k++)
    {
        printf("%.2X  ", *(xptr+k));
    }
    printf("\n");
}

int
main(int argc, char **argv)
{
    float         starting_value = 1.23;
    float         loadavg;
    unsigned long reversed_float;

    /* prove we really are starting with 1.23 */
    printf( "%f \n", starting_value);
    show_hex(&starting_value);

    /* see man page for return type of htonl() */
    reversed_float = htonl(starting_value);
    show_hex(&reversed_float);
   
    /* implicit conversion shown explicitly (i.e. has no effect) */
    reversed_float = htonl((long)starting_value);
    show_hex(&reversed_float);
   
    /* and another implicit conversion */
    loadavg = reversed_float; //htonl(starting_value);
    show_hex(&loadavg);

    /* again, shown explicitly */
    loadavg = (float)(unsigned long)16777216; //htonl(starting_value);
    show_hex(&loadavg);
    loadavg = (float)(unsigned long)htonl((long)starting_value); //htonl(starting_value);
    show_hex(&loadavg);
    /* show what was stored into loadavg */
    printf( "%f \n", loadavg);

    /* show this is the same as the original problem */
    loadavg = htonl(1.23);
    printf( "%f \n", loadavg);

    /* A (not the only one) correct solution                   */
    /* Casting a pointer:                                      */
    /*                       address of source                 */
    /*                                       |                 */
    /* pointer to UNSIGNED type              |                 */
    /*                       |               |                 */
    /*   dereference pointer |               |                 */
    /*                     | |               |                 */
    /*                     | |               |                 */
    /*                     | |               |                 */
    reversed_float = htonl(*(unsigned long *)&starting_value);

    show_hex(&reversed_float);
   
    /*
     *   paramValues[0] = (unsigned char *) &reversed_float;
     */
}

------------------------------------------------------
$ ./tst
1.230000
A4  70  9D  3F 
00  00  00  01 
00  00  00  01 
00  00  80  4B 
00  00  80  4B 
16777216.000000
16777216.000000
3F  9D  70  A4 
$




"Sun, Way" <Way.Sun@sciatl.com> wrote:
Hi,
I'm having trouble inserting a float into pg using binary format.
Hope someone on the list can help me point out what I'm doing wrong....
    float    loadavg;
    loadavg = htonl(1.23);
    paramValues[0] = (char *) &loadavg
    paramLengths[0] = sizeof(loadavg);
    paramFormats[0] = 1;       
    res = PQexecParams(conn,
                       "INSERT INTO cpu (loadavg) VALUES ($1::real)",
                       1,       
                       NULL,    
                       paramValues,
                       paramLengths,
                       paramFormats,
                       1);     
From psql, select returns the value as 1.20765e-38 instead of 1.23.....:(
Thanks
Way
      - - - - - Appended by Scientific Atlanta, a Cisco company - - - - -         
This e-mail and any attachments may contain information which is confidential,
proprietary, privileged or otherwise protected by law. The information is solely
intended for the named addressee (or a person responsible for delivering it to
the addressee). If you are not the intended recipient of this message, you are
not authorized to read, print, retain, copy or disseminate this message or any
part of it. If you have received this e-mail in error, please notify the sender
immediately by return e-mail and delete it from your computer.

Boardwalk for $500? In 2007? Ha!
Play Monopoly Here and Now (it's updated for today's economy) at Yahoo! Games. --0-1778365493-1184021166=:69903--