Login | Register For Free | Help
Search for: (Advanced)

Mailing List Archive: Zope: DB

[ANN] Modified version of DCOracle2 is available

 

 

Zope db RSS feed   Index | Next | Previous | View Threaded


maciej.wisniowski at coig

Nov 17, 2006, 8:31 AM

Post #1 of 24 (6792 views)
Permalink
[ANN] Modified version of DCOracle2 is available

Hi

Due to discussion on Zope list and some work I did before
I've published modified version of DCOracle2.

What is in this version:
1. Bug fixes
- StoredProcedures caused deadlocks in database
- StoredProcedures caused conflict errors under heavy load
removed self._errors reference and added _p_resolveConflict
- other I don't remember now

2. Changes
Changes are only in python files
- DCOracle2 now uses connection pool
Pool implementation is based on psycopg but
it doesn't use volatile attributes.

As a result:
- Open/Close button in ZMI works as it should
- DCOracle is possibly slower a bit

- Ability to reconnect after the connection is broken
When Oracle is restarted, connection is closed by firewall
etc. Zope will show an error but only once for every broken
connection. Second request will cause DCOracle2 to reconnect

3. To do:
- Possibly useage of raise ConflictError may cause that there
will be no errors shown during reconnect

- Pool size attribute may be set via ZMI

- Testing...

This code is now in use at my company, but not yet in production
environment. So far this works with Zope 2.8.x and Oracle9.

If somebody knows better place for this code then let me know.
Any ideas how should file headers (I mean licences) look?
Comments are welcome

Code is here:

http://code.google.com/p/dcoracle2da/

Should be available via svn with:

svn checkout http://dcoracle2da.googlecode.com/svn/trunk/ dcoracle2da

THERE IS NO GUARANTEE THAT THIS WILL WORK FOR YOU, SO BE CAREFULL :)

--
Maciej Wisniowski

_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


chris at simplistix

Nov 21, 2006, 2:42 AM

Post #2 of 24 (6644 views)
Permalink
Re: [ANN] Modified version of DCOracle2 is available [In reply to]

Maciej Wisniowski wrote:
> Due to discussion on Zope list and some work I did before
> I've published modified version of DCOracle2.

Can I suggest you sign a contributor agreement and contribute this
directly to the Zope CVS or SVN repository? I think DCOracle2 may still
be in CVS, but Jen Vagelpohl has become quite adept and moving things
from one to the other. If you tickle him nicely, he may move it to SVN
if it's not already there ;-)

> What is in this version:
> 1. Bug fixes
> - StoredProcedures caused deadlocks in database
> - StoredProcedures caused conflict errors under heavy load
> removed self._errors reference and added _p_resolveConflict
> - other I don't remember now

With unit tests I hope?

> 2. Changes
> Changes are only in python files
> - DCOracle2 now uses connection pool
> Pool implementation is based on psycopg but
> it doesn't use volatile attributes.

Please be careful, I made one attempt at this and it's fiendishly
difficult to get right...

> As a result:
> - Open/Close button in ZMI works as it should

Hurrah! For the record, the DCOracle2 trunk in CVS did this too..

> - DCOracle is possibly slower a bit

How so?

> - Ability to reconnect after the connection is broken
> When Oracle is restarted, connection is closed by firewall
> etc. Zope will show an error but only once for every broken
> connection. Second request will cause DCOracle2 to reconnect

I may have done something similar on the trunk, I suggest you have a
check how that code compares with yours...

> - Testing...

You really really really need to write the tests first for this kind of
problem...

cheers,

Chris

--
Simplistix - Content Management, Zope & Python Consulting
- http://www.simplistix.co.uk
_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


jens at dataflake

Nov 21, 2006, 5:10 AM

Post #3 of 24 (6639 views)
Permalink
Re: [ANN] Modified version of DCOracle2 is available [In reply to]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1


On 21 Nov 2006, at 11:42, Chris Withers wrote:
> I think DCOracle2 may still be in CVS, but Jens Vagelpohl has
> become quite adept and moving things from one to the other. If you
> tickle him nicely, he may move it to SVN if it's not already there ;-)

Sorry, this is no longer the case, the required access privileges for
me to do these migrations have been removed without warning. Jim
Fulton and the ZC admin team are the only people who may perform
tasks that require root-level access.

Another good argument to push for the foundation to have their own
infrastructure and move the repositories there.

jens

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (Darwin)

iD8DBQFFYvrJRAx5nvEhZLIRAn+PAKCcNnZGfoqEEON49EQjRKnyom2s1gCdGzYX
XjXzJvFnx+Tz+rZJVzoNr/0=
=ceJO
-----END PGP SIGNATURE-----
_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


maciej.wisniowski at coig

Nov 21, 2006, 6:33 AM

Post #4 of 24 (6659 views)
Permalink
Re: [ANN] Modified version of DCOracle2 is available [In reply to]

> Can I suggest you sign a contributor agreement and contribute this
> directly to the Zope CVS or SVN repository? I think DCOracle2 may
> still be in CVS, but Jen Vagelpohl has become quite adept and moving
> things from one to the other. If you tickle him nicely, he may move it
> to SVN if it's not already there ;-)
Seems that there is no DCOracle2 in SVN.
I've just seen Jens answer. I'll think about.

