It has been a long time since my last post. Currently I'm very busy on a big project...
Yesterday we had a nice problem. A company I work is using the following configuration on it's webserver: IIS, PHP and MSSQL. (yes an S). What's wrong with this configuration? Well two things: IIS and MSSQL.
That's not all: in PHP we connect to the database via 2 methods: via the ADODB COM object and the normal mssql functions from PHP.
We had the following problem in a certain module:
Every time we executed a Second query, the second query failed but we NEVER got an error message. Example:
(pseudo code:)
BEGIN TRANSACTION
SELECT * FROM mytable WHERE kind='item1'
... more code ...
SELECT * FROM mytable WHERE kind='item2' // <= This query failed every time, without an error message
... more code 2 ...
COMMIT TRANSACTION
Looks very strange, but after too many hours we found our problem:
(pseudo code:)
BEGIN TRANSACTION
... code 1 ...
SELECT * FROM mytable WHERE kind='item1'
...
INSERT .. INTO mytable( ... ) VALUES ( ... )
..
SELECT * FROM mytable WHERE kind='item2' // <= This query failed every time, without an error message
... code 3 ...
COMMIT TRANSACTION
What's the problem:
BEGIN TRANSACTION # is executed by ADODB
SELECT * FROM ... # is executed by mssql_* functions from PHP
INSERT ... # is executed by ADODB
SELECT * FROM # is executed by mssql_* functions from PHP
..
Well when retrieving the data again the mssql_* connection fails because it notices the data has changed and it must wait for the other transaction to finish...
Aaaaaargh.... 8 hours of my time wasted!
Btw. A few year ago we had a good reason forusing ADODB in PHP: the mssql version of PHP wasn't realiable. And we are using "queries from hell" ( sql server XML queries ), which (at that time) couldn't be retrieve by the mssql functions of PHP.