Обсуждение: How do you convert PostgreSQL internal binary field to C datatypes
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,
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
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
От
"Jeroen T. Vermeulen"
Дата:
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
>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/