> With unit tests I hope?
No. I'll possibly try to write some, but so far I have no idea
how to write unit tests for things like conflict errors that
appear under high load and tests without real Oracle database.
I must admit that I've not looked at original DCO2 tests yet.
I'll try and ask for help if needed :)

> Please be careful, I made one attempt at this and it's fiendishly
> difficult to get right...
I've used pool implementation from psycopg so I think it is rather
good code base. As I said before, after discussion with Dieter Maurer
I've changed this a bit, that it doesn't use volatile atributes. I think
the code in few places is even simpler now. So far it works for some
time in a site used by hundred or more users (kind of test environment
for end user) and I've seen no problems due to connection pool or lack
of volatile attibutes yet.

>> - Open/Close button in ZMI works as it should
> As a result:
>
> Hurrah! For the record, the DCOracle2 trunk in CVS did this too..
Really? I can't find that code? You mean:
http://cvs.zope.org/Products/DCOracle2/?only_with_tag=HEAD ?
I've checked DABase.py and DB.py and nothing.

> - DCOracle is possibly slower a bit
>
> How so?
In standard implementation connection was
usually ready in _v_database_connection, now,
every time it has to be taken from the pool first.
But I didn't tested this yet, but I will.

>> When Oracle is restarted, connection is closed by firewall
>> etc. Zope will show an error but only once for every broken
>> connection. Second request will cause DCOracle2 to reconnect
> - Ability to reconnect after the connection is broken
>
> I may have done something similar on the trunk, I suggest you have a
> check how that code compares with yours...
Seems your implementation is better! I'll have a closer look.

Great thanks for the comments!

--
Maciej Wisniowski


_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


chris at simplistix

Nov 21, 2006, 7:30 AM

Post #5 of 24 (6664 views)
Permalink
Re: [ANN] Modified version of DCOracle2 is available [In reply to]

Maciej Wisniowski wrote:
>> Can I suggest you sign a contributor agreement and contribute this
>> directly to the Zope CVS or SVN repository? I think DCOracle2 may
>> still be in CVS, but Jen Vagelpohl has become quite adept and moving
>> things from one to the other. If you tickle him nicely, he may move it
>> to SVN if it's not already there ;-)
> Seems that there is no DCOracle2 in SVN.
> I've just seen Jens answer. I'll think about.

It's in CVS.

Yes, it's an old tool, but since Zope Corp have hamstrung the one person
who was prepared to do this kind of migration, you/we/anyone has no
choice but to use CVS :-(

I'm CC'ing Jim in the vague hope that he could somehow arrange for Jens
to get his privileges back :-S

He can also help you with the contributor agreement side of things...


>> With unit tests I hope?
> No. I'll possibly try to write some, but so far I have no idea
> how to write unit tests for things like conflict errors that
> appear under high load and tests without real Oracle database.
> I must admit that I've not looked at original DCO2 tests yet.
> I'll try and ask for help if needed :)

Yeah, I definitely wrote a suite of unit tests on my branch, I suspect
they might need a live Oracle instance to connect to. ConflictErrors can
be "simulated" but it is tricky...

> I've used pool implementation from psycopg so I think it is rather
> good code base. As I said before, after discussion with Dieter Maurer
> I've changed this a bit, that it doesn't use volatile atributes. I think
> the code in few places is even simpler now. So far it works for some
> time in a site used by hundred or more users (kind of test environment
> for end user) and I've seen no problems due to connection pool or lack
> of volatile attibutes yet.

Well, as I said, good luck ;-) I just spent a day debugging a similar
type of issue in ZRDB/DA.py...

>> Hurrah! For the record, the DCOracle2 trunk in CVS did this too..
> Really? I can't find that code? You mean:
> http://cvs.zope.org/Products/DCOracle2/?only_with_tag=HEAD ?
> I've checked DABase.py and DB.py and nothing.

Looks like I never merged to HEAD :-S

http://cvs.zope.org/Products/DCOracle2/?only_with_tag=chrisw_fixconnectionleak_branch

>> - DCOracle is possibly slower a bit
>>
>> How so?
> In standard implementation connection was
> usually ready in _v_database_connection, now,
> every time it has to be taken from the pool first.
> But I didn't tested this yet, but I will.

I think this will make no noticeable difference...

cheers,

Chris

--
Simplistix - Content Management, Zope & Python Consulting
- http://www.simplistix.co.uk
_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


charlie at egenix

Nov 21, 2006, 7:36 AM

Post #6 of 24 (6652 views)
Permalink
Re: [ANN] Modified version of DCOracle2 is available [In reply to]

Am 21.11.2006, 15:33 Uhr, schrieb Maciej Wisniowski
<maciej.wisniowski [at] coig>:

>> Please be careful, I made one attempt at this and it's fiendishly
>> difficult to get right...
> I've used pool implementation from psycopg so I think it is rather
> good code base. As I said before, after discussion with Dieter Maurer
> I've changed this a bit, that it doesn't use volatile atributes. I think
> the code in few places is even simpler now. So far it works for some
> time in a site used by hundred or more users (kind of test environment
> for end user) and I've seen no problems due to connection pool or lack
> of volatile attibutes yet.

Eek! ZpsycopgDA is GPL'd! Does that mean you've put your version of
DCOracle under GPL?

But that aside I am not sure it is the best implementation of connection
pooling out there.

Charlie
_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


fog at initd

Nov 21, 2006, 7:41 AM

