Thread: How do you convert PostgreSQL internal binary field to C datatypes

How do you convert PostgreSQL internal binary field to C datatypes

From
Jeff Lynn
Date:
Does someone out there has some example(s) of converting the 
PostgreSQL's internal (network byte order) representation of  float4, 
flost8, date, datetime and timestamp into Win32 float, double, CTime or 
any Win32 date/time construct under Intel x86 h/w architecture?

Andrew McNamara in previous post suggested that there are answers in the 
server source codes.  But for a beginner like me, even though I have 
extensive coding experience in C, C++ and Java, such undertaking for a 
significant product like PostgreSQL is a bit overwhelming for now.

I really do want to explore the usage of PostgreSQL further!  So any 
help will be greatly appreciated. 

Jeff,



Re: How do you convert PostgreSQL internal binary field to C datatypes

From
Alvaro Herrera
Date:
Jeff Lynn wrote:
> Does someone out there has some example(s) of converting the 
> PostgreSQL's internal (network byte order) representation of  float4, 
> flost8, date, datetime and timestamp into Win32 float, double, CTime or 
> any Win32 date/time construct under Intel x86 h/w architecture?
> 
> Andrew McNamara in previous post suggested that there are answers in the 
> server source codes.  But for a beginner like me, even though I have 
> extensive coding experience in C, C++ and Java, such undertaking for a 
> significant product like PostgreSQL is a bit overwhelming for now.
> 
> I really do want to explore the usage of PostgreSQL further!  So any 
> help will be greatly appreciated. 

The code to handle these datatypes lies mostly here:
http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/utils/adt/

For example, in float.c look for float4send which directs you to
pq_sendfloat4 which you can find in 

http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/libpq/pqformat.c

-- 
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.


I give up!  What ever data returned from the binary output of 
PGgetvalue() is, I don't know how to convert it into StringInfo, which 
is being used by a few routines that "seems" to convert internal binary 
format into float8.  By casting the returned data into StringInfo, 
crashes the function.

If PostgreSQL  support only a half baked C API, may as well don't bother.

So I gave up.   I will not use PostgreSQL until there is a simple way to 
extract data directly into C or C++ data type.  Is one expect to fetch 
data in text, then use text to int, text to double, text to date 
function before you can make any business logic works?

Thanks all of you trying to help.  Appreciated!

Jeff



Re: How do you convert PostgreSQL internal binary field to C datatypes

From
Reid Thompson
Date:
On Wed, 2007-05-23 at 11:47 -0400, Jeff Lynn wrote:
> I give up!  What ever data returned from the binary output of 
> PGgetvalue() is, I don't know how to convert it into StringInfo, which 
> is being used by a few routines that "seems" to convert internal binary 
> format into float8.  By casting the returned data into StringInfo, 
> crashes the function.
> 
> If PostgreSQL  support only a half baked C API, may as well don't bother.
> 
> So I gave up.   I will not use PostgreSQL until there is a simple way to 
> extract data directly into C or C++ data type.  Is one expect to fetch 
> data in text, then use text to int, text to double, text to date 
> function before you can make any business logic works?
> 
> Thanks all of you trying to help.  Appreciated!
> 
> Jeff
> 
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
> 
>                http://www.postgresql.org/docs/faq

I would think that using the info provided at these links should enable
what you wish....

LINK 1:

http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/libpq/pqformat.c?rev=1.45;content-type=text%2Fplain

/* --------------------------------*        pq_sendfloat4    - append a float4 to a StringInfo buffer** The point of
thisroutine is to localize knowledge of the external binary* representation of float4, which is a component of several
datatypes.**We currently assume that float4 should be byte-swapped in the same way* as int4.  This rule is not perfect
butit gives us portability across* most IEEE-float-using architectures.* --------------------------------*/
 
void
pq_sendfloat4(StringInfo buf, float4 f)
{union{    float4        f;    uint32        i;}            swap;
swap.f = f;swap.i = htonl(swap.i);
appendBinaryStringInfo(buf, (char *) &swap.i, 4);
}


