php5 orcale9i connection problems

dhills

Good morning

I am experiencing an intermittent connection problem
When connecting to oracle using $oci_new_connect.

It appears that if two connections use the same username
And servicename they can interfere with each other.

I get a range of errors including OCISessionBegin: ORA-03127:
no new operations allowed until the active operation ends

and OCIFetch: ORA-01001: invalid cursor.

The problem is intermittent, the query will run
With out problem if there is only one user connected . The data is fine no
Clobs or binary stuff, just varchar2(20).

I am not using persistent connections and use the
$oci_new_connect each time. And close connection asap.

To get around this I have set up 10 different service names and
Would like to use them sequentially.

I would like to store the last used connection servicename as a
Globally available Environment variable on the windows server
Running apache.(or just a counter counting up to 10)

I can then read and increment the variable with each new connection.

Any idea’s on the best way to achieve this.

Thanks

David

Sxooter

Is this two connections in the same script, or different scripts?

dhills

Good Morning

It definately occurs when the same
script is run by different web users. (same orcale username
and service name)

I'm not 100% sure if different scripts interfere with each
other but i Dont think so.

Thanks

David

Sxooter

Wow, that does sound like a pretty nasty problem. If it's a bug int he PHP/OCI code it really needs to be fixed, not just worked around.

On the other hand, you've got stuff to get done too.

Unfortunately, PHP doesn't really lend itself to things like system wide global vars. I'd recommend using sqllite to store the small data set you'll need and pulling it / updating the counter from / to there.

dhills

Thanks for the reply.

I could not see any easy way of using a global
server side variable from php . ( I tried a few different
thing like using a COM object that used the winapi
to right the local variable, but it would not work)

I'll have a look a sqllite , I can not use oracle
(maybe I could if i used a ODBC or PDO connection
instead of php's OCI_)I'd be in a chicken and the egg
stiuation.

Thanks

David

Sxooter

Another possibility is to use a file in a temp directory and flock when accessing it. It can be a slight bottle neck, but not much, since the accesses should be short.

dhills

Yeh I've considered this.

open the file with write access, if it wont let you,
wait 0.1 second and try again.

Is this flocking!!!!!!!!!!!!!

thanks

David

Sxooter

Take a look here:

http://us3.php.net/manual/en/function.flock.php

dhills

thanks

I thought flocking was an abstract concept
not an actual function