Post #7 of 24 (6669 views)
Permalink
Re: [ANN] Modified version of DCOracle2 is available [In reply to]

Il giorno mar, 21/11/2006 alle 16.36 +0100, Charlie Clark ha scritto:
> Eek! ZpsycopgDA is GPL'd! Does that mean you've put your version of
> DCOracle under GPL?

I hope he did. But ZPsycopgDA is also distributed under the ZPL so there
should be no problem unless he choosed an incompatible license.

> But that aside I am not sure it is the best implementation of
> connection pooling out there.

I am sure it is not. But it is one of the simpliest, at least. :)

federico

--
Federico Di Gregorio http://people.initd.org/fog
Debian GNU/Linux Developer fog [at] debian
INIT.D Developer fog [at] initd
Alla faccia della trasparenza! Ma che si fa, lo si prende e lo si
mena? (In pubblico, per trasparenza.) -- <mrjive>
Attachments: signature.asc (0.18 KB)


charlie at egenix

Nov 21, 2006, 7:44 AM

Post #8 of 24 (6661 views)
Permalink
Re: [ANN] Modified version of DCOracle2 is available [In reply to]

Am 21.11.2006, 16:41 Uhr, schrieb Federico Di Gregorio <fog [at] initd>:

> Il giorno mar, 21/11/2006 alle 16.36 +0100, Charlie Clark ha scritto:
>> Eek! ZpsycopgDA is GPL'd! Does that mean you've put your version of
>> DCOracle under GPL?
> I hope he did. But ZPsycopgDA is also distributed under the ZPL so there
> should be no problem unless he choosed an incompatible license.

One of those things that happen all too easily... I'd forgotten you'd gone
over to dual-licensing, thanks.

>> But that aside I am not sure it is the best implementation of
>> connection pooling out there.
> I am sure it is not. But it is one of the simpliest, at least.

I wasn't criticising it per se, of course, just the practice of following
another implementation.

Charlie
_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


fog at initd

Nov 21, 2006, 7:50 AM

Post #9 of 24 (6657 views)
Permalink
Re: [ANN] Modified version of DCOracle2 is available [In reply to]

Il giorno mar, 21/11/2006 alle 16.44 +0100, Charlie Clark ha scritto:
>
> >> But that aside I am not sure it is the best implementation of
> >> connection pooling out there.
> > I am sure it is not. But it is one of the simpliest, at least.
>
> I wasn't criticising it per se, of course, just the practice of
> following
> another implementation.

Yep, sorry. I forgot to add an ":)" after my comment.

--
Federico Di Gregorio http://people.initd.org/fog
Debian GNU/Linux Developer fog [at] debian
INIT.D Developer fog [at] initd
One key. One input. One enter. All right. -- An american consultant
(then the system crashed and took down the *entire* network)
Attachments: signature.asc (0.18 KB)


maciej.wisniowski at coig

Nov 21, 2006, 11:46 AM

Post #10 of 24 (6653 views)
Permalink
Re: [ANN] Modified version of DCOracle2 is available [In reply to]

>> Eek! ZpsycopgDA is GPL'd! Does that mean you've put your version of
>> DCOracle under GPL?
>
> I hope he did. But ZPsycopgDA is also distributed under the ZPL so there
> should be no problem unless he choosed an incompatible license.
As you might see I've asked about licensing in my first post.
So far code is at google site and I've chosen GPL 2.0 licensing for it.
There still are old licence headers in files itself.
I have no much experience with this, so thats why I asked about.
Psycopg is GPL, DCOracle2 is ZPL. ZPL seems to be less restrictive.
Am I right?

>> But that aside I am not sure it is the best implementation of
>> connection pooling out there.
>
> I am sure it is not. But it is one of the simpliest, at least. :)
Yup, it is simple, and because of this I was able to implement this
very quickly. It is also stable. I've used psycopg a lot, so that's
why I've chosen this. I was not looking at other implementations
so far. But any suggestions, what might be better, are welcome (also
because of licensing). Anybody who wants to contribute is welcome too,
of course.

--
Maciej Wisniowski
_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


maciej.wisniowski at coig

Nov 21, 2006, 2:58 PM

Post #11 of 24 (6628 views)
Permalink
Re: [ANN] Modified version of DCOracle2 is available [In reply to]

I'll correct myself.
> Psycopg is GPL, DCOracle2 is ZPL.
As Federico said everything under ZPsycopgDA
may be GPL or ZPL. I've used pool.py and pool_base.py.
First is from ZPsycopgDA, latter not (but
it is used by pool.py). Seems that is only
possible to use GPL here, right?

--
Maciej Wisniowski

_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


fog at initd

Nov 21, 2006, 3:03 PM

Post #12 of 24 (6627 views)
Permalink
Re: [ANN] Modified version of DCOracle2 is available [In reply to]

Il giorno mar, 21/11/2006 alle 23.58 +0100, Maciej Wisniowski ha
scritto:
> I'll correct myself.
> > Psycopg is GPL, DCOracle2 is ZPL.
> As Federico said everything under ZPsycopgDA
> may be GPL or ZPL. I've used pool.py and pool_base.py.
> First is from ZPsycopgDA, latter not (but
> it is used by pool.py). Seems that is only
> possible to use GPL here, right?

