[Zope] SLQ-connection Pooling

[Bo M. Maryniuck]

On Thursday 08 August 2002 12:35, Dario Lopez-K=C3=A4sten wrote:
> I can't be bothered to write a proper product just yet. Besides, I want=
 to
> read up on the source of the DA, zSQL, RDB stuff in Zope first, and I j=
ust
> don't have the time.

It will not help you, if you want ZSQLMethod. Because you need bindarray =
which=20
is can not be available in ZSQLMethod. Most fun is to call IN/OUT overloa=
ded=20
procedures: without the bindarray you are outside of all and Python will=20
never recognize which the procedure to call as it "should":

# This always raise Oracle error:
data =3D conn.oraclePackage.overloadedProcedure([parameters])

> Not sure what you mean here by not good solution here... The query is
> prepared in a PythonScript. It is then sent by the PythonScript to an
> External Method that connects as the given user, prepares the query,
> executes it, collects the result disconnects and sends back a result se=
t to
> the PythonScript, which then hands it back to the rest of Zope.


> All this *should* in theory work pretty well, and (I hope) still work
> within the transaction.
Yes, here you always in transaction, but only at that connect time, AFAIK=
=2E

> The effects we are seing is that queries execute and return their resul=
t
> set within 1-3 seconds instead of having to wait 30-130 seconds for the
> result set.
Well, I see the potential problem can be one of the following:
 - Full scan of the DB? You should NOT download (and fetch!) entire table=
=2E
 - Slow connection time?
 - Slow ExternalMethod call. I use ExternalMethod for just dummy-and-smal=
l=20
tests, but in production I _never_ use it. *AFAIK* ExternalMethod loads o=
nce=20
per call (or please correct me somebody here if I fail!). I mean, your=20
ExternalMethod will load DCOracle2 driver per each call. This is might be=
 the=20
main problem why you should wait so long... But I'm not sure about=20
ExternalMethod mechanism (I've never looked over its sources, etc).

> This still doesn't answer the actual question.=20
There is probably one _good_ solution: use Zope Product way to make all i=
n=20
one. But for _simple_ projects, where you should just fetch the data and=20
update it, you only need ZSQLMethod, nothing more.

HINT: Do everything or mostly all in that fastest unbreakable Oracle. :-)=
 Tell=20
to Oracle do *all* the nasty job: find the overloaded procedures, calcula=
te=20
data, call stored procedures, convert types etc. For example there is big=
=20
trouble with NUMBER: Python will return you four binary bytes. To solve t=
his,=20
just convert it to VARCHAR2 or so in the Oracle side.

> Are SQL connections pooled
> in Zope or not, i.e. if I have a connection object that is open, does t=
hat
> mean that that connection is pooled?
What you meen "pooled"? You have just OPENED connection object. "Pooled" =
is=20
rather several opened connections...

--=20
Sincerely yours, Bogdan M. Maryniuck

"Are [Linux users] lemmings collectively jumping off of the cliff of
reliable, well-engineered commercial software?"
(By Matt Welsh)