[Hackers: Can anyone comment on my idea on how point 1 below could be
done, or not if thats the case? Thanks, Peter]

-----Original Message-----
From: Assaf Arkin [mailto:arkin@exoffice.com]
Sent: Wednesday, December 08, 1999 7:41 PM
To: Peter Mount
Cc: pgsql-interfaces@hub.org
Subject: Re: [INTERFACES] Transaction support in 6.5.3/JDBC


> PM: JDBC based code should never issue begin/commit/rollback commands,
> and should use the similarly named methods in Connection. This is
> because a JDBC driver could be issuing these statements internally,
and
> it would be confused. With our driver, you could currently get away
with
> it, but it's not guaranteed to stay that way.

Inside a transaction, the application should not even use
commit/rollback on the JDBC connection, only through the transaction
monitor API. This is easy to solve, I simply return a ClientConnection
wrapper that prevents that. But someone can still send a commit/rollback
statement directly through the JDBC driver.

What I'm more afraid of is some operation that will cause a
commit/rollback to occur, e.g. a failed update, a trigger or stored
procedure.

PM: This is tricky. Some JDBC drivers do parse the SQL before sending to
the backend, but we dont - mainly because its faster to let the
backend's parser do the job, and also it keeps our size down. The latter
affects applet users more than anything else.

PM: I suppose we could add a check for the simplest cases, ie sql
containing just "begin" "commit" "rollback" etc, but it won't catch all
possible cases.

> PM: Hmmm, in theory if a transaction is in a dead state (ie: an SQL
> statement failed, so anything else is ignored until the rollback),
there
> should be a message in the notify queue. Our JDBC driver keeps these
in
> the warnings queue, so you could read them prior to calling commit()
> yourself.

Thanks I'll try to look that out.


I've minimized all the special requirements I need from the driver to
three methods calls:

1. enbleSQLTransactions -- prevents a commit/rollback from being
executed directly in SQL; you can never be too careful ;-)

PM: I wonder if we can get this functionality in the backend's parser -
ie, for the API interfaces, they can set a variable on startup that
disables begin, commit and rollback, then when they need to use them, it
can then either temporarily clear the variable, or use a prefix that
forces the statement to work?

PM: enableSQLTransactions can then act immediately above this
functionality.

2. prepare -- should return false if the transaction is read-only, true
if it will commit, throw an exception if it will rollback

3. isCriticalError -- should tell me if a critical error occured in the
connection and the connection is no longer useable

How do I detect no. 3? Is there are certain range of error codes, should
I just look at certain PSQLExceptions as being critial (e.g. all I/O
related errors)?

PM: Don't rely on the text returned from PSQLException to be in English.
We are pretty unique in that the driver will return an error message in
the language defined by the locale of the client (also depends on if we
have translated the errors into that language). What I could to is add a
method to PSQLException that returns the original id of the Exception,
and another to return the arguments supplied. That may make your code
more portable.

Peter

-- 
Peter Mount
Enterprise Support
Maidstone Borough Council
Any views stated are my own, and not those of Maidstone Borough Council.



Thu, 9 Dec 1999 07:28:11 -0000 
Peter Mount <petermount@it.maidstone.gov.uk>
RE: [INTERFACES] Transaction support in 6.5.3/JDBC
> PM: I wonder if we can get this functionality in the backend's parser -
> ie, for the API interfaces, they can set a variable on startup that
> disables begin, commit and rollback, then when they need to use them, it
> can then either temporarily clear the variable, or use a prefix that
> forces the statement to work?
> 
> PM: enableSQLTransactions can then act immediately above this
> functionality.

That's what I was hoping for.


> 2. prepare -- should return false if the transaction is read-only, true
> if it will commit, throw an exception if it will rollback

Works fine now that I've added a check for *ABORT STATUS*.


> 3. isCriticalError -- should tell me if a critical error occured in the
> connection and the connection is no longer useable
> 
> How do I detect no. 3? Is there are certain range of error codes, should
> I just look at certain PSQLExceptions as being critial (e.g. all I/O
> related errors)?
> 
> PM: Don't rely on the text returned from PSQLException to be in English.
> We are pretty unique in that the driver will return an error message in
> the language defined by the locale of the client (also depends on if we
> have translated the errors into that language). What I could to is add a
> method to PSQLException that returns the original id of the Exception,
> and another to return the arguments supplied. That may make your code
> more portable.

I'm not looking into the messages, I know their language dependent. I
even added two or three new error messages, but only in English.

I'm looking for either specific error codes, range of error codes, or
some class extending PSQLException that will just indicate that this
connection is no longer useful. For example, if an I/O error occurs,
there's no ReadyForQuery reply, there's garbled response, etc.

arkin

> 
> Peter
> 
> --
> Peter Mount
> Enterprise Support
> Maidstone Borough Council
> Any views stated are my own, and not those of Maidstone Borough Council.
> 
> ************

-- 
____________________________________________________________
Assaf Arkin                               arkin@exoffice.com
CTO                                  http://www.exoffice.com
Exoffice, The ExoLab Company             tel: (650) 259-9796

Thu, 09 Dec 1999 10:42:43 -0800
Assaf Arkin <arkin@exoffice.com>
Re: [INTERFACES] Transaction support in 6.5.3/JDBC