Yes. ZPsycopgDA can be under the ZPL because the ZPL is GPL-compatible.
But if you directly include GPL'ed code you must use GPL.

Have fun,
federico

--
Federico Di Gregorio http://people.initd.org/fog
Debian GNU/Linux Developer fog [at] debian
INIT.D Developer fog [at] initd
Ma chi sei?....-il trafficante di Nutella? -- Giorgia
Attachments: signature.asc (0.18 KB)


maciej.wisniowski at coig

Nov 21, 2006, 10:34 PM

Post #13 of 24 (6648 views)
Permalink
Re: [ANN] Modified version of DCOracle2 is available [In reply to]

> Yes. ZPsycopgDA can be under the ZPL because the ZPL is GPL-compatible.
> But if you directly include GPL'ed code you must use GPL.
>
OK. Thanks for the answer. One more question.
If I'll create application using ZPsycopgDA which
is ZPL (but ZPsycopgDA uses psycopg which is GPL),
then does my application have to be GPL too?

Federico, as I see you're one of psycopg2 developers so
a little question about this. I've found a bug in
ZPsycopgDA. It should have one more statement
when catching exceptions:
if e.args[0].find("deadlock detected") > -1:
raise ConflictError

There are two places in ZPsycopgDA/db.py
where above code should be added. These are near:
if e.args[0].find("concurrent update") > -1:
raise ConflictError

Can I submit this issue somewhere (AFAIK there
is no bugtracker at initd.org) or this e-mail
is enough?

The problem is in psycopg2.
Zope3 psycopg implementation
already has such statement.

--
Maciej Wisniowski
_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


fog at initd

Nov 22, 2006, 1:12 AM

Post #14 of 24 (6626 views)
Permalink
Re: [ANN] Modified version of DCOracle2 is available [In reply to]

Il giorno mer, 22/11/2006 alle 07.34 +0100, Maciej Wisniowski ha
scritto:
> > Yes. ZPsycopgDA can be under the ZPL because the ZPL is GPL-compatible.
> > But if you directly include GPL'ed code you must use GPL.
> >
> OK. Thanks for the answer. One more question.
> If I'll create application using ZPsycopgDA which
> is ZPL (but ZPsycopgDA uses psycopg which is GPL),
> then does my application have to be GPL too?

I've seen infinite discussions about what a "mere aggregate" is and if
using a standard API from an interpreted programming language is linking
or not. psycopg2 is under the GPL so if you derive an adapter from it it
should be under the GPL too. If you derive from ZPsycopgDA or write an
app that used ZPysocpgDA you can go with anuthing compatible with the
ZPL. Being the copyright holder for both I can choose any combination of
licenses and by saying that ZPsycopgDA is (also) ZPL even if it calls
psycopg that is GPL-only I suppose I am making a clear statement about
what you can or can't do.

> Can I submit this issue somewhere (AFAIK there
> is no bugtracker at initd.org) or this e-mail
> is enough?

Please use our tracker at http://initd.org/tracker/psycopg.

Thank you very much,
federico

--
Federico Di Gregorio http://people.initd.org/fog
Debian GNU/Linux Developer fog [at] debian
INIT.D Developer fog [at] initd
Viviamo in un mondo reale, Ciccio. -- Lucy
Attachments: signature.asc (0.18 KB)


maciej.wisniowski at coig

Nov 22, 2006, 1:54 AM

Post #15 of 24 (6643 views)
Permalink
Re: [ANN] Modified version of DCOracle2 is available [In reply to]

> Being the copyright holder for both I can choose any combination of
> licenses and by saying that ZPsycopgDA is (also) ZPL even if it calls
> psycopg that is GPL-only I suppose I am making a clear statement about
> what you can or can't do.
>
Thank you. Now it is absolutely clear for me.
> Please use our tracker at http://initd.org/tracker/psycopg.
>
Hm... I've registered but when I wanted to submit a
ticket I get:
TICKET_CREATE privileges are required to perform
this operation

--
Maciej Wisniowski
_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


maciej.wisniowski at coig

Nov 22, 2006, 6:12 AM

Post #16 of 24 (6644 views)
Permalink
Re: [ANN] Modified version of DCOracle2 is available [In reply to]

> Eek! ZpsycopgDA is GPL'd! Does that mean you've put your version of
> DCOracle under GPL?
>
> But that aside I am not sure it is the best implementation of
> connection pooling out there.
I don't want to reinvent the whell and create own
pool management so I searched a bit and found
something interesting.
What do you think about SQLAlchemy? It has is nice (I think)
pool implementation, which may be used separately. See:
http://www.sqlalchemy.org/docs/pooling.myt

I'm especially interested in licensing.
SQLAlchemy is on MIT license, so I think if it will be
possible to use SQLAlchemy pool implementation then
then this DCOracle2 version may be ZPL. Am I right?


I did very first attempt to use this with my version of DCOracle2
and so far I am able to connect to Oracle from Zope and do
some queries with SQLAlchemy QueuePool in use.

If somebody wants to take a look then SQLAlchemized
version, with some code from ChrisW branch
(reconnect after connection is broken) is now at:
https://dcoracle2da.googlecode.com/svn/branches/sqlalchemypool

In general all this code is considered 'under developement'. There
is nothing in tags/ yet.

--
Maciej Wisniowski

_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


dieter at handshake

Nov 22, 2006, 10:45 AM

Post #17 of 24 (6639 views)
Permalink
Re: [ANN] Modified version of DCOracle2 is available [In reply to]

Maciej Wisniowski wrote at 2006-11-22 15:12 +0100:
> ...
>I'm especially interested in licensing.
>SQLAlchemy is on MIT license, so I think if it will be
>possible to use SQLAlchemy pool implementation then
>then this DCOracle2 version may be ZPL. Am I right?

I fear that there is a rule that everything in the Zope repositories
should be ZPL -- to have a uniform license for all components from
these repositories.

Of course, if your DCO2 is not in the Zope repositories,
you should be able to include MIT licensed code in a ZPL licensed
version of DCO2.



--
Dieter
_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


maciej.wisniowski at coig

Nov 22, 2006, 11:01 PM

Post #18 of 24 (6633 views)
Permalink
Re: [ANN] Modified version of DCOracle2 is available [In reply to]

> I fear that there is a rule that everything in the Zope repositories
> should be ZPL -- to have a uniform license for all components from
> these repositories.
>
> Of course, if your DCO2 is not in the Zope repositories,
> you should be able to include MIT licensed code in a ZPL licensed
> version of DCO2.
You are right but now I hope it is not necessary to put any
MIT licensed code from SQLAlchemy into DCOracle2. Simple
statement like:

from SQLAlchemy.pool import QueuePool

is enough. That just means that it is necessary to install
SQLAlchemy to use this version of DCOracle2, but there
are no parts of it's code here. Seems this is same as
need to have Oracle Client installed in system in order to
use DCOracle2.

--
Maciej Wisniowski

_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


Klaus.Happle at haufe

Nov 23, 2006, 9:59 AM

Post #19 of 24 (6653 views)
Permalink
AW: [ANN] Modified version of DCOracle2 is available [In reply to]

Hi

you remember my report

http://mail.zope.org/pipermail/zope/2005-August/160762.html

of an BUG for the handling of LONGs in DCO2?

The consequence of this BUG is stochastic results for LONG fields

We use an Fix of this BUG:

Hint: The Documentation of the OCI from Oracle say us:

defnp (IN), iter (IN), bufpp (OUT), alenpp (IN/OUT), piecep (IN/OUT), indpp (IN), rcodep (IN)

Caution: When working with callback parameters, it is important
to keep in mind what is meant by IN and OUT for the parameter
mode. Normally, in an OCI function, an IN parameter refers to data
being passed to Oracle, and an OUT parameter refers to data
coming back from Oracle. In the case of callbacks, this is reversed.
IN means data is coming from Oracle into the callback, and OUT
means data is coming out of the callback and going to Oracle.

Docu from OCI for OCIDefineByPos:
indp (IN/OUT), alenp (IN/OUT), rcodep (OUT): Ignored for dynamic binds.

First we fix Cursor_ResultSet and Second we fix Cursor_fetch:

First Fix:

static PyObject *Cursor_ResultSet(Cursor *self, int count) {
PyObject *list;
ResultSet *rs;
int status;
int i;
sword mode = OCI_DEFAULT;
dvoid *valuep;
ub4 width;
LongFetch *lf;

TRACE(T_ENTRY,("sAd", "Cursor_ResultSet", self, count));

if (self->definition == NULL) {
TRACE(T_ERROR,("ss","Cursor_ResultSet","description is NULL"));
PyErr_SetString(ProgrammingErrorObject,
"cursor description is None");
return NULL;
}

self->batchsz = count;

if ((list = Py_BuildValue("[]")) == NULL) {
TRACE(T_ERROR,("ss","Cursor_ResultSet",
"PyBuildValue returned NULL"));
return NULL;
}

for (i = 1; i <= PyList_Size(self->definition); i++) {
mode = OCI_DEFAULT;
if ((rs = (ResultSet *) ResultSet_alloc(self, i, count))
== NULL) {

Py_DECREF(list);
TRACE(T_ERROR,("ss","Cursor_ResultSet",
"ResultSetAlloc returned NULL"));
return NULL;
}

valuep = rs->valuep;
width = rs->width;
rs->fetchResultCode = OCI_SUCCESS;

if (self->flags & LONG_COLUMN && (char) i == self->longcol) {
mode = OCI_DYNAMIC_FETCH;
lf = (LongFetch *) rs->valuep;
longFetchInit(lf);
/*valuep = NULL;*/
width = 0x7FFFFFFF; /* Max unsigned long */
rs->indp = &lf->ind; //KMH, 2.8.2005 synchronisation of dynamicFetch with ResultSet
rs->rcodep = &lf->rcode; //KMH, 2.8.2005 synchronisation of dynamicFetch with ResultSet
}

TRACE(T_CALL,("sdAddd", "OCIDefineByPos", i, valuep, width,
rs->cdty, mode));

/* Now bind the result set */
/*
Docu from OCI:
indp (IN/OUT), alenp (IN/OUT), rcodep (OUT): Ignored for dynamic binds.
*/
status = OCIDefineByPos(self->stmtp, &(rs->defnp),
self->errhp, i, valuep, width,
rs->cdty, (dvoid *) rs->indp,
rs->rlenp, rs->rcodep, mode);

TRACE(T_RETURN,("sR", "OCIDefineByPos", status));

if (status != OCI_SUCCESS) {
Py_DECREF(rs);
Py_DECREF(list);
return RaiseOCIError(self->errhp, OCI_HTYPE_ERROR);
}

if (self->flags & LONG_COLUMN && (char) i == self->longcol) {

TRACE(T_CALL,("sA", "OCIDefineDynamic", rs->valuep));

status = OCIDefineDynamic(rs->defnp, self->errhp,
(dvoid *) rs->valuep,
(OCICallbackDefine) dynamicFetch);

TRACE(T_RETURN,("sR", "OCIDefineDynamic", status));

if (status != OCI_SUCCESS) {
Py_DECREF(rs);
Py_DECREF(list);
return RaiseOCIError(self->errhp,
OCI_HTYPE_ERROR);
}
}

PyList_Append(list, OBJECT(rs));
Py_DECREF(rs); /* Now that its in the list ... */
}

if (self->results != NULL) {
Py_DECREF(self->results);
}

self->results = list;
self->current = 0;

Py_INCREF(Py_None);

TRACE(T_EXIT,("s","Cursor_ResultSet"));

return Py_None;
}

