pg.ddx.io pgsql-interfaces@postgresql.org mailing list archive
help / color / mirror / Atom feedArray elements (urgent help needed)
6+ messages / 3 participants
[nested] [flat]
* Array elements (urgent help needed)
@ 2004-10-04 02:58 Akbar Mokhtarani <akbarm@slac.stanford.edu>
2004-10-04 03:45 ` Re: Array elements (urgent help needed) Brett Schwarz <brett_schwarz@yahoo.com>
2004-10-04 04:33 ` Re: Array elements (urgent help needed) Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 2 replies; 6+ messages in thread
From: Akbar Mokhtarani @ 2004-10-04 02:58 UTC (permalink / raw)
To: pgsql-interfaces
I am desparate to find a way to access array elements from my c/c++
program. I have stored int and double data as arrays in my tables and need
to retrieve them from the database. I use a binary cursor to store the
query results and PQgetvalue gives me a char* pointer to the storage area
where the data is stored (this is according to the documentation). I have
tried to understand the internal structure of the storage area, but it
changes with data type. I suspect there is a more reliable/elegant way to
get access to the elements. What is the correct way of doing this?
Thanks in advance.
Akbar Mokhtarani
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Array elements (urgent help needed)
2004-10-04 02:58 Array elements (urgent help needed) Akbar Mokhtarani <akbarm@slac.stanford.edu>
@ 2004-10-04 03:45 ` Brett Schwarz <brett_schwarz@yahoo.com>
2004-10-04 04:15 ` Re: Array elements (urgent help needed) Akbar Mokhtarani <akbarm@slac.stanford.edu>
1 sibling, 1 reply; 6+ messages in thread
From: Brett Schwarz @ 2004-10-04 03:45 UTC (permalink / raw)
To: Akbar Mokhtarani <akbarm@slac.stanford.edu>; pgsql-interfaces
I really haven't work with arrays that much at the C
level, but I thought that the result from PQgetvalue
was just a char string. If it is an array, I thought
that the string would look like: {elem1, elem2,
elem3, ...}.
I don't think there are any helper functions for this,
so you need to manually walk that char string to peel
out the elements...at least as far as I am aware. You
might want to look at the how the operators for arrays
access the elements, e.g. the <> operator for arrays.
Again, I'm not expert, but this is what I had thought
is the case. I hope I understood your question.
HTH,
--brett
--- Akbar Mokhtarani <akbarm@slac.stanford.edu> wrote:
> I am desparate to find a way to access array
> elements from my c/c++
> program. I have stored int and double data as arrays
> in my tables and need
> to retrieve them from the database. I use a binary
> cursor to store the
> query results and PQgetvalue gives me a char*
> pointer to the storage area
> where the data is stored (this is according to the
> documentation). I have
> tried to understand the internal structure of the
> storage area, but it
> changes with data type. I suspect there is a more
> reliable/elegant way to
> get access to the elements. What is the correct way
> of doing this?
>
> Thanks in advance.
>
> Akbar Mokhtarani
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Array elements (urgent help needed)
2004-10-04 02:58 Array elements (urgent help needed) Akbar Mokhtarani <akbarm@slac.stanford.edu>
2004-10-04 03:45 ` Re: Array elements (urgent help needed) Brett Schwarz <brett_schwarz@yahoo.com>
@ 2004-10-04 04:15 ` Akbar Mokhtarani <akbarm@slac.stanford.edu>
0 siblings, 0 replies; 6+ messages in thread
From: Akbar Mokhtarani @ 2004-10-04 04:15 UTC (permalink / raw)
To: Brett Schwarz <brett_schwarz@yahoo.com>; +Cc: pgsql-interfaces
Yes the result from PQgetvalue is a char*. I looked at the array.h and
used the ArrayType structure defined there to walk through the memory
area. The first 4 bytes gives you the dimension of the array, the second 8
bytes are not used and the third 6 bytes gives the number of elements, and the
rest are 4 bytes pointers to the data type stored in the array.
Using this I was
able to parse arrays of int, double, and char BUT not string. This worked
fine on a cluster of intel based machines. However, when I moved the code
to another intel based machine, the program crashed. I have not been able
to parse the memory on this machine. It is an AMD processor which should
be the same as intel processor.
This made me believe that there must be a better way of doing this without
knowing the intenal structure of the memory.
Akbar Mokhtarani
On Sun, 3 Oct 2004, Brett Schwarz wrote:
> I really haven't work with arrays that much at the C
> level, but I thought that the result from PQgetvalue
> was just a char string. If it is an array, I thought
> that the string would look like: {elem1, elem2,
> elem3, ...}.
>
> I don't think there are any helper functions for this,
> so you need to manually walk that char string to peel
> out the elements...at least as far as I am aware. You
> might want to look at the how the operators for arrays
> access the elements, e.g. the <> operator for arrays.
>
> Again, I'm not expert, but this is what I had thought
> is the case. I hope I understood your question.
>
> HTH,
>
> --brett
>
> --- Akbar Mokhtarani <akbarm@slac.stanford.edu> wrote:
>
> > I am desparate to find a way to access array
> > elements from my c/c++
> > program. I have stored int and double data as arrays
> > in my tables and need
> > to retrieve them from the database. I use a binary
> > cursor to store the
> > query results and PQgetvalue gives me a char*
> > pointer to the storage area
> > where the data is stored (this is according to the
> > documentation). I have
> > tried to understand the internal structure of the
> > storage area, but it
> > changes with data type. I suspect there is a more
> > reliable/elegant way to
> > get access to the elements. What is the correct way
> > of doing this?
> >
> > Thanks in advance.
> >
> > Akbar Mokhtarani
> >
> >
> > ---------------------------(end of
> > broadcast)---------------------------
> > TIP 4: Don't 'kill -9' the postmaster
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Array elements (urgent help needed)
2004-10-04 02:58 Array elements (urgent help needed) Akbar Mokhtarani <akbarm@slac.stanford.edu>
@ 2004-10-04 04:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-10-08 17:08 ` Re: Array elements (urgent help needed) Akbar Mokhtarani <akbarm@slac.stanford.edu>
1 sibling, 1 reply; 6+ messages in thread
From: Tom Lane @ 2004-10-04 04:33 UTC (permalink / raw)
To: Akbar Mokhtarani <akbarm@slac.stanford.edu>; +Cc: pgsql-interfaces
Akbar Mokhtarani <akbarm@slac.stanford.edu> writes:
> I am desparate to find a way to access array elements from my c/c++
> program. I have stored int and double data as arrays in my tables and need
> to retrieve them from the database. I use a binary cursor to store the
> query results and PQgetvalue gives me a char* pointer to the storage area
> where the data is stored (this is according to the documentation).
What PG version are you using?
In 7.3 and earlier you have to deal with the server's internal
representation, warts and all. (I think what you're getting burnt by is
failing to account for MAXALIGN alignment, but it's hard to be sure
without more detail.)
7.4 and up have a documented, more or less platform-independent
representation, but it's not necessarily any easier to work with since
you do need to be able to convert to whatever internal representation
you want to use.
In any case, you need to read the server-side source code to see what
you're dealing with. None of these details have made it into the SGML
documentation, but they're covered fairly well in the source comments.
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Array elements (urgent help needed)
2004-10-04 02:58 Array elements (urgent help needed) Akbar Mokhtarani <akbarm@slac.stanford.edu>
2004-10-04 04:33 ` Re: Array elements (urgent help needed) Tom Lane <tgl@sss.pgh.pa.us>
@ 2004-10-08 17:08 ` Akbar Mokhtarani <akbarm@slac.stanford.edu>
2004-10-08 17:17 ` Re: Array elements (urgent help needed) Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 6+ messages in thread
From: Akbar Mokhtarani @ 2004-10-08 17:08 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-interfaces
Hi Tom,
I am using PG 7.4.5. I searched the source code and found few pointers. I
was able to solve my problem, sort of. I am including my observation and
a test program for experts to review and correct. Hope people find it
useful.
Thanks,
Akbar Mokhtarani
On Mon, 4 Oct 2004, Tom Lane wrote:
>
> What PG version are you using?
>
> In 7.3 and earlier you have to deal with the server's internal
> representation, warts and all. (I think what you're getting burnt by is
> failing to account for MAXALIGN alignment, but it's hard to be sure
> without more detail.)
>
> 7.4 and up have a documented, more or less platform-independent
> representation, but it's not necessarily any easier to work with since
> you do need to be able to convert to whatever internal representation
> you want to use.
>
> In any case, you need to read the server-side source code to see what
> you're dealing with. None of these details have made it into the SGML
> documentation, but they're covered fairly well in the source comments.
>
> regards, tom lane
>
Accessing array elements in a Postges tables
through the libpq interface
Akbar Mokhtarani LBNL
(amokhtarani@lbl.gov)
The PQgetvalue(res, row, field) function returns a char* to the beginning of
the memory where the array is stored. It is up to the user to walk the
memory space and extract the elements. After few days of head scraching and
searching the documentation, I came accross the following comment in
src/backend/utils/adt/arrayfuncs.c:
/*----------
* A standard varlena array has the following internal structure:
* <size> - total number of bytes (also, TOAST info flags)
* <ndim> - number of dimensions of the array
* <flags> - bit mask of flags
* <elemtype> - element type OID
* <dim> - size of each array axis (C array of int)
* <dim_lower> - lower boundary of each dimension (C array of int)
* <actual data> - whatever is the stored data
* The actual data starts on a MAXALIGN boundary. Individual items in the
* array are aligned as specified by the array element type.
*
This didn't quite agree with what I found but it gave me a starting point.
Here is what I found and hope experts will correct me if I am wrong:
The first 20 bytes seem to have the following information about the array:
--first 4 bytes don't know what it is but it is always 1
--second 4 bytes " " " " " " " " " 0
--third 4 bytes oid of the datatype in the array
--4th 4 bytes number of elements in the array
--5th 4 bytes dimension of the array
Form here we have the actual data. Each datum is preceeded with a 4 byte
integer indicating the number of bytes the next element occupies. Knowing
the number of elements one can parse the memory and get access to the
elements.
I wrote a small program to test the above conjecture. I was able to walk
through some basic data types (int, float, float8, char*, char**,
timestamp ). I was not able to get access to numeric type.
Note:The timestamp elements come back in number of seconds
from 1-1-2000. Need a utility function to convert it to a more
readable format. There must such a function somewhere in the source, but
I haven't found it yet.
Here is the test program and table schema I used to test it:
Note: I was not formally trained as a software engineer. I found my
way to here through physics, so forgive me if the code doesn't stand
up to seasoned programmers' standard.
/*
* testArray.c
*
* test program to access array elements through the libpq interface
*
* tested with PostgreSQL 7.4.5
*/
/* use the following table to test the program
drop table myarray;
create table myarray(
name text,
intarray int[],
floatarray real[],
chararray char[],
textarray text[],
numarray numeric[],
t1 timestamp default '-infinity',
t2 timestamp default 'infinity',
doublearray float8[],
timearray timestamp[] default '{-infinity, infinity}'
);
INSERT INTO myarray VALUES('First', '{4,5,3,2,6}', '{2.3,3.5}',
'{"C", "F","D"}', '{"One", "Two", "Three"}', '{45.3, 67.8}',
'Jan-1-2004', 'Feb-2-2004', '{ 5691.3456, 6798.986, 3435.786}',
'{Mar-8-2001, Jun-15-2003}');
INSERT INTO myarray VALUES('Second', '{4,2,6}', '{2.3,3.5, 6.7}',
'{"C", "F","D", "G"}', '{ "Two", "Three"}', '{56.89,45.3, 67.8}',
'Feb-2-1999', 'Mar-4-2000','{ 456.456, 607.9686, 3145.786}');
INSERT INTO myarray VALUES('Third', null, '{1.4,2.3,3.5}',
'{"C", "F","D"}', '{"One", "Two", "Three","Four"}', '{45.3, 67.8}'
,'Feb-2-2000', 'Mar-4-2001','{ 256.3456, 167.986, 35.786}' );
*/
#include <algorithm>
#include <stdio.h>
#include <stdlib.h>
#include "postgresql/libpq-fe.h"
//#include "libpq-fe.h"
#include <iostream>
#include <string>
using namespace std;
#include <netinet/in.h>
static void
exit_nicely(PGconn *conn)
{
PQfinish(conn);
exit(1);
}
// a helper function to get the number of elements in an array
int getNoEle(char* m) { return ntohl(*(int*)(m + 3*sizeof(int)));}
//template function for most multi-byte data types
template <class T> void fillArray(T *&, char*);
//specialized template functions for char and string
void fillArray(char *&, char*);
void fillArray(char **&, char*);
//functions for byte swaping
#define ByteSwap(x) byteswap((unsigned char*) &x, sizeof(x))
#define byteSwap(x,n) byteswap((unsigned char*) &x, n)
void byteswap(unsigned char *b, int n)
{
register int i = 0;
register int j = n-1;
while (i<j)
{
std::swap(b[i], b[j]);
i++, j--;
}
}
int
main(int argc, char **argv)
{
const char *conninfo;
PGconn *conn;
PGresult *res;
/*
* If the user supplies a parameter on the command line, use it as the
* conninfo string; otherwise default to setting dbname=template1 and
* using environment variables or defaults for all other connection
* parameters.
*/
if (argc > 1)
conninfo = argv[1];
else
conninfo = "hostaddr=127.0.0.1 dbname =test user=akbarm";
/* 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 '%s' failed.\n", PQdb(conn));
fprintf(stderr, "%s", PQerrorMessage(conn));
exit_nicely(conn);
}
/*
* Our test case here involves using a cursor, for which we must be
* inside a transaction block. We could do the whole thing with a
* single PQexec() of "select * from pg_database", but that's too
* trivial to make a good example.
*/
/* Start a transaction block */
res = PQexec(conn, "BEGIN");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, "BEGIN command failed: %s", PQerrorMessage(conn));
PQclear(res);
exit_nicely(conn);
}
/*
* Should PQclear PGresult whenever it is no longer needed to avoid
* memory leaks
*/
PQclear(res);
/*
* Fetch rows from pg_database, the system catalog of databases
*/
string command ="DECLARE myportal BINARY CURSOR FOR select * from myarray";
res = PQexec(conn, command.c_str());
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, "DECLARE CURSOR failed: %s", PQerrorMessage(conn));
PQclear(res);
exit_nicely(conn);
}
PQclear(res);
res = PQexec(conn, "FETCH ALL in myportal");
if (PQresultStatus(res) != PGRES_TUPLES_OK)
{
fprintf(stderr, "FETCH ALL failed: %s", PQerrorMessage(conn));
PQclear(res);
exit_nicely(conn);
}
for (int i = 0; i < PQntuples(res); i++)
{
char* name = PQgetvalue(res, i, 0);
char* intar = PQgetvalue(res, i, 1);
char* floatar = PQgetvalue(res, i, 2);
char* charar = PQgetvalue(res, i, 3);
char* textar = PQgetvalue(res, i, 4);
char* time1 = PQgetvalue(res, i, 6);
char* time2 = PQgetvalue(res, i, 7);
char* dblar = PQgetvalue(res, i, 8);
char* timear = PQgetvalue(res, i, 9);
//int array
int* ii;
if(PQgetlength(res, i, 1) > 0){
fillArray(ii, intar);
for (int j=0; j<getNoEle(intar); j++)
cout << " ii[" << j << "]: " << ii[j];
cout << endl;
}
//char array
char* cc;
if(PQgetlength(res, i, 3) > 0){
fillArray(cc, charar);
for (int j=0; j<getNoEle(charar); j++)
cout << " cc[" << j << "]: " << cc[j];
cout << endl;
}
//float array
float* fl;
if( PQgetlength(res, i, 2)> 0){
fillArray(fl, floatar);
for (int j=0; j<getNoEle(floatar); j++)
cout << " fl[" << j << "]: " << fl[j];
cout << endl;
}
//string array
char** str;
if( PQgetlength(res, i, 4) > 0){
fillArray(str, textar);
for (int j=0; j<getNoEle(textar); j++)
cout << " str[" << j << "]: " << str[j];
cout << endl;
}
//array of doubles
double * dbl;
if(PQgetlength(res, i, 8) > 0){
fillArray(dbl, dblar);
for (int j=0; j<getNoEle(dblar); j++)
cout << " dbl[" << j << "]: " << dbl[j];
cout << endl;
}
//timestamps
long long t1 = *(long long*) time1;
long long t2 = *(long long*) time2;
byteSwap( t1, PQgetlength(res, i, 6));
byteSwap( t2, PQgetlength(res, i, 7));
cout << " time1: " << t1 << " time2: " << t2 << endl;
//timestamp array
long long* ll;
if( PQgetlength(res, i, 9) > 0){
fillArray(ll, timear);
for (int j=0; j<getNoEle(timear); j++)
cout << " time[" << j << "]: " << ll[j];
cout << endl;
}
}
/* close the portal ... we don't bother to check for errors ... */
res = PQexec(conn, "CLOSE myportal");
PQclear(res);
/* end the transaction */
res = PQexec(conn, "END");
PQclear(res);
/* close the connection to the database and cleanup */
PQfinish(conn);
return 0;
}
template<class T>
void fillArray(T *&ar, char* mem){
int nEle = getNoEle(mem);
ar = new T[nEle];
char* start = mem + 5*sizeof(int);
int intSize = sizeof(int);
for(int i=0; i<nEle; i++){
int size = ntohl(*(int*)(start));
ar[i] = (*(T*)(start + intSize));
byteSwap(ar[i], size);
start += size + intSize;
}
}
void fillArray(char *&ar, char* mem){
int nEle = getNoEle(mem);
ar = new char[nEle];
char* start = mem + 5*sizeof(int);
int intSize = sizeof(int);
for(int i=0; i<nEle; i++){
int size = ntohl(*(int*)(start));
ar[i] = *(char*)(start + intSize);
start += size + intSize;
}
}
void fillArray(char **&ar, char* mem){
int nEle = getNoEle(mem);
ar = new char*[nEle];
char* start = mem + 5*sizeof(int);
int intSize = sizeof(int);
for(int i=0; i<nEle; i++){
int size = ntohl(*(int*)(start));
ar[i] =new char[size];
strncpy(ar[i], (char*)(start + intSize), size+1);
start += size + intSize;
}
}
Attachments:
[text/plain] arrayAccess.txt (9.9K, ../../Pine.LNX.4.58.0410081002170.27696@noric12.slac.stanford.edu/2-arrayAccess.txt)
download | inline:
Accessing array elements in a Postges tables
through the libpq interface
Akbar Mokhtarani LBNL
(amokhtarani@lbl.gov)
The PQgetvalue(res, row, field) function returns a char* to the beginning of
the memory where the array is stored. It is up to the user to walk the
memory space and extract the elements. After few days of head scraching and
searching the documentation, I came accross the following comment in
src/backend/utils/adt/arrayfuncs.c:
/*----------
* A standard varlena array has the following internal structure:
* <size> - total number of bytes (also, TOAST info flags)
* <ndim> - number of dimensions of the array
* <flags> - bit mask of flags
* <elemtype> - element type OID
* <dim> - size of each array axis (C array of int)
* <dim_lower> - lower boundary of each dimension (C array of int)
* <actual data> - whatever is the stored data
* The actual data starts on a MAXALIGN boundary. Individual items in the
* array are aligned as specified by the array element type.
*
This didn't quite agree with what I found but it gave me a starting point.
Here is what I found and hope experts will correct me if I am wrong:
The first 20 bytes seem to have the following information about the array:
--first 4 bytes don't know what it is but it is always 1
--second 4 bytes " " " " " " " " " 0
--third 4 bytes oid of the datatype in the array
--4th 4 bytes number of elements in the array
--5th 4 bytes dimension of the array
Form here we have the actual data. Each datum is preceeded with a 4 byte
integer indicating the number of bytes the next element occupies. Knowing
the number of elements one can parse the memory and get access to the
elements.
I wrote a small program to test the above conjecture. I was able to walk
through some basic data types (int, float, float8, char*, char**,
timestamp ). I was not able to get access to numeric type.
Note:The timestamp elements come back in number of seconds
from 1-1-2000. Need a utility function to convert it to a more
readable format. There must such a function somewhere in the source, but
I haven't found it yet.
Here is the test program and table schema I used to test it:
Note: I was not formally trained as a software engineer. I found my
way to here through physics, so forgive me if the code doesn't stand
up to seasoned programmers' standard.
/*
* testArray.c
*
* test program to access array elements through the libpq interface
*
* tested with PostgreSQL 7.4.5
*/
/* use the following table to test the program
drop table myarray;
create table myarray(
name text,
intarray int[],
floatarray real[],
chararray char[],
textarray text[],
numarray numeric[],
t1 timestamp default '-infinity',
t2 timestamp default 'infinity',
doublearray float8[],
timearray timestamp[] default '{-infinity, infinity}'
);
INSERT INTO myarray VALUES('First', '{4,5,3,2,6}', '{2.3,3.5}',
'{"C", "F","D"}', '{"One", "Two", "Three"}', '{45.3, 67.8}',
'Jan-1-2004', 'Feb-2-2004', '{ 5691.3456, 6798.986, 3435.786}',
'{Mar-8-2001, Jun-15-2003}');
INSERT INTO myarray VALUES('Second', '{4,2,6}', '{2.3,3.5, 6.7}',
'{"C", "F","D", "G"}', '{ "Two", "Three"}', '{56.89,45.3, 67.8}',
'Feb-2-1999', 'Mar-4-2000','{ 456.456, 607.9686, 3145.786}');
INSERT INTO myarray VALUES('Third', null, '{1.4,2.3,3.5}',
'{"C", "F","D"}', '{"One", "Two", "Three","Four"}', '{45.3, 67.8}'
,'Feb-2-2000', 'Mar-4-2001','{ 256.3456, 167.986, 35.786}' );
*/
#include <algorithm>
#include <stdio.h>
#include <stdlib.h>
#include "postgresql/libpq-fe.h"
//#include "libpq-fe.h"
#include <iostream>
#include <string>
using namespace std;
#include <netinet/in.h>
static void
exit_nicely(PGconn *conn)
{
PQfinish(conn);
exit(1);
}
// a helper function to get the number of elements in an array
int getNoEle(char* m) { return ntohl(*(int*)(m + 3*sizeof(int)));}
//template function for most multi-byte data types
template <class T> void fillArray(T *&, char*);
//specialized template functions for char and string
void fillArray(char *&, char*);
void fillArray(char **&, char*);
//functions for byte swaping
#define ByteSwap(x) byteswap((unsigned char*) &x, sizeof(x))
#define byteSwap(x,n) byteswap((unsigned char*) &x, n)
void byteswap(unsigned char *b, int n)
{
register int i = 0;
register int j = n-1;
while (i<j)
{
std::swap(b[i], b[j]);
i++, j--;
}
}
int
main(int argc, char **argv)
{
const char *conninfo;
PGconn *conn;
PGresult *res;
/*
* If the user supplies a parameter on the command line, use it as the
* conninfo string; otherwise default to setting dbname=template1 and
* using environment variables or defaults for all other connection
* parameters.
*/
if (argc > 1)
conninfo = argv[1];
else
conninfo = "hostaddr=127.0.0.1 dbname =test user=akbarm";
/* 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 '%s' failed.\n", PQdb(conn));
fprintf(stderr, "%s", PQerrorMessage(conn));
exit_nicely(conn);
}
/*
* Our test case here involves using a cursor, for which we must be
* inside a transaction block. We could do the whole thing with a
* single PQexec() of "select * from pg_database", but that's too
* trivial to make a good example.
*/
/* Start a transaction block */
res = PQexec(conn, "BEGIN");
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, "BEGIN command failed: %s", PQerrorMessage(conn));
PQclear(res);
exit_nicely(conn);
}
/*
* Should PQclear PGresult whenever it is no longer needed to avoid
* memory leaks
*/
PQclear(res);
/*
* Fetch rows from pg_database, the system catalog of databases
*/
string command ="DECLARE myportal BINARY CURSOR FOR select * from myarray";
res = PQexec(conn, command.c_str());
if (PQresultStatus(res) != PGRES_COMMAND_OK)
{
fprintf(stderr, "DECLARE CURSOR failed: %s", PQerrorMessage(conn));
PQclear(res);
exit_nicely(conn);
}
PQclear(res);
res = PQexec(conn, "FETCH ALL in myportal");
if (PQresultStatus(res) != PGRES_TUPLES_OK)
{
fprintf(stderr, "FETCH ALL failed: %s", PQerrorMessage(conn));
PQclear(res);
exit_nicely(conn);
}
for (int i = 0; i < PQntuples(res); i++)
{
char* name = PQgetvalue(res, i, 0);
char* intar = PQgetvalue(res, i, 1);
char* floatar = PQgetvalue(res, i, 2);
char* charar = PQgetvalue(res, i, 3);
char* textar = PQgetvalue(res, i, 4);
char* time1 = PQgetvalue(res, i, 6);
char* time2 = PQgetvalue(res, i, 7);
char* dblar = PQgetvalue(res, i, 8);
char* timear = PQgetvalue(res, i, 9);
//int array
int* ii;
if(PQgetlength(res, i, 1) > 0){
fillArray(ii, intar);
for (int j=0; j<getNoEle(intar); j++)
cout << " ii[" << j << "]: " << ii[j];
cout << endl;
}
//char array
char* cc;
if(PQgetlength(res, i, 3) > 0){
fillArray(cc, charar);
for (int j=0; j<getNoEle(charar); j++)
cout << " cc[" << j << "]: " << cc[j];
cout << endl;
}
//float array
float* fl;
if( PQgetlength(res, i, 2)> 0){
fillArray(fl, floatar);
for (int j=0; j<getNoEle(floatar); j++)
cout << " fl[" << j << "]: " << fl[j];
cout << endl;
}
//string array
char** str;
if( PQgetlength(res, i, 4) > 0){
fillArray(str, textar);
for (int j=0; j<getNoEle(textar); j++)
cout << " str[" << j << "]: " << str[j];
cout << endl;
}
//array of doubles
double * dbl;
if(PQgetlength(res, i, 8) > 0){
fillArray(dbl, dblar);
for (int j=0; j<getNoEle(dblar); j++)
cout << " dbl[" << j << "]: " << dbl[j];
cout << endl;
}
//timestamps
long long t1 = *(long long*) time1;
long long t2 = *(long long*) time2;
byteSwap( t1, PQgetlength(res, i, 6));
byteSwap( t2, PQgetlength(res, i, 7));
cout << " time1: " << t1 << " time2: " << t2 << endl;
//timestamp array
long long* ll;
if( PQgetlength(res, i, 9) > 0){
fillArray(ll, timear);
for (int j=0; j<getNoEle(timear); j++)
cout << " time[" << j << "]: " << ll[j];
cout << endl;
}
}
/* close the portal ... we don't bother to check for errors ... */
res = PQexec(conn, "CLOSE myportal");
PQclear(res);
/* end the transaction */
res = PQexec(conn, "END");
PQclear(res);
/* close the connection to the database and cleanup */
PQfinish(conn);
return 0;
}
template<class T>
void fillArray(T *&ar, char* mem){
int nEle = getNoEle(mem);
ar = new T[nEle];
char* start = mem + 5*sizeof(int);
int intSize = sizeof(int);
for(int i=0; i<nEle; i++){
int size = ntohl(*(int*)(start));
ar[i] = (*(T*)(start + intSize));
byteSwap(ar[i], size);
start += size + intSize;
}
}
void fillArray(char *&ar, char* mem){
int nEle = getNoEle(mem);
ar = new char[nEle];
char* start = mem + 5*sizeof(int);
int intSize = sizeof(int);
for(int i=0; i<nEle; i++){
int size = ntohl(*(int*)(start));
ar[i] = *(char*)(start + intSize);
start += size + intSize;
}
}
void fillArray(char **&ar, char* mem){
int nEle = getNoEle(mem);
ar = new char*[nEle];
char* start = mem + 5*sizeof(int);
int intSize = sizeof(int);
for(int i=0; i<nEle; i++){
int size = ntohl(*(int*)(start));
ar[i] =new char[size];
strncpy(ar[i], (char*)(start + intSize), size+1);
start += size + intSize;
}
}
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: Array elements (urgent help needed)
2004-10-04 02:58 Array elements (urgent help needed) Akbar Mokhtarani <akbarm@slac.stanford.edu>
2004-10-04 04:33 ` Re: Array elements (urgent help needed) Tom Lane <tgl@sss.pgh.pa.us>
2004-10-08 17:08 ` Re: Array elements (urgent help needed) Akbar Mokhtarani <akbarm@slac.stanford.edu>
@ 2004-10-08 17:17 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 6+ messages in thread
From: Tom Lane @ 2004-10-08 17:17 UTC (permalink / raw)
To: Akbar Mokhtarani <akbarm@slac.stanford.edu>; +Cc: pgsql-interfaces
Akbar Mokhtarani <akbarm@slac.stanford.edu> writes:
* A standard varlena array has the following internal structure:
This didn't quite agree with what I found but it gave me a starting point.
That's because it's describing the *internal* structure. Since you're
on 7.4, what you are actually seeing is the on-the-wire representation
emitted by array_send(), which looks like:
/* Send the array header information */
pq_sendint(&buf, ndim, 4);
pq_sendint(&buf, v->flags, 4);
pq_sendint(&buf, element_type, sizeof(Oid));
for (i = 0; i < ndim; i++)
{
pq_sendint(&buf, ARR_DIMS(v)[i], 4);
pq_sendint(&buf, ARR_LBOUND(v)[i], 4);
}
/* Send the array elements using the element's own sendproc */
/* (so N element values in whatever the datatype format is) */
regards, tom lane
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2004-10-08 17:17 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2004-10-04 02:58 Array elements (urgent help needed) Akbar Mokhtarani <akbarm@slac.stanford.edu>
2004-10-04 03:45 ` Brett Schwarz <brett_schwarz@yahoo.com>
2004-10-04 04:15 ` Akbar Mokhtarani <akbarm@slac.stanford.edu>
2004-10-04 04:33 ` Tom Lane <tgl@sss.pgh.pa.us>
2004-10-08 17:08 ` Akbar Mokhtarani <akbarm@slac.stanford.edu>
2004-10-08 17:17 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox