Thread: 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@xs4all.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.