Second Fix:

static PyObject *Cursor_fetch(Cursor *self, PyObject *args) {
int count = 1;
ub4 rcount;
int status;
ub2 *rlenp;
ResultSet *rs;
int i;
int j;
int resetlong = 0;
int releaseresults = 0;
PyObject *answer;

TRACE(T_ENTRY,("sAA", "Cursor_fetch", self, args));

/* bindObject may have bound this cursor, in which case we
** must refetch the description
*/
if (self->flags & CURSOR_INSPECT)
Cursor_getdesc(self);

if (!PyArg_ParseTuple(args, "|i", &count)) return NULL;

TRACE(T_ARGS,("sd", "Cursor_fetch", count));

if (count < 1 || count > 200) {
PyErr_SetString(PyExc_ValueError, "Count out of range");
return NULL;
}

if (self->flags & LONG_COLUMN) {
count = 1; /* Should we raise an error? */

if (self->current != -1)
resetlong = 1;
}


if (self->flags & NO_RESULT) {
TRACE((T_PROGRAM|T_ERROR),("ss","Cursor_fetch",
"no result set from execute"));
PyErr_SetObject(ProgrammingErrorObject,
Py_BuildValue("s",
"No results available from last execute operation"));
return NULL;
}

if (self->current == -1)
if (Cursor_ResultSet(self, count) == NULL) return NULL;

/* Set the result widths */

for (i = 0; i < PyList_Size(self->results); i++) {
if ((rs = (ResultSet *) PyList_GetItem(self->results, i))
== NULL) return NULL;

rlenp = rs->rlenp;
for (j = 0; j < rs->size; j++) {
*rlenp = (ub2) rs->width;
rlenp++;
}

if (resetlong == 1 && (i+1) == self->longcol) {
LongFetch *lf = (LongFetch *) rs->valuep;
longFetchRelease(lf);
longFetchInit(lf);
rs->indp = &lf->ind; //KMH, 2.8.2005 synchronisation of dynamicFetch with ResultSet
rs->rcodep = &lf->rcode; //KMH, 2.8.2005 synchronisation of dynamicFetch with ResultSet
}
}


TRACE(T_CALL,("sd", "OCIStmtFetch", count));

Py_BEGIN_ALLOW_THREADS

/*
** NB: A fetch of length 0 will cancel the cursor
**
** Do we *need* to cancel the cursor when the Cursor is
** deallocated or will Oracle figure it out when the statement
** handle is released?
*/

status = OCIStmtFetch(self->stmtp, self->errhp, count,
OCI_FETCH_NEXT, OCI_DEFAULT);

Py_END_ALLOW_THREADS

TRACE(T_RETURN,("sR", "OCIStmtFetch", status));

/*
** Copy the fetch status into each result
*/
if (status == OCI_SUCCESS_WITH_INFO) {
for (i = 0; i < PyList_Size(self->results); i++) {
rs = (ResultSet *) PyList_GetItem(self->results, i);
rs->fetchResultCode=status;
}
}
if (status == OCI_SUCCESS) {
for (i = 0; i < PyList_Size(self->results); i++) {
rs = (ResultSet *) PyList_GetItem(self->results, i);
rs->fetchResultCode=status;
}
}
TRACE(T_VERBOSE,("sd", "Cursor_fetch status", status));

if (status != OCI_SUCCESS && status != OCI_SUCCESS_WITH_INFO) {
text buff[OCI_ERROR_MAXMSG_SIZE];
sb4 errcode;

OCIErrorGet(self->errhp, 1, NULL, &errcode, buff,
sizeof(buff), OCI_HTYPE_ERROR);
/* Oracle errors meaning "end of fetch"
** 1403 is
** 1002 is fetch out of sequence
*/

TRACE(T_OERROR,("sdS","OCIStmtFetch",errcode,buff));

if (errcode != 1403 && errcode != 1002) {/* Not End of data */
Py_DECREF(self->results);
self->results = NULL;
self->current = -1;
return RaiseOCIError(self->errhp, OCI_HTYPE_ERROR);
}
releaseresults = 1;
}

TRACE(T_HCALL,("sAsAs", "OCIAttrGet", self->stmtp,
"OCI_HTYPE_STMT", &rcount, "OCI_ATTR_ROW_COUNT"));
status = OCIAttrGet((dvoid *) self->stmtp, OCI_HTYPE_STMT,
(ub4 *) &rcount, 0, OCI_ATTR_ROW_COUNT,
self->errhp);
TRACE(T_HRETURN,("sRd", "OCIAttrGet", status, rcount));

if (status == OCI_SUCCESS) {
if (self->batchct == -1) self->batchct = 0;
self->count = rcount - self->batchct;
self->batchct = rcount;
} else
return RaiseOCIError(self->errhp, OCI_HTYPE_ERROR);

answer = self->results;
if (!releaseresults)
Py_INCREF(answer);
else {
self->results = NULL; /* Our caller gets our only copy*/
self->current = -1;
}

TRACE(T_EXIT,("sA", "Cursor_fetch", answer));

return answer;

}