LINK2:


http://www.postgresql.org/docs/8.2/interactive/libpq-example.html

Example 29-3. libpq Example Program 3

/** testlibpq3.c*      Test out-of-line parameters and binary I/O.** Before running this, populate a database with the
followingcommands* (provided in src/test/examples/testlibpq3.sql):** CREATE TABLE test1 (i int4, t text, b bytea);**
INSERTINTO test1 values (1, 'joe''s place', '\\000\\001\\002\\003\\004');* INSERT INTO test1 values (2, 'ho there',
'\\004\\003\\002\\001\\000');**The expected output is:** tuple 0: got*  i = (4 bytes) 1*  t = (11 bytes) 'joe's place'*
b = (5 bytes) \000\001\002\003\004** tuple 0: got*  i = (4 bytes) 2*  t = (8 bytes) 'ho there'*  b = (5 bytes)
\004\003\002\001\000*/
#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <sys/types.h>
#include "libpq-fe.h"

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


static void
exit_nicely(PGconn *conn)
{   PQfinish(conn);   exit(1);
}

/** This function prints a query result that is a binary-format fetch from* a table defined as in the comment above.
Wesplit it out because the* main() function uses it twice.*/
 
static void
show_binary_results(PGresult *res)
{   int         i,               j;   int         i_fnum,               t_fnum,               b_fnum;
   /* Use PQfnumber to avoid assumptions about field order in result */   i_fnum = PQfnumber(res, "i");   t_fnum =
PQfnumber(res,"t");   b_fnum = PQfnumber(res, "b");
 
   for (i = 0; i < PQntuples(res); i++)   {       char       *iptr;       char       *tptr;       char       *bptr;
 int         blen;       int         ival;
 
       /* Get the field values (we ignore possibility they are null!) */       iptr = PQgetvalue(res, i, i_fnum);
tptr= PQgetvalue(res, i, t_fnum);       bptr = PQgetvalue(res, i, b_fnum);
 
       /*        * The binary representation of INT4 is in network byte order, which        * we'd better coerce to the
localbyte order.        */       ival = ntohl(*((uint32_t *) iptr));
 
       /*        * The binary representation of TEXT is, well, text, and since libpq        * was nice enough to append
azero byte to it, it'll work just fine        * as a C string.        *        * The binary representation of BYTEA is
abunch of bytes, which could        * include embedded nulls so we have to pay attention to field length.        */
 blen = PQgetlength(res, i, b_fnum);
 
       printf("tuple %d: got\n", i);       printf(" i = (%d bytes) %d\n",              PQgetlength(res, i, i_fnum),
ival);      printf(" t = (%d bytes) '%s'\n",              PQgetlength(res, i, t_fnum), tptr);       printf(" b = (%d
bytes)", blen);       for (j = 0; j < blen; j++)           printf("\\%03o", bptr[j]);       printf("\n\n");   }
 
}

int
main(int argc, char **argv)
{   const char *conninfo;   PGconn     *conn;   PGresult   *res;   const char *paramValues[1];   int
paramLengths[1];  int         paramFormats[1];   uint32_t    binaryIntVal;
 
   /*    * If the user supplies a parameter on the command line, use it as the    * conninfo string; otherwise default
tosetting dbname=postgres and using    * environment variables or defaults for all other connection parameters.    */
if(argc > 1)       conninfo = argv[1];   else       conninfo = "dbname = postgres";
 
   /* Make a connection to the database */   conn = PQconnectdb(conninfo);
   /* Check to see that the backend connection was successfully made */   if (PQstatus(conn) != CONNECTION_OK)   {
fprintf(stderr, "Connection to database failed: %s",               PQerrorMessage(conn));       exit_nicely(conn);   }
 
   /*    * The point of this program is to illustrate use of PQexecParams() with    * out-of-line parameters, as well
asbinary transmission of data.    *    * This first example transmits the parameters as text, but receives the    *
resultsin binary format.  By using out-of-line parameters we can    * avoid a lot of tedious mucking about with quoting
andescaping, even    * though the data is text.  Notice how we don't have to do anything    * special with the quote
markin the parameter value.    */
 
   /* Here is our out-of-line parameter value */   paramValues[0] = "joe's place";
   res = PQexecParams(conn,                      "SELECT * FROM test1 WHERE t = $1",                      1,       /*
oneparam */                      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 */
 
   if (PQresultStatus(res) != PGRES_TUPLES_OK)   {       fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));
  PQclear(res);       exit_nicely(conn);   }
 
   show_binary_results(res);
   PQclear(res);
   /*    * In this second example we transmit an integer parameter in binary    * form, and again retrieve the results
inbinary form.    *    * Although we tell PQexecParams we are letting the backend deduce    * parameter type, we really
forcethe decision by casting the parameter    * symbol in the query text.  This is a good safety measure when sending
* binary parameters.    */
 
   /* Convert integer value "2" to network byte order */   binaryIntVal = htonl((uint32_t) 2);
   /* Set up parameter arrays for PQexecParams */   paramValues[0] = (char *) &binaryIntVal;   paramLengths[0] =
sizeof(binaryIntVal);  paramFormats[0] = 1;        /* binary */
 
   res = PQexecParams(conn,                      "SELECT * FROM test1 WHERE i = $1::int4",                      1,
/* one param */                      NULL,    /* let the backend deduce param type */                      paramValues,
                    paramLengths,                      paramFormats,                      1);      /* ask for binary
results*/
 
   if (PQresultStatus(res) != PGRES_TUPLES_OK)   {       fprintf(stderr, "SELECT failed: %s", PQerrorMessage(conn));
  PQclear(res);       exit_nicely(conn);   }
 
   show_binary_results(res);
   PQclear(res);
   /* close the connection to the database and cleanup */   PQfinish(conn);
   return 0;
}


