Re: Need Info

Hi,

I am facing one problem while using the C++ classes provided by the
Postgres to connect to the backend database and execute query.
I am using PgDatabase class to connect to the backend postgres database
and execute query. Now, when I am using the following method of
PgDatabase class, to access the tuples and fields, its always providing
me return values as char *.
const char* GetValue(int tup_num, int field_num)

My problem is - for my application, I need to have the values back in
their proper format, that means if the datatyoe of one field is integer,

I want to get it back as an integer type and not as a string.

Is there any way in Postgres, to do that ?

Thanks and Regards,
Shamik

On Mon, Jan 28, 2002 at 12:15:20PM +0530, Shamik Majumder wrote:
>
> My problem is - for my application, I need to have the values back in
> their proper format, that means if the datatyoe of one field is integer,
> I want to get it back as an integer type and not as a string.

Two ways:

1. Binary result sets. This gets a little hairy because you'll need to
do your own conversion to your client platform's sizes, byte sex etc.
Documentation doesn't provide all that much in the way of details either.

2. Convert the strings to the right types yourself, using either sscanf()
or atoi() and its friends.

Or if you happen to be writing in C++, you may want to check out my new
C++ frontend (currently in development but quite usable), which lets you
write stuff like

Result R = T.Exec("select number from mytable where id=123");
int n;
if (!R[0][0].to(n)) throw runtime_error("No number set!");

You can find this library at

http://members.ams.chello.nl/j.vermeulen31/proj-libpqxx.html

HTH,

Jeroen

> This scares the hell out of me. How do you know whether the old
> session committed your transaction, but the connection died just before
> it could tell you so? libpqxx is the very LAST place in the chain that
> is qualified to undertake the task of error recovery.

I guess you're right in that the best place to solve these problems (in the
absence of two-phase commit) is ultimately within the client. What libpqxx
tries to do is to encourage people to write restartable transactions, and
provide a reusable solution to the problem.

Your point about TCP is well taken--the big assumption currently underlying
the libpqxx mechanism is that sending the final commit and closing the
connection are atomic and both sides of the connection will have the same
idea of whether it failed or succeeded. You are right in that this is a
risk.

Even so, compensating for this seems doable, with the major stumbling block
probably being garbage collection on transaction state information.
Admittedly this goes beyond a mere language binding, but it seems to me
that this could be a helpful tool to the developer. After all the problem
exists whether it's left for the client to deal with or not.

> In any case, I do not think that libpq or libpqxx can or should try to
> hide this problem from the client.

Hiding it completely is impossible, but I'd like to help the client in
trying to cope with it. And I'm willing to invest some time in trying to
make that work because IMHO the value to users could be considerable.

Jeroen

<jtv(at)xs4all(dot)nl> writes:
>> In any case, I do not think that libpq or libpqxx can or should try to
>> hide this problem from the client.

> Hiding it completely is impossible, but I'd like to help the client in
> trying to cope with it. And I'm willing to invest some time in trying to
> make that work because IMHO the value to users could be considerable.

Certainly there's value in providing a framework that helps the client
to deal with this. I was just objecting to what might have been a
misreading of your meaning: it seemed you were saying that libpqxx
solves the problem so the client doesn't have to think about it.

regards, tom lane

> Certainly there's value in providing a framework that helps the client
> to deal with this. I was just objecting to what might have been a
> misreading of your meaning: it seemed you were saying that libpqxx
> solves the problem so the client doesn't have to think about it.

Basically, the framework lets you write your transaction as a functor class
derived from the Transactor class. In return for this effort, the
execution code does its best to minimize the risk window (which currently
still exists, but I'd like to work to "eliminate" it insofar as this can be
done).

The setup of Transactor encourages the programmer to put all "transient"
state involved in the transaction inside the functor class as data
members. If the connection is no longer there, for example, the framework
tries to rerun the functor. It first restores the transaction's initial
state simply by copy-constructing the functor object. If any results or
other changes need to be written back to the rest of the program, this can
be done from the optional OnCommit() method (or the programmer may opt to
write results back to the program's state immediately, and write undo code
in the optional OnAbort() method).

I think the interface isn't too bad, and next it would be worthwhile to
install more solid ACID support behind it. It's not quite "a poor man's
XA" but my toes curl when I think of every programmer having to deal with
these issues for himself.

Jeroen

> The setup of Transactor encourages the programmer to put all
> "transient" state involved in the transaction inside the functor class
> as data members. If the connection is no longer there, for example,
> the framework tries to rerun the functor. It first restores the
> transaction's initial state simply by copy-constructing the functor
> object.

....And of course I should have added that the rerun involves setting up a
new back-end transaction (which is again handled by the framework).

I've considered special-case optimizations such as recording & replaying
SQL, but felt a bit out of my depth when trying to define (in reasonably
simple terms) when this would be safe. After all the client's transaction
may eg. read a record that another client may have modified between
retries. So I stuck with the more general solution of replaying the client
code, and coupling each attempt to its own backend transaction.

Of course this still doesn't let you do long transactions in an iffy
network environment. What it does buy you--I think--is the ability to keep
a connection open for a long time and running batches of smaller
transactions, without having to care (much) about temporary loss of
connection. From what I've seen, such loss typically occurs when the
connection is idle between transactions.

Jeroen

I'm having troubles to connect via the .NET ODBC provider and I don't know
if it is a problem of the Postgres ODBC Driver or the problem is in the
ODBC provider of Microsoft.

Thank you.

Oscar Serrano.