Klaus Happle


-----Ursprüngliche Nachricht-----
Von: zope-db-bounces [at] zope [mailto:zope-db-bounces [at] zope] Im Auftrag von Maciej Wisniowski
Gesendet: Freitag, 17. November 2006 17:32
An: Zope-DB [at] zope
Betreff: [Zope-DB] [ANN] Modified version of DCOracle2 is available


Hi

Due to discussion on Zope list and some work I did before
I've published modified version of DCOracle2.

What is in this version:
1. Bug fixes
- StoredProcedures caused deadlocks in database
- StoredProcedures caused conflict errors under heavy load
removed self._errors reference and added _p_resolveConflict
- other I don't remember now

2. Changes
Changes are only in python files
- DCOracle2 now uses connection pool
Pool implementation is based on psycopg but
it doesn't use volatile attributes.

As a result:
- Open/Close button in ZMI works as it should
- DCOracle is possibly slower a bit

- Ability to reconnect after the connection is broken
When Oracle is restarted, connection is closed by firewall
etc. Zope will show an error but only once for every broken
connection. Second request will cause DCOracle2 to reconnect

3. To do:
- Possibly useage of raise ConflictError may cause that there
will be no errors shown during reconnect

- Pool size attribute may be set via ZMI

- Testing...

This code is now in use at my company, but not yet in production
environment. So far this works with Zope 2.8.x and Oracle9.

If somebody knows better place for this code then let me know.
Any ideas how should file headers (I mean licences) look?
Comments are welcome

Code is here:

http://code.google.com/p/dcoracle2da/

Should be available via svn with:

svn checkout http://dcoracle2da.googlecode.com/svn/trunk/ dcoracle2da

THERE IS NO GUARANTEE THAT THIS WILL WORK FOR YOU, SO BE CAREFULL :)

--
Maciej Wisniowski

_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db
_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


maciej.wisniowski at coig

Nov 24, 2006, 1:22 AM

Post #20 of 24 (6606 views)
Permalink
Re: AW: [ANN] Modified version of DCOracle2 is available [In reply to]

> you remember my report
>
> http://mail.zope.org/pipermail/zope/2005-August/160762.html
>
> of an BUG for the handling of LONGs in DCO2?
>
In fact I forgot about this...

> The consequence of this BUG is stochastic results for LONG fields
>
> We use an Fix of this BUG:
>
I've put your code into dco2.c. Thank you very much for
these fixes.

--
Maciej Wisniowski
_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


mmhamze at pleiades

Dec 4, 2006, 9:20 AM

Post #21 of 24 (6432 views)
Permalink
Re: [ANN] Modified version of DCOracle2 is available [In reply to]

Maceij -
I still do not understand how to get hold of the new dco2.c file (along
with other files in the DCOracle2 package).
You state right from the start:
******************
Code is here:

http://code.google.com/p/dcoracle2da/

Should be available via svn with:

svn checkout http://dcoracle2da.googlecode.com/svn/trunk/ dcoracle2da
******************

Can someone give clear instructions on how to get these files?

On a different note:
The 'official' distribution of DCOracle2 (from zope.org) compiled fine
with Oracle 9i on Solaris. However, with Oracle 10g on Solaris, I ran
into a compilation error and I had to add the line:

typedef unsigned int dword;

I did not have the time to investigate the reason that this happened
with Oracle 10g.
Thanks,
Maan


>> you remember my report
>>
>> http://mail.zope.org/pipermail/zope/2005-August/160762.html
>
>> of an BUG for the handling of LONGs in DCO2?
>>
>>In fact I forgot about this...

>> The consequence of this BUG is stochastic results for LONG fields
>>
>> We use an Fix of this BUG:
>>

>I've put your code [Happle Dr., Klaus Martin] into dco2.c. Thank you
very much >for these fixes.

--
Maciej Wisniowski


_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


maciej.wisniowski at coig

Dec 4, 2006, 9:56 AM

Post #22 of 24 (6432 views)
Permalink
Re: [ANN] Modified version of DCOracle2 is available [In reply to]

> I still do not understand how to get hold of the new dco2.c file (along
> with other files in the DCOracle2 package).
> You state right from the start:
> ******************
> Code is here:
>
> http://code.google.com/p/dcoracle2da/
>
> Should be available via svn with:
>
> svn checkout http://dcoracle2da.googlecode.com/svn/trunk/ dcoracle2da
> ******************
>
> Can someone give clear instructions on how to get these files?
To get files you need to have Subversion (SVN) client installed on
your machine. On modern linux you should already have it, otherwise
it should be availaible from system repositories. Home page is
http://subverstion.tigris.org
To check if you have Subversion type in the console:

