[Zope-DB] Re: DCOracle2 Error: (1012, 'ORA-01012: not logged on')

[Matthew T. Kromer]

Chris Withers wrote:

> Matthew T. Kromer wrote:
>
>> This error is curious, because it's coming from the commit or 
>> rollback call to the database connection.
>
>
> Indeed, that's the point I failed to make successfully ;-)
>
>> This should only happen when that connection object is used during 
>> the course of a transaction, ie someone DID something to the 
>> database.  Yet then the database connection went away.  It's likely 
>> that Oracle was restarted or someone closed the connection (contrary 
>> to Dieter's experience, I've never seen Oracle crash).
>
>
> This isn't Oracle being restarted, and it isn't Oracle crashing. But 
> would you agree that something is happening mid-request to cause the 
> Oracle connection to drop? What sort of things could the client 
> (Zope/ZOracleDA,DCOracle2 in this case) do that would cause this to 
> happen?
>
>> The normal try/except pairing in ZOracleDA's code doesn't handle 
>> oracle errors arising during commit or rollback.  I'd argue it's 
>> PROBABLY doing the right thing letting it complain about this, since 
>> the proper time to notice the database went away was before trying to 
>> commit or abort.
>
>
> Indeed.
>
>> Having said that, if you want to monkey with the DA code a little 
>> bit, there IS support for two-phase commit in DCOracle2, just not in 
>> ZOracleDA.  What you'll need to do is catch the tpc_vote call and 
>> then do a connection.prepare(), and then I think you also need to 
>> change the connection.commit() to connection.commit(twophase=1) [n.b. 
>> I'm not sure if you don't have to commit twice, once with twophase=1 
>> and once without].
>
>
> Would you mind if I made these changes (and some other bug fixes that 
> people have suggested) on a branch in CVS, ready for you to review, 
> merge and release?



Go for it!  Thats why the code is out there in cvs.zope.org.  I've had 
people send me diffs and I've looked them over from time to time, but 
its MUCH easier to have them be in CVS.  Others have sent me whole-file 
changes leaving me to figure out what the diffs are and I cringe at 
trying to figure out and port people's changes.

The best thing to start with is to come up with a solid set of test 
cases that the DBAPI test runner can handle.  I think in the tests/ 
directory (vs test/ which is my one-off test folder) there's a 
testDCOracle2.py file which is the stub for the api2test.py program.  I 
think the dco2test.py program is the kick-off bit if you want to start 
following there.  I *dont* have a comprehensive test suite, just a set 
of exerciser scripts in my test/ directory.

>
>> The tpc_vote phase is allowed to raise an error, and the 
>> connection.prepare() should see that the connection to Oracle is down 
>> just like the commit/rollback sees.
>
>
> OK, what would be the downsides of doing it like this? (ie, why wasn't 
> it implemented this way to start off with? ;-)


The ZOracleDA piece is very old, its a direct munge of the original 
ZOracleDA, just barely tweaked enough to support DCOracle2 vs DCOracle.

The Zope DA mechanisms are getting somewhat creaky IMHO.  I'd *like* to 
fix them, but not so much that I've taken the time to do so in off 
hours.  I'd prefer to start with something that looks & smells a bit 
like the original Aqueduct product and go from there.  There are just 
some unpleasant bits down in the code.

I'd also like to get rid of some of the <dtml-var> junk that SQL methods 
use and implement some kind of query builder instead (not necessarily 
XML based, that's verbose to the extreme) but something that can handle 
the basic SQL parse and do conditional segments, ie

SELECT [results] FROM [tables] WHERE [condition] [etc]

UPDATE [vars] SET [sets] WHERE [condition]

and have the various bits in brackets be constructed automatically WITH 
THE CORRECT SQL SYNTAX ie using commas, quotes etc where necesary.  I'd 
like to have it smart enough to do BINDs if the underlying database 
supported it.  DTML can't (easily) handle the SET clause of an update, 
for instance, if there are variable things being set.

I haven't given it a whole lot of thought because its a big enough 
undertaking to do it right, I'd probably end up with something 
half-assed, unique to me, and unused by all.

Doing a through the web query builder seems enormously clunky, as well. 

-- 
Matt Kromer
Zope Corporation  http://www.zope.com/