Re: How do you convert PostgreSQL internal binary field to C datatypes

From
"Jeroen T. Vermeulen"
Date:
On Wed, May 23, 2007 22:47, Jeff Lynn wrote:

> So I gave up.   I will not use PostgreSQL until there is a simple way to
> extract data directly into C or C++ data type.  Is one expect to fetch
> data in text, then use text to int, text to double, text to date
> function before you can make any business logic works?

If you're working in C++ (as opposed to C, which I for one thought you
were using) then it's really easy.  As one example of the C++ API, if you
have a result set "data" and you want to extract an integer value at row
r, column c:
 int x = data[r][c].as<int>();

See http://pqxx.org/ for more on the C++ API.


Jeroen




Re: How do you convert PostgreSQL internal binary field to C datatypes

From
Andrew McNamara
Date:
>I give up!  What ever data returned from the binary output of 
>PGgetvalue() is, I don't know how to convert it into StringInfo, which 
>is being used by a few routines that "seems" to convert internal binary 
>format into float8.  By casting the returned data into StringInfo, 
>crashes the function.
>
>If PostgreSQL  support only a half baked C API, may as well don't bother.
>
>So I gave up.   I will not use PostgreSQL until there is a simple way to 
>extract data directly into C or C++ data type.  Is one expect to fetch 
>data in text, then use text to int, text to double, text to date 
>function before you can make any business logic works?

Even in a binary format, much processing is necessary - the format has
to be architecture-independent. So, whether you choose text or binary
format, you will have to "parse" the returns, and "format" the parameters.

Please, just try the text format for parameters and result. I could
measure no difference in performance (if anything, the text format was
fractionally faster)! The text format is much easier to get working and
more forgiving with respect to typing. Once your application is working,
then evaluate whether the text conversion is a problem.

-- 
Andrew McNamara, Senior Developer, Object Craft
http://www.object-craft.com.au/