svn


If you have SVN client then simply type:

"svn checkout
https://dcoracle2da.googlecode.com/svn/tags/1.0RC1 DCOracle2"

This will get files from googlecode into folder named 'DCOracle2'.


The addres above is to most current version I have although there may
still be some errors. This version uses SQLAlchemy pool so if you want
to use this with Zope you'll need to install SQLAlchemy from
sqlalchemy.org too. See INSTALL file.

Currently I had not much time to do something with this project,
but I'll try to make it cleaner soon, write some tests (so far I was
not able to run original tests...) and to put this into official Zope
repositories.


> On a different note:
> The 'official' distribution of DCOracle2 (from zope.org) compiled fine
> with Oracle 9i on Solaris. However, with Oracle 10g on Solaris, I ran
> into a compilation error and I had to add the line:
>
> typedef unsigned int dword;
>
> I did not have the time to investigate the reason that this happened
> with Oracle 10g.
Thanks for this info. We have this DCOracle2 compiled with Oracle10 on
RedHatEnterprise 4. Unfortunatelly I have no Solaris. But would be nice
if you can check dco2.c from this version on Solaris machine. At last
I'll add a note about this Solaris issue into INSTALL file.

--
Maciej Wisniowski


_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


mmhamze at pleiades

Dec 4, 2006, 10:23 AM

Post #23 of 24 (6426 views)
Permalink
RE: [ANN] Modified version of DCOracle2 is available [In reply to]

Thanks Maceij for the information -

>>Currently I had not much time to do something with this project,
>>but I'll try to make it cleaner soon, write some tests (so far I was
>>not able to run original tests...) and to put this into official Zope
>>repositories.

I use DCOracle2 with Sun Solaris and Windows XP for direct Oracle
connectivity. I do not use Zope, so I only use the dco2.pyd part only.
The original author of this package was wise enough to keep dco2.pyd
completely separate from the Zope pieces.
I know that dco2.c for Windows is slightly different from the Solaris
one.
dco2.c for Windows adds the line:

__declspec (dllexport)

right at the start of Module initialization section.
I mention that to turn attention to the need of trying to keep
descriptions clear and the target platform of a package, and to keep
divergences at a minimum.
I wonder if simple IFDEF's can do the trick.


> On a different note:
> The 'official' distribution of DCOracle2 (from zope.org) compiled fine
> with Oracle 9i on Solaris. However, with Oracle 10g on Solaris, I ran
> into a compilation error and I had to add the line:
>
> typedef unsigned int dword;
>
> I did not have the time to investigate the reason that this happened
> with Oracle 10g.

>>Thanks for this info. We have this DCOracle2 compiled with Oracle10 on
>>RedHatEnterprise 4. Unfortunatelly I have no Solaris. But would be
nice
>>if you can check dco2.c from this version on Solaris machine. At last
>>I'll add a note about this Solaris issue into INSTALL file.

It will be better to investigate further as to what is causing this
error related to dword to appear on Solaris with Oracle 10g but not
Oracle 9i. Maybe a note in INSTALL file turning attention to this issue
in case it arises would be fine.
By the way, this line:
typedef unsigned int dword;

goes on line 187 right before the line:
#define NATIVENUMERIC 1

Maan

_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db


maciej.wisniowski at coig

Dec 11, 2006, 1:14 PM

Post #24 of 24 (6326 views)
Permalink
Re: [ANN] Modified version of DCOracle2 is available [In reply to]

Just a little information about project status.

I did some more work with pooling. I've implemented SQLAlchemy pooling
into DCOracle2 and it works nice, although I'm thinking about
own pooling.

Details are in CHANGELOG. In short:

- DCOracle2 now uses connections pool from SQLAlchemy (in fact it is a
pool of resource managers)

- DCOracle2 now reconnects after database failure (ZPublisher Retries -
based on ChrisW code)

- Stored procedures are simplified, fixed and they're no longer resource
managers itself

- path to DCOracle2 (dco2.p from Klaus Martin Happle) included

- docs updated a bit

- DCOracle2 is not using volatile attributes at all

Issues:
DCOracle2 is not working on x86_64 architecture (causes segfaults).
I'm looking for solution but it is possibly necessary to dig into C
code so any help apreciatted here

Still no new tests but I made a progress and at last executed
present test cases :)

Current version is available from subversion repository:
svn checkout
https://dcoracle2da.googlecode.com/svn/tags/1.0RC2 DCOracle2


I've also put a little fix in handling DateTimes
lower than 1901 (ugly thing)...

I'm also wondering about possibility to use REQUEST object to store
connection object taken from pool thorough the request. I mean something
like:
1. get connection from the pool and mark as used
2. bind connection into REQUEST object (maybe just weakref)
3. every subsequent call will get object form request instead
of creating a thread lock etc.

Any thoughts about DateTime and/or REQUEST?


BTW. Possibly I'll be able to commit the code into Zope repositories
soon.

--
Maciej Wisniowski
_______________________________________________
Zope-DB mailing list
Zope-DB [at] zope
http://mail.zope.org/mailman/listinfo/zope-db

Zope db RSS feed   Index | Next | Previous | View Threaded
 
 


Interested in having your list archived? Contact Gossamer Threads
 
  Web Applications & Managed Hosting Powered by Gossamer Threads Inc.